This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many reader asked me how to find collation of current database. There are two different ways to find out SQL Server database collation.
1) Using T-SQL (My Recommendation)
Run following Script in Query Editor
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
ResultSet:
SQLCollation
————————————
SQL_Latin1_General_CP1_CI_AS
2) Using SQL Server Management Studio
Refer the following two diagram to find out the SQL Collation.
Write Click on Database
Click on Properties
Reference : Pinal Dave (https://blog.sqlauthority.com)
27 Comments. Leave new
respecte sir can you tell me the code for selecting every n”th row of a table in sqlserver2005
Hi to all…
I am new to SQL. here is my doubt.
I have a stored procedure in that how can i find the collation errors.
Example: i am having a query like this, it is a stored procedure in that how can i find collation error is there or not.
My SP is like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[CC_BTCC_WRITE_QUEUE_ROUTING_T0_XML](@ReqID bigint, @tmpxml nvarchar(4000) out,@QueueID nvarchar(15),@ID as int)
AS
BEGIN
–Write Strategy Routing Rules
/**** STRATEGY Time Based Routing****/
Declare @tmpTBRoute nvarchar(1000)
Declare @QueRoutingID nvarchar(100)
Declare @QueRoutingType nvarchar(100)
EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,N”,@ID
DECLARE GETSTRATEGY_ROUTING_CURSOR CURSOR FORWARD_ONLY
FOR
SELECT N”, ID, RoutingType
FROM dbo.WRK_CC_Routing
WHERE ParentID = @QueueID
AND ReqID = @ReqID
ORDER BY RoutingMediaType, RoutingType, Behaviour,Name
–OPEN
OPEN GETSTRATEGY_ROUTING_CURSOR
–GET initial
FETCH NEXT FROM GETSTRATEGY_ROUTING_CURSOR
INTO @tmpTBRoute ,@QueRoutingID, @QueRoutingType
–Iterate
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,@tmpTBRoute,@ID
/**** STRATEGY Queue Routing Forward Numbers ****/
EXEC CC_BTCC_WRITE_STRATEGY_ROUTING_FORWARD_NUMBERS_T0_XML @ReqID , @tmpxml out, @QueRoutingID, ‘Standard’, @ID
/*======= STRATEGY Queue Routing Forward Numbers ======*/
/**** STRATEGY Queue Routing Search List Items ****/
EXEC CC_BTCC_WRITE_STRATEGY_ROUTING_SEARCH_ITEMS_T0_XML @ReqID, @tmpxml out, @QueRoutingID , @QueRoutingType , @ID
/**** STRATEGY Queue Routing Search List Items ****/
–GET Next
EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,N”,@ID
FETCH NEXT FROM GETSTRATEGY_ROUTING_CURSOR
INTO @tmpTBRoute ,@QueRoutingID, @QueRoutingType
END
CLOSE GETSTRATEGY_ROUTING_CURSOR
DEALLOCATE GETSTRATEGY_ROUTING_CURSOR
EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,N”,@ID
/*======= STRATEGY Time Based Routing ======*/
END
When i am executing this SP i am getting commands completed successfully. But some collation error is in this SP.
In this SP how can i find the collation error is occurs or not.
Help me friends.
Waiting for good reply..
The following also works. Little bit less typing but otherwise its returns the same result
SELECT collation_name FROM sys.databases WHERE name = ‘AdventureWorks’
Hi,
How can I find collation of my csv file.
Thanks
how can i change the Current Collation via T-SQL ?
Hi,
Could you please tell me how to change the collation of an existing database to UTF-16 for working with Cognos.
If the solution is in series of steps it would be helpful.
Thanks
Quite helpful.
Nice discussion about Collation.
I’m getting scalar variable errors when I am declaring the statements can an incrrect collation cause this?
Here’s a way to get the server collation (since the server can have a different collation than the db):
select serverproperty(‘collation’)
how can i list out all the DBs and there respective collation settings for an instance?
Hello Pankaj,
Use the below query:
select name, collation_name, * from sys.databases
Regards,
Pinal Dave
Thanks for your post it was very helpful!
Respected Sir,
how to find whether the installed version of sql server in the local system is client version or Server Version.
SELECT @@VERSION
In sql 2005 the script is working fine.
select name, collation_name, * from sys.databases
Could you please tell me how can i find in sql 2000.
Hi Pinal,
I’ve quite different problem. In my production DB I’ve recently modified size of one varchar(30) to varchar(256) and then after the related SPs stops functioning and after research work I’ve found that it is because of collation problem.
So, can you please tell me what execectly it is, what to do in order to solve this issue and do I need to each and every SPs that uses this table..
Thanks.
How to change Default Collation to UTF_8 in database
Hi…
How can i Find what type of database installed in my system (eg: sql server2005, 2008, oracle,..)
Hi,
How to check collation information at cloumn level in table, Is there any query?
Regards,
Alok
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = ‘U’
AND name = )
AND name =
Is there an easy way to change the collation of the server?
I installed SQL2008R2 to my new laptop but it hasn’t the collation I need for my databases. On my old laptop it was ‘Latin1_General_CI_AS’ and on my new one it is ‘SQL_Latin1_General_CP1_CI_AS’.
All my old databases I’ve attached, but when I create a temp table I get error messages that the collations are not the same. So SQL can’t compare the varchar fields that I use to make a join.
Regards,
Frank Meijer