BCP problem in Windows Server 2003 Help.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

BCP problem in Windows Server 2003 Help.

Author

Topic

akaninakai
Starting Member

2 Posts

Posted - 2009-05-12 : 11:38:51

I received the following errors using BCP in Windows 2003:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
And
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

And this is causing to stop the load process.

The same load works in Windows XP.
MDAC versions:
Windows 2003 is 2.82.3959.0
Windows XP is 2.81.1117.0

Any Ideas? I didn't find any paper from MS with this problem.

bcp command:
bcp <schema>..<table> in F:\part.DAT -c -r\n -t"|" -b30000 -U<User> -P<Password> -S<Server>

krharm
Starting Member

1 Post

Posted - 2009-05-12 : 15:14:18

It appears that you may have 2 issues. The first issue with the right truncation means that a field in the flat file exceeds the field length of the corresponding SQL column. The invalid character issue sounds like a flat file field contains a value inconsistent with the SQL column type.

What I would suggest is creating a staging table in SQL with all columns being a VARCHAR(MAX). If the BCP completes without error, then I would start looking at the data in the flat file for field lengths and data inconsitency.

http://www.perptech.com

akaninakai
Starting Member

2 Posts

Posted - 2009-05-12 : 15:52:44

Yes that will resolve the issue temporally , but why it's works using the SQL server Client in Windows XP ? But in Windows 2003 brings this error, I executed the same process in others Windows 2003 and the problem persists.

Also the same Load process in Production runs in SQL server 2000 fine we are trying to migrate to 2005.

Log from executed from a OS ( Windows XP)

Starting copy...
30000 rows sent to SQL Server. Total sent: 30000
30000 rows sent to SQL Server. Total sent: 60000
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
30000 rows sent to SQL Server. Total sent: 90000
30000 rows sent to SQL Server. Total sent: 120000
30000 rows sent to SQL Server. Total sent: 150000
30000 rows sent to SQL Server. Total sent: 180000
30000 rows sent to SQL Server. Total sent: 210000
30000 rows sent to SQL Server. Total sent: 240000
30000 rows sent to SQL Server. Total sent: 270000
30000 rows sent to SQL Server. Total sent: 300000
30000 rows sent to SQL Server. Total sent: 330000
30000 rows sent to SQL Server. Total sent: 360000
30000 rows sent to SQL Server. Total sent: 390000
30000 rows sent to SQL Server. Total sent: 420000
30000 rows sent to SQL Server. Total sent: 450000
30000 rows sent to SQL Server. Total sent: 480000
30000 rows sent to SQL Server. Total sent: 510000
30000 rows sent to SQL Server. Total sent: 540000
30000 rows sent to SQL Server. Total sent: 570000
30000 rows sent to SQL Server. Total sent: 600000
30000 rows sent to SQL Server. Total sent: 630000
30000 rows sent to SQL Server. Total sent: 660000
30000 rows sent to SQL Server. Total sent: 690000
30000 rows sent to SQL Server. Total sent: 720000

749656 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 2763605

Log from Windows Server 2003:

Starting copy...
30000 rows sent to SQL Server. Total sent: 30000
30000 rows sent to SQL Server. Total sent: 60000
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
30000 rows sent to SQL Server. Total sent: 90000
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation

110124 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 2875 Average : (38304.00 rows per sec.)

***
****
The Load stop at the 110124 row of 749656 rows.

Any CLUE? it's the server or any missing dll or dependencies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources