question

Phil Factor avatar image
Phil Factor asked

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?
sql-server-2005bcp
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
Can you please let us know, what happens if you use -w OR -C RAW?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
can you bcp from a view?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Also, can you please specify the AdventureWorks DBs and Server collations for both SQL Server instances? Thanks.
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
The -w and the -CRAW make no difference I'm afraid
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
I Get.... SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation; SQLCollation ------------------------- Latin1_General_CI_AS (1 row(s) affected) in 2005 it is SQLCollation ------------------------------ SQL_Latin1_General_CP1_CI_AS
0 Likes 0 ·
Show more comments
Phil Factor avatar image
Phil Factor answered
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!
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
hmmm..this is where I wanted to get that environment information. So you are saying it was pointing to an older bcp version?
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
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.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
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.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Please respond to my last comment whenever you have the time. It would be so nice of you. Thanks.
0 Likes 0 ·
namrta avatar image
namrta answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.