DatabaseProcApplicationCreatedLinks
sybsystemprocssp_start_rep_agent  31 Aug 14Defects 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 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 @has_sa_role int /* User has SA role. */
32        declare @has_repl_role int /* User has REPLICATION role. */
33        declare @operation int /* Type of restart: resync, recovery,... */
34        declare @recovery_background int
35        declare @recovery_foreground int
36        declare @dbresync_purge int
37        declare @dbresync_init int
38        declare @dbresync int
39        declare @no_more_arguments int
40    
41        select @recovery_background = 1 /* RA_STARTUP_RECOVERY_BACKGROUND */
42        select @recovery_foreground = 2 /* RA_STARTUP_RECOVERY_FOREGROUND */
43        select @dbresync_purge = 8 /* RA_STARTUP_RESYNC_PURGE */
44        select @dbresync_init = 16 /* RA_STARTUP_RESYNC_INIT */
45        select @dbresync = 32 /* RA_STARTUP_RESYNC */
46    
47        if @@trancount = 0
48        begin
49            set chained off
50        end
51    
52        set transaction isolation level 1
53    
54        select @sptlang = @@langid
55    
56        if @@langid != 0
57        begin
58            if not exists (
59                    select * from master.dbo.sysmessages where error
60                        between 17050 and 17069
61                        and langid = @@langid)
62                select @sptlang = 0
63        end
64    
65        /*
66        **  Verify the database name and get the @dbid and @dbuid
67        */
68        select @dbid = dbid, @dbuid = suid
69        from master.dbo.sysdatabases
70        where name = @dbname
71    
72        /*
73        **  If @dbname not found, say so and list the databases.
74        */
75        if @dbid is NULL
76        begin
77            /*
78            ** 17421, "No such database -- run sp_helpdb to list databases."
79            */
80            raiserror 17421
81            return (1)
82        end
83    
84        /*
85        ** Check SA and REPLICATION role.
86        ** Keep their status in local variables for auditing later.
87        */
88        select @has_sa_role = charindex("sa_role", show_role())
89        select @has_repl_role = charindex("replication_role", show_role())
90    
91        /*
92        **  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.
95        */
96        if (suser_id() != @dbuid)
97        begin
98            /* Check if we have sa_role or replication_role. */
99            if (@has_sa_role = 0 and @has_repl_role = 0)
100           begin
101               /*
102               ** Audit failure. This will result in three messages, but
103               ** we will live with that until there is a better 'proc_role()'
104               ** interface.
105               */
106               select @procval = proc_role("sa_role")
107               select @procval = proc_role("replication_role")
108   
109               /* 18375, "You are not authorized to execute this stored 
110               ** procedure. Only the System Administrator (SA), the 
111               ** Database Owner (DBO) or a user with replication_role 
112               ** authorization can execute this stored procedure."
113               */
114               raiserror 18375
115               return (1)
116           end
117       end
118   
119       /* Audit success(es) */
120       if (@has_sa_role > 0)
121           select @procval = proc_role("sa_role")
122       if (@has_repl_role > 0)
123           select @procval = proc_role("replication_role")
124   
125       /* Check if Rep Agent threads are enabled. */
126       if is_rep_agent_enabled() = 0
127       begin
128           /* 18373, "SQL Server is not currently configured to use Replication 
129           ** Agent threads. Use sp_configure to set this property."
130           */
131           raiserror 18373
132           return (1)
133       end
134   
135       if is_rep_agent_enabled(@dbid) = 0
136       begin
137           /* 18374, "Database '%1!' is not configured to use Replication Agent. 
138           ** Run sp_config_rep_agent without parameters to see a list of 
139           ** databases that use Replication Agent. Use the ENABLE option of 
140           ** sp_config_rep_agent to configure a database to use this feature."
141           */
142           raiserror 18374, @dbname
143           return (1)
144       end
145   
146       /*
147       ** Validate the arguments.
148       */
149       select @mode = lower(@mode)
150       select @no_more_arguments = 1
151       if @mode = "recovery"
152       begin
153           select @operation = @recovery_background
154           select @no_more_arguments = 0
155   
156       end
157       else if @mode = "recovery_foreground"
158       begin
159           select @operation = @recovery_foreground
160           select @no_more_arguments = 0
161       end
162       else if @mode = "resync purge"
163       begin
164           select @operation = @dbresync_purge
165       end
166       else if @mode = "resync init"
167       begin
168           select @operation = @dbresync_init
169       end
170       else if @mode = "resync"
171       begin
172           select @operation = @dbresync
173       end
174       else if @mode is null
175       begin
176           select @operation = 0
177       end
178       else
179       begin
180           /* 18378, "Incorrect syntax for sp_start_rep_agent. Usage: 
181           ** sp_start_rep_agent  [, {recovery|recovery_foreground} 
182           ** [,,  [,,
183           ** , ]]|{'resync'|'resync init'|'resync purge'}]."
184           */
185           raiserror 18378
186           return (1)
187       end
188   
189       if (@no_more_arguments = 1)
190       begin
191           if (@connect_ds is NOT NULL
192                   or @connect_db is NOT NULL
193                   or @rs_servername is NOT NULL
194                   or @rs_username is NOT NULL
195                   or @rs_password is NOT NULL)
196           begin
197               /* 18379, "Parameters other than the database name can be 
198               ** specified only when starting the Replication Agent thread 
199               ** in recovery mode. The Replication Agent thread was not 
200               ** started."
201               */
202               raiserror 18379
203               return (1)
204           end
205       end
206   
207       if @operation = 0
208       begin
209           if (rep_agent_admin("start thread", @dbid) = 0)
210           begin
211               /* 18421, "Failed to start the Replication Agent thread for 
212               ** database '%1!'."
213               */
214               raiserror 18421, @dbname
215               return (1)
216           end
217       end
218       else if (@operation = @recovery_background or @operation = @recovery_foreground)
219       begin
220           /* Need to start in recovery mode */
221           if (rep_agent_admin("start thread", @dbid, @operation, @connect_ds,
222                       @connect_db, @rs_servername, @rs_username, @rs_password) = 0)
223           begin
224               /* 18421, "Failed to start the Replication Agent thread for 
225               ** database '%1!'."
226               */
227               raiserror 18421, @dbname
228               return (1)
229           end
230       end
231       else
232       begin
233           /* Need to start in db resync mode */
234           if rep_agent_admin("start thread", @dbid, @operation) = 0
235           begin
236               /* 18421, "Failed to start the Replication Agent thread for 
237               ** database '%1!'."
238               */
239               raiserror 18421, @dbname
240               return (1)
241           end
242       end
243   
244       /* Success message */
245       /* 18380, "Replication Agent thread is started for database '%1!'." */
246       exec sp_getmessage 18380, @msg output
247       print @msg, @dbname
248   
249       return (0)
250   


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 61
 QTYP 4 Comparison type mismatch smallint = int 61
 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 246
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 249
 QISO 3 Set isolation level 52
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
59
 VNRD 3 Variable is not read @sptlang 62
 VNRD 3 Variable is not read @procval 123
 VUNU 3 Variable is not used @name 28
 VUNU 3 Variable is not used @recovery 30
 MSUB 2 Subquery Marker 58
 MTR1 2 Metrics: Comments Ratio Comments: 52% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 23 = 31dec - 10exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 121 14

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysmessages (1)  
reads table master..sysdatabases (1)  
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)