代码之家  ›  专栏  ›  技术社区  ›  lampShadesDrifter

tsv上的“BCP复制失败”(没有进一步的错误消息信息)

bcp sql-server
  •  0
  • lampShadesDrifter  · 技术社区  · 6 年前

    尝试在linux CentOS7上使用BCP实用程序将tsv文件复制到远程MSSQL Server DB(在Windows Server 2012计算机中),得到了

    BCP复制失败

    有问题的BCP命令是 

    /opt/mssql-tools/bin/bcp "$TABLE" in $DATAFILES/part-m-00000 \
        -S -D $SERVER_DSN \
        -U $USER \
        -P $PASSWORD \
        -d $DB \
        -c \
        -t "\t" \
        -e /home/me/projects/bcp-experiments/examples/recent-err-rows.log

    版本是 

    [me@mapr001 examples]$ bcp -v
BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 17.2.0000.1

    目标表有如下所示的模式(这里没有很好的格式化数据的方法,它是一个非常宽的表) 

    Column_name Type    Computed    Length  Prec    Scale   Nullable    TrimTrailingBlanks  FixedLenNullInSource    Collation
TDL_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
DETAIL_TYPE smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
TYPE    smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
POST_DATE   datetime    no  8                   yes (n/a)   (n/a)   NULL
ORIG_POST_DATE  datetime    no  8                   yes (n/a)   (n/a)   NULL
ORIG_SERVICE_DATE   datetime    no  8                   yes (n/a)   (n/a)   NULL
PERIOD  varchar no  6                   yes no  yes SQL_Latin1_General_CP1_CI_AS
TX_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
TRAN_TYPE   smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
ALLOWED_AMOUNT  numeric no  9   12      2       yes (n/a)   (n/a)   NULL
CHARGE_SLIP_NUMBER  varchar no  15                  yes no  yes SQL_Latin1_General_CP1_CI_AS
TYPE_OF_SERVICE smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
MATCH_TRX_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
MATCH_TX_TYPE   smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
MATCH_PROC_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
MATCH_PROV_ID   varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
MATCH_LOC_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ACCOUNT_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
PAT_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
AMOUNT  numeric no  9   12      2       yes (n/a)   (n/a)   NULL
PATIENT_AMOUNT  numeric no  9   12      2       yes (n/a)   (n/a)   NULL
INSURANCE_AMOUNT    numeric no  9   12      2       yes (n/a)   (n/a)   NULL
RELATIVE_VALUE_UNIT numeric no  9   12      2       yes (n/a)   (n/a)   NULL
CUR_CVG_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CUR_PLAN_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CUR_PAYOR_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CUR_FIN_CLASS   varchar no  66                  yes no  yes SQL_Latin1_General_CP1_CI_AS
PERFORMING_PROV_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
BILLING_PROVIDER_ID varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
ORIGINAL_CVG_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ORIGINAL_PLAN_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ORIGINAL_PAYOR_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ORIGINAL_FIN_CLASS  varchar no  66                  yes no  yes SQL_Latin1_General_CP1_CI_AS
PROC_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
PROCEDURE_QUANTITY  numeric no  5   8       3       yes (n/a)   (n/a)   NULL
CPT_CODE    varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
MODIFIER_ONE    varchar no  254                 yes no  yes SQL_Latin1_General_CP1_CI_AS
MODIFIER_TWO    varchar no  254                 yes no  yes SQL_Latin1_General_CP1_CI_AS
MODIFIER_THREE  varchar no  254                 yes no  yes SQL_Latin1_General_CP1_CI_AS
MODIFIER_FOUR   varchar no  254                 yes no  yes SQL_Latin1_General_CP1_CI_AS
DX_ONE_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
DX_TWO_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
DX_THREE_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
DX_FOUR_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
DX_FIVE_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
DX_SIX_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
SERV_AREA_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
LOC_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
DEPT_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
POS_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CUSTOMER_ITEM_ONE   varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CUSTOMER_ITEM_TWO   varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CUSTOMER_ITEM_THREE varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CUSTOMER_ITEM_FOUR  varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CUSTOMER_ITEM_FIVE  varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
BILL_CLAIM_AMOUNT   numeric no  9   12      2       yes (n/a)   (n/a)   NULL
INVOICE_NUMBER  varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
ABF_FORM_ID varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CLM_CLAIM_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
BILL_HOLD_AMOUNT    numeric no  9   12      2       yes (n/a)   (n/a)   NULL
PAT_AGING_DAYS  int no  4   10      0       yes (n/a)   (n/a)   NULL
INS_AGING_DAYS  int no  4   10      0       yes (n/a)   (n/a)   NULL
ACTION_CVG_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ACTION_PLAN_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ACTION_PAYOR_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ACTION_FIN_CLASS    varchar no  66                  yes no  yes SQL_Latin1_General_CP1_CI_AS
DEBIT_GL_NUM    varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CREDIT_GL_NUM   varchar no  20                  yes no  yes SQL_Latin1_General_CP1_CI_AS
REASON_CODE_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
USER_ID varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
COPAY_INDICATOR smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
TX_NUM  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
ORIG_PRICE  numeric no  9   12      2       yes (n/a)   (n/a)   NULL
PRICE_CONTRACT_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CONTRACT_DISCOUNT   numeric no  9   12      2       yes (n/a)   (n/a)   NULL
INT_PAT_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
ORIG_AMT    numeric no  9   12      2       yes (n/a)   (n/a)   NULL
PAT_TYPE_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
PRIM_CARE_PROV  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
REFERRAL_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
REFERRAL_SOURCE_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
PRIM_LOCATION   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
RVU_WORK    numeric no  9   12      2       yes (n/a)   (n/a)   NULL
RVU_OVERHEAD    numeric no  9   12      2       yes (n/a)   (n/a)   NULL
RVU_MALPRACTICE numeric no  9   12      2       yes (n/a)   (n/a)   NULL
POSTING_BATCH_NUM   varchar no  12                  yes no  yes SQL_Latin1_General_CP1_CI_AS
VOID_USER_ID    varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
R_NEW_CHG_TX_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
R_ORIG_CHG_TX_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
R_ORIG_POST_DATE    datetime    no  8                   yes (n/a)   (n/a)   NULL
R_ORIG_AMOUNT   numeric no  9   12      2       yes (n/a)   (n/a)   NULL
R_ORIG_PAT_AMOUNT   numeric no  9   12      2       yes (n/a)   (n/a)   NULL
R_ORIG_FIN_CLASS    varchar no  66                  yes no  yes SQL_Latin1_General_CP1_CI_AS
R_ORIG_PAYOR_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
R_ORIG_PLAN_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
R_ORIG_CVG_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
REFERENCE_NUMBER    varchar no  255                 yes no  yes SQL_Latin1_General_CP1_CI_AS
PAYMENT_SOURCE_C    smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
TX_COMMENT  varchar no  255                 yes no  yes SQL_Latin1_General_CP1_CI_AS
ANESTHESIA_TYPE_C   varchar no  66                  yes no  yes SQL_Latin1_General_CP1_CI_AS
EMERGENCY_STATUS_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
PHYSICAL_STATUS_C   varchar no  66                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CONCURRENCY_CALC    numeric no  5   6       2       yes (n/a)   (n/a)   NULL
CONCURRENCY_OVER    numeric no  5   6       2       yes (n/a)   (n/a)   NULL
BASE_UNITS  numeric no  5   6       2       yes (n/a)   (n/a)   NULL
TIMED_UNITS numeric no  5   6       2       yes (n/a)   (n/a)   NULL
PHY_STATUS_UNITS    numeric no  5   6       2       yes (n/a)   (n/a)   NULL
EMERGENCY_UNITS numeric no  5   6       2       yes (n/a)   (n/a)   NULL
AGE_UNITS   numeric no  5   6       2       yes (n/a)   (n/a)   NULL
MED_SUP_MOD_OVER_C  varchar no  1                   yes no  yes SQL_Latin1_General_CP1_CI_AS
ANESTHESIA_CERT varchar no  254                 yes no  yes SQL_Latin1_General_CP1_CI_AS
TECH_CHARGE_FLAG    varchar no  1                   yes no  yes SQL_Latin1_General_CP1_CI_AS
NIA_OUTCOME_CODE_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
PROC_MED_NEC_FLAG   varchar no  1                   yes no  yes SQL_Latin1_General_CP1_CI_AS
CRD_CHARGE_SLIP_NO  varchar no  15                  yes no  yes SQL_Latin1_General_CP1_CI_AS
VISIT_NUMBER    varchar no  50                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CRNA_CHARGE_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ORIG_ANES_CHG_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CONTESTED_FLAG_YN   varchar no  1                   yes no  yes SQL_Latin1_General_CP1_CI_AS
PAT_ENC_CSN_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ACTION_USER_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
TDL_EXTRACT_DATE    datetime    no  8                   yes (n/a)   (n/a)   NULL
INSURANCE_ID    varchar no  17                  yes no  yes SQL_Latin1_General_CP1_CI_AS
SPECIMEN_ID varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
TEST_ID varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
LAB_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
PANEL_ID    varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
PROV_SPECIALTY_C    varchar no  66                  yes no  yes SQL_Latin1_General_CP1_CI_AS
DIST_USER_ID    varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
UNDIST_USER_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
PROC_ALT_IDFR   varchar no  50                  yes no  yes SQL_Latin1_General_CP1_CI_AS
SERV_PROV_SUFFIX    varchar no  30                  yes no  yes SQL_Latin1_General_CP1_CI_AS
SERV_PROV_ATTR_1_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
SERV_PROV_ATTR_2_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
SERV_PROV_ATTR_3_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
BILL_PROV_SUFFIX    varchar no  30                  yes no  yes SQL_Latin1_General_CP1_CI_AS
BILL_PROV_ATTR_1_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
BILL_PROV_ATTR_2_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
BILL_PROV_ATTR_3_C  smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
VERIFIED    varchar no  1                   yes no  yes SQL_Latin1_General_CP1_CI_AS
RVU_PROC_UNITS  numeric no  9   12      2       yes (n/a)   (n/a)   NULL
CASE_STATE_C    smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
CASE_RATE_ID    varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CASE_TX_STATE_C smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
MATCH_RFD_TX_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
MATCH_RFD_TX_AMT    numeric no  9   12      2       yes (n/a)   (n/a)   NULL
CUR_ACTN_PAYOR_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ORIG_PST_BATCH_NUM  varchar no  12                  yes no  yes SQL_Latin1_General_CP1_CI_AS
ORIG_REF_NUM    varchar no  255                 yes no  yes SQL_Latin1_General_CP1_CI_AS
ORIG_PAY_SOURCE_C   smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
TX_DESC_OVERRIDE    varchar no  255                 yes no  yes SQL_Latin1_General_CP1_CI_AS
TRANSFERRED_TX_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
IS_TRANFERED_YN varchar no  1                   yes no  yes SQL_Latin1_General_CP1_CI_AS
CM_ORIG_DPLY_ID varchar no  25                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CM_ORIG_ETR_CID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CM_ORIG_ACCT_LINE   int no  4   10      0       yes (n/a)   (n/a)   NULL
CM_HOME_LINK    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
HOMED_INDICATOR_YN  varchar no  1                   yes no  yes SQL_Latin1_General_CP1_CI_AS
CM_PHY_OWNER_ID varchar no  25                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CM_LOG_OWNER_ID varchar no  25                  yes no  yes SQL_Latin1_General_CP1_CI_AS
CM_ORIG_ACCT_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
CM_HOMED_INSTANT    datetime    no  8                   yes (n/a)   (n/a)   NULL
CH_EDIT_SEQUENCE    int no  4   10      0       yes (n/a)   (n/a)   NULL
CH_EDIT_CONTEXTS    varchar no  254                 yes no  yes SQL_Latin1_General_CP1_CI_AS
ACTION_AMOUNT   numeric no  9   18      2       yes (n/a)   (n/a)   NULL
HOM_CLARITY_FLG_YN  varchar no  254                 yes no  yes SQL_Latin1_General_CP1_CI_AS
CSH_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ORIG_HTR_ID numeric no  9   18      0       yes (n/a)   (n/a)   NULL
WORKSTATION_ID  varchar no  18                  yes no  yes SQL_Latin1_General_CP1_CI_AS
POS_SESSID  varchar no  100                 yes no  yes SQL_Latin1_General_CP1_CI_AS
POS_TXID    varchar no  100                 yes no  yes SQL_Latin1_General_CP1_CI_AS
VOID_POS_SESSID varchar no  90                  yes no  yes SQL_Latin1_General_CP1_CI_AS
VOID_POS_TXID   varchar no  90                  yes no  yes SQL_Latin1_General_CP1_CI_AS
POS_TX_LINE int no  4   10      0       yes (n/a)   (n/a)   NULL
VOID_POS_TX_LINE    int no  4   10      0       yes (n/a)   (n/a)   NULL
HSP_ACCOUNT_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
MATCH_PAYOR_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
ACTION_MATCH_LINE   smallint    no  2   5       0       yes (n/a)   (n/a)   NULL
DYNAMIC_ALLOWED_AMT numeric no  9   12      2       yes (n/a)   (n/a)   NULL
ACTIVE_AR_AMOUNT    numeric no  9   18      2       yes (n/a)   (n/a)   NULL
EXTERNAL_AR_AMOUNT  numeric no  9   18      2       yes (n/a)   (n/a)   NULL
BAD_DEBT_AR_AMOUNT  numeric no  9   18      2       yes (n/a)   (n/a)   NULL
BILL_AREA_ID    numeric no  9   18      0       yes (n/a)   (n/a)   NULL
FIN_DIV_ID  numeric no  9   18      0       yes (n/a)   (n/a)   NULL
FIN_SUBDIV_ID   numeric no  9   18      0       yes (n/a)   (n/a)   NULL
etl_date    datetime    no  8                   yes (n/a)   (n/a)   NULL

    tsv文件中的模拟示例行看起来像 

    19133829999997  99  99  2017-01-06 00:00:00.0   2010-07-09 00:00:00.0   2010-07-06 00:00:00.0   299907  19999920    1   null    -1249999    null    32899994    9   50999   null    99  21600999999 32131999999.18  199.98  9   null    null    null    null    9   109999  109999  null    null    null    9   329999  9   76999   99  null    null    null    209999  null    null    null    null    null    99  219999  2199999999  2999    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    9   null    null    null    XXXXXXXX    null    9   999.1   null    null    B2639999    199.1   null    null    null    109999  99  1.99    null    null    2479999 null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    null    N   null    N   null    N   null    9   null    null    N   24999999    null    2017-01-07 00:00:00.0   null    null    null    null    null    null    null    21ZX9   null    null    null    null    null    null    null    null    null    null    1.99    null    null    null    null    null    null    2499999 null    null    null    null    N   null    null    null    null    N   null    null    null    null    null    null    null    Y   null    null    null    null    null    null    null    null    null    null    null    null    null    3   null    199.99  null    null    null    2018-08-17 15:48:43.0

    使用 表和数据文件 有用吗 在这一点上,错误消息太简短,无法给我更多的调试(以前从未使用过BCP实用程序)。如果有人有使用这个工具的经验,任何调试建议或如何解决这个问题将不胜感激。

    1 回复  |  直到 6 年前
        1
  •  3
  •   lampShadesDrifter    6 年前

    在进一步调试之后,发现当使用BCP将tsv文件复制到MSSQL DB时 tsv中的文本“null”值 其中作为字符串导入,并且(我假设)这是导致错误的原因,因为目标表中的某些字段需要数值,等等(在使用BCP之前,使用的是一个更隐藏的处理方法)。将命令更改为表单 

        echo "Replacing null literal values with empty chars"
    NULL_WITH_TAB="null\t"
    TAB="\t"
    sed -i -e "s/$NULL_WITH_TAB/$TAB/g" $filename

    /opt/mssql-tools/bin/bcp "$TABLE" in "$filename" \
        $TO_SERVER_ODBCDSN \
        -U $USER \
        -P $PASSWORD \
        -d $DB \
        $RECOMMEDED_IMPORT_MODE \
        -t "\t" \
        -e /home/me/projects/bcp-experiments/examples/recent-err-row.log

    推荐文章
    K.Z  ·  无法使用ADO获取Insert命令的记录Id。NET 4.0中的NET
    2 年前
    Duvan  ·  将逗号(,)替换为点(.)[副本]
    2 年前
    Yara1994  ·  如果另一列具有特定值-SQL Server,则删除具有唯一Id的所有行
    2 年前
    Simon Worth  ·  是否可以使用SqlClient自动获取查询字符串中定义的参数列表
    2 年前
    Mateen Bagheri  ·  选择表的计数并选择其自身
    2 年前
    SoT  ·  SQL Server中求和函数的工作方式
    2 年前
    rapha123  ·  如何使用REPLACE函数使用SQL更改这两个单词?
    2 年前
    NKAT  ·  将列值聚合到列表中会产生错误
    2 年前
    deanpillow  ·  返回两列中有一个匹配值的记录
    3 年前
    snowflakes74  ·  在Dapper中异步查询多个结果
    3 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号