Can you determine which SPID is waiting on OPENQUERY?
-
Take a look at the ::fn_get_sql() system function. You'll probably need to couple that with a cursor but that'll tell you the SQL statments which are executing.
K. Brian Kelley
@kbriankelley -
Thanks for the tip Brian! I had messed around with that earlier and even though I could use that to get the command that is executing, it doesn't return the spid. So in a situation where there are many spids all running the same command, I can't isolate which one is actually in my stored procedure's call dependency.
Here is a setup of what I am shooting for. This doesn't really "do" anything but tries to make more clear the conditions under which I am trying to get the spid of the calling function: (note my comment with no code is what I am working on)
USE tempdb
GO
CREATE PROC SP_DoSomething AS BEGIN
SET NOCOUNT ON
-- need to find out which spid is in my call dependency here
SELECT 1 AS ID
END
GO
CREATE FUNCTION FN_TestCallDependency()
RETURNS @tThings TABLE (It VARCHAR(8000)) -- PRIMARY KEY CLUSTERED) -- no need to primary key cluster and that will happen in the front-end function
AS
BEGIN
INSERT INTO @tThings
SELECT * FROM OPENQUERY([LOOPBACK], 'SET NOCOUNT ON;EXEC tempdb.dbo.SP_DoSomething;')
RETURN
END
GO
SELECT * FROM FN_TestCallDependency()
There has got to be a way to get that spid!
-
OK I realized that I was simply wrong about Herts Chen's solution. It DOES work just as he explained. Here is my new code:
USE tempdb
GO
CREATE PROC SP_DoSomething AS BEGIN
SET NOCOUNT ON
DECLARE @nSPID INT
SELECT @nSPID = CONVERT(VARCHAR, spid)
FROM master.dbo.sysprocesses
WHERE waitresource = 'LOOPBACK (SPID=' + CAST(@@SPID as varchar) + ')'
SELECT @nSPID AS ID
END
GO
CREATE FUNCTION FN_TestCallDependency()
RETURNS @tThings TABLE (It VARCHAR(8000)) -- PRIMARY KEY CLUSTERED) -- no need to primary key cluster and that will happen in the front-end function
AS
BEGIN
INSERT INTO @tThings
SELECT * FROM OPENQUERY([LOOPBACK], 'SET NOCOUNT ON;EXEC tempdb.dbo.SP_DoSomething;')
RETURN
END
GO
SELECT * FROM FN_TestCallDependency()
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply