Grab Active Query Text via VBA Proc

[idbr]

Hi,

I'd like to be able to grab the contents of the currently active query window in an instance of SQL Server 2005 (Management Studio) from an Access VBA procedure. Can anyone help?

I'd originally posted this on the vba forum btw, advised to post it here...

Thanks, Iain

 

[nomadicalloy]

You can query the sys tables to see the active querry. Try something similar

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
--AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)


SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000 -- The wait time is measured in milliseconds
AND spid > 50



This is Signature

http://www.xpherion.com/

 

[AlexCuse]

nomadicalloy - Won't that return the currently running query (not the active window, as idbr is looking for?).



Ignorance of certain subjects is a great part of wisdom

 

[nomadicalloy]

AlexCuse, you are correct, thats why I mentioned "something similar" in my answer.
idbr, can you please give more details about your project?



This is Signature

http://www.xpherion.com/

 

[idbr]

It's to help with documenting our work. I'm a data manager with a team of analysts working on an incidents based system running various ad-hoc data manipulation/retrieval/analysis tasks.

Standard practice for incident resolution is to save the sql run to a text file in a new folder, tagged with the incident number and analyst name.

Currently this means a manual copy, paste to text file, save file, create folder in right location with correct name and filename.

I'd like to have an app running that automates this process, i.e. switch to app, click button, job done.

 

[nomadicalloy]

you can retrieve machine name, content of the query and run time etc from systables in sql. This might be easier solution for your problem. Only remaining problem is to tag incident number.

This is Signature

http://www.xpherion.com/