Temp procedure messes up collation

Question

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

Maybe you should change your server's collation to SQL_SLOVAK_CP1250_CI_AS? — Denis Rubashkin, Dec 08 '17 at 10:29
@DenisRubashkin will try, but seems like a nuke option. Could be other databases running on the server at the same time with different collations. — Vladislav Zalesak, Dec 08 '17 at 10:32

Michał Turczyn · Answer 1 · 2017-12-08T10:35:33.580

0

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

edited Dec 08 '17 at 10:35

answered Dec 08 '17 at 10:25

Michał Turczyn

32,028
14
47
69

Thx but nope, would say the collate comes too late, after the param gets passed to the procedure, it gets 'truncated' to Latin1 collation – Vladislav Zalesak Dec 08 '17 at 10:27
True, but the temp procedure stores into a normal, non-temp, table and there the collation is ok. – Vladislav Zalesak Dec 08 '17 at 10:47
Temporary objects are held in master db, which has latin collation. Without `#` you create this procedure in one of your databases, which (most likely) has slovak collation. Thus the difference. – Michał Turczyn Dec 08 '17 at 10:51
I understand that, the question is, how can i really use tempdb (and mostly temp procedures) with different databases that have differetn collations. – Vladislav Zalesak Dec 08 '17 at 11:25
Then, in procedure, you have to explicitly collate. – Michał Turczyn Dec 08 '17 at 12:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/160791/discussion-between-vladislav-zalesak-and-michal-turczyn). – Vladislav Zalesak Dec 08 '17 at 12:30

Temp procedure messes up collation

1 Answers1