DatabaseProcApplicationCreatedLinks
sybsystemprocssp_start_rep_agent  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_start_rep_agent"	18378
4     **
5     ** 17421, "No such database -- run sp_helpdb to list databases."
6     ** 18373, "SQL Server is not currently configured to use Replication Agent threads. Use sp_configure to set this property."
7     ** 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."
8     ** 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."
9     ** 18378, "Incorrect syntax for sp_start_rep_agent. Usage: sp_start_rep_agent  [, {recovery|recovery_foreground} [,,  [,, , ]]]."
10    ** 18379, "Parameters other than the database name can be specified only when starting the Replication Agent thread in recovery mode. The Replication Agent thread was not started."
11    ** 18380, "Replication Agent thread is started for database '%1!'."
12    ** 18421, "Failed to start the Replication Agent thread for database '%1!'."
13    */
14    create or replace procedure sp_start_rep_agent
15        @dbname varchar(255), /* database name - required */
16        @mode varchar(20) = NULL,
17        @connect_ds varchar(255) = NULL,
18        @connect_db varchar(255) = NULL,
19        @rs_servername varchar(255) = NULL,
20        @rs_username varchar(255) = NULL,
21        @rs_password varchar(255) = NULL
22    as
23    
24        declare @dbid int /* dbid of the database */
25        declare @dbuid int /* id of the owner of the database */
26        declare @msg varchar(1024)
27        declare @sptlang int
28        declare @name varchar(30)
29        declare @procval int
30        declare @recovery int
31        declare @operation int /* Type of restart: resync, recovery,... */
32        declare @recovery_background int
33        declare @recovery_foreground int
34        declare @dbresync_purge int
35        declare @dbresync_init int
36        declare @dbresync int
37        declare @no_more_arguments int
38        declare @dummy int
39        declare @nullarg char(1)
40        declare @gp_enabled int
41        declare @status1 int
42        declare @status2 int
43        declare @stream_repl_enabled varchar(5)
44    
45        select @status1 = 1
46        select @status2 = 1
47        select @stream_repl_enabled = "false"
48        select @recovery_background = 1 /* RA_STARTUP_RECOVERY_BACKGROUND */
49        select @recovery_foreground = 2 /* RA_STARTUP_RECOVERY_FOREGROUND */
50        select @dbresync_purge = 8 /* RA_STARTUP_RESYNC_PURGE */
51        select @dbresync_init = 16 /* RA_STARTUP_RESYNC_INIT */
52        select @dbresync = 32 /* RA_STARTUP_RESYNC */
53    
54        if @@trancount = 0
55        begin
56            set chained off
57        end
58    
59        set transaction isolation level 1
60    
61        select @sptlang = @@langid
62    
63        if @@langid != 0
64        begin
65            if not exists (
66                    select * from master.dbo.sysmessages where error
67                        between 17050 and 17069
68                        and langid = @@langid)
69                select @sptlang = 0
70        end
71    
72        /*
73        **  Verify the database name and get the @dbid and @dbuid
74        */
75        select @dbid = dbid, @dbuid = suid
76        from master.dbo.sysdatabases
77        where name = @dbname
78    
79        /*
80        **  If @dbname not found, say so and list the databases.
81        */
82        if @dbid is NULL
83        begin
84            /*
85            ** 17421, "No such database -- run sp_helpdb to list databases."
86            */
87            raiserror 17421
88            return (1)
89        end
90    
91        /*
92        **  If granular permissions is not enabled only the Database Owner (DBO) or
93        **  Accounts with SA role or replication role can execute it.
94        **  First check if we are the DBO if the database name is specified.
95        **  If granular permissions is enabled then users with
96        **  'monitor server replication' or 'manage replication' permission can
97        **  execute it.
98        */
99        select @nullarg = NULL
100   
101       execute @status1 = sp_aux_checkroleperm "sa_role", "manage replication",
102           @nullarg, @gp_enabled output
103   
104       if (@status1 != 0)
105           execute @status2 = sp_aux_checkroleperm "replication_role",
106               @nullarg, @nullarg, @gp_enabled output
107   
108       if (@gp_enabled = 0)
109       begin
110           if (@dbname is NULL) or (suser_id() != @dbuid)
111           begin
112               /* Check if we have sa_role or replication_role. */
113               if ((@status1 != 0) and (@status2 != 0))
114               begin
115                   /*
116                   ** Audit failure. This will result in three messages, 
117                   ** but we will live with that until there is a 
118                   ** better 'proc_role()' interface.
119                   */
120                   select @procval = proc_role("sa_role")
121                   select @procval = proc_role("replication_role")
122   
123                   /* 18375, "You are not authorized to execute this 
124                   ** stored procedure. Only the System Administrator (SA),
125                   **  the Database Owner (DBO) or a user with 
126                   ** replication_role authorization can execute this 
127                   ** stored procedure."
128                   */
129                   raiserror 18375
130                   return (1)
131               end
132           end
133   
134           /* Audit success(es) */
135           if (@status1 = 0)
136               select @procval = proc_role("sa_role")
137           if (@status2 = 0)
138               select @procval = proc_role("replication_role")
139       end
140       else
141       begin
142           select @dummy = proc_auditperm("manage replication", @status1, @dbname)
143           if (@status1 != 0)
144           begin
145               return 1
146           end
147       end
148   
149       /* Check if Rep Agent threads are enabled. */
150       if is_rep_agent_enabled() = 0
151       begin
152           /* 18373, "SQL Server is not currently configured to use Replication 
153           ** Agent threads. Use sp_configure to set this property."
154           */
155           raiserror 18373
156           return (1)
157       end
158   
159       if is_rep_agent_enabled(@dbid) = 0
160       begin
161           /* 18374, "Database '%1!' is not configured to use Replication Agent. 
162           ** Run sp_config_rep_agent without parameters to see a list of 
163           ** databases that use Replication Agent. Use the ENABLE option of 
164           ** sp_config_rep_agent to configure a database to use this feature."
165           */
166           raiserror 18374, @dbname
167           return (1)
168       end
169   
170       /*
171       ** Validate the arguments.
172       */
173       select @mode = lower(@mode)
174       select @no_more_arguments = 1
175       if @mode = "recovery"
176       begin
177           select @operation = @recovery_background
178           select @no_more_arguments = 0
179   
180       end
181       else if @mode = "recovery_foreground"
182       begin
183           select @operation = @recovery_foreground
184           select @no_more_arguments = 0
185       end
186       else if @mode = "resync purge"
187       begin
188           select @operation = @dbresync_purge
189       end
190       else if @mode = "resync init"
191       begin
192           select @operation = @dbresync_init
193       end
194       else if @mode = "resync"
195       begin
196           select @operation = @dbresync
197       end
198       else if @mode is null
199       begin
200           select @operation = 0
201       end
202       else
203       begin
204           /* 18378, "Incorrect syntax for sp_start_rep_agent. Usage: 
205           ** sp_start_rep_agent  [, {recovery|recovery_foreground} 
206           ** [,,  [,,
207           ** , ]]|{'resync'|'resync init'|'resync purge'}]."
208           */
209           raiserror 18378
210           return (1)
211       end
212   
213       if (@operation != 0)
214       begin
215           /* 
216           ** If we have a non-zero startup mode (i.e. recovery or resync)
217           ** we need to check if 'stream replication' is enabled. We do
218           ** not support recovery or resync modes with stream replication
219           */
220           select @stream_repl_enabled =
221               convert(varchar(5),
222               rep_agent_config(@dbid,
223                   "current config",
224                   "stream replication"))
225           if (@stream_repl_enabled = "true")
226           begin
227               /*
228               ** 17225 
229               ** "The '%1!' option is not supported when '%2!' is %3!."
230               ** Note that we get "enabled" from 19247 for parameter 3.
231               */
232               exec sp_getmessage 19247, @msg output
233               raiserror 17225, @mode, 'stream replication', @msg
234               return (1)
235           end
236       end
237   
238       if (@no_more_arguments = 1)
239       begin
240           if (@connect_ds is NOT NULL
241                   or @connect_db is NOT NULL
242                   or @rs_servername is NOT NULL
243                   or @rs_username is NOT NULL
244                   or @rs_password is NOT NULL)
245           begin
246               /* 18379, "Parameters other than the database name can be 
247               ** specified only when starting the Replication Agent thread 
248               ** in recovery mode. The Replication Agent thread was not 
249               ** started."
250               */
251               raiserror 18379
252               return (1)
253           end
254       end
255   
256       if @operation = 0
257       begin
258           if (rep_agent_admin("start thread", @dbid) = 0)
259           begin
260               /* 18421, "Failed to start the Replication Agent thread for 
261               ** database '%1!'."
262               */
263               raiserror 18421, @dbname
264               return (1)
265           end
266       end
267       else if (@operation = @recovery_background or @operation = @recovery_foreground)
268       begin
269           /* Need to start in recovery mode */
270           if (rep_agent_admin("start thread", @dbid, @operation, @connect_ds,
271                       @connect_db, @rs_servername, @rs_username, @rs_password) = 0)
272           begin
273               /* 18421, "Failed to start the Replication Agent thread for 
274               ** database '%1!'."
275               */
276               raiserror 18421, @dbname
277               return (1)
278           end
279       end
280       else
281       begin
282           /* Need to start in db resync mode */
283           if rep_agent_admin("start thread", @dbid, @operation) = 0
284           begin
285               /* 18421, "Failed to start the Replication Agent thread for 
286               ** database '%1!'."
287               */
288               raiserror 18421, @dbname
289               return (1)
290           end
291       end
292   
293       /* Success message */
294       /* 18380, "Replication Agent thread is started for database '%1!'." */
295       exec sp_getmessage 18380, @msg output
296       print @msg, @dbname
297   
298       return (0)
299   


exec sp_procxmode 'sp_start_rep_agent', 'AnyMode'
go

Grant Execute on sp_start_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 68
 QTYP 4 Comparison type mismatch smallint = int 68
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_start_rep_agent  
 MNER 3 No Error Check should check return value of exec 232
 MNER 3 No Error Check should check return value of exec 295
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 238
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 258
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 298
 QISO 3 Set isolation level 59
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
66
 VNRD 3 Variable is not read @sptlang 69
 VNRD 3 Variable is not read @procval 138
 VNRD 3 Variable is not read @dummy 142
 VUNU 3 Variable is not used @name 28
 VUNU 3 Variable is not used @recovery 30
 MSUB 2 Subquery Marker 65
 MTR1 2 Metrics: Comments Ratio Comments: 49% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 28 = 38dec - 12exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 146 14

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysdatabases (1)  
reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
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)