BCP queryout collation problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP queryout collation problem

Author

Topic

leth
Starting Member

3 Posts

Posted - 2010-03-15 : 11:30:58

Hello,

What is the collation that should be used when using bcp queryout to a CSV file?

As some background, I'm setting up a nightly batch file to export data to a CSV file. Most of it is working, however I'm stuck on a field that has a replace() operation which is causing a collation conflict.

Here is a sample of the view I'm using to define the query:

CREATE VIEW [InventoryLevels] AS
SELECT
ItemNumber AS [PN]
,ISNULL((SELECT SUM(Quantity) FROM ItemInventory AS a WHERE a.ItemNumber=i.ItemNumber and a.Stockroom='WH'),0) AS [INVENTORY]
,LEFT(REPLACE(CONVERT(nvarchar,getdate(),113),' ',''),9) + ' ' + ltrim(right(CONVERT(nvarchar,getdate(),100),7)) + ' EST' AS [TIMESTAMP]
FROM ItemMaster AS i WHERE ItemNumber LIKE '%FI' and ItemStatus='A'

Note: I'm using the replace statement to format the Timestamp in the specific way. The resulting format is required for the other system which this csv file is being used for.

My bcp command looks like this:

bcp "SELECT * FROM DBName..InventoryLevels" queryout C:\temp\inv.csv -T -t, -c

The error I get is:

SQLState = 37000, NativeError = 446
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve collation conflict for replace operation.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

The database collation is Latin1_General_BIN (required by our MRP software), and the server collation is SQL_Latin1_General_CP1_CI_AS.

I've tried adding "COLLATE" statements to the Timestamp column, but it does not fix the bcp error. The view works just fine in query analyzer. The BCP statement works if I remove the Timestamp column.

I’ve been pulling my hair out on this for a couple hours now; any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 11:41:18

try dumping this into a temporary table with collation setting as database_default and then use it in bcp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

leth
Starting Member

3 Posts

Posted - 2010-03-15 : 13:22:46

Ok, I tried doing this two different ways through the batch file, both of which are still giving me collation conflicts on the replace() statement.

The first method was:

osql -Q "select * INTO DBName..TempInv FROM DBName..InventoryLevels" -E
bcp "SELECT * FROM DBName..TempINV" queryout C:\temp\inv.csv -T -t, -c
osql -Q "drop table DBName..TempINV" -E

The second method was:

osql -Q "CREATE TABLE DBName..[TempINV] ([PN] [varchar] (30) COLLATE database_default NULL, [INVENTORY] [int] NULL, [TIMESTAMP] [varchar] (19) COLLATE database_default NULL) ON [PRIMARY]" -E
osql -Q "INSERT INTO DBName..TempINV ([PN],[INVENTORY],[TIMESTAMP]) (select [PN],[INVENTORY],[TIMESTAMP] FROM DBName..InventoryLevels)" -E
bcp "SELECT * FROM DBName..TempINV " queryout C:\temp\inv.csv -T -t, -c
osql -Q "drop table DBName..TempINV" -E

In the first method the bcp command fails. In the second method the second osql command fails ("Cannot resolve collation conflict for replace operation." in both cases)

I'm really unsure why this won't work. It works fine through query analyzer, but not from the command line.

Is there a specific collation type that should be used when exporting to CSV files?

What would cause the other varchar fields ([PN]) export properly, but not the one with the replace() statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 13:53:49

try manually specifying collation for each character data columns using COLLATE database_default

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

leth
Starting Member

3 Posts

Posted - 2010-03-15 : 14:06:55

I modified the second command in the second method, but still receive the same error on this command:

osql -Q "INSERT INTO DBName..TempINV ([PN],[INVENTORY],[TIMESTAMP]) (select [PN] COLLATE database_default,[INVENTORY],[TIMESTAMP] COLLATE database_default FROM DBName..InventoryLevels)" -E

Is this the correct way to do what you suggested?

Could it be some problem with collating the "space" character in my replace command within the view?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources