August 30, 2012 at 8:46 am
I'm trying to figure out why I'm getting collation conflicts like the following:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (SQL-42000)
Statement(s) could not be prepared. (SQL-42000)
The command structure is:
MERGE INTO target_db.target_schema.target_table AS T USING (SELECT col1 FROM OPENROWSET (BULK file.txt, FORMATFILE=format.fmt, FIRSTROW = 2) AS Z) AS S ON (T.col1=S.col1) WHEN MATCHED THEN ...
So basically I'm doing a merge into a table using a set of fields that I get from a bulk operation. The format file for the bulk operation hardcodes the collation as the _AS collation. The database has been created with the _AS collation and all the columns have the _AS collation. However, the server default is the _AI collation. Is that somehow messing this up?
Unfortunately we don't have the ability to change the query itself since it's hardcoded into a program, so I'm trying to see if I can fix this by altering the DB instead. Any idea at least what is going on?
August 30, 2012 at 9:44 am
Check the collation of the target column - it may be different from the database default.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2012 at 1:59 pm
The columns are all the correct collation, as is the format file. I wonder if, since the server default collation is the _AI one, the BULK INSERT is using the tempdb with an _AI collation for the openrowset and therefore encounters that collation problem.
Could that be an issue? Any other ideas?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply