December 6, 2007 at 3:54 pm
Hi All
I'm writing an SP that needs to get create a temporary table (to pass data to an SP).
The collation order of the databases on the target systems frequently does not match the collation order of the tempdb (no idea why and I can't change this).
Is there a way to get the collation order of a specific database on the target system and use this to dynamically set the collation order of my temp table's varchar column.
For example, if I get the collation or of the Db 'Orders' at run time and its Latin1_General_CI_AS, can I somehow use this in the create table command for the temp table?
TIA
Mark
December 6, 2007 at 4:04 pm
This is what I learned:
you can create temp tables and specify collation of character fields to current database collation.
collate database_default works nicely, for example
create table #temptable
(
field1 int,
field2 varchar(15) collate database_default
)
if you use character fields in joins to permanent tables, you have to specify collation hint as well:
select t1.field1, t1.field2
from t1 inner join #temptable on t1.field2 = #temptable.field2 collate database_default
evantually, if you use subqueries, you have to specify collation hint too:
select t1.field1
from t1 where field2 in
(
select field2 collate database_default
from #temptable
)
HTH
Piotr
...and your only reply is slàinte mhath
December 6, 2007 at 4:30 pm
Thanks Piotr
I'd discovered that, but for sme reasons the DB's that are installed on the target do not use default collation either. Bit of a wierd setup, but I'm stuck with it 😉
Regards
Mark
December 7, 2007 at 3:56 am
no prob.
does it mean that collate database_default does not work either?
...and your only reply is slàinte mhath
December 7, 2007 at 2:34 pm
it doesn't work as it's using the default and not the collation of the target DB
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply