Is it possible to change tempdb?

Hi,
Is it possible to define another database to function as the tempdb instead
of the default tempdb?
I'm using sql2000
Thanks,
I.P.hi I.P.,
"I.P." <lafafa@.yahoo.com> ha scritto nel messaggio
news:cr8l1d$84e$1@.news.iucc.ac.il
> Hi,
> Is it possible to define another database to function as the tempdb
> instead of the default tempdb?
> I'm using sql2000
>
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
same as master...
you can not define an "alternative" user defined tempdb database...
but, as SQL Server 2000 supports multiple collations both at database level
and column level, you should not have this issue...
why do you need a different collation?
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Andrea,
Thanks for the answer.
I need a different collation as my sql setup is Latin1 and my specific DB is
Hebrew. While using temp tables in my DB I have a problem as the tempdb
doesn't understand Hebrew... Is there a solution for such problem?
Thanks,
I.P.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:33q6rrF41pmbiU1@.individual.net...
> hi I.P.,
> "I.P." <lafafa@.yahoo.com> ha scritto nel messaggio
> news:cr8l1d$84e$1@.news.iucc.ac.il
>> Hi,
>> Is it possible to define another database to function as the tempdb
>> instead of the default tempdb?
>> I'm using sql2000
> 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
> same as master...
> you can not define an "alternative" user defined tempdb database...
> but, as SQL Server 2000 supports multiple collations both at database
> level
> and column level, you should not have this issue...
> why do you need a different collation?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>|||We have a similar issue on some systems where the collation at installation
has been incorrectly set relative to user databases, causing join errors
with temp tables. You can rebuild the system databases, as the other posters
point out, or when the temp tables are created, you can specify an explicit
column-level collation, thus overriding the tempdb default.
Rgds,
Paul Ibison|||In addition, rather than specifiying a specific collation you can specify
database_default as the collation for columns in a temp table and they will
inherit the current database collation rather than using that if tempdb
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oi8B48M8EHA.2572@.tk2msftngp13.phx.gbl...
> We have a similar issue on some systems where the collation at
> installation
> has been incorrectly set relative to user databases, causing join errors
> with temp tables. You can rebuild the system databases, as the other
> posters
> point out, or when the temp tables are created, you can specify an
> explicit
> column-level collation, thus overriding the tempdb default.
> Rgds,
> Paul Ibison
>|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oi8B48M8EHA.2572@.tk2msftngp13.phx.gbl...
> We have a similar issue on some systems where the collation at
> installation
> has been incorrectly set relative to user databases, causing join errors
> with temp tables. You can rebuild the system databases, as the other
> posters
> point out, or when the temp tables are created, you can specify an
> explicit
> column-level collation, thus overriding the tempdb default.
> Rgds,
> Paul Ibison
>