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 Dahl
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
>
|||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!TK2MSFTN GP10.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!TK2MSFTN GP10.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...
>
>
|||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!TK2MSFTN GP10.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...[vbcol=seagreen]
> 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!TK2MSFTN GP10.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...
for a
>
>
|||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!TK2MSFTN GP10.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...
> for a
>
>

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...
>
>|||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...
for a[vbcol=seagreen]
>
>|||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...
> for a
>
>

Code Page

I read text files in ASP on server side, then try to enter data into a
database in SQL Server. The files are in ibm852 (1250) coding - at least
that's the code page with which they are shown properly when putting the
lines read on the output.
However, when I enter them into the SQL Server (currently simply by setting
a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
it through the connection such as oConn.Execute sSQL), the special
characters (Hungarian) are all changed to meaningless characters, such as
'hell:' for 'hell' etc.
The texty columns are of type varchar(n). I also tried nvarchar, but nothing
has changed.
So, how can I set the appropriate code page in SQL Server or transform the
strings so that the special characters don't get messed up when entered?
Agoston,
Try changing the column type to nvarchar(n) and executing this query:
INSERT INTO T1(6, N'hell')
Perhaps you simply forgot to type the N required to signify a Unicode
string.
Steve Kass
Drew University
Agoston Bejo wrote:

>I read text files in ASP on server side, then try to enter data into a
>database in SQL Server. The files are in ibm852 (1250) coding - at least
>that's the code page with which they are shown properly when putting the
>lines read on the output.
>However, when I enter them into the SQL Server (currently simply by setting
>a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
>it through the connection such as oConn.Execute sSQL), the special
>characters (Hungarian) are all changed to meaningless characters, such as
>'hell:' for 'hell' etc.
>The texty columns are of type varchar(n). I also tried nvarchar, but nothing
>has changed.
>So, how can I set the appropriate code page in SQL Server or transform the
>strings so that the special characters don't get messed up when entered?
>
>
|||"Steve Kass" <skass@.drew.edu> wrote in message
news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
> Agoston,
> Try changing the column type to nvarchar(n) and executing this query:
> INSERT INTO T1(6, N'hell')
> Perhaps you simply forgot to type the N required to signify a Unicode
> string.
It doesn't change a thing. The same messy characters are in the db. Any
other ideas?
[vbcol=seagreen]
> Steve Kass
> Drew University
> Agoston Bejo wrote:
setting[vbcol=seagreen]
execute[vbcol=seagreen]
nothing[vbcol=seagreen]
the[vbcol=seagreen]
|||I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
Unicode string? If not, it will not preserve the accented characters.
There ought to be some way to specify that it be Unicode, similar to the
way you do in SQL Server with the N prefix. If that fails to produce
the right result, I'm not sure what could be happening, since Unicode
strings shouldn't be affected by code page settings, but I'd probably
try specifying the 1250 code page somewhere on the ASP page - maybe the
ASP programmers have a better idea.
SK
Agoston Bejo wrote:

>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
>
>
>It doesn't change a thing. The same messy characters are in the db. Any
>other ideas?
>
>
>setting
>
>execute
>
>nothing
>
>the
>
>
>
|||Steve Kass wrote:
> I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
> Unicode string? If not, it will not preserve the accented characters.
> There ought to be some way to specify that it be Unicode, similar to
> the way you do in SQL Server with the N prefix. If that fails to
> produce the right result, I'm not sure what could be happening, since
> Unicode
> strings shouldn't be affected by code page settings, but I'd probably
> try specifying the 1250 code page somewhere on the ASP page - maybe
> the ASP programmers have a better idea.
>
Good thought, but vbscript is unicode by default.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"