DatabaseProcApplicationCreatedLinks
sybsystemprocssp_stop_rep_agent  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_stop_rep_agent"	18376
4     **
5     ** 17421, "No such database -- run sp_helpdb to list databases."
6     ** 18374, "Database '%1!' is not configured to use Replication Agent. Run sp_config_rep_agent without parameters to see a list of databases that use Replication Agent. Use the ENABLE option of sp_config_rep_agent to configure a database to use this feature."
7     ** 18375, "You are not authorized to execute this stored procedure. Only the System Administrator (SA), the Database Owner (DBO) or a user with replication_role authorization can execute this stored procedure."
8     ** 18376, "Incorrect syntax for sp_stop_rep_agent. Usage: sp_stop_rep_agent  [,nowait]."
9     ** 18377, "The Replication Agent thread for database '%1!' is being stopped."
10    ** 18422, "The Replication Agent thread for database '%1!' is not currently running.
11    */
12    
13    create or replace procedure sp_stop_rep_agent
14        @dbname varchar(255), /* database name - required */
15        @optname varchar(20) = NULL /* with nowait option */
16    as
17    
18        declare @dbid int, /* dbid of the database */
19            @dbuid int, /* id of the owner of the database */
20            @msg varchar(1024),
21            @sptlang int,
22            @name varchar(30),
23            @procval int,
24            @nowait int, /* is stop graceful ? */
25            @has_sa_role int, /* User has SA role. */
26            @has_repl_role int, /* User has REPLICATION role. */
27            @builtin_retstat int
28    
29        declare @dummy int
30        declare @nullarg char(1)
31        declare @gp_enabled int
32        declare @status1 int
33        declare @status2 int
34    
35        select @status1 = 1
36        select @status2 = 1
37    
38        if @@trancount = 0
39        begin
40            set chained off
41        end
42    
43        set transaction isolation level 1
44    
45        select @sptlang = @@langid
46    
47        if @@langid != 0
48        begin
49            if not exists (
50                    select * from master.dbo.sysmessages where error
51                        between 17050 and 17069
52                        and langid = @@langid)
53                select @sptlang = 0
54        end
55    
56        /*
57        **  Verify the database name and get the @dbid and @dbuid
58        */
59        select @dbid = dbid, @dbuid = suid
60        from master.dbo.sysdatabases
61        where name = @dbname
62    
63        /*
64        **  If @dbname not found, say so and list the databases.
65        */
66        if @dbid is NULL
67        begin
68            /*
69            ** 17421, "No such database -- run sp_helpdb to list databases."
70            */
71            raiserror 17421
72            return (1)
73        end
74    
75        /*
76        **  If granular permissions is not enabled only the Database Owner (DBO) or
77        **  Accounts with SA role or replication role can execute it.
78        **  First check if we are the DBO.
79        **  If granular permissions is enabled then users with 
80        **  'monitor server replication' or 'manage replication' permission can 
81        **  execute it.
82        */
83        select @nullarg = NULL
84    
85        execute @status1 = sp_aux_checkroleperm "sa_role", "manage replication",
86            @nullarg, @gp_enabled output
87    
88        execute @status2 = sp_aux_checkroleperm "replication_role", @nullarg, @nullarg,
89            @gp_enabled output
90    
91        if (@gp_enabled = 0)
92        begin
93            if (suser_id() != @dbuid)
94            begin
95                /* Check if we have sa_role or replication_role. */
96                if (@status1 != 0 and @status2 != 0)
97                begin
98                    /*
99                    ** Audit failure. This will result in three messages, 
100                   ** but we will live with that until there is a better 
101                   ** 'proc_role()' interface.
102                   */
103                   select @procval = proc_role("sa_role")
104                   select @procval = proc_role("replication_role")
105   
106                   /* 18375, "You are not authorized to execute this 
107                   ** stored procedure. Only the System Administrator(SA),
108                   ** the Database Owner (DBO) or a user with 
109                   ** replication_role authorization can execute this 
110                   ** stored procedure."
111                   */
112                   raiserror 18375
113                   return (1)
114               end
115           end
116           /* Audit success(es) */
117           if (@status1 = 0)
118               select @procval = proc_role("sa_role")
119           if (@status2 = 0)
120               select @procval = proc_role("replication_role")
121       end
122       else
123       begin
124           select @dummy = proc_auditperm("manage replication", @status1, @dbname)
125           if (@status1 != 0)
126           begin
127               return 1
128           end
129       end
130   
131       /* Verify that the database is using the Rep Agent. */
132       if is_rep_agent_enabled(@dbid) = 0
133       begin
134           /* 18374, "Database '%1!' is not configured to use Replication Agent. 
135           ** Run sp_config_rep_agent without parameters to see a list of 
136           ** databases that use Replication Agent. Use the ENABLE option of 
137           ** sp_config_rep_agent to configure a database to use this feature."
138           */
139           raiserror 18374, @dbname
140           return (1)
141       end
142   
143       /*
144       **  Verify that if optvalue if specified is the correct value.
145       */
146       if @optname = "nowait"
147       begin
148           select @nowait = 1
149       end
150       else if @optname is null
151       begin
152           select @nowait = 0
153       end
154       else
155       begin
156           /* 18376, "Incorrect syntax for sp_stop_rep_agent. Usage: 
157           ** sp_stop_rep_agent  [,nowait]."
158           */
159           raiserror 18376
160           return (1)
161       end
162   
163       /* Call the builtin to do the work. If it encounters an error, it will
164       ** raise an exception and output a message to the client.
165       */
166       select @builtin_retstat = rep_agent_admin("stop thread", @dbid, @nowait)
167   
168       if (@builtin_retstat = 1)
169       begin
170           /* 18377, "The Replication Agent thread for database '%1!' is 
171           ** being stopped."
172           */
173           exec sp_getmessage 18377, @msg output
174           print @msg, @dbname
175           return (0)
176       end
177       else if (@builtin_retstat = - 1)
178       begin
179           /* 
180           ** 18422, "The Replication Agent thread for database '%1!' is not 
181           ** currently running.
182           */
183           raiserror 18422, @dbname
184       end
185       else if (@builtin_retstat = 0)
186       begin
187           /* 19653, "Failed to stop the Replication Agent thread for 
188           ** database '%1!'.
189           */
190           raiserror 19653, @dbname
191       end
192       return (1)
193   


exec sp_procxmode 'sp_stop_rep_agent', 'AnyMode'
go

Grant Execute on sp_stop_rep_agent to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 52
 QTYP 4 Comparison type mismatch smallint = int 52
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_stop_rep_agent  
 MNER 3 No Error Check should check return value of exec 85
 MNER 3 No Error Check should check return value of exec 173
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 192
 QISO 3 Set isolation level 43
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
50
 VNRD 3 Variable is not read @sptlang 53
 VNRD 3 Variable is not read @procval 120
 VNRD 3 Variable is not read @dummy 124
 VUNU 3 Variable is not used @name 22
 VUNU 3 Variable is not used @has_sa_role 25
 VUNU 3 Variable is not used @has_repl_role 26
 MSUB 2 Subquery Marker 49
 MTR1 2 Metrics: Comments Ratio Comments: 54% 13
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 20dec - 7exi + 2 13
 MTR3 2 Metrics: Query Complexity Complexity: 78 13

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table master..sysmessages (1)  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)