Re: sql2k Collation problems.

Ok, I see what is happening now, and it's ok for people who have control
over the instance of SQL Server, but when you're a software vendor who
ships pre-configured databases to customers, it's a pain in the.....

-Adrian

"Sophie Guo [MSFT]" <v-sguo@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:zyzS4r4$FHA.1240@xxxxxxxxxxxxxxxxxxxxxxxx
> 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: sql2k Collation problems.
    ... Mixed Collation Environments ... You may want to refer to the "Collations and tempdb" section: ... *The tempdb database is built each time SQL Server is started, ... and parameters in temporary stored procedures have different ...
    (microsoft.public.sqlserver.setup)
  • 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: URGENT : Change TempDB collation
    ... Everytime SQL server is re-started, tempdb takes the collation from model ... Ideally changing the collation of model db should sort the problem. ... And having tempdb collation different from users database can potentially ...
    (microsoft.public.sqlserver.server)
  • Re: Change field collation
    ... constraints and statistics on the columns before you can alter ... There are quite a lot of caveats and you use this script at your own risk. ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.programming)
Loading