September 22, 2010 at 7:36 am
Hi,
what is the impact of changing the database collation of the server and DB in sql server 2005
and how to change the server and DB collation
both should be same or different.
September 22, 2010 at 8:27 am
In short the collation tells the server how to sort character data (char, varchar, etc ...).
Changing the server collation is not easy, quickest way is to either reinstall the server with the correct collation or rerun the setup.exe with the /ACTION=RebuildDatabase and specify a new collation (a quick search in google should give you all the details you need).
Changing the collation of a DB is "easy": ALTER DATABASE MyDB COLLATE NewCollation
The db will use the new collation for new object created after the alter is run, but it'll not change already created objects ... so it's easy but not very usefull 🙂
After that you'll have to change the collation of every single character based column in your DB that is involved in an index (ALTER TABLE xxx ALTER COLUMN yy COLLATE NewCollation).
So you'll have to drop all the indexes involved (including primary keys and foreign keys), change the collation of each character column and recreate the indexes (which can take some time).
All that stuff makes you think twice before doing it.
There is an "easier" way to change the collation of a DB though, but it involves some data transfer (so not that good for huge DBs):
Create a new DB in the correct collation, recreate all objects without using the COLLATE keyword (so all the objects have the correct collation) and then transfer all the data from old db to new db.
This might seems like a real pain, but when you have a tool like RedGate SQL COMPARE, recreating the new DB is quick and easy, you "just" have to do a bunch of BCP (or use an SSIS package) to fill the new DB.
Having a DB with a different collation than the server is not a problem as long as temporary table are created in the correct collation or queries involving tempdb use the COLLATE keyword correctly.
That usualy means the application has been developped with the problem in mind.
September 22, 2010 at 10:32 pm
hi,
Thank you very much for the reply
actually one of our production server(Cluster)
Error is cannot resolve the collation conflict
db collation is "SQL_Latin1_CPI_CI_AS"
and server collation is Latin1_general_CI_AS" ,
is the equal to the operation
please suggeat the solution to fix this issue
September 23, 2010 at 7:18 am
Can you find the query generating the error?
September 26, 2010 at 10:53 pm
It would seem that your collation clash is between the SQL database collation and that of a field stored by the Windows operating system.
For example, if you compare a database field (queried from a table in SQL Server) and you compare that field with one queried from let's say a result from the Windows File Index (using SCOPE() and OPENQUERY), you would be comparing two heterogeneous collations.
You can get around this (without changing any collation setting) by issuing a collation statement in your query.
SELECT [PR].Field
FROM WindowsTable AS [PR]
INNER JOIN dbo.SQLTable AS [X]
ON [X].[Field] = [PR].[Field]
Collate Latin1_General_CI_AS
WHERE [X].Field IS NOT NULL
/* Note: the WindowsTable, could be something like:
SELECT C.*
FROM OPENQUERY(LOCALTFSCATALOG,'SELECT FileName, Path, Create, Access, Rank, HitCount
FROM SCOPE() WHERE CONTAINS(''FORMSOF (INFLECTIONAL, FieldName1, FieldName2)'')')[C]
WHERE PATINDEX('%.xml', [FileName])>0
This query finds all xml files (in your Windows File Index) that contain variations on the
search criteria, FieldName1 and FieldName2.
The results coming back from the above query will have a collation of the oprerating system, i.e.
Latin1_General_CI_AS and you would have to use the explicit collation hint in the query to equate
the two fields on colation for comparison purposes.
*/
Don't focus too much on the OPENQUERY, suffice to say that it is the progenitor of your Windows collated field, causing you the trouble.
However said, you should find the query that is causing the error first, then apply the remedy and you should be back at sipping your coffee soon:w00t:
September 27, 2010 at 12:18 am
Drikus Roux (9/26/2010)
It would seem that your collation clash is between the SQL database collation and that of a field stored by the Windows operating system.
Or more likely the server collation is one of those two, the database collation is another and he's joining or comparing to a temp table (which will have the server's collation). Both of those are perfectly valid collations that a SQL server or database (or column) could have. iirc, newer versions of SQL are likely (depending on the server's regional settings) to have the Latin1_General collation as a server default as it's the default during installation.
If that's the case, easy fix. Specify COLLATE statements when you create the temp table to explicitly define what collation it should be using.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2010 at 12:40 am
Hi Gail,
It seems to be a popular problem, i.e. to have the tempdb created with collation Latin1_General_CI_AS, and another by the other collation SQL_Latin1_CPI_CI_AS and then having collation issues.
Re-reading the post, your diagnosis is most likely correct.
The cause being as a result of running a query against the file system is a bit "far fetched" in the run of the mill SQL day;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply