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


SSMS Query Logging


SSMS Query Logging

Author
Message
Phil Parkin
Phil Parkin
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2611 Visits: 3232
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
Phil Parkin
Phil Parkin
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SomewhereSomehow
SomewhereSomehow
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 469
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
SomewhereSomehow
SomewhereSomehow
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 469
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
Phil Parkin
Phil Parkin
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SomewhereSomehow
SomewhereSomehow
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 469
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
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2611 Visits: 3232
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
ssms-man
ssms-man
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 68
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. Smile

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 Smile

So, you are welcome to give our project a try and write us your wishes/requirements. We try to implement every good idea !
ssms-man
ssms-man
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

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