Post reply

SQL Server Management Studio Tips

  • August 15, 2017 at 8:45 am

    #1955299

    kast218 - Monday, August 14, 2017 10:37 AM

    Rod at work - Monday, August 14, 2017 8:54 AM

    These tips are really great. However, I was a little disappointed to find I could use the 2 system SP's I use most frequently (sp_tables and SP_columns), into the Keyboard Shortcuts for Favorite Stored Procedures. I put them there but once I tried executing them they failed. They expect parameters, which SSMS doesn't give me a chance to enter. I expected it to paste sp_tables into the query window and then wait for me to type in a portion of the name of the table I was searching for. That didn't happen. SSMS just runs sp_tables and then immediately complained because I didn't supply parameters.

    So, I removed my added custom commands. What's the point of trying to use something when it won't let me?

    If you have some mandatory parameters in stored procedure you must add it, for example I use sp_WhoIsActive for query shortcut like this:
    sp_WhoIsActive @get_additional_info=1, @show_own_spid=1, @get_full_additional_info=1

    Also could you share sp_tables and SP_columns code via github or post public link here?

    I'm sorry, I wasn't clear. I don't see how I can use either sp_tables or sp_columns in the Keyboard Shortcuts precisely because the parameters would change almost each time I use them.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • August 15, 2017 at 2:20 pm

    #1955360

    DarthJurious - Tuesday, August 15, 2017 5:22 AM

    A trick for CMS...

    A significant limitation with CMS is that the CMS server itself can’t be included in the list of servers.

    You can register the CMS server just use a different value in Server Name.  So if your CMS is SERVER\INSTANCE, you can register as SERVER,PORT.

    Thanks for tip, I will check it tomorrow.

  • August 15, 2017 at 2:24 pm

    #1955361

    Paresh Motiwala - Tuesday, August 15, 2017 8:09 AM

    This is a very neatly compiled article. Thank you for it.

    One more thing I find all of us DBAs have to do is to run SSMS against SQL Servers in different domains using different credentials. 
    It is true you can right click on the shortcut and painfully enter the data every time you login.
    But you can use the following tip to achieve that. 

    runas /savecred /user:USER-NAME "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

    Save this shortcut in a different corner of your desktop and color code this shortcut using any of the options of the SSMS.

    Yes, thank you very much for this tip. Added with another example (great thanks to Aaron Bertrand): https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Tips.md#28

  • August 23, 2017 at 5:14 pm

    #1956519

    kast218 - Monday, August 14, 2017 10:37 AM

    If you have some mandatory parameters in stored procedure you must add it, for example I use sp_WhoIsActive for query shortcut like this:
    sp_WhoIsActive @get_additional_info=1, @show_own_spid=1, @get_full_additional_info=1

    sp_whoisactive doesn't have a get_full_additional_info param. Is that something you added? If so, what does it do?

    --
    Adam Machanic
    whoisactive

  • August 24, 2017 at 2:09 pm

    #1956719

    Adam Machanic - Wednesday, August 23, 2017 5:14 PM

    kast218 - Monday, August 14, 2017 10:37 AM

    If you have some mandatory parameters in stored procedure you must add it, for example I use sp_WhoIsActive for query shortcut like this:
    sp_WhoIsActive @get_additional_info=1, @show_own_spid=1, @get_full_additional_info=1

    sp_whoisactive doesn't have a get_full_additional_info param. Is that something you added? If so, what does it do?

    Adam, first of all great thanks for your awesome (best of the best) procedure, I use it every day and my first recommendation during troubleshooting SQL Server - run sp_WhoIsActive.
    Answer on your question: its your native parameter in stored procedure (see description below), I use latest version from your site Version 11.17
    --Get additional non-performance-related information about the session or request
        --text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,
        --ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,
        --transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type
        --
        --If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of
        --the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)
        --
        --If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be
        --populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,
        --applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
        @get_additional_info BIT = 0,

    Also I really want to contribute some enchantments in your titanic work but unfortunately I didnt understand license options for this (can I or you deprecate any changes?). If you publish you procedure on Github with any license option (Brent Ozar and Ola Hallengren already did it) I think many people will contribute or just say you big thanks.

    Once again  - great thanks for your work!

  • August 24, 2017 at 3:00 pm

    #1956728

    kast218 - Thursday, August 24, 2017 2:09 PM

    Also I really want to contribute some enchantments in your titanic work but unfortunately I didnt understand license options for this (can I or you deprecate any changes?). If you publish you procedure on Github with any license option (Brent Ozar and Ola Hallengren already did it) I think many people will contribute or just say you big thanks.

    I am not currently interested in taking third party submissions, for a number of reasons. However, I am very interested in suggestions and other forms of feedback. Please feel free to drop me a line at the e-mail address in the stored procedure with any and all ideas.

    Really glad you find it to be useful!

    BTW I think that param you're using is @get_full_inner_text? Do you ever flip @get_task_info = 2 ? That's how I personally run it most of the time.

    --
    Adam Machanic
    whoisactive

  • August 25, 2017 at 8:06 am

    #1956853

    Adam Machanic - Thursday, August 24, 2017 3:00 PM

    kast218 - Thursday, August 24, 2017 2:09 PM

    Also I really want to contribute some enchantments in your titanic work but unfortunately I didnt understand license options for this (can I or you deprecate any changes?). If you publish you procedure on Github with any license option (Brent Ozar and Ola Hallengren already did it) I think many people will contribute or just say you big thanks.

    I am not currently interested in taking third party submissions, for a number of reasons. However, I am very interested in suggestions and other forms of feedback. Please feel free to drop me a line at the e-mail address in the stored procedure with any and all ideas.

    Really glad you find it to be useful!

    BTW I think that param you're using is @get_full_inner_text? Do you ever flip @get_task_info = 2 ? That's how I personally run it most of the time.

    Adam, sorry for misunderstanding: @get_full_additional_info=1 its debug parameter that prints all dynamic sql statements in stored procedure for debugging and studying purpose.

    Could you describe your best practice of using sp_WhoIsActive? What is default parameter execution? Do you have standard job script for monitoring SQL Server?

  • March 22, 2019 at 11:46 am

    #2025247

    kast218 - Sunday, August 13, 2017 5:14 AM

    Comments posted to this topic are about the item SQL Server Management Studio Tips

    Hello, great article! Still valid in 2019, however the section for graphs " Generating Charts and Drawings in SQL Server Management Studio " has an issue. The code to download isn't working. Anyone know of where we can download it from? The script is called "Chart"

    Thanks,

    Rudy

  • March 22, 2019 at 12:06 pm

    #2025252

    Rudy Panigas - Friday, March 22, 2019 11:46 AM

    kast218 - Sunday, August 13, 2017 5:14 AM

    Comments posted to this topic are about the item SQL Server Management Studio Tips

    Hello, great article! Still valid in 2019, however the section for graphs " Generating Charts and Drawings in SQL Server Management Studio " has an issue. The code to download isn't working. Anyone know of where we can download it from? The script is called "Chart"

    Thanks,

    Chart_20111224.sql is in the zip file that is downloaded. The download link works when I tried. This is the link to the zip file:
    141994.zip

    Sue

  • March 22, 2019 at 2:05 pm

    #2025280

    kast218 - Sunday, August 13, 2017 5:14 AM

    Comments posted to this topic are about the item SQL Server Management Studio Tips

    Love that SSMS can generate scripts for different levels of SQL Server.  Sadly that does not apply to SSIS packages.  If you export an SSIS project/package (v6) from a 2012 server ssisdb, using SSMS 17, it creates a dacpac at SSIS 2017 level (v8) which cannot be re-deployed into a 2012 server as is.   Yes, you can do it in a few tedious steps.  However, either SSMS should respect the source server level and use that or give you an option as to what SSIS project/package level to use.

    Gerald Britton, Pluralsight courses

  • March 25, 2019 at 7:04 am

    #2031664

    Sue_H - Friday, March 22, 2019 12:06 PM

    Rudy Panigas - Friday, March 22, 2019 11:46 AM

    kast218 - Sunday, August 13, 2017 5:14 AM

    Comments posted to this topic are about the item SQL Server Management Studio Tips

    Hello, great article! Still valid in 2019, however the section for graphs " Generating Charts and Drawings in SQL Server Management Studio " has an issue. The code to download isn't working. Anyone know of where we can download it from? The script is called "Chart"

    Thanks,

    Chart_20111224.sql is in the zip file that is downloaded. The download link works when I tried. This is the link to the zip file:
    141994.zip

    Sue

    Thank you for the link. Interesting, the download will not work with Edge however works fine with Chrome.

    Rudy

  • March 25, 2019 at 12:09 pm

    #2031711

    Rudy Panigas - Monday, March 25, 2019 7:04 AM

    Sue_H - Friday, March 22, 2019 12:06 PM

    Rudy Panigas - Friday, March 22, 2019 11:46 AM

    kast218 - Sunday, August 13, 2017 5:14 AM

    Comments posted to this topic are about the item SQL Server Management Studio Tips

    Hello, great article! Still valid in 2019, however the section for graphs " Generating Charts and Drawings in SQL Server Management Studio " has an issue. The code to download isn't working. Anyone know of where we can download it from? The script is called "Chart"

    Thanks,

    Chart_20111224.sql is in the zip file that is downloaded. The download link works when I tried. This is the link to the zip file:
    141994.zip

    Sue

    Thank you for the link. Interesting, the download will not work with Edge however works fine with Chrome.

    Your welcome. Interesting but not surprising I guess. It's my least favorite browser due to all the issues like that.

    Sue

  • February 19, 2021 at 3:14 pm

    #3847333

    Sue_H wrote:

    Rudy Panigas - Monday, March 25, 2019 7:04 AM

    Sue_H - Friday, March 22, 2019 12:06 PM

    Rudy Panigas - Friday, March 22, 2019 11:46 AM

    kast218 - Sunday, August 13, 2017 5:14 AM

    Comments posted to this topic are about the item SQL Server Management Studio Tips

    Hello, great article! Still valid in 2019, however the section for graphs " Generating Charts and Drawings in SQL Server Management Studio " has an issue. The code to download isn't working. Anyone know of where we can download it from? The script is called "Chart"

    Thanks,

    Chart_20111224.sql is in the zip file that is downloaded. The download link works when I tried. This is the link to the zip file:

    141994.zip

    Sue

    Thank you for the link. Interesting, the download will not work with Edge however works fine with Chrome.

    Your welcome. Interesting but not surprising I guess. It's my least favorite browser due to all the issues like that.

    Sue

     

     

    Here in 2021 the link to the article at sqlmag.com doesn't seem to work, but I found the article at this link:

    https://www.itprotoday.com/sql-server/generating-charts-and-drawings-sql-server-management-studio

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply