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 (http://blog.SQLAuthority.com)
pinaldave
respecte sir can you tell me the code for selecting every n”th row of a table in sqlserver2005
Saravanan,
You can use Row_Number() function to perform your request.
http://search.pinaldave.com/?domains=blog.sqlauthority.com&q=row_number%28%29&sitesearch=blog.sqlauthority.com&sa=Google+Search&client=pub-6502880871475562&forid=1&ie=ISO-8859-1&oe=ISO-8859-1&cof=GALT%3A%23008000%3BGL%3A1%3BDIV%3A%23336699%3BVLC%3A2266cc%3BAH%3Acenter%3BBGC%3AFFFFFF%3BLBGC%3A336699%3BALC%3A2266cc%3BLC%3A2266cc%3BT%3A000000%3BGFNT%3A336699%3BGIMP%3A336699%3BFORID%3A11&hl=en
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
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’
[...] 11, 2008 by pinaldave Previously I have written two different ways to find database collation SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS. One of blog reader jwwishart has posted another method for doing the [...]
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.