March 7, 2019 at 9:14 pm
Comments posted to this topic are about the item Scripting procedure versions
March 7, 2019 at 11:33 pm
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”
March 8, 2019 at 5:03 am
Guessed and got it wrong.
Bad end to the week.
March 8, 2019 at 6:53 am
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.
March 8, 2019 at 7:10 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.
_______________________________________________________________
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/
March 8, 2019 at 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.
March 8, 2019 at 7:40 am
RonKyle - Friday, March 8, 2019 7:13 AMWeird. 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/
March 8, 2019 at 9:16 am
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.
March 8, 2019 at 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?
March 8, 2019 at 12:39 pm
Scott Coleman - Friday, March 8, 2019 11:32 AMYou 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/
March 8, 2019 at 1:53 pm
Sean Lange - Friday, March 8, 2019 12:39 PMScott Coleman - Friday, March 8, 2019 11:32 AMYou 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
March 8, 2019 at 2:14 pm
Sue_H - Friday, March 8, 2019 1:53 PMSean Lange - Friday, March 8, 2019 12:39 PMScott Coleman - Friday, March 8, 2019 11:32 AMYou 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/
March 8, 2019 at 2:24 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.
It is deprecated, so I wouldn't expect much.
March 8, 2019 at 2:39 pm
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.
March 8, 2019 at 2:42 pm
Sean Lange - Friday, March 8, 2019 2:14 PMGosh 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 16 total)
You must be logged in to reply to this topic. Login to reply