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?

Presenting a webinar, not the same as a conference

Last saturday Back in January, I presented my first webinar for the Global Power BI usergroup.
It was a redelivery of the Personal BI to Personal Data Science session I’ve already given twice together with my colleague Kimberly Hermans (twitter).
Although there was no real negative feedback and even some positive feedback in private, I don’t think I did great.

I approached the webinar the same way as I do a regular presentation.
And boy oh boy, was I wrong to do it this way…

There’s all kinds of different and extra things to take into account compared to an in-person conference or usergroup presentation.

  • The software you’re using
    We used Google+ Hangouts and while I tried it out before the webinar, I didn’t prepare how I would be taking questions. That could’ve gone a lot better.
  • The microphone you use
    The microphone I used was the one that came with my phone. The quality wasn’t the best, especially combined with the room I was in.
  • The room you’re in
    Using a cheap microphone tends to be OK. But in the wrong room you’ll get a lot of echo or environment noise.
  • No or very limited interaction with your audience
    This one struck me the worst. I couldn’t interact with or read the audience which made me unusually nervous.

On top of that, because of conflicting schedules I had to present alone this time.
I thought I would be OK as I knew most of the data science stuff on a basic level.
But it also means that all the interaction and the dynamic that previously existed in the presentation was gone. No jokes, no natural tempo changes, no interaction between presenters.
In my opinion this was the main killer of the webinar.

What’s next?

UserGroup

The Virtual Global Power BI User Group is still organising monthly webinars. You can join or present yourself as well.
Or just participate in the usergroup via different channels like TwitterFacebook, LinkedIn and our YouTube channel.

Personal

I’m embarking on a new webinar journey as well.
More news will follow soon.

 

%d bloggers like this: