Professional Web Applications Themes

BulkCopy Import question - Microsoft SQL / MS SQL Server

Hi all, I have exported all the content of one of my tables to a text file. If I were to clear the content of that table, then did a import data in Enterprise manager, everything worked fine. However when I tried to import the data programmatically, the ImportData for the BulkCopy object always failed. I looked at the log file, and it's giving me the following error message. # Row 1, Column 2: String data, right truncation # (The text file was generated by Enterprise Manager, the file was comma separated file) Does anyone know what this message mean? ...

  1. #1

    Default BulkCopy Import question

    Hi all,

    I have exported all the content of one of my tables
    to a text file. If I were to clear the content of that table,
    then did a import data in Enterprise manager, everything
    worked fine. However when I tried to import the data
    programmatically, the ImportData for the BulkCopy object
    always failed. I looked at the log file, and it's giving me
    the following error message.

    # Row 1, Column 2: String data, right truncation #

    (The text file was generated by Enterprise Manager, the file
    was comma separated file)

    Does anyone know what this message mean?

    Thanks in advance,

    Frank Cheng

    if FAILED(hr =
    CoCreateInstance(CLSID_SQLDMOServer,NULL,CLSCTX_IN PROC_SERVER,IID_ISQLDMOSer
    ver,(LPVOID*)&pSQLServer)) return (FALSE);
    if FAILED(hr =
    CoCreateInstance(CLSID_SQLDMOBulkCopy,NULL,CLSCTX_ INPROC_SERVER,IID_ISQLDMOB
    ulkCopy,(LPVOID*)&bcp)) return (FALSE);
    if FAILED(hr = pSQLServer->SetEnableBcp(TRUE)) return (FALSE);
    if FAILED(hr = pSQLServer->SetLoginSecure(bTrusted)) return (FALSE);
    if FAILED(hr = pSQLServer->Connect(wServer,wUsername,wPassword)) return
    (FALSE);
    if FAILED(hr = bcp->SetColumnDelimiter(TEXT(","))) return (FALSE);
    if FAILED(hr = bcp->SetRowDelimiter(TEXT("\n"))) return (FALSE);
    if FAILED(hr = bcp->SetDataFileType(SQLDMODataFile_Default)) return
    (FALSE);
    if FAILED(hr = bcp->SetErrorFilePath(OLESTR("C:\\SQLHBLOG.TXT"))) return
    (FALSE);
    if FAILED(hr = bcp->SetServerBCPDataFileType(SQLDMOBCPDataFile_Char ))
    return (FALSE);
    if FAILED(hr = bcp->SetMaximumErrorsBeforeAbort(1)) return (FALSE);
    if FAILED(hr = bcp->SetUseBulkCopyOption(TRUE)) return (FALSE);
    if FAILED(hr = bcp->SetUseExistingConnection(TRUE)) return (FALSE);
    if FAILED(hr = bcp->SetImportRowsPerBatch(1000)) return (FALSE);
    if FAILED(hr = bcp->SetTruncateLog(TRUE)) return (FALSE);
    if FAILED(hr = bcp->SetIncludeIdentityValues(TRUE)) return (FALSE);
    if FAILED(hr = pSQLServer->GetDatabaseByName(wDatabase,(IWSQLDMODatabase
    **)(&db))) return (FALSE);
    if FAILED(hr = bcp->SetDataFilePath(wFilename)) return (FALSE);
    if FAILED(hr = db->GetTableByName(wTable,&tbl)) return (FALSE);
    if FAILED(hr = tbl->ImportData(bcp)) return FALSE;




    Frank Guest

  2. #2

    Default Re: BulkCopy Import question

    I believe that error typically indicates that the format of the file does
    not match what is expected, and would occur with BCP and an incorrect format
    file, or the data length exceeds the column length, or the buffer provided
    to SQLGetData (ODBC) is too small for the column data.

    HTH,
    Tore.


    "Frank Cheng" <com> wrote in message
    news:phx.gbl... 
    CoCreateInstance(CLSID_SQLDMOServer,NULL,CLSCTX_IN PROC_SERVER,IID_ISQLDMOSer 
    CoCreateInstance(CLSID_SQLDMOBulkCopy,NULL,CLSCTX_ INPROC_SERVER,IID_ISQLDMOB 


    Tore Guest

  3. #3

    Default Re: BulkCopy Import question

    Hi Tore,

    Thanks for the reply.
    When I exported (Enterprise manager) my data, I specify double quote as my
    text qualifier.
    However when I did the import programmatically, the ImportData thought the
    the double-quote around my char/text fields were part of the text. My new
    question
    is - how can I specify the text qualifier in my BulkCopy Object (I didn't
    see any properties
    that did it)

    Thanks in advance,

    Frank Cheng

    "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
    news:phx.gbl... 
    format 
    >[/ref]
    CoCreateInstance(CLSID_SQLDMOServer,NULL,CLSCTX_IN PROC_SERVER,IID_ISQLDMOSer 
    >[/ref]
    CoCreateInstance(CLSID_SQLDMOBulkCopy,NULL,CLSCTX_ INPROC_SERVER,IID_ISQLDMOB [/ref]
    return [/ref]
    pSQLServer->GetDatabaseByName(wDatabase,(IWSQLDMODatabase 
    >
    >[/ref]


    Frank Guest

  4. #4

    Default Re: BulkCopy Import question

    What should I put in the column delimiter and row delimiter?
    My file is a comma delimited file, "\n" is the row delimiter

    if FAILED(hr = bcp->SetColumnDelimiter(TEXT(",\""))) return (FALSE);
    if FAILED(hr = bcp->SetRowDelimiter(TEXT("\n"))) return (FALSE);

    it still didn't work. It thought the column delimiter was ,"

    Frank Cheng


    "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
    news:phx.gbl... [/ref]
    my [/ref]
    the [/ref]
    new [/ref]
    didn't [/ref]
    > does 
    > > format [/ref]
    > provided 
    > >[/ref]
    >[/ref]
    CoCreateInstance(CLSID_SQLDMOServer,NULL,CLSCTX_IN PROC_SERVER,IID_ISQLDMOSer 
    > >[/ref]
    >[/ref]
    CoCreateInstance(CLSID_SQLDMOBulkCopy,NULL,CLSCTX_ INPROC_SERVER,IID_ISQLDMOB [/ref][/ref]
    (FALSE); [/ref]
    > return 
    > > return [/ref][/ref]
    bcp->SetServerBCPDataFileType(SQLDMOBCPDataFile_Char )) 
    > > pSQLServer->GetDatabaseByName(wDatabase,(IWSQLDMODatabase 
    > >
    > >[/ref]
    >
    >[/ref]


    Frank Guest

Similar Threads

  1. QuickTime VR import question
    By Doug in forum Macromedia Flash
    Replies: 0
    Last Post: September 20th, 05:57 PM
  2. Replies: 1
    Last Post: February 4th, 11:00 PM
  3. Question re import of Picture from CDR
    By Wayne Bourke in forum Macromedia Freehand
    Replies: 1
    Last Post: August 16th, 08:06 AM
  4. DMO BulkCopy and trigger
    By shyam in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 11:18 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139