Database | Proc | Application | Created | Links |
sybsystemprocs | sp_import_qpgroup ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_import_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 ** 17703, "The name supplied is not a user, group, or aliased." 10 ** 17756, "The execution of the stored procedure '%1!' in database 11 ** '%2!' was aborted because there was an error in writing the 12 ** replication log record." 13 ** 18660, "There is already a query plans group named '%1!' in this database." 14 */ 15 16 /* 17 ** sp_import_qpgroup inserts in SYSQUERYPLANS the opaque user table 18 ** created by sp_export_qpgroup. By that , it creates a new group 19 ** (with the given name) for a specific user. 20 */ 21 22 create procedure sp_import_qpgroup 23 @tab varchar(255), 24 @usr varchar(30), 25 @group varchar(30) 26 as 27 declare 28 @gid int, 29 @uid int, 30 @old_id int, 31 @new_id int, 32 @dbname char(30) 33 34 35 set nocount on 36 37 select @dbname = db_name() 38 39 if user_id() != 1 and charindex("sa_role", show_role()) = 0 40 begin 41 /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */ 42 raiserror 17230 43 return 2 44 end 45 46 /* 47 ** AP group handling may not be done within a transaction. 48 */ 49 if @@trancount > 0 50 begin 51 /* 52 ** 17260, "Can't run %1! from within a transaction." 53 */ 54 raiserror 17260, "sp_import_qpgroup" 55 return 1 56 end 57 58 /* Don't start a transaction for the user, he does not expect it. */ 59 set chained off 60 61 /* Don't hold long READ locks, the user might not appreciate it. */ 62 set transaction isolation level 1 63 64 exec sp_aux_get_qpgroup @group, @gid out 65 66 select @uid = user_id(@usr) 67 68 if @uid is null 69 begin 70 /* 17703, "The name supplied is not a user, group, or aliased." */ 71 raiserror 17703 72 return 2 73 end 74 75 if @gid is null 76 begin 77 /* if the group doesn't exist, add it */ 78 exec sp_add_qpgroup @group 79 exec sp_aux_get_qpgroup @group, @gid out 80 end 81 else 82 begin 83 /* if it exists, check that it's empty */ 84 if exists (select 1 85 from sysqueryplans 86 where gid = @gid 87 and uid = @uid) 88 begin 89 /* 18660, "There is already a query plans group named '%1!' in this database." */ 90 raiserror 18660, @group 91 return 2 92 end 93 end /* Adaptive Server has expanded all '*' elements in the following statement */ 94 95 /* 96 ** Create 2 work tables, one with the structure of SYSQUERYPLANS, 97 ** to hold the APs to be imported, another one to hold the old-new 98 ** AP id mapping. Note that the first table will be useless when 99 ** dynamic cursors will work in TSQL, as @tab could be used instead. 100 */ 101 102 select sysqueryplans.uid, sysqueryplans.gid, sysqueryplans.hashkey, sysqueryplans.id, sysqueryplans.type, sysqueryplans.sequence, sysqueryplans.status, sysqueryplans.text, sysqueryplans.dbid, sysqueryplans.qpdate, sysqueryplans.sprocid, sysqueryplans.hashkey2, sysqueryplans.key1, sysqueryplans.key2, sysqueryplans.key3, sysqueryplans.key4 into #aps 103 from sysqueryplans 104 where 1 = 0 105 106 create table #ids_map(old_id int, new_id int) 107 108 execute ("insert into #aps 109 select * from " + @tab 110 ) 111 112 /* 113 ** For each AP to insert, generate the new ID and update the 114 ** old-new AP ID map. 115 */ 116 117 declare ids cursor for select distinct id from #aps 118 119 open ids 120 fetch ids into @old_id 121 122 while @@sqlstatus = 0 123 begin 124 create plan 'select 1' '()' into @group 125 and set @new_id 126 127 delete sysqueryplans where id = @new_id 128 insert #ids_map values (@old_id, @new_id) 129 130 fetch ids into @old_id 131 end 132 133 /* 134 ** This transaction also writes a log record for replicating the 135 ** invocation of this procedure. If logexec() fails, the transaction 136 ** is aborted. 137 ** 138 ** IMPORTANT: The name rs_logexec is significant and is used by 139 ** Replication Server. 140 */ 141 begin transaction rs_logexec 142 143 /* finally, feed SYSQUERYPLANS */ 144 insert sysqueryplans 145 select @uid, @gid, t.hashkey, m.new_id, 146 t.type, t.sequence, t.status, t.text, 147 t.dbid, t.qpdate, t.sprocid, t.hashkey2, 148 t.key1, t.key2, t.key3, t.key4 149 from #aps t, #ids_map m 150 where t.id = m.old_id 151 152 /* 153 ** Write the log record to replicate this invocation 154 ** of the stored procedure. 155 */ 156 if (logexec() != 1) 157 begin 158 /* 159 ** 17756, "The execution of the stored procedure 160 ** '%1!' in database '%2!' was aborted 161 ** because there was an error in writing 162 ** the replication log record." 163 */ 164 raiserror 17756, "sp_import_qpgroup", @dbname 165 rollback transaction rs_logexec 166 return (1) 167 end 168 169 commit transaction rs_logexec 170 return 0 171
exec sp_procxmode 'sp_import_qpgroup', 'AnyMode' go Grant Execute on sp_import_qpgroup to public go
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_add_qpgroup ![]() calls proc sybsystemprocs..sp_aux_get_qpgroup ![]() reads table sybsystemprocs..sysattributes ![]() calls proc sybsystemprocs..sp_aux_sargs_qpgroup ![]() calls proc sybsystemprocs..sp_aux_sargs_qpgroup ![]() read_writes table sybsystemprocs..sysattributes ![]() calls proc sybsystemprocs..sp_aux_get_qpgroup ![]() read_writes table sybsystemprocs..sysqueryplans ![]() read_writes table tempdb..#ids_map (1) read_writes table tempdb..#aps (1) |