Can you determine which SPID is waiting on OPENQUERY?
-
I am using OPENQUERY within a UDF to call a stored procedure (yes feel free to flame me for doing such a thing, but I am definately sure I want to do this). Within the procedure I have a need to know which spid called me with OPENQUERY. I thought I had the solution from Herts Chen here http://www.pinpub.com/Media/MediaManager/nSQLsample.pdf on page 3 but unfortunately what he describes simple doesn't work. There is no useful information in the waitresource of sysprocesses (I am running SQL Server 2000 sp4 and SQL Server 2005).
Does anyone know of tables other than sysprocesses that might track the spid call stack?
Thanks,
-Chad
-
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply