File transfer through SQL Server connection

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?开发者_开发知识库

---

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.

---

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.

---

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.