Hi,
i have several questions about collation sequence i hope you guys will be able to help me with.
First :
If i have a collaction, let's say French_BIN ou SQL_Latin1_General_CP1_CI_AS, how do i know how to understand it ? I mean, is it Case Sensitive, Binary sort, Which page code, ... ?
Second :
I have 2 Databases. One using a collation X another using a collation Y.
I want to export the tables (data + keys) from one Database to another.
As far as i've noticed, not only tables are exported, but so is the collation. The result ? The two Databases appear to be using collation X. Am i missing something ?1. For French collation the code page is default 1252, refer to books online for more information.
BOL refers SQL Server supports having multiple, language-specific copies of the error messages stored in master.dbo.sysmessages. All instances of SQL Server contain the set of English messages. SQL Server is localized, or translated, into French, German, Spanish, and Japanese versions. Installations of localized versions of SQL Server install the translated set of messages in addition to the English set. When SQL Server sends a message to a connection, it uses the localized message if the language ID of the connection matches one of the language IDs present in sysmessages. If there is no message in sysmessages with the same language ID, the English version of the message is sent.
1988-2002 Microsoft Corporation. All Rights Reserved.
2. From SQL 2000 you can have column/table/database wide collation settings. So if you export the X collation to a Y collation database, only exported table will have X collation but not on database. Still it permits Y collation on database level, but X collation on table level.
Refer to books online for all kind of information about Collations.
HTH|||How can I transform the collation of an exported base?
In the properties,we don't have any more access to the list of collation,|||In SQL 2K you can change collation by using ALTER DATABASE... ALTER TABLE ... ALTER COLUMN on the database.|||How can I make the tables inherit of the database properties when I make an alter database?|||If you affect the collation to DATABASE level, the new data will be affected but not the existing data. To do so you should use ALTER TABLE to do on table level.
Do refer to Books online for COLLATION and other information.|||thanks for all
Showing posts with label sequence. Show all posts
Showing posts with label sequence. Show all posts
Friday, February 10, 2012
Collation Sequence Incompatibilities in ODBC
We are getting the following errors when running our application on a new database server that has a default collation sequence different from the server we developed the application on.
We are using SQL_Latin1_General_CP1_Cl_AS on our dev box, but some production boxes may not have this collation sequence since some of our clients are international.
The database itself uses this sequence and show this sequence even on the other server. If we restore the database on a server with this sequence the problem disappears, but that is not always an option.
I want to figure out how to bypass it, by either altering the collation sequence of the database or something else. In short I have to figure out how to make it work in their environment. Any ideas are much appreciated. Thanks. -- Henry :-)
P.S. I checked Microsoft and know that the issue exists. The only article I could find had to do with SMS and their recommendation of installing on a server with a different collation sequence does not resolve my problem. I can't always do that.
Our application is written in Cold Fusion 5.0
============ Error Messages ============
Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve
collation conflict for equal to operation.
Error Occurred While Processing Request
Error Diagnostic Information
System Error &H80004005 (-2147467259).
===================================While this does not directly address the issue (My guess is both installs are 7.0?), perhaps your issue would be addressed by Sql Server 2k features. Sql Server 2k supports collation configurations somewhat differently from 7.0 i.e.(Sql Server 2K should support installing a server instance with a given collation and sucessfully hosting DBs with a variety of different collation configurations). It may be worth your while to test Sql Server 2k against your specific application(s) collation requirements.|||No. Both installs are SQL Server 2000. At least one is service pack 2.|||No. Both installs are SQL Server 2000. At least one is service pack 2.
S1 Then you must know about / have attempted using the tsql collation clause in queries (to force character string expressions which may include columns to apply a particular collation cast)? While one might have to add the clause to numerous queries and tsql statements in application code or withing stored procs, views, etc., the advantage of this approach is that the DB itself is not changed. You might want to isolate some problem code on a test restore configuration to get a better idea of how involved the approach may be.
S2 Another option may be (as you mentioned) to use Alter Table on the tables of the restored DB (on the target) to make it compatible with the applications in the locale where it is restored. Have you tested the results with this approach using a development test install / DB restore setup?
S3 I suggest keeping service packs and hotfixes the same accross installations (if for nothing else, to eliminate added vairables in addressing possible issues).
We are using SQL_Latin1_General_CP1_Cl_AS on our dev box, but some production boxes may not have this collation sequence since some of our clients are international.
The database itself uses this sequence and show this sequence even on the other server. If we restore the database on a server with this sequence the problem disappears, but that is not always an option.
I want to figure out how to bypass it, by either altering the collation sequence of the database or something else. In short I have to figure out how to make it work in their environment. Any ideas are much appreciated. Thanks. -- Henry :-)
P.S. I checked Microsoft and know that the issue exists. The only article I could find had to do with SMS and their recommendation of installing on a server with a different collation sequence does not resolve my problem. I can't always do that.
Our application is written in Cold Fusion 5.0
============ Error Messages ============
Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve
collation conflict for equal to operation.
Error Occurred While Processing Request
Error Diagnostic Information
System Error &H80004005 (-2147467259).
===================================While this does not directly address the issue (My guess is both installs are 7.0?), perhaps your issue would be addressed by Sql Server 2k features. Sql Server 2k supports collation configurations somewhat differently from 7.0 i.e.(Sql Server 2K should support installing a server instance with a given collation and sucessfully hosting DBs with a variety of different collation configurations). It may be worth your while to test Sql Server 2k against your specific application(s) collation requirements.|||No. Both installs are SQL Server 2000. At least one is service pack 2.|||No. Both installs are SQL Server 2000. At least one is service pack 2.
S1 Then you must know about / have attempted using the tsql collation clause in queries (to force character string expressions which may include columns to apply a particular collation cast)? While one might have to add the clause to numerous queries and tsql statements in application code or withing stored procs, views, etc., the advantage of this approach is that the DB itself is not changed. You might want to isolate some problem code on a test restore configuration to get a better idea of how involved the approach may be.
S2 Another option may be (as you mentioned) to use Alter Table on the tables of the restored DB (on the target) to make it compatible with the applications in the locale where it is restored. Have you tested the results with this approach using a development test install / DB restore setup?
S3 I suggest keeping service packs and hotfixes the same accross installations (if for nothing else, to eliminate added vairables in addressing possible issues).
Subscribe to:
Posts (Atom)