BCP COMMAND

Question

BCP COMMAND

Sathya-243416

SSC Rookie

Points: 34
More actions
July 4, 2005 at 5:59 am

#167708

Hi,
We are a part of Migration project from Sybase 11.5 to SQL Server 2000. There is a need for using BCP.
We are aware that BCP can be used for export and import of data and the command has to be executed in the server preferably with the .txt file (to import or to export) in the same machine where the BCP is executed.
SITUATION:
We need to run the BCP command in the server but the .txt file from which it should be imported or exported is in the client machine (the folder is shared). I need to use this .txt file and the command to be executed in the server.
exec XP_cmdshell 'BCP
in "[file path- in client machine]" -U [login id] -P [password]-S [Server Name]-c -t\t -r\n '
Throws an error.
"Unable to open BCP Host data file.".
Let us know if this is possible.
Regards,
Sathya

Viewing 11 posts - 1 through 10 (of 10 total)

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

What account is SQL Server service running under, if it is Local System then it will not be able to access the network, see the following from BOL

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Far away is close at hand in the images of elsewhere.
Anon.

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

Check if the path is correct and is included into:

"c:\path"

Sathya-243416 SSC Rookie Points: 34 More actions · Answer 3

net : I included the path in C:\BCPTEST\TESTBCP.TXT and executed the command. Still the same error.

David :

The account is not with Local System. The account is as follows. Any other suggestion on this.

philcart SSC-Forever Points: 47795 More actions · Answer 4

Also, for the best speed you should use BULK INSERT instead. It's much quicker than BCP for importing data.

--------------------
Colt 45 - the original point and click interface

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

but you write:

"C:\BCPTEST\TESTBCP.TXT and executed the command. Still the same error".

I am using BCP and BULK INSERT and some time i get the same error in case of bad path or "" missed...

try again...

fasttrack SSCertifiable Points: 6071 More actions · Answer 6

check also - S is the right server e. g. localhost

philcart SSC-Forever Points: 47795 More actions · Answer 7

You should use UNC filenames. EG: \\ClientMachine\ClientShare\ClientFile.txt

--------------------
Colt 45 - the original point and click interface

Jersey Moe SSChasing Mays Points: 652 More actions · Answer 8

Why not ftp the files from the Sybase server to MSSQL server?

Or, use DTS to connect and have it pull your data for you?

--JerseyMo

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 9

I wouldn't do the BCP out of SQLServer.

You should go the your preferred command prompt, check whether you can access to SQLServer, after you can issue the BCP command (maybe all of then in a batch file).

And also you have to know if you want to access an other machine than the server itself where SQLServer is running the user where the SQLSErver service is running must have enough permission to access to the network and also the client machine. In Windows 2000, the local system account does allow network access.

You could also take care of the -Ccodepage parameter if your language is not English other it will fail with the date and number conversion

Bye
Gabor

Sathya-243416 SSC Rookie Points: 34 More actions · Answer 10

Hi all,

Thanks for all your suggestions and comments.

We changed MS SQL Server logon account in the SQL Service to an account belonging to the domain (admin account) which had permissions on the folder created in the Client machine and BCP worked fine.

Regards,

Sathya