I have a text file sitting on client machine and want to move it to the database server (MS SQL 2008) but I don't have any access to the server except through the SQL Server client. Can I transfer this file to the server using SQL client connection?
A:
Do you want to put it in the database, or in the filesystem? If the former, consider a text or varchar(max).
If the latter, use SQL injection and xp_cmdshell as needed. :) Actually, in this case you should ask the admin for a more appropriate transfer mechanism.
Matthew Flaschen
2010-05-20 04:00:05
is xp_cmdshell better then writing extended stored procedure?
wasim
2010-05-20 04:04:55
+2
A:
Yes you can do it but not through standard SQL. You will have to write an extended stored procedure that will alow you to connect through sql client connection and to access server file system. But you will have to address a lot of user privileges issues.
Leslie Norman
2010-05-20 04:00:13
is extended stored procedure better then using xp_cmdshell that Mathew suggested?
wasim
2010-05-20 04:05:49
xp_cmdShell has limitations of at most 8000 chars while you have better control on extended/CLR procedure
Leslie Norman
2010-05-20 04:10:02
I've never used it but saw it recommended around here: FILESTREAM - http://technet.microsoft.com/en-us/library/bb933993.aspx
IMHO
2010-05-20 18:06:20
A:
If you want to save the file into the database then is a no brainer.
If you want to save into the file system the use a CLR stored procedure marked as having EXTERNAL_ACCESS. You can pass a BLOB parameter to the procedure and the procedure in turn can write the BLOB content to the disk, using ordinary FileStream operations. If the file is very large then special care needs to be taken to prevent memory bloat.
Remus Rusanu
2010-05-20 04:05:19