August 31, 2007 at 9:50 am
I know that this is probably not how linked servers are meant to be used, but this is an application that I inherited. The application is written in ASP.NET 1.1 using SQL Server 2000. I need to give the user the ability to upload a Microsoft Access like database file that is then interrogated and it's pertinent data is dumped into SQL Server. Currently, when the user uploads the file is saved to the hard disk. They have a linked server set up to use that file. Well, actually, the application tries to save the user's file as a particular name. If the file is in use, the execution of the save code is deferred for 30 seconds and tries again to save as that name. When the save has been completed successfully then the stored procedure that reads the file is executed.
This works fine and good when you have only one or two people accessing the system, but this is a web application, so concurrency is definitely a possibility. Currently, traffic is very low, and the probability of two concurrent users using the same part of the application is pretty low. However, one of the design specifications is to allow a least six simultaneous users to be able to perform the above operation successfully.
I was just wondering if there would be a better way somehow. I tried to take the logic out of the stored procedure and do it all in my server-side ASP code, but the concurrency problem became much much worse throwing exceptions all over everywhere saying that a connection has already been open for the data reader, or columns were not found during my dynamic page population after the initial process was complete and the user was redirected to a summary screen. Any ideas...
Jim
August 31, 2007 at 10:06 am
Yikes. Have you considered switching from the linked servers to a OPENDATASOURCE or OPENROWSET syntax? It's functionally the same as building a throwaway, one-time linked server. That way - you can control where the database is stored (it no longer needs to be a "fixed" filename, so you don't have concurrency issues at that level).
Example pulled right out of BOL/MSDN:
USE Northwind
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO
If you were using .NET 2.0 - I'd advise you look into building a console app that would run something like a DTS package from the individual machine, which would then get rolled-out using a click-once app. But you're in 1.1, so click-once doesn't even exist....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 31, 2007 at 10:13 am
It originally started with using those constructs, but they receive even more obscure errors like "Microsoft.Jet.OLEDB.4.0 reported an error..." But a linked server route fixed that. I wish we were using SQL Server 2005, that way I could use a CLR style stored procedure and be done with it.
August 31, 2007 at 11:09 am
well - you could always "split the difference", and split the work into two separate pieces (perhaps - don't know what exactly you're doing). Such as - get the ASP.NET app to simply upload the file and put it somewhere on the server. At that point - ASP.NET is "done", and something else (like a console app, or a Windows Service) watches for incoming files to process. Not as conceptually elegant, but sometimes a whole lot more reliable (and you don't have to give the ASP.NET service account a whole bunch of special permissions, worry about keeping a page from timing out while all of this processing happens, etc....)
By doing it that way - you could STILL use a DTS package (which would take a parameter as to the name of the file to use). DTS is specifically built to deal with this kind of stuff - so I find it more reliable than any of the other methods.
Of course - this kind of shoots the old "immediate feedback" concept, and would then usually mean you would have to re-engineer this thing. If the concurrency/immediate feedback is something important - then I don't know what to tell you that would improve your existing app.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply