<<urgent>>BCP OUT and BCP IN Error.

Question

<<urgent>>BCP OUT and BCP IN Error.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
September 23, 2010 at 9:17 am

#142086

Hello Experts
I need some suggestion to fix the issue.
There is a table in source server. I need to refresh the table contents from Prod to Test Servers. In both the servers, the table contents are same (schema/structure-wise).
I did the BCP OUT of the contents from Prod Server:
used the below command:
exec master..xp_cmdshell 'bcp "select * from ABCPROD.dbo.actbal" queryout "C:\actbal_bkp.txt" -c -T -S "KFTUSPAWBAGTMC5"'
In the Destination TEST Server, (This is in SQL Server 2005),
used the below command:
exec master..xp_cmdshell 'bcp GTMUPD..actbal_bak in "D:\Manoj_Table_Contents_BCP\actbal_bak2.txt" -n -c -T -S KFTUSOKTULAP235'
But getting the below error:
========================
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL
== What is the error?
== Is there an issue with the BCP Syntax and the switches which I am using?
==Invalid character value for cast specification.
The above furnished error is coming.
I need refresh 4 such tables.
Please assist me.
Thanks.

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

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

have a look here, seems like same problem with resolution: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/080f01a0-4011-4bf6-a7ea-5ef0d2479030

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

The link doesn't help me much....!

exec master..xp_cmdshell 'bcp hghj.dbo.file1 in "C:\Manoj_Table_Contents_BCP\file1.txt" -w -n -T -S W2TZDF4B02'

I created the table in my local SQL Server (SQL 2K5), with the same schema as available in Prod Box, and tried to do BCP IN, still getting the below error.

Any suggestion pls?

=====================

Warning: -n overrides -w.

NULL

Starting copy...

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

NULL

0 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 1

NULL

Thanks.

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

Not an expert here, but I see you are using -n. from that same thread saw:

When you export native there are no record or row delimiters defined. The image type as well as other datatypes like nvarchar(max) do not have fixed length. When you reimport there is now way to tell where fields and records start or end.

There are two solutions (described in detail below)

* - Use delimiters that do not occur in the data using the \t and switches

* - Edit the format file to specify the size of the image (only works if it is fixed and known)

The way arround that I've found is to export with delimiters (works even in binary exports!). There is also a microsoft solution that proposes specifying the actual size of the field if the data size of the image is known (like in the case of a bitmap), that way it can still import even without delimeters. This solution requires you to edit the format file (http://support.microsoft.com/default.aspx/kb/271344).

BTW. You can export with custom delimiters using the \t and switches with string switches of up to 10 chars. I like to use combinations of tabs and characters that I suspect won't occur in my data.

and previous post:

I ran into the same problem. It appears to have something to do with the -n (native format) switch. I changed it to -c (character format) and my import worked.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

I just checked the datatype of all the available columns in the table, and found that, the table doesn't have: BLOB or image type data. The used data types are-

timestamp, varchar, int, float, smallint, char.

Not only that, the source table (SQL Server 2000) and target table (SQL Server 2005) are with the same Schema Structure.

Can you please suggest, if I have done any mistake while using the Switches or the query?

If I use, BULK INSERT, can this issue be fixed?

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

I had fixed the issue.

Actually, there was a timestamp column in the source table, so while doing the BCP IN, it was erroring out since the timestamp field must be generated automatically.

I used Export/Import Utility and able to successfully complete the table content out to destination.

Thanks.