Issue
if you’re developing databases in SSDT, like you should, you’re probably getting a lot of build warnings.
One of the warnings you’ll see the most often is the “unresolved reference”.
Usually you solve these by adding either the master, the msdb or some application database as a database reference.
This post is about a warning you might get when out of habit (or, if like me, you didn’t know any better yet) you’re using old system views like sys.sysprocesses. You expect it to work but it simply doesn’t…
Even after you try add the master and msdb databases as references you’ll notice that you’ll still see “SQL71502: Procedure has an unresolved references to object sys.sysprocesses”.
Solution
The view sys.sysprocesses and several other system views only exist for backwards compatibility but they should still work. At least for now…
So what’s going on and how do we fix this?
As it turns out, there seem to be a couple of possibilities to fix this issue.
- You should be in master database context as the documentation notes.
This means you need to prefix with the relevant database as in: master.sys.sysprocesses - Rename sys.sysprocesses to dbo.sysprocesses
Gert Drapers provided this solution on the msdn SSDT forum back in 2013 and it still works (at least for SQL Server 2014 with SSDT 14.0.61021.0) - You might want to rewrite your specific query to use the new and relevant tables/views. This ensures your code won’t break in the future. Especially since you’re using a view that’s included for pre-SQL Server 2000 backwards compatibility.
Refer to this mapping of system tables to system views in the documentation to help you rewrite your query.
The 3rd solution has my personal preference, as it seems to be the cleanest. But it does mean you’ll need to take a bit more time to rewrite and test your query.
What do you do when you come across this issue?
