November 16, 2013 at 7:08 pm
Hi,
I would like to know if there is any property in Management Studio that I can change to save automaticaly all the querys run through it (query window) into a file in a directoty.
Case scenario:
Several people will use the query window and will run sql ddl and dml.
I would like that all the sql introduced through query window could be saved into some directory or to an database automaticaly with having to had any type of special inputs into the script.
Is possible?
Thank you.
November 17, 2013 at 3:50 am
There are Add-Ons available that provide the option of a "query history". Tools like SQL Prompt (Red Gate) or SSMSBoost (Solutions Crew).
You'd need to find the folder where this information is stored and either copy it to an archive folder or do whatever you need to do with those files.
November 17, 2013 at 8:00 am
Hi,
Thank you for the feedback.
I would like to achieve this without a third party tool.
Can't I say to the Management Studio automatically save (without having to ask the user) all the sql run through the query window to some directory?
I now I can put SQL Server to store all the query results and query text into a definied directory but it always (at the begining of the query) asks the user for which file (insede de definied directory) to stored this information.
What I want is let him run what he wants (the query) and that SQL Server stores the sql executed in the query window into other database without his knowledge or at least without he has to confirm.
I could do this using triggers inside the database but that would downgrade the performance and I have several Bulk Insert operations. So, this poit that is not an option.
Can someone help?
Thank you
November 17, 2013 at 8:14 am
Let me repeat what I posted before. SSMS has no option out of the box.
As much as you request it, if the software doesn't have this option, it doesn't help to request this option to be there. It is not.
If you don't want to use a 3rd party tool, then you're stuck with what you already described as your options.
You could also convince Microsoft to add this option. There'll be many people out there supporting the request but I doubt MS will include it in the near future... Or you could write your own Management Studio...
November 17, 2013 at 8:22 am
Thank you for the feedback.
Ok. I undestand it.
What about the profiler?
1) Can I ask the profiler to log all the querys that are pass throug the query window? Olher querys are not important in this case so I would like the profiler to log only does run through the query window
2) One of the problems that I have with profiler is that when the server restarts I have to restart the tracer again. Can'i I define that the trace will start again automatically after the server restarts?
Thank you
November 17, 2013 at 8:36 am
You would need to setup a server side trace (runs without the profiler User Interface being required, look up "sql server side trace"). Otherwise there'll be quite some overhead, even if the trace is pretty good filtered.
The filter to get the SSMS requests would be ApplicationName LIKE "Microsoft SQL Server Management Studio%", which could then be further reduced to the information you need.
To restart the trace automatically, use EXEC sp_procoption 'your server side trace start procedure','startup',true
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply