Unresolved reference in SSDT using old SQL Server system views

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…

SQL71502: Procedure has an unresolved references to object sys.sysprocesses

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.

  1. 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
  2. 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)
  3. 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?

Rubbing DevOps on your Database

DLM Lifecycle

I’ve had quite a busy year and one of the things I’ve done was attend a 3 day Database Lifecycle Management (DLM) training.
If you’re into DevOps, Continuous Integration (CI) , Continuous Delivery or Deployment (CD) or you’re just automating as much as possible, then it’s very likely you’ll run into some challenges regarding your databases.

For most people, overcoming these challenges cost a lot of time.
But even before you can spend a lot of time overcoming your challenges, you’ll notice that there are a ton of tools out there that can help you.
So you’ll first have to pick the tools you’re going to use and then you need to learn how to use them.

This brings us back to the training I got to attend thanks to my employer Ordina.
More specifically, they were 3 workshops lead by Alex Yates (blog | twitter) from DLM Consultants (website | twitter).
In total they covered the 3 different main parts of the Database Lifecycle Management process.

  • Database Source Control
  • Database Continuous Integration
  • Database Release Management

Read on for my experience with these full-day online workshops.

Read more

%d bloggers like this: