Database | Proc | Application | Created | Links |
sybsystemprocs | sp_rename_qpgroup | 31 Aug 14 | Defects 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 |