CODEWITHSUNDEEP
c#sql-serverstored-procedurescollation
iminiki
iminiki

Reputation: 2573

How can I change a Stored Procedure's Collation in SQL?

In my C# program when I try to run one of my stored procedures, it gives me an exception which is related to a conflict between collations. How can I fix it?

Error Description: "Cannot resolve the collation conflict between "Arabic_CI_AS" and "Latin1_General_CI_AS" in the equal to operation."

Upvotes: 2

Views: 15995

Answers (2)

dean
dean

Reputation: 10098

Collations in SQL Server define set of rules for comparing and sorting strings.

99 times out of 100 this particular error is a result of a cross-database query, with a join or where condition involving 'string' columns from tables in different databases (one of them typically being the tempdb).

You can specify a collation as part of a query, either a named one or the database_default keyword. See https://msdn.microsoft.com/en-us/library/ms184391.aspx for more info.

Just beware that this will practically disable (as far as this particular query is concerned) an index on the dynamically collated column if one exists.

Upvotes: 2

Łukasƨ Fronczyk
Łukasƨ Fronczyk

Reputation: 457

I had the same error when creating sp that had some nvarchar arguments. These arguments were conditionally modified inside of this sp. Here's an example of the code that causes the error same as yours:

create procedure a
    @name nvarchar(128),
as
    if (@name = '')
        throw 51000, 'Empty name', 1;
go

The solution here seems to be defining a collation (the best would be the database_default):

create procedure a
    @name nvarchar(128),
as
    if (@name = '' collate database_default)
        throw 51000, 'Empty name', 1;
go

Upvotes: 2

Related Questions