Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

BCP Error with datetime columns Expand / Collapse
Posted Sunday, March 6, 2011 11:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 6:17 PM
Points: 5, Visits: 84
Sorry, I just realised this was under Server 2000. Re-posted under 2005.

Hi all,
I have tried searching but cannot find a specific answer to my problem.

I am using BCP with a native file format (-n not using -N) to extract data for a delta load.

Using the main database, this works fine. The problem comes about when I have to pull data directly from a source database.

The source database is a clone of a SIEBEL database put onto a SQL Server 2005 instance.

The problem is, I belive, an issue with the date format in the extract file.

BCP statement:
WHERE LAST_UPD BETWEEN (DATE) AND (DATE)" queryout [FileName] -n -q -S[LocalServer] -T >[LogFile]

When I try to import into a table using bulk insert (for testing- this will happen in the final product) Bulk insert statement:
BULK INSERT [database].dbo.[Table_Name]
FROM '[sourceFile]';
DATAFILETYPE = 'native',

I get an error:
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row1, column 2 (Created_Date).

I have tride changing the datetime columns to char(500) data types, and I get the following error message:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 2. Verify that the field terminator and row terminator are specified correctly

When I use a unicode BCP statement (with a -w switch) with nominated field and row terminators, the table loads fine.

There is, however, a requirement to use the native file formats for the final product.

SQL Server 2005 service pack 3

Source database is a Clone of SIEBEL 7 stored on a SQL Server 2005 database.
Post #1073958
Posted Monday, October 10, 2011 5:41 AM


Group: General Forum Members
Last Login: Monday, September 26, 2016 1:18 PM
Points: 188, Visits: 271
Hi All,

Even i am also facing the same problem, Any quick response is helpfull.

Thank you,
Post #1187837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse