<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
SSMS Query Logging
21 posts, Page 1 of 3
1
2
3
»
»»
SSMS Query Logging
Rate Topic
Display Mode
Topic Options
Author
Message
Phil Parkin
Phil Parkin
Posted Monday, September 24, 2012 2:31 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 4,328,
Visits: 9,667
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1363363
Jason-299789
Jason-299789
Posted Monday, September 24, 2012 2:50 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 838,
Visits: 2,200
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
Phil Parkin
Phil Parkin
Posted Monday, September 24, 2012 3:40 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 4,328,
Visits: 9,667
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1363384
SomewhereSomehow
SomewhereSomehow
Posted Monday, September 24, 2012 3:44 AM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, May 27, 2013 8:32 AM
Points: 75,
Visits: 412
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
SomewhereSomehow
SomewhereSomehow
Posted Monday, September 24, 2012 9:05 AM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, May 27, 2013 8:32 AM
Points: 75,
Visits: 412
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
Phil Parkin
Phil Parkin
Posted Monday, September 24, 2012 10:23 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 4,328,
Visits: 9,667
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1363604
SomewhereSomehow
SomewhereSomehow
Posted Monday, September 24, 2012 11:39 PM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, May 27, 2013 8:32 AM
Points: 75,
Visits: 412
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
Jason-299789
Jason-299789
Posted Tuesday, September 25, 2012 12:25 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 838,
Visits: 2,200
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
ssms-man
ssms-man
Posted Tuesday, September 25, 2012 3:09 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:29 PM
Points: 10,
Visits: 35
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
ssms-man
ssms-man
Posted Monday, October 08, 2012 9:22 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:29 PM
Points: 10,
Visits: 35
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 »
21 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
