Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    86

    Unanswered: BCP issue on MS SQL Server 2005

    1 of 95 tables failed on BCP in on right truncation error ..
    -- bcp version: 9.00.1399.06

    I've tried using native format, pipe delimited etc .. Same error for all attempts.. no diference in the right trunc error (re: below)

    Table is all varchar columns, none larger than varchar(100) ..

    Source and target tables were built from the same create table script.

    There are no column level discrpancies.

    Source Server: SS05
    Target Server: SS05 Express Edition
    bcp stmt: bcp <dbname>..<tablename> in <tablename>.dat -c -t'|' -U<login> -S<server>

    -- table syntax --
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[DAV_LBMasterCW](
    [id] [int] NOT NULL,
    [Fac] [varchar](4) NULL,
    [FacService] [varchar](3) NULL,
    [HSCode] [varchar](10) NULL,
    [HSDesc] [varchar](50) NULL,
    [SchDptCode] [varchar](4) NULL,
    [SchDptDesc] [varchar](50) NULL,
    [ApptType] [varchar](3) NULL,
    [ApptTypeDesc] [varchar](50) NULL,
    [AWDPreReg] [varchar](3) NULL,
    [AWDReminderCall] [varchar](3) NULL,
    [RegSystem] [varchar](3) NULL,
    [AWDBusLine] [varchar](25) NULL,
    [EligBusLine] [varchar](25) NULL,
    [Comment] [varchar](100) NULL,
    [EligBusLineActive] [int] NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    --- <tablename>.dat - source data in pipe delimted format --

    2|MONTE| |ADL|Med desc||||||||Inpatient|Inpatient||1
    3|MONTE| |CAR|Med desc||||||||Inpatient|Inpatient||1
    4|MONTE| |CDB|Med desc||||||||Inpatient|Inpatient||1
    5|MONTE| |CDY|Med desc||||||||Inpatient|Inpatient||1
    1|MONTE| |MDV|Med desc||||||||Inpatient|Inpatient||1
    6|MONTE| |GYN|Med desc||||||||Inpatient|Inpatient||1
    7|MONTE| |MDG|Med desc||||||||Inpatient|Inpatient||1
    8|MONTE| |MDO|Med desc||||||||Inpatient|Inpatient||1
    9|MONTE| |MDS|Med desc||||||||Inpatient|Inpatient||1
    10|MONTE| |MED|Med desc||||||||Inpatient|Inpatient||1
    11|MONTE| |MLV|Med desc||||||||Inpatient|Inpatient||1
    12|MONTE| |MPA|Med desc||||||||Inpatient|Inpatient||1
    13|MONTE| |NBN|Med desc||||||||Inpatient|Inpatient||1
    14|MONTE| |OBN|Med desc||||||||Inpatient|Inpatient||1
    15|MONTE| |OBS|Med desc||||||||Inpatient|Inpatient||1
    16|MONTE| |OBX|Med desc||||||||Inpatient|Inpatient||1
    17|MONTE| |ORP|Med desc||||||||Inpatient|Inpatient||1
    18|MONTE| |ORT|Med desc||||||||Inpatient|Inpatient||1
    19|MONTE| |PED|Med desc||||||||Inpatient|Inpatient||1
    20|MONTE| |PPL|Med desc||||||||Inpatient|Inpatient||1
    21|MONTE| |RAD|Med desc||||||||Inpatient|Inpatient||1
    22|MONTE| |RNC|Med desc||||||||Inpatient|Inpatient||1
    23|MONTE| |SGB|Med desc||||||||Inpatient|Inpatient||1
    24|MONTE| |SGG|Med desc||||||||Inpatient|Inpatient||1
    25|MONTE| |SGW|Med desc||||||||Inpatient|Inpatient||1
    26|MONTE| |SMD|Med desc||||||||Inpatient|Inpatient||1
    27|MONTE| |SUR|Med desc||||||||Inpatient|Inpatient||1
    28|MONTE| |URO|Med desc||||||||Inpatient|Inpatient||1
    29|MONTE| |VAS|Med desc||||||||Inpatient|Inpatient||1

    -------------------------------- error is ----------------------

    Starting copy...
    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
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][SQL Native Client]String data, right truncation

    BCP copy in failed

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    bcp northwind.dbo.DAV_LBMasterCW in paul.txt
    -Usa -Sstg_sec-master -t"|" -c

    Starting copy...

    29 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.): total 15


    worked for me, I extended col sizes, and it worked. (MONTE (Montefiore ?) is larger than varchar(4) first of all)

    CREATE TABLE [dbo].[DAV_LBMasterCW](
    [id] [int] NOT NULL,
    [Fac] [varchar](10) NULL,
    [FacService] [varchar](10) NULL,
    [HSCode] [varchar](15) NULL,
    [HSDesc] [varchar](50) NULL,
    [SchDptCode] [varchar](4) NULL,
    [SchDptDesc] [varchar](50) NULL,
    [ApptType] [varchar](3) NULL,
    [ApptTypeDesc] [varchar](50) NULL,
    [AWDPreReg] [varchar](3) NULL,
    [AWDReminderCall] [varchar](3) NULL,
    [RegSystem] [varchar](3) NULL,
    [AWDBusLine] [varchar](25) NULL,
    [EligBusLine] [varchar](25) NULL,
    [Comment] [varchar](100) NULL,
    [EligBusLineActive] [int] NULL
    ) ON [PRIMARY]

  3. #3
    Join Date
    Mar 2007
    Posts
    86

    BCP error resolved

    Thanks .. that made me consider another cause .. the column size was changed by the vendor on the source db.. it appears they have a VPN connect to our in-house source server. I didn't know that .. they've been fixing errors on their product .. I didn't know that. The column sizes caused that error.

    Thanks I'm new to MS SQL Server, been a Sybase DBA for 15 years, doing this for a few months now. I'm using cygwin as a UNIX vairat for shell scripts to MS SQL server, for utilitiy automation (mass bcp in-out etc) .. I uncoverd this while testing one of the scripts that reloads tables from a table list .. thanks again for the suggestion. The SS05 message is misleading (Sbyase's equiv is fairly brain-dead as well, for similar errors).

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I came from the UNIX Sybase (10,11)/Oracle (8,9) side too. I managed to script with cmd everything I did with ksh and bsh. I used to use MKS Toolkit when I first came to the windows side to emulate UX shell scripting, but now I managed to get things (that I used to do in ksh) done with DOS cmd scripts.

    Get's a bit cumbersome when you need to emulate awk and sed/cut functionality though, you need to wrap everything in a for loop and set "tokens and delims=" to cut parts of strings. When it comes down to it, the older tools (bcp/bulk insert) are the fastest for moving large amounts of data compared to DTS and SSIS, to this day I still script everything out rather than use those tools.

    These windows guys would never get through vi as an editing tool.
    Last edited by PMASchmed; 04-03-09 at 09:32.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •