BCP Error: Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations

When I try to BCP out the contents of Adventureworks in SQL Server 2005 with this command ...

bcp [AdventureWorks].[Sales].[Store] out "D:\MyDirectory\Sales_Store.bcp" -SPhilsTestServer\SQL2005 -n -T

I get the error

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations

but only with the STORE table. The rest works fine. Whereas the identical command

bcp [AdventureWorks].[Sales].[Store] out "D:\MyDirectory\Sales_Store.bcp" -SPhilsTestServer\SQL2008 -n -T
works fine with the SQL Server 2008 instance. I've googled for an answer to this and all I can find is a lot of other very frustrated people posting on forums the same question and getting a variety of answers, none of which helps any of them. Is this another of Microsoft's 'stealth' bugs or is there a simple answer?
more ▼

asked Nov 24, 2011 at 04:39 AM in Default

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

Can you please let us know, what happens if you use -w OR -C RAW?
Nov 24, 2011 at 05:20 AM Usman Butt
can you bcp from a view?
Nov 24, 2011 at 05:21 AM Fatherjack ♦♦
Also, can you please specify the AdventureWorks DBs and Server collations for both SQL Server instances? Thanks.
Nov 24, 2011 at 05:30 AM Usman Butt
The -w and the -CRAW make no difference I'm afraid
Nov 24, 2011 at 05:32 AM Phil Factor

I Get....

    SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

    (1 row(s) affected)

in 2005 it is

Nov 24, 2011 at 05:38 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I'm going to answer my own question, since I've now discovered what was wrong (it got worse with even more weird errors when trying to BCP in). if you get weird BCP errors, it is worth first doing a

BCP -v

This will tell you the version of BCP that you're using. It should be the latest one since it is (supposedly) backward-compatible. Every time Microsoft installs a new instance, it alters the PATH to get the directory of the utilities first in line for being scanned for the exe file. This assumes that you always install the latest version of SWL Server last. The problem here is that with SQL 2000 there were no such things as instances, so you install a later version first and then you might then pop in SQL Server 2000 for testing backward-compatibility.

If you decide to add a retro instance such as SQL 2000 or 2005 on a Dev machine, you will need to alter the PATH environment variable so that it reads the latest version first before going all historical. Otherwise you get wierd BCP errors like this.

I've been trawling the internet looking at the answers when this same problem comes up on the internet. I never found this silly and obvious answer given!

So beware. If you get BCP or BULK COPY errors on a machine that don't make any sense, check the version number of the BCP you're using first!
more ▼

answered Nov 25, 2011 at 04:09 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

hmmm..this is where I wanted to get that environment information. So you are saying it was pointing to an older bcp version?
Nov 25, 2011 at 04:39 AM Usman Butt
Yes. In effect. Because it adds that path to the collection of paths in the PATH environment variable and always adds it BEFORE the other SQL Server paths, Windows finds the first BCP.EXE in the BINN directory of the most recently installed instance. They assume that any new instance will be of a later version of SQL Server but it ain't necessarily so.
Nov 25, 2011 at 09:27 AM Phil Factor
I am itching my head why then SQL 2008 instance was able to execute with the same old BCP? Please shed light on this as well.
Nov 26, 2011 at 06:36 AM Usman Butt
Please respond to my last comment whenever you have the time. It would be so nice of you. Thanks.
Nov 28, 2011 at 04:40 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 24, 2011 at 04:39 AM

Seen: 3291 times

Last Updated: Nov 24, 2011 at 04:39 AM