October 15, 2008 at 4:27 pm
Hi everyone
A developer asked for the collation on a server to be changed from Latin1_General_CI_AS to ...AI. There are 150+ jobs related to the single user database on this server. I did the following
Back up msdb
Detach user database
Uninstall SQL
Reinstall SQL w/ new collation
Attach user database
Restore msdb
This caused the following error to appear when I tried to view the properties of any database, "Cannot resolve the collation conflict between Latin1_General_CI_AI and Latin1_General_CI_AS in the equal to operation. Running, "select name, collation_name, compatibility_level from sys.databases" returned all of the database, except msdb, in the new collation.
All of the jobs were running, but when I tried to look at the properties everything except owner was blank. There were no steps, schedules, etc.
From here I rebuilt the system database via the setup.exe and got everything on the same collation. I had scripted out all of the jobs to files prior so I then ran those 150+ scripts. Again all the jobs are running, but I can't see any details in the Properties of the job. If I generate a script, I can see the steps and schedule.
What happened here? Did changing the collation have something to do with this?
Thanks,
Tim
PS I didn't check the jobs prior so I don't know if they were always blank. The developer is in Europe and and want to get this working before they wake up over there 🙂
October 16, 2008 at 6:52 am
Changing the collation started the problem because the msdb you restored was created using the original collation.
As far as why you are still having problems I have no idea. Never changed the collation of an existing instance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 23, 2010 at 5:19 pm
Peters i has the same issue and looking for a solution. I restored another msdb database of matching collation and deleted everything in there and trying to move all jobs and pkgs from older server to this, its easy to move jobs but having diffuculty in moving pkgs saved in msdb. Let me know if you find any solution.
August 24, 2010 at 4:54 pm
Sorry Tara,
That was almost two years and one job ago. My memory doesn't reach back that far. Best of luck.
Tim
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply