Home Dashboard Directory Help
Search

SSMS 2012 inconsistently handles numbered procedures by AaronBertrand


Status: 

Closed
 as Fixed Help for as Fixed


6
0
Sign in
to vote
Type: Bug
ID: 764197
Opened: 9/22/2012 1:40:33 PM
Access Restriction: Public
0
Workaround(s)
view
3
User(s) can reproduce this bug

Description

In Management Studio 2012, attempting to modify a numbered procedure only yields the first numbered procedure, rather than all versions. Create the following numbered procedure:

CREATE PROCEDURE dbo.foo
AS SELECT 1;
GO
CREATE PROCEDURE dbo.foo;2
AS SELECT 2;

Now different functionality within SSMS treats this "single object" differently:

- Right-click procedure > Modify only shows the first version
- Right-click procedure > Script as alter only shows the first version
- Right-click procedure > Script as Create shows both
- Right-click procedure > Script as Drop and Create shows both
- Right-click database > Tasks > Generate Scripts shows both

It is also inconsistent within metadata queries (though I will admit that much of this may be by design):

- sp_helptext shows only the first version
- sys.sql_modules shows only the first version
- INFORMATION_SCHEMA.ROUTINES shows only the first version
- OBJECT_DEFINITION shows only the first version
- syscomments shows both
- sys.numbered_procedures shows only the second version

I know about this bug, which actually caused an error when trying to Modify a numbered procedure, and which was fixed in SQL Server 2008:

http://connect.microsoft.com/SQLServer/feedback/details/290112/impossible-to-manage-sps-that-have-multiple-versions

I realize that numbered procedures are deprecated, but while they continue to be supported, the tooling should be consistent in how they are handled. Users will have to manage their existing numbered procedures from the newest version of the tool, even if they are in the lowest version supported by the tool, long after the feature is actually discontinued in the engine.
Details
Sign in to post a comment.
Posted by AaronBertrand on 4/25/2013 at 9:18 AM
Closed as fixed? When? Where? How? More comments please?
Posted by mobe67 on 2/14/2013 at 4:03 AM
I'm having this problem too. Will be solved ? Does this way of work storeds, using numbered storeds, will be non supported in future versions ?? Must we change all our numbered storeds to unique name storeds ? Thanks.
Posted by Microsoft on 9/25/2012 at 2:49 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.

Thanks,

Alex Grach [MSFT SQL SERVER]
Sign in to post a workaround.