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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior 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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
PS net use /?
at the OS command prompt will get some syntax help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.