BCP problems - help?

Question

Post reply

BCP problems - help?

David Kingman

Say Hey Kid

Points: 675
More actions
February 14, 2007 at 10:56 am

#175081

I am getting
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
When trying to load data into a SQL Server 2005 Express DB on my PC:
DECLARE @CMD VARCHAR(8000)
SET @CMD = 'BCP "SYMITAR.dbo.ACCOUNT" IN "C:\test\EXTRACT.ACCOUNT.txt" '
SET @CMD = @CMD + '-t~ -r/n -f "C:\test\ACCOUNT.FMT" -S"IS763\SQLEXPRESS" -T -b50000'
EXEC Master.dbo.xp_CmdShell @CMD
GO
It has no problem queryout (ing) to C:\test\... I have checked the file name, permissions and the path at least 5 times.
Any help?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

Is the file on your local C drive or on the C drive of the server?

try doing an master..xp_cmdshell 'dir C:\test\' in query analyzer and check if you can see the text and format files.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

David Kingman Say Hey Kid Points: 675 More actions · Answer 2

Thanks for the reply.

This is SQL Server 2005 Express installed on my PC. Doing the 'dir C:\test\' showed the files and had no effect

as did 'CD C:\test\' had no effect either.

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 3

See if putting quotes around delimiters helps:

SET @CMD = @CMD + '-t"~" -r"/n" -f "C:\test\ACCOUNT.FMT" -S"IS763\SQLEXPRESS" -T -b50000'

Also, see if removing quotes for DB name helps.

SET @CMD = 'BCP SYMITAR.dbo.ACCOUNT IN "C:\test\EXTRACT.ACCOUNT.txt" '

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

David Kingman Say Hey Kid Points: 675 More actions · Answer 4

Well, this worked:

DECLARE @CMD VARCHAR(8000)

SET @CMD = 'BCP SYMITAR..ACCOUNT IN C:\test\ACCOUNT.txt '

SET @CMD = @CMD + '-t~ -r/n -e "C:\test\error.txt" -f C:\test\ACCOUNT.fmt -SIS763\SQLEXPRESS -V 70 -T -b50000'

EXEC Master.dbo.xp_CmdShell @CMD

GO

The last thing I changed was the format file name.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Just as a test, why not add PRINT @CMD instead of the EXEC statement. The cut the output line out and paste it into a DOS window and see of the command works.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."