DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_qpgroup  31 Aug 14Defects Dependencies

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


exec sp_procxmode 'sp_drop_qpgroup', 'AnyMode'
go

Grant Execute on sp_drop_qpgroup to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 84
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {gid}		 102
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 120
 QTYP 4 Comparison type mismatch smallint = int 120
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 121
 QTYP 4 Comparison type mismatch smallint = int 121
 MGTP 3 Grant to public sybsystemprocs..sp_drop_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MLCH 3 Char type with length>30 char(255) 32
 MNER 3 No Error Check should check return value of exec 82
 MNER 3 No Error Check should check return value of exec 84
 MNER 3 No Error Check should check @@error after delete 119
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 139
 QISO 3 Set isolation level 72
 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}		 120
 MSUB 2 Subquery Marker 101
 MTR1 2 Metrics: Comments Ratio Comments: 68% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 8dec - 7exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 46 24

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