SQL Server error 17310

April 14, 2015 at 20:33

The problem

Recently I had to migrate a SQL2005 database to a SQL2008R2 instance. So I picked up a copy of the database and restored in onto a test server. After restoring the database I changed a few settings (change compatibility mode, set page verify to checksum, changed recovery model) and ran dbcc checkdb with datapurity. Everything finished without errors. I opened the database in the object explorer to see If everything looked ok before I continued with testing. When I opened the stored procedure folder it took a few seconds and… error!

ServerError

I opened the errorlog and saw this.

ErrorLog

This looked quite serious. If the error 17310 tells you to call PSS it must mean that this is not a normal user mistake. Before calling support I decided to look on the internet to see if I could find any additional info about this error. I could not find a page describing this scenario. There is fix for this error if you receive it during start up. It can be found here.
I knew that the database on the SQL 2005 server was in use and that there were no reports of anything strange there. So I connected to this instance, tried to open the stored procedure and the same error was raised.

I decided to see if a list with the procedures would help me. I captured the query that SSMS executes to get a list of procedures (you can find the query here). I copied the query in a new query windows and executed it again with the same results. I started stripping down the query to find the part that was causing the error and found out it was this part

I changed this part of the query to only take sp.principal_id, and the query ran without error. In my result set I could see many procedures without a schema name or Urn. I took the object_id of one of the procedures without a schema and ran a very simple query:

which resulted of course in an error.

I then decided to just look at the other meta data that is available in SQL Server and queried sys.procedures joined to sys.schemas. It returned records but not all. Changing it to a left join gave all procedures some without a schema.

The cause

So the reason for the error was that in this database there were procedures with a schema_id (in this case schema_id = 6) referencing a non existing schema. I do not know this happened, but I know the database was migrated from SQL 7 to SQL2005, and probably something happened with the user(s) owning the procedures. Maybe somebody decided to remove the orphaned users from the database, leaving the procedures without a valid schema.

The solution

Now that I knew what was causing the issue it was time to come up with a solution. I queried sys.schemas and did not find a schema with id 6. next I ran this query to create a new schema

I queried sys.schemas again and saw that the newly created migrationtemp schema had an id of 5.

I did the same again:

and this time the schema_id of the new schema was 6.

I went back to the object explorer opened the stored procedures folder and… It returned all my procedures. Some with the dbo schema and some with my migration schema.

Conclusion

I was very happy that I managed to solve this problem without PSS. however I would not recommend to just go ahead and mess around if you receive this error. In this case the troubleshooting was not too difficult and the solution fairly easy, but that may not be the case if you receive the same error.