Jun 15 2006
Obscure SQL Server Errors
I’m working on a large system at the moment, and recently we upgraded the database from Microsoft SQL Server 2000 to Microsoft SQL Server 2005. At the time everything seemed to go according to plan with no changes required to our code as we’re not using any of the more advanced features to SQL Server. However, three months down the line and I’ve started noticing things that didn’t get migrated with the upgrade.
Its these things that I’m trying to bring back that are throwing up some rather obscure SQL Server error messages. I should first go back and give an overview of the upgrade process so as to provide a setting in which these errors are ocurring. The installation of SQL Server 2005 went smoothly and was installed along side SQL Server 2000, the plan was that it should be possible to do a database transfer between the two and SQL Server 2005 will handle upgrading all the fiddly bits. This didn’t quite happen as I was only able to attempt the transfer on a remote machine and I didn’t have sufficient permissions. Instead we stopped SQL Server 2000, attached the database files to SQL Server 2005 and uninstalled SQL Server 2000, as far as I could tell this was a perfectly acceptable way of doing it.
The first signs of a problem was when I tried to access the database diagrams in one of our databases and I received the following error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files pages of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
This seemed straight forward and I set to work sorting out permissions… only they were already sorted! So what now? The help provided by microsoft was less that helpful and I couldn’t figure it out. In the end I gave up and went to make a coffee. When I came back some kind sole had sorted it out. It turns out that in order for the SQL Server 2005 management tools to add the require objects the database must be set to SQL Server 2005 compatability mode in the Database Properties dialog box. After finding out what the problem was I did find this Microsoft page: http://support.microsoft.com/?kbid=910228. What is strange is that of the three databases that we disconnected from SQL Server 2000 and attached to SQL Server 2005 only one of them was still set to SQL Server 2000 compatability mode, even though the same process had been used for all three.
The second problem came when I noticed that our sessions table was starting to get rather full. We’re supposed to have an SQL Server Agent job that periodically (every 3 hours) to clear old sessions out of the table so that it only contains recent session details. However it seems that during the migration to SQL Server 2005 this job went AWOL and nobody noticed. The stored procedure was still in the database its just that there was nothing to call it. Howver, when I tried to add a new SQL Server Ajent job I got the following error message:
Create failed for Job ‘Session Undertaker’ (Microsoft.SqlServer.Smo)
…
Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’; column does not allow nulls. INSERT fails.
Now obviously from the message I know exactly where to sort out the problem!!! Err… google? Nope, no answers there. In the end I finally noticed that when I create the new job on my desktop machine the SQL Server Management Tool tried to be clever and set the owner of the job to being the account I was logged into my machine as. In a simple network setup that I guess that would normally be fine, however in this network I log into one network domain but the SQL Server machine is a member of another network domain and therefore knew nothing about my domain login account. All it required was to change the job owner account to being my account on the other network domain.
This, however wasn’t the end of the cryptic error messages. When I finally saved the SQL Server Agent job, with the correct owner account, I got the following error message:
You cannot perform operation Remove on an object in state Existing. (Microsoft.SqlServer.Smo)
Ok… what object? And why if it exists can’t you remove it? Surely it would be more of a problem if the object you tried to remove didn’t exist? Once again the Microsoft help was about as helpful as using a sledgehammer for brain sujery. I still have no idea what the cause of the error was, although I have been able to replicate it, and as the job seems to be running correctly I don’t see the point in worrying about it at this stage.
I’m having tthe same problem migrating from 2000 to 2005. When you say “All it required was to change the job owner account to being my account on the other network domain.” how exactly did you do this? (email me back direct please…)
Hi Dan, sorry about the delay… I’ve been on holiday and had shed loads to catch up on (and I only have teh internets at work
)
To answer your question, I don’t know. I tried it using the Management Studio but it wouldn’t allow me. After setting the DB to SQL Server 2005 compatability the diagramming worked and so I didn’t need to change the owner.
I believe there may be a stored procedure to do it but in the end I didn’t require it and so I don’t know what its called and I haven’t tried it.
Thanks a ton for the information. I had the exact same problem with creating a job script from another domain. Your tip worked like a champ.
Your solution to the database diagram problem was spot on and ended my morning of cursing and swearing – thanks
I’ve been there. I know the pain you must have been suffering! And that is why I posted this blog
I had the exact same problem with the database diagrams after importing several ms2000 databases (via the file import feature, since the move or copy wizard didn’t do the job, just as you describe) – but it didn’t help changing to 2005 compatibility mode as you write. The solution in my case was to change the database to 2005 compatibility mode AND to run the following query:
ALTER AUTHORIZATION ON DATABASE::database_name TO database_owner
Only after running the query the management tool would allow me to view or create database diagrams from the imported databases.
Thank you so much for this! I was getting the “Cannot insert the value NULL into column ‘owner_sid’” message and tearing my hair out. It was the exact same issue – the SQL Server just didn’t recognise the account I was using.
The “You cannot perform operation Remove on an object in state Existing.” error have arised when job has more then 16 steps, i think.