BCP problems (SQL2K)

Question

Post reply

BCP problems (SQL2K)

Markus2006

SSChasing Mays

Points: 616
More actions
March 10, 2008 at 2:36 am

#184868

I'm working on application that uses BCP to copy data between servers and I encountered few problems that maybe someone could help me with.
1. One of the tables that has to be transferred between server has column named "Desc", which is a restricted word in SQL Server. BCP during import fails with "incorrect syntax near 'Desc'" error. I solved the problem by renaming the column to 'Desc_Tmp' before the import and renaming it back to 'Desc' after the import. Any better ideas?
2. Several rows in one of tables contain tab character in varchar column. They cause BCP import failure since tab is a default delimiter for field when data in exported in character mode. I solved the problem by specifying field delimiter as "|\t" (which is not likely to happen). So the problem is solved but it raises a question whether a tab character is allowed in a column. It Enterprise Manager when I choose Open Table I get non-printable character (square) in these records.
3. One of the tables that is imported has column name starting with a digit 3 e.g.: "3Weeks". BCP fails to import this table with "Incorrect syntax near '3'" error. If I rename the column to e.g. "Tmp_3Weeks" everything works fine. It seems to me that such column names are allowed in SQL server but not allowed in BCP.
Any comments?
Marek

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Marek, can you post the BCP command as you're using it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Markus2006 SSChasing Mays Points: 616 More actions · Answer 2

Here it is (for the problem #3 - but they are generated automatically so the are the same in all cases):

bcp [dbname].[dbo].[tablename] in "inputfile.dat" -c -C 1250 -t "|\t" -r "|" -T -E -h"TABLOCK" -b50000

You can see that the field and record terminating characters are specified (they are the same when I export data). The batchsize is specified to avoide huge transactions where large tables are imported.

And of course I provide correct names for database, table and input file.

EDIT: it seems that the forum cannot display correctly special characters. The -r option contains three characters: pipeline, slash and "n". The forum displays only the first character (is it a bug in the forum?)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Since you're going from SQL Server TO SQL Server, you can solve the tab problem by using the "Native" format for both the export and the import. It's also a bit faster performance wise.

So far as the column names go, dunno what to tell you other than what you're already doing... that's the bad part about folks not following recommended naming standards... nothing to do there but what you're already doing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Oh yeah... the tabs are being correctly stored... they never show up correctly in a grid mode... if you want to verify they are there, look at the data in the text mode. Tabs in a column usually mean the same thing as having a CSV column... both are usually a bad idea because it violates the rules of normalization.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Markus2006 SSChasing Mays Points: 616 More actions · Answer 5

In the first version I was using native format but than I found out that national charactes are not transferred correctly despite the fact that I provided correct code page. Then I found out that -c option solves the problem (at least of the characters). I also wanted to avoid creating format files (on the fly).

I will try -n option once again.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Markus2006 (3/10/2008)
In the first version I was using native format but than I found out that national charactes are not transferred correctly despite the fact that I provided correct code page. Then I found out that -c option solves the problem (at least of the characters). I also wanted to avoid creating format files (on the fly).
I will try -n option once again.

If you've already proven that it doesn't do what you want, why would you try it again?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Markus2006 SSChasing Mays Points: 616 More actions · Answer 7

It's because the servers have collation set up in a different way; on one server it's on server level, and on the other it's on database level. And also something tells me that it should work (at least I don't see a reason why it does not work).

Marek

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Markus2006 (3/10/2008)
I'm working on application that uses BCP to copy data between servers and I encountered few problems that maybe someone could help me with.
1. One of the tables that has to be transferred between server has column named "Desc", which is a restricted word in SQL Server. BCP during import fails with "incorrect syntax near 'Desc'" error. I solved the problem by renaming the column to 'Desc_Tmp' before the import and renaming it back to 'Desc' after the import. Any better ideas?
2. Several rows in one of tables contain tab character in varchar column. They cause BCP import failure since tab is a default delimiter for field when data in exported in character mode. I solved the problem by specifying field delimiter as "|\t" (which is not likely to happen). So the problem is solved but it raises a question whether a tab character is allowed in a column. It Enterprise Manager when I choose Open Table I get non-printable character (square) in these records.
3. One of the tables that is imported has column name starting with a digit 3 e.g.: "3Weeks". BCP fails to import this table with "Incorrect syntax near '3'" error. If I rename the column to e.g. "Tmp_3Weeks" everything works fine. It seems to me that such column names are allowed in SQL server but not allowed in BCP.
Any comments?
Marek

1. Use a pair of views (one on the source and one on the target) to rename the columns.

2. don't know

3. It's a limitation of how bcp is importing the data. Not likely to be fixed any time soon. This also can be solved with a pair of BCP_Export/Import views to rename the column(s).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Markus2006 SSChasing Mays Points: 616 More actions · Answer 9

Barry,

Thank for the hint with import view. In fact I'm exporting data by means of a view. In this case I don't even need to rename the 'Desc'. It is sufficient if I add brackets to the column: [Desc].

I did not know the I can import data into a view on SQL2K. I just checked it and it works. So it is looks like I need to create a temporary view on the destination server with the column defined as: [Desc] AS [Tmp_Desc] and it will work. The problem is that I already wrote the code to rename the columns (and checked that it works). But the solution with a view is nicer so most probably I will change the code.

Thanks again,

Marek

Markus2006 SSChasing Mays Points: 616 More actions · Answer 10

In the meantime I checked that BCP import works fine with views with columns renamed from e.g.: [Desc] to [Tmp_Desc_Tmp] - so renaming columns in the database is not required. During the export I also use views but in this case renaming columns is not required (I just add square brackets to every column).

Marek