Posted by wjreichard on 04/11/07 19:59

OK ... I think I got something that might work? I will create a unique
SQL login for the Worldship application and then using the above
method posted in my 1st post execute a stored procedurer and then
access the ODBC SQL with code prototyped in the following SP:

CREATE PROCEDURE dbo.sp_ups_pull AS
DECLARE @spid int
DECLARE @dbcc_cmd varchar(512)

CREATE TABLE #who (
spid int,
ecid int,
status varchar(255),
loginname varchar(255),
hostname varchar(255),
blk int,
dbname varchar(255),
cmd varchar(2048)
)
INSERT INTO #who EXEC ('sp_who worldshipuser')

SET @spid = (SELECT spid FROM #who)
SET @dbcc_cmd = 'DBCC INPUTBUFFER(' +
rtrim(ltrim(convert(char,@spid))) + ')'

CREATE TABLE #buffer (
EventType varchar(512),
Parameters int,
EventInfo varchar(2048)
)

INSERT INTO #buffer EXEC (@dbcc_cmd)

SELECT EventInfo FROM #buffer

[Additional biz logic etc.]
GO

---------------------------------------------------------------------------------------------------------------------------------------------
Which return the ODBC SQL ... which I will parse the orderid from the
WHERE cluase.

So does anyone see major issues using this method? How do Input
Buffers relate to ODBC connections ... I guess I will need to ensure
that there is only ever one row returned from sp_who for my unique DB
users, Any one see other problems .... or a better solution?

[Back to original message]