SQL Server Performance Forum – Threads Archive

SQLDATETIME in BCP file

Hi all,
i m having problems with the sqldatetime…i m using sql server 2005 YUKON..according to my format file i cant put the date on the right column…my bcp command is copying everything but its shifting the date column with the column on the right hand side…here is my format file (bulk_format.fmt):
9.0
32
1SQLCHAR 02 "" 1 first SQL_Latin1_General_CP1_CI_AS
2SQLCHAR 015 ""2second SQL_Latin1_General_CP1_CI_AS
3SQLCHAR 05 ""3third3 SQL_Latin1_General_CP1_CI_AS
4SQLCHAR 010 ""4third4 SQL_Latin1_General_CP1_CI_AS
5SQLCHAR 010 ""5third5 SQL_Latin1_General_CP1_CI_AS
6SQLCHAR 020 ""6third6 SQL_Latin1_General_CP1_CI_AS
7SQLCHAR 00 ""7third7 SQL_Latin1_General_CP1_CI_AS
8SQLCHAR 0260 ""8third8 SQL_Latin1_General_CP1_CI_AS
9SQLCHAR 04 ""9third9 SQL_Latin1_General_CP1_CI_AS
10SQLCHAR 0100 ""10third0 SQL_Latin1_General_CP1_CI_AS
11SQLCHAR 012 "" 11l SQL_Latin1_General_CP1_CI_AS
12SQLCHAR 055 ""12m SQL_Latin1_General_CP1_CI_AS
13SQLCHAR 03 ""13fift SQL_Latin1_General_CP1_CI_AS
14SQLCHAR 020 ""14sixth SQL_Latin1_General_CP1_CI_AS
15SQLCHAR 010 ""15seventh SQL_Latin1_General_CP1_CI_AS
16SQLCHAR 09 ""16eighth SQL_Latin1_General_CP1_CI_AS
17SQLDATETIME 00 "" 17 S SQL_Latin1_General_CP1_CI_AS
18SQLCHAR 0255 ""18tenth SQL_Latin1_General_CP1_CI_AS
19SQLCHAR 00 ""19eleventh SQL_Latin1_General_CP1_CI_AS
20SQLCHAR 01 ""20twelfth SQL_Latin1_General_CP1_CI_AS
21SQLCHAR 01 "" 21thirteeen SQL_Latin1_General_CP1_CI_AS
22SQLCHAR 010 ""22a SQL_Latin1_General_CP1_CI_AS
23SQLCHAR 00 ""23b SQL_Latin1_General_CP1_CI_AS
24SQLCHAR 00 ""24c SQL_Latin1_General_CP1_CI_AS
25SQLCHAR 00 ""25d SQL_Latin1_General_CP1_CI_AS
26SQLCHAR 03 ""26e SQL_Latin1_General_CP1_CI_AS
27SQLCHAR 00 ""27f SQL_Latin1_General_CP1_CI_AS
28SQLCHAR 00 ""28g SQL_Latin1_General_CP1_CI_AS
29SQLCHAR 00 ""29h SQL_Latin1_General_CP1_CI_AS
30SQLCHAR 00 ""30i SQL_Latin1_General_CP1_CI_AS
31SQLCHAR 00 ""31j SQL_Latin1_General_CP1_CI_AS
32SQLCHAR 00 "
"32k SQL_Latin1_General_CP1_CI_AS
on the 17th column if i m putting the prefix length and host file data length as 0 0 then it copies everything into my table but on the 17th column(date column) it writes NULL and the date is shifted to the 18th column along with the values of 18th column itself…and if i m writting 0 8 infront then it gives me error as INVALID COLUMN VALUE FROM BCP CLIENT FOR COLID 16… my bcp command is :
bcp master.dbo.other_bulk_insert in c: empulk_data.txt -fc: empulk_format.fmt -Sserver -Uuser -Ppasword -T can someone pleaseeeeeee tell me how i should write the SQLDATETIME??????
thnks for HELP…

Try changing the SQLDATETIME line to: 17 SQLDATETIME 0 8 "" 17 S ""
Differences?
-The length of the column and no Collation. Nathan H. Omukwenyi

First of all thanks for your reply…but
it doesnt work…its still giving the same error
SQLState = 42000, Native Error = 4896
Error=[Microsoft][SQL Native Client][SQL Server]Invalid column value from bcp client for colid 16
BCP copy in failed…
NOW WAT TO DO?????
thnks

Check the data types and ensure correct values passed in FORMATFILE for 15 to 18. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

agaain another problem now…[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] since the fields 15,16 are numeric…so,if i m writting SQLNUMERIC there instead of SQLCHAR..it gives me error as NUMERIC value out of range…though the default value can be uptill 12 and in my case its just 9 and 10…i really cant understand what to do with this problem…please try to suggest something else?????? thnx

Ok, check the table schema data type and ensure you’ve entered correct values in format file.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

i tried everythin..its still the same…so,now atlast i decided to work with 3 columns first…so i decided to find the solution for date time first… format file has one datetime column..the format of it is as follow <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />bulk_format.fmt) 9.0 3 1SQLCHAR 012 " " 1 number SQL_Latin1_General_CP1_CI_AS 2 SQLDATETIME 0 8 " " 2 date_time SQL_Latin1_General_CP1_CI_AS 3SQLCHAR 04 "
" 3alphabet SQL_Latin1_General_CP1_CI_AS and the format of my input data file is as follow: 12345896236507-08-2005a 56785698412305-06-2009b 91023698547502-08-2003c 35691458963201-02-2000d now,whenever i m trying to run this in CMD…it gives me the error as: Starting Copy… SQLState = 42000, NativeError=4896 error= [microsoft][sql native client][sql server] Invalid column value from bcp client for colid 2 BCP copy in failed….. if i m trying to write SQLNUMERIC instead of SQLCHAR then instead of copying the numbers…it just writes ZEROS in all the rows of the first column.. So, now can anyone pleaseeeeeeeee tell me..how to deal with this???? thnks…for all the previous replies

What is this colid 2?
I obeserve this is related to SQL 2005, in that case refer to SQL 2005 books online for any changes in BCP formatfile option in this version. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Your dates have a length of 10 characters. Wild thought here:
Can you try changing the 0 8 prefix and data lengths to 0 10? Nathan H. Omukwenyi

hi,
thanks for all the replies..i even tried writtin 0 10 but it didnt work at all…so,finally i found another solution by writting 0 16 with the datatype SQLCHAR and it worked FINALLY…
thnks once again.

]]>