Scripting procedure versions

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715446

    Comments posted to this topic are about the item Scripting procedure versions

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71364

    I'm really glad we do not have to do this any more - using schemas makes life so much easier...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • paul s-306273

    SSChampion

    Points: 10555

    Guessed and got it wrong.
    Bad end to the week.

  • RonKyle

    SSC-Dedicated

    Points: 31457

    I'm really glad we do not have to do this any more - using schemas makes life so much easier

    This approach to stored procedures has nothing to do with schemas.  Before they were deprecated, I used this a lot for my data warehouse ETL.  For example, if it took 4 queries to transform a particular dimension, I created a set through <Name of Stored Proc>;2 - <Name of Stored Proc>;5 -.  Then the <Name of Stored Proc> procedure called the ;2 through ;5 in turn.  But this meant I only had to call one stored procedure and only one stored procedure appeared in the solution explorer.  Now I have to have separate stored procedures for each or call multiple queries from the same stored procedure.  So it's a pity these are going away in my view.  I don't think many people really knew how to use them.

  • Sean Lange

    SSC Guru

    Points: 286423

    Weird. I tried this on a 2008R2 instance and a 2014 and in both cases when I choose modify in object explorer I get ONLY the first version not both as the answer indicates here.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • RonKyle

    SSC-Dedicated

    Points: 31457

    Weird. I tried this on a 2008R2 instance and a 2014 and in both cases when I choose modify in object explorer I get ONLY the first version not both as the answer indicates here.

    I've only used these on 2005, but I always got all of them back.  I will try it in 2016 as a test.

  • Sean Lange

    SSC Guru

    Points: 286423

    RonKyle - Friday, March 8, 2019 7:13 AM

    Weird. I tried this on a 2008R2 instance and a 2014 and in both cases when I choose modify in object explorer I get ONLY the first version not both as the answer indicates here.

    I've only used these on 2005, but I always got all of them back.  I will try it in 2016 as a test.

    In both cases if I choose "script to new window" I get both. But choosing "modify" returns only the original.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • jascunce

    Ten Centuries

    Points: 1368

    Just wanted to throw this out there. If you look in sys.sql_modules, only the definition for the 1st version of stored procedure is reported. This was bugging me a bit so I dug a little and found that both versions are stored in sys.syscomments. This, like stored procedure versioning, is deprecated and will be removed altogether in the future.

  • Scott Coleman

    One Orange Chip

    Points: 27377

    You can find the second and later versions in sys.numbered_procedures.

    Wouldn't this behavior depend on which version on Management Studio you're using, rather than the SQL Server where the procs are deployed?  The Object Browser SMO libraries are defined in the client, not on the server.

    I tried this on SQL 2008 R2, SQL 2012, and SQL 2016 (both as client tool versions and as target server), plus the latest SSMS 17.9.1, and in all cases the Modify option scripted both versions.

    I found this link saying Modify does not script both versions in SQL 2012 https://stackoverflow.com/questions/12546418/modifying-numbered-procedures-in-management-studio-2012 but I could not reproduce it.  Possibly it was fixed?

  • Sean Lange

    SSC Guru

    Points: 286423

    Scott Coleman - Friday, March 8, 2019 11:32 AM

    You can find the second and later versions in sys.numbered_procedures.

    Wouldn't this behavior depend on which version on Management Studio you're using, rather than the SQL Server where the procs are deployed?  The Object Browser SMO libraries are defined in the client, not on the server.

    I tried this on SQL 2008 R2, SQL 2012, and SQL 2016 (both as client tool versions and as target server), plus the latest SSMS 17.9.1, and in all cases the Modify option scripted both versions.

    I found this link saying Modify does not script both versions in SQL 2012 https://stackoverflow.com/questions/12546418/modifying-numbered-procedures-in-management-studio-2012 but I could not reproduce it.  Possibly it was fixed?

    Excellent point Scott. Seems that in SSMS 2014 it does not return both versions, only the original which sounds similar to the SO post you linked.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sue_H

    SSC Guru

    Points: 90195

    Sean Lange - Friday, March 8, 2019 12:39 PM

    Scott Coleman - Friday, March 8, 2019 11:32 AM

    You can find the second and later versions in sys.numbered_procedures.

    Wouldn't this behavior depend on which version on Management Studio you're using, rather than the SQL Server where the procs are deployed?  The Object Browser SMO libraries are defined in the client, not on the server.

    I tried this on SQL 2008 R2, SQL 2012, and SQL 2016 (both as client tool versions and as target server), plus the latest SSMS 17.9.1, and in all cases the Modify option scripted both versions.

    I found this link saying Modify does not script both versions in SQL 2012 https://stackoverflow.com/questions/12546418/modifying-numbered-procedures-in-management-studio-2012 but I could not reproduce it.  Possibly it was fixed?

    Excellent point Scott. Seems that in SSMS 2014 it does not return both versions, only the original which sounds similar to the SO post you linked.

    Maybe not. I just tried with a SSMS 2014 (ver 12.0.5600.1) and I got both versions on modify.

    Sue

  • Sean Lange

    SSC Guru

    Points: 286423

    Sue_H - Friday, March 8, 2019 1:53 PM

    Sean Lange - Friday, March 8, 2019 12:39 PM

    Scott Coleman - Friday, March 8, 2019 11:32 AM

    You can find the second and later versions in sys.numbered_procedures.

    Wouldn't this behavior depend on which version on Management Studio you're using, rather than the SQL Server where the procs are deployed?  The Object Browser SMO libraries are defined in the client, not on the server.

    I tried this on SQL 2008 R2, SQL 2012, and SQL 2016 (both as client tool versions and as target server), plus the latest SSMS 17.9.1, and in all cases the Modify option scripted both versions.

    I found this link saying Modify does not script both versions in SQL 2012 https://stackoverflow.com/questions/12546418/modifying-numbered-procedures-in-management-studio-2012 but I could not reproduce it.  Possibly it was fixed?

    Excellent point Scott. Seems that in SSMS 2014 it does not return both versions, only the original which sounds similar to the SO post you linked.

    Maybe not. I just tried with a SSMS 2014 (ver 12.0.5600.1) and I got both versions on modify.

    Sue

    Gosh MS really has made this one fun. My version is 12.0.2000.8 and it gets only one. I just used another machine which has 12.0.4100.1 and it returns both versions. I was connected to the same database in both versions of SSMS. There is nothing like consistency and this is certainly nothing like consistency.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • RonKyle

    SSC-Dedicated

    Points: 31457

    Gosh MS really has made this one fun. My version is 12.0.2000.8 and it gets only one. I just used another machine which has 12.0.4100.1 and it returns both versions. I was connected to the same database in both versions of SSMS. There is nothing like consistency and this is certainly nothing like consistency.

    It is deprecated, so I wouldn't expect much.

  • Scott Coleman

    One Orange Chip

    Points: 27377

    So depending on your Management Studio version, some times answer 1 is correct even if the server is SQL 2017?  Sounds like 82 of us deserve a point.

  • Sue_H

    SSC Guru

    Points: 90195

    Sean Lange - Friday, March 8, 2019 2:14 PM

    Gosh MS really has made this one fun. My version is 12.0.2000.8 and it gets only one. I just used another machine which has 12.0.4100.1 and it returns both versions. I was connected to the same database in both versions of SSMS. There is nothing like consistency and this is certainly nothing like consistency.

    I do think Scott is correct about it being based off SSMS.
    And I have convinced myself that upgrading, using other versions of SSMS on the same PC can pick up bugs from previous versions of SSMS...in other words, the files don't get all updated or references get out of whack. I've had to uninstall all versions of SSMS and reinstall to get rid of bugs before. I've seen similar things up here and on other forums. So I am convincing myself again that you are hitting that issue 🙂

    Sue

Viewing 15 posts - 1 through 15 (of 17 total)

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