Sorry, I just realised this was under Server 2000. Re-posted under 2005.
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 "Select * FROM [SIEBEL_SOURCE_INSTANCE].[SIEBEL_SOURCE_DB].[SOURCE_TABLE]
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]
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.