sp_WhoIsActive and build warnings in VS Database Project
-
I just tried adding sp_WhoIsActive to a VS database project (for a non-system database – not sure whether that is an issue).
When I build the database project, I get approx 30 warnings that look similar to these:
I've tried changing table aliases and putting square brackets all over the offending code, but I can't seem to resolve these warnings. Has anyone been through this themselves?
Here is the section of code where things start going awry:SELECT
y.resource_type,
y.database_name,
y.object_id,
y.file_id,
y.page_type,
y.hobt_id,
y.allocation_unit_id,
y.index_id,
y.schema_id,
y.principal_id,
y.request_mode,
y.request_status,
y.session_id,
y.resource_description,
y.request_count,
s.request_id,
s.start_time,
CONVERT(sysname, NULL) AS object_name,
CONVERT(sysname, NULL) AS index_name,
CONVERT(sysname, NULL) AS schema_name,
CONVERT(sysname, NULL) AS principal_name,
CONVERT(NVARCHAR(2048), NULL) AS query_error
INTO #locks
FROM
(
SELECT
sp.spid AS session_id,
CASE sp.status
WHEN 'sleeping' THEN CONVERT(INT, 0)
ELSE sp.request_id
END AS request_id,
CASE sp.status
WHEN 'sleeping' THEN sp.last_batch
ELSE COALESCE(req.start_time, sp.last_batch)
END AS start_time,
sp.dbid
FROM sys.sysprocesses AS sp
OUTER APPLY
(
SELECT TOP(1)
CASE
WHEN
(
sp.hostprocess > ''
OR r.total_elapsed_time < 0
) THEN
r.start_time
ELSE
DATEADD
(
ms,
1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
)
END AS start_time
FROM sys.dm_exec_requests AS r
WHERE
r.session_id = sp.spid
AND r.request_id = sp.request_id
) AS reqThe absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Maybe try adding a reference to the master database?
Sue
- Sue_H - Friday, November 3, 2017 10:22 AM
Thanks, Sue, but MSDB and Master are already referenced from this project.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Friday, November 3, 2017 10:34 AM
That issue/error is obnoxious. There is a three part name earlier in the procedure, those used to break with the same error but I thought it had been fixed.
Maybe you found a new one to add to the issues - String_split, white space in column names, three part names have all been issues that I know of.
I'm interested in whatever this one is - please post back if you figure it out.Sue
- Sue_H - Friday, November 3, 2017 11:51 AM
If you look closely at the first warning, you'll see that it is referring to a perceived ambiguity between
sys.sysprocesses.sp::spid
and
sys.sysprocesses.spidWhile the second one of these appears in the script, where is that double colon coming from in the first? I searched the entire script for '::' and no occurrences are found.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Friday, November 3, 2017 12:05 PM
I did see that - and that's why I opened up the stored proc to look for three part names with sysprocesses. I found the earlier one with master.dbo.fn_varbintohexstr and that was it. I have seen the errors display that way before but not always - no idea why it does that sometimes.
They all do the same pointing to the aliased sys.sysprocesses view with ::, the old syntax required for some of the system objects.
I wonder if its confused between sys.sysprocesses and sysprocesses for some reason? Maybe try removing the sys qualification from sysprocesses?
And the way they are qualified in the error, it looks like three part name instead of having an alias.Sue
Edit: There are some other three part names using tempdb. And other stored procedures using sys.xxxxx but not objects you could reference by dbo or sys and have it work (e.g. dbo.sysprocesses vs sys.sysprocesses).
-
Interesting find...check this out:
References to sys.sysprocesses generating SQL71502 Warning (unresolved refrerence)So they say the workaround is dbo.sysprocesses.
Sue
- Sue_H - Friday, November 3, 2017 1:05 PM
Thank you, Sue
Changing sys.sysprocesses to dbo.sysprocesses got rid of the build warning.
However, I did note the following in SSMS:Intellisense does not like dbo.sysprocesses!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Monday, November 6, 2017 6:59 AMThank you, Sue
Changing sys.sysprocesses to dbo.sysprocesses got rid of the build warning.
However, I did note the following in SSMS:Intellisense does not like dbo.sysprocesses!
I thought both intellisense and SSDT were owned by the same group at MS. Guess it can still be the left hand not knowing what the right hand is doing.
I don't know much about the setting to suppress t-sql warnings. That link indicated you could do it at the file level - I've only played with setting it for the project. Can you set it for just that file?Sue
- Sue_H - Monday, November 6, 2017 7:35 AM
Well, that's a trick I've never used before. The warnings can be suppressed at file level – just need to get the file properties and then fill in the numbers of the warnings to be suppressed:
Thanks very much for your input in this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Monday, November 6, 2017 7:52 AMThe warnings can be suppressed at file level – just need to get the file properties and then fill in the numbers of the warnings to be suppressed:
And here I thought that was a useless, kind of dangerous setting....thanks for figuring that out.
Guess it does have some good uses when use with more granularity like that.Sue
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply