Solved

BCP problem

Posted on 2004-03-31
15
1,394 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 99
datetime in sql 6 31
Add '#' to end of file 2 31
SQL log file keeps growing despite getting successful log backups 4 35
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

23 Experts available now in Live!

Get 1:1 Help Now