CODEWITHSUNDEEP
sql-serverstored-proceduresprocedurecollationtempdb
Vladislav Zalesak
Vladislav Zalesak

Reputation: 713

Temp procedure messes up collation

Issue: i have a procedure (simplified), created by .NET application:

create procedure #SaveData
@InValue varchar(128)
as
begin
    insert into Production.TargetTable (PREFIX_Value) select @InValue
end

Problem is, the database uses SQL_SLOVAK_CP1250_CI_AS collation. TempDB uses default SQL_Latin1_General_CP1_CI_AS collation.

Problem simplified:

-- this doesnt work, returns RTC
create procedure #SaveData
@InValue varchar(128)
as
begin
    select @InValue
end

-- this doesnt work, returns RTC
create procedure #SaveData
@InValue varchar(128)
as
begin
    select @InValue collate SQL_SLOVAK_CP1250_CI_AS
end

-- this does work, returns ŘŤČ
create procedure SaveData
@InValue varchar(128)
as
begin
    select @InValue
end

Which causes that instead for instead of test string ŘŤČ, RTC is saved. When i remove the # from the Procedure name and dont create it as a temp procedure, everything works.

Now, one fix found to work, is to change the param type from varchar to nvarchar. But this would be a lot of work (many different procedures). Is there any global approach that could work?

Thank you and have a nice day

Upvotes: 0

Views: 143

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37500

The problem is in column collation. Any value passed to it will have its collation changed to column collation:

declare @table table(txt varchar(10) collate SQL_Latin1_General_CP1_CI_AS)
insert into @table values ('ŘŤČ' collate SQL_SLOVAK_CP1250_CI_AS)
--result will be changed, even with explicit collation
select * from @table
go

declare @table table(txt varchar(10) collate SQL_SLOVAK_CP1250_CI_AS)
insert into @table values ('ŘŤČ')
--correct output
select * from @table
go

So you have to change collation in column:

alter table TABLE_NAME alter column TextCol varchar(...) collate SQL_SLOVAK_CP1250_CI_AS

Upvotes: 0

Related Questions