|
|
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?
Navigation:
[Reply to this message]
|