Collation name Compatibility_183_406_30003?

Hello!
I may see the collation name Compatibility_183_406_30003 specified for a
database. What is that?
Best regards,
Henrik DahlProbabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>|||Adriano is correct that this results from an upgrade of a SQL 7.0
instance that has non-standard collation settings. What you have is
called a "compatibility collation", and is described in more detail in
270042 INF: Description of SQL Server Compatibility Collations
(http://support.microsoft.com/?id=270042).
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Wed, 30 Jun 2004 08:46:21 +0200
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
X-Tomcat-NG: microsoft.public.sqlserver.server
Probabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>|||Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation (normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > Hello!
> >
> > I may see the collation name Compatibility_183_406_30003 specified for a
> > database. What is that?
> >
> >
> > Best regards,
> >
> > Henrik Dahl
> >
> >
>
>|||There are three general ways to solve the issue you are facing:
1. When you join a user db table to a temp table, include "COLLATE
database_default" as a modifier for the join predicate. For example:
SELECT * FROM usrtbl
INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
2. Change the collation of the existing database to match the
instance-level default collation. The transfer approach you mention is
the currently the easiest way to do this.
3. Ensure that the instance default collation matches the database's
collation. This can be done with rebuildm.exe for most collations, but
unfortunately not for compatibility collations. If you want a SQL 2000
instance to use a compatibility collation for tempdb you must either
perform an unattended install and specify the collation name in the .ISS
(setup answer file), or upgrade an existing SQL 7.0 instance so that the
new SQL 2K instance inherits the 7.0 instance's compatibility collation.
Option #1 is the best long-term solution. Once you make this change,
your code will be insulated from the problem from that point forward and
you won't have to care whether the user database collation matches the
tempdb collation at your customer sites.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
<#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
<RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Thu, 1 Jul 2004 09:32:37 +0200
Lines: 92
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
X-Tomcat-NG: microsoft.public.sqlserver.server
Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation
(normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to
the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > Hello!
> >
> > I may see the collation name Compatibility_183_406_30003 specified
for a
> > database. What is that?
> >
> >
> > Best regards,
> >
> > Henrik Dahl
> >
> >
>
>|||Thank you very much Bart,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:eKbtWL8XEHA.1008@.cpmsftngxa06.phx.gbl...
> There are three general ways to solve the issue you are facing:
> 1. When you join a user db table to a temp table, include "COLLATE
> database_default" as a modifier for the join predicate. For example:
> SELECT * FROM usrtbl
> INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
> 2. Change the collation of the existing database to match the
> instance-level default collation. The transfer approach you mention is
> the currently the easiest way to do this.
> 3. Ensure that the instance default collation matches the database's
> collation. This can be done with rebuildm.exe for most collations, but
> unfortunately not for compatibility collations. If you want a SQL 2000
> instance to use a compatibility collation for tempdb you must either
> perform an unattended install and specify the collation name in the .ISS
> (setup answer file), or upgrade an existing SQL 7.0 instance so that the
> new SQL 2K instance inherits the 7.0 instance's compatibility collation.
> Option #1 is the best long-term solution. Once you make this change,
> your code will be insulated from the problem from that point forward and
> you won't have to care whether the user database collation matches the
> tempdb collation at your customer sites.
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> <RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Thu, 1 Jul 2004 09:32:37 +0200
> Lines: 92
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Thanks Bart, we are experiencing this issue on many customers.
> After the upgrade to the SQL 2k, any "join" with a temporary table results
> in a collation conflict, thus blocking the application.
> Currently, I solve the issue by
> 1) Build a new native SQL2k database with the preferred collation
> (normally
> I use default one)
> 2) Script the database structure WITHOUT indexes/fkeys/indexed views and
> using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
> on the db created previously.
> 3) DTS import/export from the source SQL7 migrated db to the native SQL2k
> one.
> 4) Script the keys, indexes, views, stored, and so on and apply them to
> the
> SQL2k native one.
> I cannot use Transfer SQL objects because sometimes this leads to errors
> related to foreign keys not correctly managed by the DTS itself. However,
> this was true on the original version of SQL and on the SP1 and 2. I don't
> know if current SP solves the issue.
> Could you please check and let me know if another way to solve the
> "collation conflict" issue exists without using the steps above?
> Thank you very much,
> Regards,
> Adriano
> "Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
> news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> > Adriano is correct that this results from an upgrade of a SQL 7.0
> > instance that has non-standard collation settings. What you have is
> > called a "compatibility collation", and is described in more detail in
> > 270042 INF: Description of SQL Server Compatibility Collations
> > (http://support.microsoft.com/?id=270042).
> >
> > HTH,
> > Bart
> > --
> > Bart Duncan
> > Microsoft SQL Server Support
> >
> > Please reply to the newsgroup only - thanks.
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > --
> > From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> > References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> > Subject: Re: Collation name Compatibility_183_406_30003?
> > Date: Wed, 30 Jun 2004 08:46:21 +0200
> > Lines: 24
> > X-Priority: 3
> > X-MSMail-Priority: Normal
> > X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> > Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> > Newsgroups: microsoft.public.sqlserver.server
> > NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> > Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> > Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> > X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> > Probabily you restored a database from the SQL7 format to a SQL 2k.
> >
> > Collations are managed differently.
> >
> > For this reason, in order to avoid collation conflicts you may want to
> > rebuild your database changing the collation to a SQL 2k default one.
> >
> > Ciao
> >
> > "Henrik Dahl" <q@.q.q.q> wrote in message
> > news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > > Hello!
> > >
> > > I may see the collation name Compatibility_183_406_30003 specified
> for a
> > > database. What is that?
> > >
> > >
> > > Best regards,
> > >
> > > Henrik Dahl
> > >
> > >
> >
> >
> >
> >
>
>