SQL Server Performance

SQLDATETIME in BCP file

Discussion in 'SQL Server DTS-Related Questions' started by cutiepie, Sep 22, 2005.

  1. cutiepie New Member

    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...
  2. vbkenya New Member

    Try changing the SQLDATETIME line to:

    17 SQLDATETIME 0 8 "" 17 S ""


    Differences?
    -The length of the column and no Collation.

    Nathan H. Omukwenyi
  3. cutiepie New Member

    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
  4. satya Moderator

    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.
  5. cutiepie New Member

    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??????<br />thnx
  6. satya Moderator

    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.
  7. cutiepie New Member

    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...<br />format file has one datetime column..the format of it is as follow <img src='/community/emoticons/emotion-6.gif' alt=':(' />bulk_format.fmt)<br /><br />9.0<br />3<br />1SQLCHAR 012 " " 1 number SQL_Latin1_General_CP1_CI_AS<br />2 SQLDATETIME 0 8 " " 2 date_time SQL_Latin1_General_CP1_CI_AS<br />3SQLCHAR 04 "
    " 3alphabet SQL_Latin1_General_CP1_CI_AS<br /><br /><br />and the format of my input data file is as follow:<br />12345896236507-08-2005a<br />56785698412305-06-2009b<br />91023698547502-08-2003c<br />35691458963201-02-2000d<br /><br />now,whenever i m trying to run this in CMD...it gives me the error as:<br />Starting Copy...<br />SQLState = 42000, NativeError=4896<br />error= [microsoft][sql native client][sql server] Invalid column value from bcp client for colid 2<br />BCP copy in failed.....<br />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..<br />So, now can anyone pleaseeeeeeeee tell me..how to deal with this????<br /><br />thnks...for all the previous replies
  8. satya Moderator

    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.
  9. vbkenya New Member

    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
  10. cutiepie New Member

    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.

Share This Page