Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
icw
Constraint Violating Yak Guru
378 Posts |
 Posted - 2007-05-22 : 12:27:34
|
| hi All, I am using GoldMine which is hosted on a MSSQL Server.Got an error when I tried to move (via the COPY/MOVE Records feature) 5 contacts into my "Deletes" database General SQL error. Native SQL error: 446 1: Server message: Cannot resolve collation conflict for equal to operation. I checked the collation of all databases and the "Deletes" database did indeed have a different collation to the main one. So I used the following statement to change it so it was the same as the main one ALTER DATABASE GMSM_DEL COLLATE SQL_Latin1_General_CP1_CI_AS Now they are the same collation... I am still getting the same error. Can anyone help ? I suspect it might be due to the collation of the tempdb although I am not sure and would appreciate some expert advice. The tempdb and model databases are set to latin_general_CI_ASas opposed to SQL_Latin1_General_CP1_CI_AS.Do i need to do something to make them all the same?What are the implications?PS - i am in no way an expert and lay my career into your hands forthwith.Thanks ICW |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-22 : 12:33:36
|
| Did you check table or even column collation in deletes db? Since only new object will be created with new collation as I understand. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 12:35:02
|
| If you are creating a Temp table make sure you put a COLLATE statement on any column defined as char/varchar/text (or the "N" variants of those). Then it won't matter what collation TEMPDB has.Kristen |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2007-05-22 : 17:21:08
|
| can I just use that same alter statement on all the databases to make sure they are all set the same? Forgive me if this is an ignorant question. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-22 : 22:55:35
|
| Can't do that on system dbs. |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2007-05-23 : 03:57:02
|
| Thanks for all you answers so far.I have noticed that although i used the 'alter database' statement to change the del database to use the different collation all the tables are still appearing to me to be set to use "SQL_Latin1_General_CP1_CI_AS" I found this out by going to Query Analyzer and right-clicking and choosing"Script object to new window as Create"So what this says to me is that the database is now set to use one collation - SQL_Latin1_General_CP1_CI_AS - and he tables are set to use another - latin_general_CI_ASHave I understood this wrong?Can I change the tables to use the new collation?if so what is the syntax? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-05-23 : 05:04:49
|
| backup your database.create new tables with the proper collation you need (on each text column).write + execute a script to transfer the data from the old tables into the new tables.rename the old tables out of the way.rename the new tables to match the original old names.test your application.backup your database.after a period of time, remove the out-of-the-way tables.backup your database. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-23 : 05:23:27
|
| I'd make a brand new database, along the lines that Andrew says.My thinking is that you may have trouble with "rename the old tables out of the way" if they have FKs and some other constraint names that will "argue" with the "create new tables" part.In SQL 2000 Enterprise Manager you can generate a script of all the objects in your database. Create that to a file, and edit it (Query Analyser or even Notepad will do).The Script will contain COLLATE statements for ALL the Text stuff in the database. Thus you can Find & Replace all those with your preferred collation.Use the script to make a brand new database. Transfer all the data from the Old database with DTS. Test the new database against your application (or: rename the old database "out of the way" and rename the new one to match the old one's name).NOTE: If you have Foreign Keys in your database then that may cause some problems with DTS. The easier way around this is to MOVE the script stuff that creates the Foreign Keys to a second script file. So the database is initially created without them, you DTS the data in, and THEN run the Foreign Key script.Kristen |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2007-05-23 : 08:10:19
|
| Thanks a lot guysthat's worked! |
|
|
|
|
|
|
database collation issue