SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Always save query to file


Always save query to file

Author
Message
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4414 Visits: 1366
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.
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23613 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4414 Visits: 1366
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
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23613 Visits: 13559
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...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4414 Visits: 1366
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
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23613 Visits: 13559
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



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search