Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dumpoptimize | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** This procedure configures the amount of data dumped by the Backup Server 5 ** during the 'dump database' command. It sends bs_optimize RPC to the local 6 ** Backup Server, passing all the parameters to the stored procedure as it is. 7 ** The parsing of all the options is done by the Backup Server. 8 ** 9 ** This can be issued only by users with SA role or oper_role or by the 10 ** Database owner. 11 */ 12 13 /* 14 ** Messages for "sp_dumpoptimize" 15 ** 16 ** 17260, "Can't run %1! from within a transaction." 17 ** 19958, "Backup Server policy is set to '%1!'." 18 */ 19 20 create procedure sp_dumpoptimize 21 @opt1 varchar(255) = NULL, /* First optimization parameter */ 22 @opt2 varchar(255) = NULL /* Second optimization parameter */ 23 as 24 25 declare @bs_name varchar(255) /* BS name */ 26 declare @bs_policy varchar(255) 27 declare @msg varchar(1024) /* Display BS policy */ 28 begin 29 30 if @@trancount > 0 31 begin 32 /* 17260, "Can't run %1! from within a transaction." */ 33 raiserror 17260, "sp_dumpoptimize" 34 return (1) 35 end 36 37 38 /* 39 ** Only the Database Owner (DBO) or accounts with either SA role or oper_role 40 ** can execute this procedure. 41 ** 42 ** If user does not have either the sa role or the oper_role or they are not the 43 ** database owner, we cannot continue any further. 44 */ 45 if ((user_id() != 1) and (proc_role("oper_role") = 0) 46 and (proc_role("sa_role") = 0)) 47 return (1) 48 49 select @bs_policy = srvnetname from master..sysservers where srvname = "SYB_BACKUP" 50 51 /* 52 ** If the Multiple Backup server feature is enabled with Backup server policy 53 ** as $dedicated or $roundrobin, execute bs_optimize for all the active 54 ** Backup servers within cluster. 55 */ 56 if ((lower(@bs_policy) = "$dedicated") or (lower(@bs_policy) = "$roundrobin")) 57 begin 58 /* 19958, "Backup Server policy is set to '%1!'." */ 59 exec sp_getmessage 19958, @msg output 60 print @msg, @bs_policy 61 62 declare instancenames cursor for 63 select srvname from master..sysservers where srvstatus2 = 4 64 65 open instancenames 66 67 fetch instancenames into @bs_name 68 69 while (@@sqlstatus = 0) 70 begin 71 select @bs_name = @bs_name + "_BS" 72 73 /* 19959, "Attempting to execute RPC for Backup Server '%1!'." */ 74 exec sp_getmessage 19959, @msg output 75 print @msg, @bs_name 76 77 exec (@bs_name + '...bs_optimize @opt1, @opt2') 78 fetch instancenames into @bs_name 79 end 80 close instancenames 81 end 82 else 83 begin 84 /* Issue the bs_optimize RPC to the local Backup Server */ 85 exec SYB_BACKUP...bs_optimize @opt1, @opt2 86 87 end 88 return (0) 89 end 90 91
exec sp_procxmode 'sp_dumpoptimize', 'AnyMode' go Grant Execute on sp_dumpoptimize to public go
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..sysservers (1) |