September 24, 2012 at 2:31 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 24, 2012 at 2:50 am
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
September 24, 2012 at 3:40 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 24, 2012 at 3:44 am
What about server side trace? Is it an option or not for some reasons?
September 24, 2012 at 9:05 am
Also found this one:
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.
September 24, 2012 at 10:23 am
SomewhereSomehow (9/24/2012)
Also found this one: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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 24, 2012 at 11:39 pm
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[/url], 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 ..." =)
September 25, 2012 at 12:25 am
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
September 25, 2012 at 3:09 am
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[/url] 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 !
October 8, 2012 at 9:22 am
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.
October 8, 2012 at 10:35 am
I've downloaded and installed SSMS Boost. Some interesting features and some usability issues too (eg Script Object as CREATE/ALTER fails with 'No suitable objects found at cursor position' unless you are in the same database context as the object which you are trying to script - even if fully qualified - puzzled me for a while). Promising though - keep it up.
My own development request - please add support for collapsible regions like in C#. This has been removed from SSMS Tools latest version because regions are apparently 'supported' in SSMS 2012. But they're not really, IMO.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2012 at 2:27 am
ssms-man (10/8/2012)
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.
thanks for the update Ill have a look at down loading it and installing it.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 9, 2012 at 4:45 am
Phil Parkin (10/8/2012)
I've downloaded and installed SSMS Boost. Some interesting features and some usability issues too (eg Script Object as CREATE/ALTER fails with 'No suitable objects found at cursor position' unless you are in the same database context as the object which you are trying to script - even if fully qualified - puzzled me for a while). Promising though - keep it up.
Right, this is "known" feature 😉 It is in "to fix" list for next release.
My own development request - please add support for collapsible regions like in C#. This has been removed from SSMS Tools latest version because regions are apparently 'supported' in SSMS 2012. But they're not really, IMO.
Why do you write,that they are not supported in SSMS2012 ? I can see them there. Or don't you like the way they are implemented ?
October 9, 2012 at 5:19 am
ssms-man (10/9/2012)
Phil Parkin (10/8/2012)
I've downloaded and installed SSMS Boost. Some interesting features and some usability issues too (eg Script Object as CREATE/ALTER fails with 'No suitable objects found at cursor position' unless you are in the same database context as the object which you are trying to script - even if fully qualified - puzzled me for a while). Promising though - keep it up.Right, this is "known" feature 😉 It is in "to fix" list for next release.
My own development request - please add support for collapsible regions like in C#. This has been removed from SSMS Tools latest version because regions are apparently 'supported' in SSMS 2012. But they're not really, IMO.
Why do you write,that they are not supported in SSMS2012 ? I can see them there. Or don't you like the way they are implemented ?
OK - first of all my disclaimer: we are in the process of upgrading to SSMS 2012, so I have only been using it for the last week. Maybe I have missed something 🙂
But it seems that SSMS 2012 does not give you control over your regions, rather it adopts an 'I know best' attitude when determining what the regions should be.
With SSMS tools, pre 2012, I was able to structure long and complex scripts into collapsible sections. Simply by pressing CTRL-K, CTRL-J, the regions would collapse, leaving me with a screen of headings which made navigation easy. See attached image for an example. Those regions contains many lines of code which would not be grouped in SSMS 2012.
I know that there is the 'hide' function in SSMS 2012, which works the way I want, but the hidden areas are not persisted between editing sessions.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2012 at 6:04 am
Phil Parkin (10/9/2012)
ssms-man (10/9/2012)
Phil Parkin (10/8/2012)
I've downloaded and installed SSMS Boost. Some interesting features and some usability issues too (eg Script Object as CREATE/ALTER fails with 'No suitable objects found at cursor position' unless you are in the same database context as the object which you are trying to script - even if fully qualified - puzzled me for a while). Promising though - keep it up.Right, this is "known" feature 😉 It is in "to fix" list for next release.
My own development request - please add support for collapsible regions like in C#. This has been removed from SSMS Tools latest version because regions are apparently 'supported' in SSMS 2012. But they're not really, IMO.
Why do you write,that they are not supported in SSMS2012 ? I can see them there. Or don't you like the way they are implemented ?
OK - first of all my disclaimer: we are in the process of upgrading to SSMS 2012, so I have only been using it for the last week. Maybe I have missed something 🙂
But it seems that SSMS 2012 does not give you control over your regions, rather it adopts an 'I know best' attitude when determining what the regions should be.
With SSMS tools, pre 2012, I was able to structure long and complex scripts into collapsible sections. Simply by pressing CTRL-K, CTRL-J, the regions would collapse, leaving me with a screen of headings which made navigation easy. See attached image for an example. Those regions contains many lines of code which would not be grouped in SSMS 2012.
I know that there is the 'hide' function in SSMS 2012, which works the way I want, but the hidden areas are not persisted between editing sessions.
Ok, I understand what you want and will add to "to investigate" list. We did not re-use regions till now, so we'll need some time to invest before we find approriate interfaces of SQL Editor control.
<by the way - I just have updated 2.6 version once again, with some small fixes/additions reported by users since yesterday>
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply