BCP Remote Files

Question

BCP Remote Files

Angel DBA Mex

Ten Centuries

Points: 1085
More actions
August 7, 2008 at 3:46 pm

#192154

Hello to everyone, I try to explain my problem, i need with BCP utility put a file in remote machine, I writed this script exec master..xp_cmdshell 'bcp sysobjects out \\machine\direcoty\out.txt -T -w', allways this send a error, I've been searching information but I don't find some, only to reverse, put file in local path since a remote server, we have a sql server 2000 SP4 with windows server 2003 SP2, thank for you help
Angel Miranda Nieto
DBA Specialist, BI, MCSA SQL Server

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

Angel Miranda Nieto (8/7/2008)
Hello to everyone, I try to explain my problem, i need with BCP utility put a file in remote machine, I writed this script exec master..xp_cmdshell 'bcp sysobjects out \\machine\direcoty\out.txt -T -w', allways this send a error, I've been searching information but I don't find some, only to reverse, put file in local path since a remote server, we have a sql server 2000 SP4 with windows server 2003 SP2, thank for you help

I want to ask some questions if you don't mind...

1. what is the error message?

2. do you have appropriate permission to the remote machine?

3. do you have permission to use xp_cmdshell?

I did try your script and it works for me

Can you please try the script below..

change dname = database name

change db_owner = your database owner

exec master..xp_cmdshell 'bcp "dbname.db_owner.sysobjects" out "\\machine\directory\out.txt" -T -w'

"-=Still Learning=-"

Lester Policarpio

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

Angel Miranda Nieto (8/7/2008)
Hello to everyone, I try to explain my problem, i need with BCP utility put a file in remote machine, I writed this script exec master..xp_cmdshell 'bcp sysobjects out \\machine\direcoty\out.txt -T -w', allways this send a error, I've been searching information but I don't find some, only to reverse, put file in local path since a remote server, we have a sql server 2000 SP4 with windows server 2003 SP2, thank for you help

What is the exact error you're getting?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Angel DBA Mex Ten Centuries Points: 1085 More actions · Answer 3

thanks foy you aswer, the error is

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

NULL

I believe that is permmisions or security, because I did the test to another server and it worked, which is the logic of permits for the execution to achieve?

Angel Miranda Nieto
DBA Specialist, BI, MCSA SQL Server

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

You are correct... BCP can write anywhere that the server can "see". The SQL Server SERVICE must have a Windows Server login that can "see" the UNC path that you're trying to write to. In Enterprise Manager, right click on the server instance, select [Properties], go to the [Security] tab, and it's at the bottom of that tab.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Angel DBA Mex Ten Centuries Points: 1085 More actions · Answer 5

thank everyone, my problem is solved, we are in contact

Angel Miranda Nieto
DBA Specialist, BI, MCSA SQL Server

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

Angel Miranda Nieto (8/11/2008)
thank everyone, my problem is solved, we are in contact

Two way street here, Angel... tell us how you solved your problem, please. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Angel DBA Mex Ten Centuries Points: 1085 More actions · Answer 7

was easy, only gave all permissions to the domain account that run the sql services in the remote server on the path where I'll put the file

Angel Miranda Nieto
DBA Specialist, BI, MCSA SQL Server

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

Thanks, Angel. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)