Database collation issue

[techipa]

Hi All,

The collation level on tempdb and the user database is different. So to avoid the collation error issue I changed the code of SP where it creates the #table with the text field to 'default collation' and that resolved the issue.

But some of the SP does not have create #table. They use the following statement where the table is created automatically in tempdb database with the text columns. I can not change the collation level to deafult DB here they do not use Create table syntax. When I deploy this SP I get the collation error.

select col1, col2
into #Temptable
from table1

Can anyone tell me how to resolve this issue?

Thanks in advance
-techiPA

 

[gmmastros]

Select into is causing your problem. To fix this issue, create the temp table and then change the select into => Insert Into.

Create Table #TempTable(Col1 Int, Col2 VarChar(20))

Insert Into #TempTable(Col1, Col2)
select col1, col2 
[!][s]into #Temptable[/s][/!]
from table1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

George,

Is there any other way than creating the temp table to resolve this issue?

Thanks,
-techiPA

 

[gmmastros]

You could try....

select col1 [!]Collate Database_Default[/!], 
       col2 [!]Collate Database_Default[/!]
into   #Temptable
from   table1

You can only specify the collate clause when the data type is a string (varchar, nvarchar, char, nchar, text & ntext).

I've never used this method, but I think it will work. I suggest you try it and let us know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

The first option did not work. So I modified the SP to create the # table and then insert the records to the # table. But still I get the error while deploying the proc.

I changed the collation of all the text columns in #table.

I will have to see if I am doing something wrong.

Thanks,
-techiPA