0

I have 2 databases (X and Y) on my SQL Server 2008 R2. For some reason the databases and the server have different encodings (SQL_Latin1_general_CP1_CI_AS and Latin1_General_CI_AS) so I'm always facing collate problems when I use temp tables or table variables.

Selecting some info from database Y into the table variable @info

When I try to join both in this way it work really fine:

select d.data_id, i.info_id, i.info_value
from X.aaa.data d
    join @info i on i.serial COLLATE DATABASE_DEFAULT =
        SUBSTRING(d.serial, 17, 10) COLLATE DATABASE_DEFAULT

But the serial length can be different for different products so I want to do it with a variable:

declare @serial_length int = 10;

-- some switch case to set @serial_length to some other value

select d.data_id, i.info_id, i.info_value
from X.aaa.data d
    join @info i on i.serial COLLATE DATABASE_DEFAULT =
        SUBSTRING(d.serial, 17, @serial_length) COLLATE DATABASE_DEFAULT

When I try this the query will just run forever and do nothing.

Is there a workaround for this problem? Or does at least anyone know why this is happening?

UPDATE: Thanks to MitchWheat the collate isn't a problem any more Changing Database Collation and dealing with TempDB Objects

But the SELECT still doesn't work. I'm using SUBSTRING(serial, 17, @serial_length) also in another procedure but without table variables and there it works.

Staeff
  • 4,994
  • 6
  • 34
  • 58
  • http://www.sqlskills.com/BLOGS/KIMBERLY/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx – Mitch Wheat Aug 30 '12 at 08:10
  • No idea, but you could try `SUBSTRING(d.serial COLLATE DATABASE_DEFAULT, 17, @serial_length) ` to see if it makes a difference – podiluska Aug 30 '12 at 08:12
  • @MitchWheat Thank you. Now i don't have to use COLLATE DATABASE_DEFAULT, but again it only works when i write 10 direct. – Staeff Aug 30 '12 at 08:23
  • 2
    How much data should be returned? What do the two execution plans look like? When it runs "forever" do you see any waits if polling `SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id > 50`? Does adding `OPTION (RECOMPILE)` make any difference? – Martin Smith Aug 30 '12 at 08:35
  • @MartinSmith this are the waits http://bullsheet.de/T/SQLWAITINFO_EtOFppv5dOBv/ and `OPTION(RECOMPILE)` didn't make a difference – Staeff Aug 30 '12 at 09:02

0 Answers0