SQL_Latin1_General_CP1_CI_AS - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

SQL_Latin1_General_CP1_CI_AS

Author

Topic

sql252
Starting Member

4 Posts

Posted - 2015-03-17 : 11:16:17

Hello everyone, please guide me fix the error. I have a DB, name: My_Db and I
SELECT name, collation_name FROM sys.databases WHERE name = 'My_Db'.
Results: name: My_Db; collation_name: SQL_Latin1_General_CP1_CI_AS

But I Execute a user store procedure, example: usp_NhatKyXuatDa. I encountered error
"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Vietnamese_CI_AS" in the equal to operation."

I don't know how to repair it. Help me! Thanks all.

QuyDo

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-17 : 12:06:11

It is possible that one or more columns in a table in your database are not using the default database collation and instead is using Vietnamese_CI_AS. You can find the collation of all the columns in your database using this query (ignore the ones where the collation is null), or use appropriate where clause to pick up only the Vietnamese_CI_AS.

SELECT 
    t.Name AS TableName,
    c.Name ColumnName,
    c.collation_name
FROM 
    sys.columns c 
	INNER JOIN  sys.tables t ON c.object_id = t.object_id
WHERE 
    t.is_ms_shipped = 0
ORDER BY
	c.collation_name

Once you find those columns, look for the usage of those columns in your stored proc. Usually it will be in a where clause or on a join. There, you will need to force the collation on the left and right sides to be the same. For example, if you have something like this:

WHERE
	t1.Column1 = t2.Column2

change it to one of the following (depending on which collation you want to use)

WHERE
	t1.Column1  COLLATE Latin1_General_CS_AS = t2.Column2 COLLATE Latin1_General_CS_AS

WHERE
	t1.Column1 COLLATE Vietnamese_CI_AS = t2.Column2  COLLATE Vietnamese_CI_AS

You don't have to collate the side on which you already have the collation you are trying to force to.

sql252
Starting Member

4 Posts

Posted - 2015-03-21 : 23:56:15

I did as instructed but all columns in my DB do not has collation_name Vietnamese_CI_AS.
How else does not?
Thanks for help!

QuyDo

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-23 : 10:30:36

You have to debug the stored procedure code to find the the line where it is reporting the error. The Vietnamese_CI_AS collation does not have to be in a base table. It can be from any virtual table or cursor such as from a view, or output of a function, or perhaps could even be coming from another database if the stored procedure links to tables in another database. Some quick checks might give you some ideas:

This would tell you if there are any objects that use the word Vietnamese_CI_AS in its definition.

select * from sys.objects where object_definition(object_id) like '%Vietnamese_CI_AS%'

This would tell you if the server collation is something other than what you expect

SELECT SERVERPROPERTY('Collation')

If none of that gives you any clue, then open up the code for the stored procedure and run it to see where the error is coming from and go from there.

sql252
Starting Member

4 Posts

Posted - 2015-03-29 : 04:39:46

Oh, my database's SERVERPROPERTY has value Vietnamese_CI_AS, this is the problem that caused the error. How can I do to change it?
Thanks!

QuyDo

Kristen
Test

22859 Posts

Posted - 2015-03-29 : 10:57:19

Personally I wouldn't change it (if it is wrong then change it, but all existing data columns that set to it by default will, now, be wrong ...)

We put a COLLATION on EVERY (without fail!) Table Create statement that includes (N)Char/Varchar columns, in particular temporary tables in SProcs etc. Doesn't then matter if the database is moved a different server where different collations are normal, or some tables/descrete logic is "reused" elsewhere in an environment that has different collations.

Our TEST environment has an unusual collation and case-sensitive to catch any instances that we miss in DEV.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-30 : 09:13:26

I agree with Kristen - changing the server collation is something that should be done rarely, and with utmost care - if ever. Changing it will affect all databases on that server, so it can have many unintended consequences.

The way collation works in SQL server is in a hierarchical model. The server collation is at the top, followed by the table column collations. If no column collation is specified when creating a table, the column gets the collation of the database. If no collation is specified when you create a database, it inherits the server collation. Given that your server collation is Vietnamese_CI_AS and the database collation is Latin1_General_CS_AS, you will need to be explicit in your queries (as in the example I had posted in an earlier post).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources