Jun 15 2006

Obscure SQL Server Errors

Published by at 1:49 pm under Programming

This is an old article and the information contained within it may be out of date, not reflect my current views and/or contain broken links. If you feel this article is still valid and requires updating, you can use the contact form to let me know. However, I make no guarantee that it will get updated.

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:

SQL Server Diagram ErrorDatabase 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 jobCreate 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:

Remove object failedYou 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.

9 responses so far