SQL Server - Grab Code From Active Query?

[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). Can anyone help?

Thanks, Iain

 

[lameid]

Try forum183

OR hope one of the SQL Server gurus happens to read this. There are a couple in here.

 

[idbr]

Ah, the age old 'which forum to post in' conundrum

I'm trying to grab the code via an Access VBA proc, so figured this was the best place to start.

Will don my asbestos pants and cross-post to the SQL forum...

 

[lameid]

Funny. I was thinking you'd have to send SQL server a command or if truly automating the app, then they'll at least be able to get you (and all of us in the forum) pointed in the right direction (object etc.)

Also, it would be wise to say you want to "do it from VBA which is more or less VB 6". I would also advise against mentioning Access as about half the time someone will tell you to post elsewhere even if you ARE asking a SQL Server question.

 

[idbr]

All makes sence, noted for future reference.

Don't suppose you know anything about api calls to SQL Server then? :-D

Cheers, Iain

 

[AlexCuse]

What exactly do you want to do this for?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[lameid]

Unfortunately no I don't know anything about SQL server API calls.. I know a little something about Transact SQL.

And AlexCuse has a good question... Why?

 

[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.

It might seem a bit trivial, but with five analysts resolving an average 15 incidents a day, even if I only save 30 seconds a hit I have an extra 37.5 minutes a day of useful time...

 

[AlexCuse]

I would write a tiny (and I mean TINY) app (in something besides access if at all possible) that your analysts can use.

Getting a screen capture from SSMS is going to be a nightmare. But consider this (I imagine it would save much time).

You could have an app with a single Rich Text Box and another standard text box to enter incident number. Then, your analysts could enter an incident number, paste their SQL code into the rich text box, and click a button to handle the entire file archiving process (creating the folder and what not). You can get analyst name from the analyst upon starting the app, or from the Windows domain login.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[idbr]

Thats pretty much how I'll be doing it by the looks of things Alex, was hoping to be a little bit sexier, but I guess you can't have everything...

Thanks, Iain

 

[lameid]

idbr,

Reading your post I was thinking the otherway than Alex...

You could switch to the app and have it do everything including put the path and file name on the clipboard. Then in SSMS, you could do a file save as and paste. I'm sure the browsing time is the worst.

I guess it all depends on what makes sense to you. Although, I must say I like Alex's recomendation because it is probably more flexible/generic.

 

[AlexCuse]

Hey, sometimes it is a good thing when you can't have everything. Imagine a nasty little virus pasting 'drop database' queries into your SSMS windows ;-)

Ignorance of certain subjects is a great part of wisdom

 

[idbr]

If only AlexCuse, if only...