Re: sql2k Collation problems.

Hello,

I suggest that you refer to the following information:

Mixed Collation Environments
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_da_1pwz.asp

You may want to refer to the "Collations and tempdb" section:

*The tempdb database is built each time SQL Server is started, and has the
same default collation as the model database, which is typically the same
as the default collation of the instance. If you create a user database and
specify a different default collation than model, the user database has a
different default collation than tempdb. All temporary stored procedures or
temporary tables are created and stored in tempdb, which means that all
implicit columns in temporary tables and all coercible-default constants,
variables, and parameters in temporary stored procedures have different
collations than comparable objects created in permanent tables and stored
procedures.*

I test it on my side:

1. Run Sp_helpdb tempdb

Collation=SQL_Latin1_General_CP1_CI_AS

2. Run Sp_helpdb mycoll

Collation=Latin1_General_CI_AS

3. Create a temporary table:

create table #temptb (cola varchar(10))

use tempdb
go
sp_help #temptb

Collation=SQL_Latin1_General_CP1_CI_AS

Therefore I think if we didn't explicit specify the collation of the
temporary table, it will use the collation of the tempdb database.

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


.

Relevant Pages

  • Re: Distributor Properties Error
    ... What is the collation for the model db and tempdb. ... Director of Text Mining and Database Strategy ... as it seems to create my Distribution database correctly. ...
    (microsoft.public.sqlserver.replication)
  • Re: Is it possible to change tempdb?
    ... > Is it possible to define another database to function as the tempdb ... no, it's not possible unless you reinstall the SQL Server 2000 instance, as ... tmpdb collation is inherited from the system databases setting and is the ... you can not define an "alternative" user defined tempdb database... ...
    (microsoft.public.sqlserver.server)
  • Re: sql2k Collation problems.
    ... > Mixed Collation Environments ... > *The tempdb database is built each time SQL Server is started, ... > different default collation than tempdb. ... All temporary stored procedures ...
    (microsoft.public.sqlserver.setup)
  • Re: COLLATION Von TEMPDB aendern
    ... > ich muss die collation von der TempDb aendern mit den befehl ... das man eine system db nicht aendern kann. ... tempdb hat immer die COLLATION der master DB. ... Next by Date: ...
    (microsoft.public.de.sqlserver)
  • Re: Using "Table" variable
    ... Does not SQL Server create and manage it in the memory? ... > Table variables are actually created in TEMPDB, ... > collation from that database by default. ...
    (microsoft.public.sqlserver.programming)