Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Always save query to file Expand / Collapse
Author
Message
Posted Saturday, November 16, 2013 7:08 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:58 PM
Points: 753, Visits: 1,070
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.
Post #1514947
Posted Sunday, November 17, 2013 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,840, Visits: 13,354
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
Post #1514964
Posted Sunday, November 17, 2013 8:00 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:58 PM
Points: 753, Visits: 1,070
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

Post #1514984
Posted Sunday, November 17, 2013 8:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,840, Visits: 13,354
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
Post #1514987
Posted Sunday, November 17, 2013 8:22 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:58 PM
Points: 753, Visits: 1,070
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

Post #1514989
Posted Sunday, November 17, 2013 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,840, Visits: 13,354
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
Post #1514991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse