DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dumpoptimize  31 Aug 14Defects 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
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: uint vs int 63
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause instancenames 63
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 20
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_dumpoptimize  
 MNER 3 No Error Check should check return value of exec 59
 MNER 3 No Error Check should check return value of exec 74
 MNER 3 No Error Check should check return value of exec 85
 MUCO 3 Useless Code Useless Begin-End Pair 28
 MUCO 3 Useless Code Useless Brackets 34
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 88
 CUPD 2 Updatable Cursor Marker (updatable by default) 63
 MDYS 2 Dynamic SQL Marker 77
 MTR1 2 Metrics: Comments Ratio Comments: 50% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 7dec - 2exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 36 20

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)