How to download files and store them in the local server path when MS SQL SERVER allows remote connections?
I am developing a windows win32 application with Delphi and MS SQL Server. it works fine on local network, but I'm trying to add support for SQL Server remote connections (= working with a DB that can be accessed with an external IP as described in this article: http://support.microsoft.com/default .aspx? scid = kb; EN-US; 914277 ).
Basically I have a table in the DB where I store the DocumentID, document description and document path (for example \\FILESERVER\MyApplicationDocuments\45.zip
).
Of course, \\FILESERVER
this is the local (LAN) path for the server, but not for the client (since I'm now trying to add support for remote connections).
So, I need a way to access \\FILESERVER
, even if of course I can't see it on the local network.
I found the following T-SQL snippet that is perfect for the "loading trick":
SELECT BulkColumn as MyFile FROM OPENROWSET (BULK '\ FILESERVER \ MyApplicationDocuments \ 45.zip, SINGLE_BLOB) AS X
With the above code, I can download the file on the client.
But how do you download it? I need an "Uppload trick" to be able to insert new files and delete or replace existing files.
Can anyone suggest? If a trick is not available, can you suggest an alternative? As an extended stored procedure or calling some .net assembly from the server.
a source to share
If you have sql 2008 you can use FILESTREAM then sql server will flush it to disk automatically.
If you have sql 2005 I would just move the data into a varbinary (max) column and deal with it that way (also pretty simple).
If none of these apply OR you cannot insert it into a varbinary column, then I would not use sql server to process the actual contents of the file, but instead just have a web service that stored the file on the file system or SAN, to which the web service can easily access. (just like IMHO)
UPDATE : Another idea that crossed my mind. If you are using SQL 2005/08 you can write CLR Stored Procedure in .Net . This can result in blob data being migrated to / from the local filesystem.
a source to share
In an ideal world, I would create a simple one: - ASP.NET Web Service - or .Net Remoting Service (faster than web service) - or a new .Net 4.0 RIA service.
Deploy it to SQL Server on a custom TCP / IP port
This service will listen on the port and the client will request a file through the service. The service will receive the file over the local local area network and communicate with the database through the local OLE DB connection.
I would not use the SQl Server web service support - these are security and performance concerns.
UPDATE: Since this is a Delphi application, you can do the same with Delphi, although this solution is still valid, there is more work to integrate different technologies. Delphi has its own tools for building remote applications.
a source to share
If you are in 2005 you can try to store the file in the temp blob field of some temporary table and then call the stored procedure which should put the file where you want and update the path field as you wish.
In this stored procedure, you must use extended stored procedures (xp_something) that allow access to the file system. This means they must be enabled for the sql server.
BTW You are trying to use a relational database as your Document database. This will sooner or later lead to the opposite consequences.
a source to share