Solved

BCP problem

Posted on 2004-03-31
15
1,391 Views
Last Modified: 2007-12-19
I am trying to use BCP to get a text file into a table. I have never used BCP before and am having trouble getting it to work. I thought if I showed you guys what I have so far, you may be able to show me where I am going wrong.

Table def(jetnet..j300)

ID                   int      4
[Text Line 1]      char      100
[Text Line 2]      char      100
[Text Line 3]      char      100
[Text Line 4]      char      100
[Text Line 5]      char      100
[Text Line 6]      char      100
[Address Line 1]      char      50
[Address Line 2]      char      50
[Address Line 3]      char      50
[Address Line 4]      char      50
[Address Line 5]      char      50
LookUpCode      char      10
[Filler 1]           char      40
[Postnet Barcode]      char      11
[Planet Barcode]      char      16
[Filler 2]          char      23
Field16            char      1
Done               int      4
DoneTime        datetime      8
StackZone        int      4
StackOp           int      4

My format file(\\Orbitty\Depts\DP\ALAN\example.fmt)

7.0
22
1    SQLNUMERIC    0    4    ""    1    ID
2    SQLCHAR    0    100    ""    2    Text Line 1
3    SQLCHAR    0    100    ""    3    Text Line 2
4    SQLCHAR    0    100    ""    4    Text Line 3
5    SQLCHAR    0    100    ""    5    Text Line 4
6    SQLCHAR    0    100    ""    6    Text Line 5
7    SQLCHAR    0    100    ""    7    Text Line 6
8    SQLCHAR    0    50    ""    8    Address Line 1
9    SQLCHAR    0    50    ""    9    Address Line 2
10    SQLCHAR    0    50    ""    10    Address Line 3
11    SQLCHAR    0    50    ""    11    Address Line 4
12    SQLCHAR    0    50    ""    12    Address Line 5
13    SQLCHAR    0    10    ""    13    LookUpCode
14    SQLCHAR    0    40    ""    14    Filler 1
15    SQLCHAR    0    11    ""    15    Postnet Barcode
16    SQLCHAR    0    16    ""    16    Planet Barcode
17    SQLCHAR    0    23    ""    17    Filler 2
18    SQLCHAR    0    1    "\r\n"    18    Field16
19    SQLNUMERIC    0    0    ""   0    Done
20    SQLDATETIME    0    0    ""    0    Done Time
21    SQLNUMERIC    0    0    ""   0    StackZone
22    SQLNUMERIC    0    0    ""   0    StackOp

Now when running the following command....

xp_cmdshell 'bcp jetnet..j300 in "\\Orbitty\Depts\DP\ALAN\example.txt" -f"\\Orbitty\Depts\DP\ALAN\example.fmt" -Usa -Ppassword'

I get the following return...

output                                                                                                                                                                                                       --------------------------------------------------------------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file


Any help will be greatly appreciated.


Thanks,

Alan


0
Comment
Question by:AlanJDM
  • 8
  • 7
15 Comments
 
LVL 69

Expert Comment

by:Callandor
ID: 10723634
Shouldn't these

19    SQLNUMERIC    0    0    ""   0    Done
20    SQLDATETIME    0    0    ""    0    Done Time
21    SQLNUMERIC    0    0    ""   0    StackZone
22    SQLNUMERIC    0    0    ""   0    StackOp

have non-zero column numbers, like your other columns?
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10723674
I tried this...

19    SQLNUMERIC    0    0    ""   19    Done
20    SQLDATETIME    0    0    ""    20    Done Time
21    SQLNUMERIC    0    0    ""   21    StackZone
22    SQLNUMERIC    0    0    ""   22    StackOp

and got the same error. The reason I set them to 0 was based on this snipet I found in a tutorial...

You need to create a format file to properly load this. In this case, look at the following format file (which you should copy if you want to run this example):

6.0
5
1    SQLCHAR    0    9    ","    1    cust_id
2    SQLCHAR    0    0    ""    0    cust_name
3    SQLCHAR    0    20    ","    3    city
4    SQLCHAR    0    2    ","    4    state
5    SQLCHAR    0    10    "\r\n"    5    zip

You have to change the server column number for cust_name to 0. This indicates to BCP that the data is not being passed in this file. You also have to reset the field length of this column to 0 and avoid specifying a row terminator.

The tutorial I am reffering to is http://www.ii.metu.edu.tr/~is503/library/TeachYourself_SQLServer65_21Days/ch09/ch09.htm#Heading6


Thanks,

Alan

0
 
LVL 69

Expert Comment

by:Callandor
ID: 10723942
Ok, I would say move the row terminator in 18

18    SQLCHAR    0    1    "\r\n"    18    Field16

to the last field (22).
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10724069
I tried that and got the same error. My thought for putting the \r\n in 18 is because the last field that is actually in the datais 18. I then tried it without the last for fields in the format file and got the same error.

My fear is that because it is not liking the host-column number it isn't even looking at the rest of the format file, which would explain why no matter what changes I make the error stays the same.


Alan
0
 
LVL 69

Expert Comment

by:Callandor
ID: 10724348
Your format file should reflect what you data file actually contains, so you have tried the original format file w/o items 19, 20, 21, and 22, and you have changed the second row to 18?
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10724397
Yes, I did try that and got the same error. The reason I put those fields in the format file is because the tutorial made it a point to include an example of a table that had more columns defined than there are in the data and they did include the extra field in the format file.


In the first example, you are going to load a table from a data file that doesn't have as many columns of data as the table. Your data file looks like this (comma delimited):

cust_id,city,state,zip_code

For instance, two sample rows might look like the following:

1,Seattle,WA,98102
2,Bellevue,WA,98004

You need to create a format file to properly load this. In this case, look at the following format file (which you should copy if you want to run this example):

6.0
5
1    SQLCHAR    0    9    ","    1    cust_id
2    SQLCHAR    0    0    ""    0    cust_name
3    SQLCHAR    0    20    ","    3    city
4    SQLCHAR    0    2    ","    4    state
5    SQLCHAR    0    10    "\r\n"    5    zip

You have to change the server column number for cust_name to 0. This indicates to BCP that the data is not being passed in this file. You also have to reset the field length of this column to 0 and avoid specifying a row terminator.


Alan


0
 
LVL 69

Expert Comment

by:Callandor
ID: 10724426
Are you not using a field delimeter?  I have always used one myself.  If you can test a sample row with comma-delimited data, that may work.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Expert Comment

by:Callandor
ID: 10724437
I think the combination of non-existent fields and no field delimeter is confusing BCP.
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10724442
I can test for the sake of knowing if it works or not, but either way that doesn't help me because the data I need to make this work with is not comma delimited. I will give it a shot though and let you know the results.


Alan
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10726271
ok, well I have made some progress but I now have another issue. My first problem was because I had my row delimiter incorrect...

"\r\n"

needed to be "\l"

because I am using a file created on a unix server and is delimited with single line feeds.

Anyway, now I get a "command successful" from the xp_cmdshell but it doesn't actually do anything. My tabel is empty after running it and I don't get any kind of messages at all. If I run it from the command line it executes, but doesn't return any message at all. I even added an error log but it is empty.

Here is where I am at...

xp_cmdshell 'bcp jetnet..j300 in "\\Orbitty\Depts\DP\ALAN\example.txt" -f"\\Orbitty\Depts\DP\ALAN\example.fmt" -e"\\Orbitty\Depts\DP\ALAN\error.log" -Usa -Ppassword'

7.0
21
1     SQLCHAR       1    100    ""      1    TextLine1
2     SQLCHAR       1    100    ""      2    TextLine2
3     SQLCHAR       1    100    ""      3    TextLine3
4     SQLCHAR       1    100    ""      4    TextLine4
5     SQLCHAR       1    100    ""      5    TextLine5
6     SQLCHAR       1    100    ""      6    TextLine6
7     SQLCHAR       1     50    ""      7    AddressLine1
8     SQLCHAR       1     50    ""      8    AddressLine2
9     SQLCHAR       1     50    ""      9    AddressLine3
10    SQLCHAR       1     50    ""     10    AddressLine4
11    SQLCHAR       1     50    ""     11    AddressLine5
12    SQLCHAR       1     10    ""     12    LookUpCode
13    SQLCHAR       1     40    ""     13    Filler1
14    SQLCHAR       1     11    ""     14    PostnetBarcode
15    SQLCHAR       1     16    ""     15    PlanetBarcode
16    SQLCHAR       1     23    ""     16    Filler2
17    SQLCHAR       1      1    "\l"   17    Field16
18    SQLNUMERIC    0      0    ""      0    Done
19    SQLNUMERIC    0      0    ""      0    DoneTime
20    SQLNUMERIC    0      0    ""      0    StackZone
21    SQLNUMERIC    0      0    ""      0    StackOp


Any ideas?
0
 
LVL 69

Expert Comment

by:Callandor
ID: 10726557
Does moving the "\|" delimeter to item 21 make a difference?  When bcp is working correctly, you should get a record count display every 2000 records, I think.
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10726832
Well, I did try that and it does seem to run longer now, but still finishes with a successful messag but nothing is uploaded to the table.



Alan
0
 
LVL 69

Expert Comment

by:Callandor
ID: 10726992
Does your version of bcp require -S servername as an argument?
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10727264
Well, good news... I got it working. The change I made to get it to work makes no sence to me, but I am just glad it works as my boss was about to pull the plug on this whole project.

The second column of the config file is described as the "prefix length" and based upon a section of the tutorial I was working with I had set all char fields to 1...

"The prefix length determines how much space is reserved to indicate how long this particular field is. For many types, this can be 0 bytes. For char or varchar data, this can be 1 byte long. For text or image datatypes, the default length is 4 bytes"

I don't know what made think to try this, but what I did is set them all to zero and voila - works great.

This is what finaly worked...

xp_cmdshell 'bcp jetnet..j300 in "\\Orbitty\Depts\DP\ALAN\example.txt" -f"\\Orbitty\Depts\DP\ALAN\example.fmt" -e"\\Orbitty\Depts\DP\ALAN\error.log" -Usa -Ppassword'

7.0
17
1     SQLCHAR       0    100    ""      1    TextLine1
2     SQLCHAR       0    100    ""      2    TextLine2
3     SQLCHAR       0    100    ""      3    TextLine3
4     SQLCHAR       0    100    ""      4    TextLine4
5     SQLCHAR       0    100    ""      5    TextLine5
6     SQLCHAR       0    100    ""      6    TextLine6
7     SQLCHAR       0     50    ""      7    AddressLine1
8     SQLCHAR       0     50    ""      8    AddressLine2
9     SQLCHAR       0     50    ""      9    AddressLine3
10    SQLCHAR       0     50    ""     10    AddressLine4
11    SQLCHAR       0     50    ""     11    AddressLine5
12    SQLCHAR       0     10    ""     12    LookUpCode
13    SQLCHAR       0     40    ""     13    Filler1
14    SQLCHAR       0     11    ""     14    PostnetBarcode
15    SQLCHAR       0     16    ""     15    PlanetBarcode
16    SQLCHAR       0     23    ""     16    Filler2
17    SQLCHAR       0      1    ""     17    Field16
18    SQLNUMERIC    0      0    ""      0    Done
19    SQLNUMERIC    0      0    ""      0    DoneTime
20    SQLNUMERIC    0      0    ""      0    StackZone
21    SQLNUMERIC    0      0    "\l"    0    StackOp


Thanks for your help. I will of course award you the points for this because you have certainly earned them sticking with me on this all day long, but if you can offer any information that could make me better understand that "prefix length" parameter, I would certainly appreciate it.

Thanks again for the help.


Alan



0
 
LVL 69

Accepted Solution

by:
Callandor earned 500 total points
ID: 10727319
I think this has the answer for you: http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/impt_bcp_13.htm

When bulk copying data to SQL Server, the prefix length is the value specified when the data file was created originally. If the data file was not created with bcp, it is unlikely that length prefix characters exist. In this instance, specify 0 for the prefix length.

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now