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.
groovbox303Asked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
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
0
 
David ToddConnect With a Mentor Senior DBACommented:
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
0
 
groovbox303Author 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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
David ToddSenior DBACommented:
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
0
 
groovbox303Author 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
 

0
 
David ToddSenior DBACommented:
PS net use /?
at the OS command prompt will get some syntax help
0
All Courses

From novice to tech pro — start learning today.