| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_start_rep_agent | | 31 Aug 14 | Defects 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} [, 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, [, , , ]]]." 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 */ [, {recovery|recovery_foreground} 182 ** [, 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, [, , 183 ** , ]]|{'resync'|'resync init'|'resync purge'}]." 184 */
exec sp_procxmode 'sp_start_rep_agent', 'AnyMode' go Grant Execute on sp_start_rep_agent to public go
| 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) |
