SQL Server Performance

BCP I/O Error

Discussion in 'General Developer Questions' started by dzschomler, Jul 22, 2003.

  1. dzschomler New Member

    I am trying to create BCP export command using a format file (I have inserted the file contents below). If I specify the number of columns as 16 or less, the export runs just fine. When I change it to 17 or higher I get the following error: [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file

    Any one know what I need to do to get this fixed?
    My Bcp.exe is version 2000.80.194.0

    {My format file}
    8.0
    25
    1SQLCHAR050","1LastName
    2SQLCHAR050","2FirstName
    3SQLCHAR050","3Address1
    4SQLCHAR050","4City
    5SQLCHAR02","5State
    6SQLCHAR 010","6ZipCode
    7SQLCHAR050","7Account
    8SQLCHAR020","8Status
    9SQLCHAR030","9CreatedBy
    10SQLCHAR020","10PendingReason
    11SQLCHAR010","11CallID
    12SQLCHAR020","12Area1
    13SQLCHAR020","13Area2
    14SQLCHAR020","14Area3
    15SQLCHAR01","15Reproducible
    16SQLCHAR030","16ProductName
    17SQLCAHR02000","17Summary
    18SQLCHAR02000","18Description
    19SQLCHAR020","19CreateDate
    20SQLCHAR0100","20Email
    21SQLCHAR020","21PhoneNumber
    22SQLCHAR 020","22Source
    23SQLCHAR 020","23Severity
    24SQLCHAR020","24Priority
    25SQLCHAR 020"
    "25ContactID
  2. gaurav_bindlish New Member

    Line 17 is

    17 SQLCAHR 0 2000 "," 17 Summary

    it should be

    17 SQLCHAR 0 2000 "," 17 Summary

    Please check syntax before posting.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. dzschomler New Member

    I updated the line you mentioned, now none of it works. I can specify 1 column or 25, I get the same error.
  4. gaurav_bindlish New Member

    Can you post a sample of data in the table as well as the table defination?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. dzschomler New Member

    Sample of Data:
    LeeRobert2655 South AveAberdeenSD57401ClosedDZAssigned1Area 1Area 1 Sub 1Area 1 Sub 1 Sub Sub 20Product 2SummaryDescription7/18/2003 8:32:00 AMrl@domain.com1234567898Source 1Low2
    ZschomlerDaniel123 Main StreetDurhamNC27713OpenATPzschomdmAssigned2UploadDial-upOn/Off Mode1Product 1Summary SummaryDescription Description7/21/2003 4:09:00 PMdz@domain.com1234567897Source 1High1

    I am trying to export a View, not a table. Here is the SQL Statement of the view:
    SELECT dbo.tblCallContact.LastName, dbo.tblCallContact.FirstName, dbo.tblCallContact.Address1, dbo.tblCallContact.City, dbo.tblCallContact.State,
    dbo.tblCallContact.ZipCode, dbo.tblCallContact.Account, dbo.tblCallData.Status, dbo.tblCallData.CreatedBy, dbo.tblCallData.PendingReason,
    dbo.tblCallData.CallID, dbo.tblCallData.Area1, dbo.tblCallData.Area2, dbo.tblCallData.Area3, dbo.tblCallData.Reproducible,
    dbo.tblCallData.ProductName, dbo.tblCallData.Summary, dbo.tblCallData.Description, dbo.tblCallData.CreateDate, dbo.tblCallContact.EMail,
    dbo.tblCallContact.PhoneNumber, dbo.tblCallData.Source, dbo.tblCallData.Severity, dbo.tblCallData.Priority, dbo.tblCallContact.ImetContactID
    FROM dbo.tblCallContact INNER JOIN
    dbo.tblCallData ON dbo.tblCallContact.ContactID = dbo.tblCallData.ContactID

    I also updated the format file to more accurately reflect data types:
    8.0
    25
    1 SQLCHAR 0 50 "," 1 LastName
    2 SQLCHAR 0 50 "," 2 FirstName
    3 SQLCHAR 0 50 "," 3 Address1
    4 SQLCHAR 0 50 "," 4 City
    5 SQLCHAR 0 2 "," 5 State
    6 SQLCHAR 0 10 "," 6 ZipCode
    7 SQLCHAR 0 50 "," 7 Account
    8 SQLCHAR 0 20 "," 8 Status
    9 SQLCHAR 0 30 "," 9 CreatedBy
    10 SQLCHAR 0 20 "," 10 PendingReason
    11 SQLBIGINT 0 10 "," 11 CallID
    12 SQLCHAR 0 20 "," 12 Area1
    13 SQLCHAR 0 20 "," 13 Area2
    14 SQLCHAR 0 20 "," 14 Area3
    15 SQLBIT 0 1 "," 15 Reproducible
    16 SQLCHAR 0 30 "," 16 ProductName
    17 SQLCHAR 0 2000 "," 17 Summary
    18 SQLCHAR 0 2000 "," 18 Description
    19 SQLDATETIM4 0 20 "," 19 CreateDate
    20 SQLCHAR 0 100 "," 20 Email
    21 SQLCHAR 0 20 "," 21 PhoneNumber
    22 SQLCHAR 0 20 "," 22 Source
    23 SQLCHAR 0 20 "," 23 Severity
    24 SQLNUMERIC 0 20 "," 24 Priority
    25 SQLCHAR 0 20 "
    " 25 ContactID
  6. satya Moderator

    How about the BCP statement & parameters used?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. dzschomler New Member

    BCP statement:
    bcp "Select * from imet..Export1" queryout Export.exe -Usa -P -S172.25.1.11 -fExport_Spects.txt

    Here is some futher wackyness:
    If I change the version to 6.0 and number of columns to 24, it works just fine.
    If I change the version to 6.0 and number of columns to 25, same error message, though I dont see anything wrong with the code of line 25.
    If I change the version to 8.0 with any number of columns specified, get error message.
  8. dzschomler New Member

    that bcp statement should be
    cbp "Select & from imet..export" queryout Export.txt -Usa -P -S172.25.1.11 -fExport_Specs.txt
  9. gaurav_bindlish New Member

    What is the format file that is generated if you manually fill in the datatypes for the fields one by one?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. dzschomler New Member

    This is taking Way to long. I'll just do it in VB.
    Thanks

Share This Page