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

Missing window when using multiple monitors

I use a laptop with a second LCD screen. By default, Windows assumes that these are side by side. However, my LCD screen sits above the laptop screen in the office.

On other occassions, I use a projector which I logically position to the left or right of the laptop screen depending on the real layout of the room.

Sometimes an Application can become lost in Windows XP. While the laptop is in dual screen usage, an application is placed on the second screen. Later, when using a single screen (or after moving the logical screen locations around), the application window remains at the original second screen location.

The solution is to move the application back to the visible screen. This is a bug in some but not all applications.

To fix:

1) Press ALT + TAB to select the invisible application
2) Press ALT + Space to select the application’s menu
3) Press M to select the Move menu item
4) Move the mouse & the application immediately snaps to the location of the mouse pointer on the screen.

For other solutions, see http://bensqanda.blogspot.com/2005/10/lost-window.html

SQL Server MVP Award

On Sunday, October 1st, 2006, Microsoft informed me that I was a Most Valued Professional (http://www.microsoft.com/mvp) for my efforts in the Irish SQL Server community.

I was out of the office for the entire week for various reasons and did not discover this honour until I checked my mail late last night.

It is both an honour to be included in this very small group of internationally recognised & respected leaders of the SQL Server community, and also a challenge to meet the expectations that accompany this award.

User Groups do not get created on their own. The SQL Server Ireland User Group would not have succeeded without the help of our tireless Microsoft contact, Colm Torris (http://blogs.technet.com/colmt/) as well as my fellow members: Rhys Kerrigan (Team DBA), David Houston (SBS Ireland), & Jarlath O’Grady.

I would like to thank those who put my name forward for this award – in particular, Colm and Chuck Boyce. They have set the standard for me to follow and provided assistance without complaint.

%d bloggers like this: