Using SQL to copy local file to Intranet server

We are thinking of changing the setup of our intranet to use SQL Server to do the actual processing of copying a local file selected by the user to a network share and saving the link in a database. The files may be Word, Excel, Powerpoint, Acrobat files up to 6 mB in size. Other users would retrieve this documents from their local PC. I have no idea how to approach this, can anyone give me some pointers?
Georgina

I also have been wanting to do something similar. When a SP is run I want it to copy a file to a location on another server on our internal network.
Even after changing the SQL process to run as a particular user instead of the system account it still does not seem to work.

Any help in copying files to a network location is greatly appreciated.
Joshua

You can do that kind of coding in stored procedures, if that's the route you want to take.

So do you have an example of how to do the file copy with in a Stored procedure then?
Thanks,
Joshua

Here's one reference to using a COM object from a SP

http://www.sqlteam.com/item.asp?ItemID=322

Unfortunately this will not work. Even if you create an object, it is still created under the SQL System process. The problem I am having is that SQL does not see network locations.

If you are connecting from one windows machine to another than this will work just fine but when you are connecting to a non-windows machine there is no easy way to match the SQL user with a remote user account to allow SQL to see the remote server.

I tried using "net use" to map a drive under the SQL Process and this works well to copy to any windows based location but not to non windows machine.

Any other ideas?

Sounds like you may need to setup the account that SQL Server runs under (When you install SQL Server, you're given the option of using Local System or a Windows User account for this) in the other system with no password, so that it can map the drives correctly? There may be security implications about allowing a mapping with no password though, unless you restrict this to a specific IP address.

yes, this works like this but I am not sure how to go about restricting the IP Address on our internal network...

Depends on what you're using to map a drive. If you're using samba on the other server, note that the smb.conf file has a hosts allow= directive where you can specify the IP addresses that are allowed to connect.