Cannot resolve collation conflict for equal to operation

Server: Msg 446, Level 16, State 9, Procedure WF_SP_RETRIEVE_ALL_NEW_CASES, Line 233
Cannot resolve collation conflict for equal to operation.

OK – I got this one today – and this is the 3rd time, at least! So it is time to produce my slant/rant. There are plenty of web sites out there the tell one who to programmatically deal with this error.

However, the most likely reason for this to occur is that a database solution has been migrated from SQL Server 7 to either SQL Server 2000 or 2005.

SQL Server 7 used a collation 1252 (dictionary sort, case insensitive, accent sensitive). However, later versions of SQL Server use Latin1_General (dictionary sort, case insensitive, accent sensitive) which is compatible with the Windows 2000 & higher operating system.

The development solution would be to change all references between system & user databases as follows:-

SELECT * FROM #temp t JOIN user_table u ON t.char_set1 collate Latin1_General_BIN = u.char_set2

Remember to change the italics to the appropriate collation set. This needs to be done for every join that is between databases of different collations. However, the usual reason is that temporary tables are used and the tempdb collations differ to the appropriate user database.

If all the user databases need to remain at the original collation setting (as mine did), then it might be easier to change the collation of the system databases. But, this requires the setup program to be re-run. All user databases are lost, & all system databases are re-initialised – thus, removing all logins, jobs & packages.

In advance of the change, locate the installation disks & copy the directory that contains the system mdf & ldf files (Disk1 z:\x86\data) to a local hard disk (z:\source). Remove the write protection on the files in this new directory.

When you are ready to make the changes, follow the steps below. The process will take less than an hour for small to medium systems (excluding the backup times)
1. Backup all databases – need I say more.
2. Open each dts packages (found in Enterprise Manager -> Group Name -> Server Name -> Data Transformation Services -> Local Packages) as a ‘Structured Storage File’ on the hard disk
3. Script off all jobs (found in Enterprise Manager -> Group Name -> Server Name -> Management -> SQL Server Agent Jobs -> Jobs) . Highlight each job and right click -> All Tasks … -> Generate SQL Server script, provide a valid filepath & filename and click OK to save to hard disk
4. Script off all users
5. exec sp_detach database_name
6. Run the rebuildm.exe found in z:\Program Files\Microsoft SQL Server\80\Tools\Binn
7. Enter the “z:\source” directory created above into the “Source directory containing Data Files:” text box.
8. Click on Settings and, on the Collations Setting dialogue box, choose the SQL Collations bullet. Scroll down & choose “Dictionary order, case-insensitive, for use with 1252 Character Set.”. Click OK to return to main Rebuildm screen.
9. Click Rebuild. A warning dialogue asks to confirm. Click yes to continue.
10. Wait…..
(if it generates an error, it is likely the source data files were still write-protected)
11. Run logins jobs
12. sp_attach the databases
13. Recreate jobs
14. Resave dts packages
15. Test

OK?

Advertisements
Leave a comment

2 Comments

  1. >Thanks for your info. Very useful and spot on for me.

    Reply
  2. >Beautiful. Thanks so much! Completely solved my issue.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: