Saving a file from a client to the server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Saving a file from a client to the server

Author

Topic

brace77
Starting Member

25 Posts

Posted - 2010-04-23 : 10:30:04

I am trying to add to my client server application the donwload + upload file feature.

Basically I have a Documents folder at server side and I want (using SQL Server) to access those files in a easy way. I use SQL Server in OpenIP (exposing SQL Server on the internet), so unless I create a VPN I have no way to do access the remote folder.

I found a way to donwload the file by using this:

SELECT BulkColumn as MyFile FROM OPENROWSET(BULK '\\FILESERVER\SharedFolder\hello.txt' , SINGLE_BLOB) AS X

where FILESERVER is a fileserver in LAN with the SQLSERVER server and SharedFolder is a folder accessible from the SQLSERVER server.

So in my client application I can retrieve as a dataset field and save it to a document.

I write in Delphi so it looks like this:

TBlobField(MyDataSet.FieldByName('MyFile')).SaveToFile('D:\Temp\hello.txt');

Now this works, but could you please comment on this technique?

Moreover I need to do the opposite: SAVING TO THE REMOTE FOLDER, so I want to save the hello.txt I have in my local D drive to the remote fileserver.

How can this be achieved easily?

Can you in case me tell me another technique?

brace77
Starting Member

25 Posts

Posted - 2010-04-26 : 10:58:06

Could anyone help me by replying? Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 03:40:44

Have you tried this?

TBlobField(MyDataSet.FieldByName('MyFile')).SaveToFile('\\FILESERVER\SharedFolder\hello.txt');

Madhivanan

Failing to plan is Planning to fail

brace77
Starting Member

25 Posts

Posted - 2010-04-27 : 04:57:24

TBlobField(MyDataSet.FieldByName('MyFile')).SaveToFile('\\FILESERVER\SharedFolder\hello.txt');

No this cannot work, because \\FILESERVER\ is a LAN path for the server where sql server is running but it is not a path for the client.

Let me better explain the scenario with an example:

SQL Server is installed in a server (let's call it SQLSERVER) in London (UK), FILESERVER is another server in the same server room in London.
SQLSERVER and FILESERVER are in the same windows domain, physically connected with a LAN CABLE, so they see eachother as \\SQLSERVER and \\FILESERVER.

Now I am connecting to SQLSERVER with my application from Los Angeles (USA) WITHOUT USING A VPN, because SQLSERVER has been setup to accept remote connections as described in this article

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

My application is made like this:

a table in sql server contains the list of documents and a reference to their physical storage (\\FILESERVER). So everytime I READ a document I read a record in the documents table and I open a file located in FILESERVER. When I need to WRITE (=INSERT/EDIT/DELETE) a document I UPDATE/INSERT/DROP a record in the documents table and REPLACE/ADD/DELETE a file in FILESERVER.

Now since SQLSERVER is exposed on the internet I can add rows to the table since I can connect the database, but I cannot easily access the files in FILESERVER. Basically I need a "download trick" and an "upload trick" to access FILESERVER in read mode (download) and write mode (upload).

AS i posted when I created this thread I already found the "download trick":

SELECT BulkColumn as MyFile FROM OPENROWSET(BULK '\\FILESERVER\SharedFolder\hello.txt' , SINGLE_BLOB) AS X

what am I asking is the "upload trick". Can anyone suggest?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 05:06:22

If you want to write data to text file, read about bcp in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

brace77
Starting Member

25 Posts

Posted - 2010-04-27 : 10:43:36

Thanks. Good link.

I was able to perform the desired operation.

I created a test database (TestDocs) containing a table (FILETEST) with two fields (ID int and FILEDATA varbinary(max)). I used this table to create a format file that then I twaeked as suggested by this article: http://madhuottapalam.blogspot.com/2008/08/creating-files-from-images-stored-in.html

I first had to enable xp_cmdshell by doing

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

Then I created the following format file:

10.0
1
1       SQLBINARY           0       0       ""   1     FILEDATA             ""

Finally I was able to upload data (the data contained in the first record, this is why I wrote ID = 1) using:

declare @SQLcommand nvarchar(4000);
set @SQLcommand = 'bcp "select FILEDATA From TestDocs.dbo.FILETEST where ID = 1"  queryout "C:\Temp\Export\MyTest.txt"  -S ' + 'SERVERNAME' + ' -U sa -P PASSWORD -f C:\Temp\PP.fmt'
exec xp_cmdshell @SQLcommand--, NO_OUTPUT

This works anyway I have 2 problems:

1) C:\Temp\Export\ is a local path on the remote server, but in my case i want to access \\FILESERVER, that is a network path for the remote server. How this is possible? (the folder is shared to everyone)
2) in my example I had to create a DB table to store temporarly the file. Is this a way to avoid this (even avoiding temp tables or table datatype variables)? I mean having something like a "select BulkColumn..." query that uses a path on the local machine, not on the remote server.

Just to explain me better on the last point, I mean is there a way to do bcp "select BulkColumn..." queryout ... so I don't need to have to explicitly manage a table that stores temporarly the data?

Thanks

brace77
Starting Member

25 Posts

Posted - 2010-04-29 : 06:40:57

As far as i understand the only way to perform the upload trick is to first save the file in a temporary table with a varbinary(max) field and then use bcp to copy it outside sql server. Is there not a way to copy it directly in another way? I mean without storing in a temporary table?

brace77
Starting Member

25 Posts

Posted - 2010-04-30 : 06:19:53

I found the solution to point (1): I had to change the owner of the SQL Server service, I set it as a user that can access the shared folder and this works.

Now can anywano help me close the loop by solving point (2)? Is it possible? Or the only solution is store data in a temp table first?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources