| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_stop_rep_agent | | 31 Aug 14 | Defects 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 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 if @@trancount = 0 30 begin 31 set chained off 32 end 33 34 set transaction isolation level 1 35 36 select @sptlang = @@langid 37 38 if @@langid != 0 39 begin 40 if not exists ( 41 select * from master.dbo.sysmessages where error 42 between 17050 and 17069 43 and langid = @@langid) 44 select @sptlang = 0 45 end 46 47 /* 48 ** Verify the database name and get the @dbid and @dbuid 49 */ 50 select @dbid = dbid, @dbuid = suid 51 from master.dbo.sysdatabases 52 where name = @dbname 53 54 /* 55 ** If @dbname not found, say so and list the databases. 56 */ 57 if @dbid is NULL 58 begin 59 /* 60 ** 17421, "No such database -- run sp_helpdb to list databases." 61 */ 62 raiserror 17421 63 return (1) 64 end 65 66 /* 67 ** Check SA and REPLICATION role. 68 ** Keep their status in local variables for auditing later. 69 */ 70 select @has_sa_role = charindex("sa_role", show_role()) 71 select @has_repl_role = charindex("replication_role", show_role()) 72 73 /* 74 ** Only the Database Owner (DBO) or 75 ** Accounts with SA role or replication role can execute it. 76 ** First check if we are the DBO. 77 */ 78 if (suser_id() != @dbuid) 79 begin 80 /* Check if we have sa_role or replication_role. */ 81 if (@has_sa_role = 0 and @has_repl_role = 0) 82 begin 83 /* 84 ** Audit failure. This will result in three messages, but 85 ** we will live with that until there is a better 'proc_role()' 86 ** interface. 87 */ 88 select @procval = proc_role("sa_role") 89 select @procval = proc_role("replication_role") 90 91 /* 18375, "You are not authorized to execute this stored 92 ** procedure. Only the System Administrator (SA), the 93 ** Database Owner (DBO) or a user with replication_role 94 ** authorization can execute this stored procedure." 95 */ 96 raiserror 18375 97 return (1) 98 end 99 else 100 begin 101 /* 102 ** Call proc_role() with each role that the user has 103 ** in order to send the success audit records. 104 ** Note that this could mean 1 or 2 audit records. 105 */ 106 if (charindex("sa_role", show_role()) > 0) 107 select @procval = proc_role("sa_role") 108 if (charindex("replication_role", show_role()) > 0) 109 select @procval = proc_role("replication_role") 110 end 111 end 112 113 /* Audit success(es) */ 114 if (@has_sa_role > 0) 115 select @procval = proc_role("sa_role") 116 if (@has_repl_role > 0) 117 select @procval = proc_role("replication_role") 118 119 /* Verify that the database is using the Rep Agent. */ 120 if is_rep_agent_enabled(@dbid) = 0 121 begin 122 /* 18374, "Database '%1!' is not configured to use Replication Agent. 123 ** Run sp_config_rep_agent without parameters to see a list of 124 ** databases that use Replication Agent. Use the ENABLE option of 125 ** sp_config_rep_agent to configure a database to use this feature." 126 */ 127 raiserror 18374, @dbname 128 return (1) 129 end 130 131 /* 132 ** Verify that if optvalue if specified is the correct value. 133 */ 134 if @optname = "nowait" 135 begin 136 select @nowait = 1 137 end 138 else if @optname is null 139 begin 140 select @nowait = 0 141 end 142 else 143 begin 144 /* 18376, "Incorrect syntax for sp_stop_rep_agent. Usage: 145 ** sp_stop_rep_agent[,nowait]." 146 */ 147 raiserror 18376 148 return (1) 149 end 150 151 /* Call the builtin to do the work. If it encounters an error, it will 152 ** raise an exception and output a message to the client. 153 */ 154 select @builtin_retstat = rep_agent_admin("stop thread", @dbid, @nowait) 155 156 if (@builtin_retstat = 1) 157 begin 158 /* 18377, "The Replication Agent thread for database '%1!' is 159 ** being stopped." 160 */ 161 exec sp_getmessage 18377, @msg output 162 print @msg, @dbname 163 return (0) 164 end 165 else if (@builtin_retstat = - 1) 166 begin 167 /* 168 ** 18422, "The Replication Agent thread for database '%1!' is not 169 ** currently running. 170 */ 171 raiserror 18422, @dbname 172 end 173 else if (@builtin_retstat = 0) 174 begin 175 /* 19653, "Failed to stop the Replication Agent thread for 176 ** database '%1!'. 177 */ 178 raiserror 19653, @dbname 179 end 180 return (1) 181
exec sp_procxmode 'sp_stop_rep_agent', 'AnyMode' go Grant Execute on sp_stop_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) |
