We help IT Professionals succeed at work.

BCP export to remote computer

Hi experts,

How to export files to remote computer using SQL server utility BCP. Do I have to create new login account on remote computer and what should be the permissons of the folder on remote computer.

Any help will be appeciated.
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT
Commented:
Hi,

Is the server remote from the destination computer?

I'd BCP to a local drive, then use a batch process to copy the file to the remote server. In that batch process shares and logins and normal file permissions apply.

It is likely that you would be running this from a SQL Agent job, so the account to use is the SQL Agent proxy account.

HTH
  David

Author

Commented:
Yes, the destination server is remote.

This is what I am trying....I have a c#.net windows application which calls a SQL server stored procedure....which holds the BCP export code. BCP to local drive works by executing the SP...but I want to export the BCP files to remote computer rather than local drive.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

There have been threads here about SQL's ability or not to work to mapped and unc network drives, particularly in backups.

Try using a unc path.

Else at the top of the procedure that does the bcp doa xp_cmdshell net use ... to map the network drive. Be aware that that might fail if the drive mapping already exists.

HTH
  David

Author

Commented:
I tried adding UNC path to BCP command but it fails....Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

This fails...'bcp "tbl" out "\\computername\ExportData\test.txt" -c -q'  but if I change it to local drive it works...'bcp "tbl" out "C:\ExportData\test.txt" -c -q' .
The SQL server is running under "Local system " service account. What kind of permissions I need to give to the network shared folder?

If possible, can you provide a example on using net use to map the network drive.

thanks
 

Senior Database Administrator
CERTIFIED EXPERT
Commented:
Hi,

The command net use is a commandline

net use t: \\servername\sharename

so from inside SQL it is
exec master..xp_cmdshell 'net use t: \\servername\sharename'

HTH
  David
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
PS net use /?
at the OS command prompt will get some syntax help