How to upload files and store them in a server local path when MS SQL SERVER allows remote connections?

Question

I am developing a win32 windows application with Delphi and MS SQL Server. it works fine in LAN but I am trying to add the 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 DB where I keep the DocumentID, the document description and the Document path (like \\FILESERVER\MyApplicationDocuments\45.zip).

Of course \\FILESERVER is a local (LAN) path for the server but not for the client (as I am now trying to add the support for remote connections).

So I need a way to access \\FILESERVER even if of course I cannot see it in LAN.

I found the following T-SQL code snippet that is perfect for the "download trick":

SELECT BulkColumn as MyFile FROM OPENROWSET(BULK '\FILESERVER\MyApplicationDocuments\45.zip' , SINGLE_BLOB) AS X

With the code above I can download a file on the client.

But how to upload it? I need an "Uppload trick" to be able to insert new files, but also to delete or replace existing files.

Can anyone suggest? If a trick is not available could you suggest an alternative? Like an extended stored procedure or calling some .net assembly from the server.

Answer 1

In ideal world I would create simple: - ASP.NET Web Service - or .Net Remoting Service (faster than web service) - or new .Net 4.0 RIA service.

Deploy it to the SQL Server on custom TCP/IP port

This service would listen to the port and client would request the file via the service. The service would get the file via local LAN and communicate with the DB via local OLE DB connection.

I would not use any SQl Server "web service" support - this is security and performance issues.

UPDATE: Since this is Delphi app - you can do the same using Delphi, even though above solution still valid, but more work to integrate different technologies. Delphi has its own tools to build remote applications

Answer 2

If you have sql 2008, then you can use FILESTREAM, then sql server will automatically throw it out to disk.

If you have sql 2005, I'd consider just moving the data into a varbinary(max) column and deal with it that way (also pretty simple).

If neither of those apply OR you can't shove it into a varbinary column, then I would not use sql server to handle the actual file contents and instead just have a web service which stored the file on the file system or a SAN that the web service can easily access. (same as IMHO)

UPDATE: One other idea that crossed my mind. If you are using SQL 2005/08 then you can write a CLR Stored procedure in .Net. This could handle transferring the blob data to / from the local file system.

Answer 3

If you are on 2005, you could try to store file in temp blob field of some temp table, and then call stored procedure which should put the file where you want it, and update path field as you want it.

In that stored procedure you must use extended stored procedures (xp_something), which allow access to file system. That means that those should be enabled for sql server.

BTW You are trying to use relational DB as Document database. That will, sooner or later, backfire.