Avatar of Viktor Wessel
Viktor Wessel
 asked on

master.dbo.dba_GetSQLForSpid

while running this script

 -- Do not lock anything, and do not get held up by any locks.
   SET TRANSACTION ISOLATION LEVEL READ
      UNCOMMITTED
 

   -- If there are blocked processes...
   IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE
      blocked != 0)
   BEGIN
 

      -- Identify the root-blocking spid(s)
      SELECT  distinct t1.spid  AS [Root blocking spids]
         , t1.[loginame] AS [Owner]
         , master.dbo.dba_GetSQLForSpid(t1.spid) AS
            'SQL Text'
         , t1.[cpu]
         , t1.[physical_io]
         , DatabaseName = DB_NAME(t1.[dbid])
         , t1.[program_name]
         , t1.[hostname]
         , t1.[status]
         , t1.[cmd]
         , t1.[blocked]
         , t1.[ecid]
      FROM  sys.sysprocesses t1, sys.sysprocesses t2
      WHERE t1.spid = t2.blocked
        AND t1.ecid = t2.ecid
        AND t1.blocked = 0
      ORDER BY t1.spid, t1.ecid
 

      -- Identify the spids being blocked.
      SELECT t2.spid AS 'Blocked spid'
         , t2.blocked AS 'Blocked By'
         , t2.[loginame] AS [Owner]
         , master.dbo.dba_GetSQLForSpid(t2.spid) AS
            'SQL Text'
         , t2.[cpu]
         , t2.[physical_io]
         , DatabaseName = DB_NAME(t2.[dbid])
         , t2.[program_name]
         , t2.[hostname]
         , t2.[status]
         , t2.[cmd]
         , t2.ecid
      FROM sys.sysprocesses t1, sys.sysprocesses t2
      WHERE t1.spid = t2.blocked
        AND t1.ecid = t2.ecid
      ORDER BY t2.blocked, t2.spid, t2.ecid
   END

i'm getting an error

Cannot find either column "master" or the user-defined function or aggregate "master.dbo.dba_GetSQLForSpid", or the name is ambiguous.

what's the problem here?
Microsoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Viktor Wessel

ASKER
Seems it doesn't work with SQL 2012
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

Did you realize why isn't working?
I gave the explanation to it.
Viktor Wessel

ASKER
I have read your explanation, and while it looks reasonable, I don't think it's not a native function because of I have an instance of 2008 server on which I can guarantee I (and noone) didn't created such a function but it's there. So I believe it's some native function that was at 2008 but was removed at 2012.

Correct me if I'm wrong but it looks like this
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Vitor Montalvão

It's very easy to proof that's not a native function:
  1. You can't find any reference to it in MSDN Books online
  2. None of native SQL Server functions start with dba_xxxxxxx
  3. You can alter it using ALTER FUNCTION dba_GetSQLForSpid

You can also get more information from the function by running the following code in the database:
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'

Open in new window

Vitor Montalvão

That's the correct explanation for the issue.