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

avatar image

Phil Factor
4.2k 8 23 21

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
show all comments (comments are locked)
10|1200 characters needed characters left

2 answers: 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

avatar image

Phil Factor
4.2k 8 23 21

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

declare @sql varchar(8000) select @sql = 'bcp "select * from Database_name.dbo.tablename" queryout H:\Tempfile_DTU_proc\test1.csv -c -t, -T -S' + @@servername exec master..xp_cmdshell @sql

more ▼

answered Mar 01 at 12:16 PM

avatar image


(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.

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: 4382 times

Last Updated: Mar 01 at 10:14 AM

Copyright 2016 Redgate Software. Privacy Policy