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 123»»»

SSMS Query Logging Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 5,245, Visits: 12,162
We have a user who has come from an environment (SAS) where all of the queries she ran were logged, along with number of rows affected and any error messages.

At the end of a session, she could save this log as a text file.

A typical session would include a multitude of DML queries to move data around and populate calculated fields.

She liked the fact that she could always go back and review the statements that were executed and how many rows were being affected and has asked whether we can provide something similar in SSMS.

A brief investigation suggested not. The SSMS Tools add-on gets quite close with its SQL History functionality, but this does not capture errors or number of rows affected.

I can write a stored proc which executes SQL for her and captures the required information, but this seems cumbersome because she'd have to wrap each of her queries with exec ''.

Does anyone have any other ideas? Thanks in advance.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1363363
Posted Monday, September 24, 2012 2:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 918, Visits: 2,507
Phil,

what about using the Results to File option in SSMS, its a bit cumbersome but should will allow the results to be stored.

Also switch on "Include the Query in the Result set" for Results to Text under Tools->Options->Query Results->SQL Server.

hope this helps.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1363371
Posted Monday, September 24, 2012 3:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 5,245, Visits: 12,162
Jason-299789 (9/24/2012)
Phil,

what about using the Results to File option in SSMS, its a bit cumbersome but should will allow the results to be stored.

Also switch on "Include the Query in the Result set" for Results to Text under Tools->Options->Query Results->SQL Server.

hope this helps.


Thanks for the response Jason.

That would be workable if it were not for the fact that there does not seem to be an option to append in the 'Messages' pane - instead it is cleared for every batch of instructions executed.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1363384
Posted Monday, September 24, 2012 3:44 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:53 PM
Points: 75, Visits: 444
What about server side trace? Is it an option or not for some reasons?


I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1363386
Posted Monday, September 24, 2012 9:05 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:53 PM
Points: 75, Visits: 444
Also found this one:
SSMS Tools Pack add-in
As it's said on the utility page:
Query Execution History logs every SQL statement that you run in a file on your disk or in a table in a database you specify with a connection string. This way, if you're working on some script you can get the full history of every change you've made. SQL Statements are saved in a list that is written to a file and/or a database with a timer which you can set to a desired interval. Older query history logs can be auto deleted. Time after which the logs are deleted is user settable. The size of the code to be saved can also be set up to the maximum of 5 MB of code.

Window Content History saves the whole text in all all opened SQL windows every N minutes. The default being 2 minutes. This feature fixes the shorcoming of the Query Execution History which is saved only when the query is run. If you're working on a large script and never execute it the existing Query Execution History wouldn't save it. By contrast the Window Content History saves everything in a .sql file so you can even open it in your SSMS. The Query Execution History and Window Content History files are correlated by teh same directory and file name so when you search through the Query Execution History you get to see the whole saved Window Content History for that query. It is turned ON by default.

But I've never tried that add-in myself, so don't know how good it works.



I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1363557
Posted Monday, September 24, 2012 10:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 5,245, Visits: 12,162
SomewhereSomehow (9/24/2012)
Also found this one:
SSMS Tools Pack add-in
As it's said on the utility page:
Query Execution History logs every SQL statement that you run in a file on your disk or in a table in a database you specify with a connection string. This way, if you're working on some script you can get the full history of every change you've made. SQL Statements are saved in a list that is written to a file and/or a database with a timer which you can set to a desired interval. Older query history logs can be auto deleted. Time after which the logs are deleted is user settable. The size of the code to be saved can also be set up to the maximum of 5 MB of code.

Window Content History saves the whole text in all all opened SQL windows every N minutes. The default being 2 minutes. This feature fixes the shorcoming of the Query Execution History which is saved only when the query is run. If you're working on a large script and never execute it the existing Query Execution History wouldn't save it. By contrast the Window Content History saves everything in a .sql file so you can even open it in your SSMS. The Query Execution History and Window Content History files are correlated by teh same directory and file name so when you search through the Query Execution History you get to see the whole saved Window Content History for that query. It is turned ON by default.

But I've never tried that add-in myself, so don't know how good it works.


If you re-read my first post you'll see that I've tried this, but thanks anyway If I could get at the underlying code and hack it a bit, I could get something nearly perfect.

I did read your server-side trace idea, but still considering my response. It's an interesting idea, but I'm not sure whether it gives the users what they want - I'm also worried about any DBA overhead.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1363604
Posted Monday, September 24, 2012 11:39 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:53 PM
Points: 75, Visits: 444
Phil Parkin (9/24/2012)

If you re-read my first post you'll see that I've tried this, but thanks anyway

I'm sorry, I missed it!
I know one more add-in SSMSBoost, the developers announced history "feature" for the future release (about 1-2 month, as they say), I don't know how it will be implemented, but I'll give them link to that thread and maybe they will consider capturing row count and error numbers.
You may also write your own add-in "with blackjack and ..." =)



I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1363794
Posted Tuesday, September 25, 2012 12:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 918, Visits: 2,507
SSMSBoost looks interesting, though looking at the list of features you dont seem to get what phil is looking for, and seems very similar to SSMSTools, all be it has a few neice additional features (lookup object etc).

The 50 Euro price tag may put people off and the need to constantly down load the free version every 45 days. Mind you I noticed that SSMS tools isnt going to be free for SQL 2012 and later versions, with a Price tag of around US$30 for a single pc.

Let us know if you find a tool Phil as I'd be interested in looking at it myself.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1363806
Posted Tuesday, September 25, 2012 3:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:38 AM
Points: 18, Visits: 59
Hello guys,
I am developer of SSMSBoost. We have started with implementation of Logging feature and already have found required interfaces in SSMS. I will take into consideration your comments and requirements and we will try to do our best implementing rowcounts and error statements logging. You can expect this feature in about 1 month.

In coming version (1 week) we will also improve our licensing model, so that using the free version will not require permanent updates so frequently. Actually the intention was to keep users updating to latest version, as far as project is in permanent improvement process. But I see that people are not happy when they are forced to update it every 45 days. On the other side, everyone wants to have latest version, so you will do it by yourself. :)

We actually tried not to duplicate ToolsPack features. Only some of them are present in SSMSBoost, because we wanted them to be implemented in a better way (like connection coloring). If you look more exactly at features list you will see that it actually differs from tools pack. I would say SSMSBoost is more focused on productivity of SQL developers, working daily in SSMS. We use the SSMSBoost ourselves on daily basis and improve it actually almost every day :)

So, you are welcome to give our project a try and write us your wishes/requirements. We try to implement every good idea !
Post #1363871
Posted Monday, October 8, 2012 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:38 AM
Points: 18, Visits: 59
Jason-299789 (9/25/2012)
SSMSBoost looks interesting, though looking at the list of features you dont seem to get what phil is looking for, and seems very similar to SSMSTools, all be it has a few neice additional features (lookup object etc).

The 50 Euro price tag may put people off and the need to constantly down load the free version every 45 days.

We have released SSMSBoost v 2.6 today. Due to user feedback, free version must be updated only every 120 days (instead of 45 in previous versions). I hope this helps. Please also note, that you do not have to buy the PRO version at all. It is kind of support for a project. Free version has full set of functions.

We have already internal 2.7 release with logging functionality. I will do a short post here, as far as we release it to public.
Post #1369885
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse