DatabaseProcApplicationCreatedLinks
sybsystemprocssp_rename_qpgroup  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_rename_qpgroup"
6     **
7     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17756, "The execution of the stored procedure '%1!' in database
10    **         '%2!' was aborted because there was an error in writing the
11    **         replication log record."
12    ** 18682, "Cannot drop or rename the default query plans groups ap_stdin and ap_stdout."
13    ** 18639, "The is no query plans group named '%1!' in this database."
14    ** 18660, "There is already a query plans group named '%1!' in this database."
15    */
16    
17    /*
18    ** sp_rename_qpgroup changes the name of an AP group, by 
19    ** modifying the SYSATTRIBUTES name/GID association.
20    */
21    
22    create procedure sp_rename_qpgroup
23        @old_name varchar(30),
24        @new_name varchar(30)
25    as
26        declare
27            @gid int,
28            @class int,
29            @attribute int,
30            @object_type char(2),
31            @dbname char(20)
32    
33        set nocount on
34    
35        select @dbname = db_name()
36    
37        if user_id() != 1 and charindex("sa_role", show_role()) = 0
38        begin
39            /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
40            raiserror 17230
41            return 2
42        end
43    
44        /*
45        ** AP group handling may not be done within a transaction, 
46        ** specifically for sp_add_qpgroup. 
47        **
48        ** Indeed, once a group is created, it can be enabled in 
49        ** DUMP mode and plans start being captured therein - as 
50        ** nested internal Xacts. So if the group creation is part 
51        ** of an user Xact that is rolled back, the APs stay behind, 
52        ** group-less. 
53        **
54        ** sp_drop_qpgroup and sp_rename_qpgroup don't raise 
55        ** the same issue, but for coherence they are subject 
56        ** to the same limitation.
57        */
58        if @@trancount > 0
59        begin
60            /*
61            ** 17260, "Can't run %1! from within a transaction."
62            */
63            raiserror 17260, "sp_rename_qpgroup"
64            return 1
65        end
66    
67        /* Don't start a transaction for the user, he does not expect it. */
68        set chained off
69    
70        /* Don't hold long READ locks, the user might not appreciate it. */
71        set transaction isolation level 1
72    
73        exec sp_aux_sargs_qpgroup @class out, @attribute out, @object_type out
74    
75        /* check the new name is not already in use */
76        exec sp_aux_get_qpgroup @new_name, @gid out
77    
78        if @gid is not null
79        begin
80            /* 18660, "There is already a query plans group named '%1!' in this database." */
81            raiserror 18660, @new_name
82            return 3
83        end
84    
85        /* it's illegal to rename the default AP groups */
86        if @old_name = "ap_stdin" or @old_name = "ap_stdout"
87        begin
88            /* 18682, "Cannot drop or rename the default query plans groups ap_stdin and ap_stdout." */
89            raiserror 18682
90            return 4
91        end
92    
93        /* get the GID */
94        exec sp_aux_get_qpgroup @old_name, @gid out
95    
96        if @gid is null
97        begin
98            /* 18639, "The is no query plans group named '%1!' in this database." */
99            raiserror 18639, @old_name
100           return 1
101       end
102   
103       /*
104       ** This transaction also writes a log record for replicating the
105       ** invocation of this procedure. If logexec() fails, the transaction
106       ** is aborted.
107       **
108       ** IMPORTANT: The name rs_logexec is significant and is used by
109       ** Replication Server.
110       */
111       begin transaction rs_logexec
112   
113       /*
114       ** Update SYSATTRIBUTES with the new name.
115       ** The SARGs were prepared by sp_aux_sargs_qpgroup.
116       */
117       update sysattributes
118       set object_cinfo = @new_name
119       where class = @class
120           and attribute = @attribute
121           and object_type = @object_type
122           and object = @gid
123   
124       /*
125       ** Write the log record to replicate this invocation
126       ** of the stored procedure.
127       */
128       if (logexec() != 1)
129       begin
130           /*
131           ** 17756, "The execution of the stored procedure
132           **         '%1!' in database '%2!' was aborted
133           **          because there was an error in writing
134           **          the replication log record."
135           */
136           raiserror 17756, "sp_rename_qpgroup", @dbname
137           rollback transaction rs_logexec
138           return (1)
139       end
140   
141       commit transaction rs_logexec
142       return 0
143   


exec sp_procxmode 'sp_rename_qpgroup', 'AnyMode'
go

Grant Execute on sp_rename_qpgroup to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 119
 QTYP 4 Comparison type mismatch smallint = int 119
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 120
 QTYP 4 Comparison type mismatch smallint = int 120
 MGTP 3 Grant to public sybsystemprocs..sp_rename_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MNER 3 No Error Check should check return value of exec 73
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check return value of exec 94
 MNER 3 No Error Check should check @@error after update 117
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 138
 MUPK 3 Update column which is part of a PK or unique index object_cinfo 118
 QISO 3 Set isolation level 71
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
119
 MTR1 2 Metrics: Comments Ratio Comments: 63% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 8dec - 7exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 45 22

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sysattributes  
calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   reads table sybsystemprocs..sysattributes  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup