| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_reptostandby | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Message for "sp_reptostandby" 4 ** 17260, "Can't run %1! from within a transaction." 5 ** 18407, "Usage: sp_reptostandby dbname [ , { ALL | L1 | NONE } ]" 6 ** 18408, "The stored procedure must be executed with the name of 7 ** the current database." 8 ** 18409, "The built-in function '%1!' failed. Please see the 9 ** other messages printed along with this message." 10 ** 18410, "The replication status of '%1!' is corrupt. Please contact 11 ** Sybase Technical Support." 12 ** 18411, "The replication status for database '%1!' is '%2!'." 13 ** 18412, "The replication status for database '%1!' is already set to 14 ** '%2!'. The replication status is not changed." 15 ** 18413, "Due to a system failure, the replication status for '%1!' 16 ** has not been changed." 17 ** 18414, "The replication status for database '%1!' has been set to '%2!'." 18 ** 18415, "ALL" 19 ** 18416, "NONE" 20 ** 18417, "L1" 21 ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) 22 ** or a user with REPLICATION authorization may execute this 23 ** stored procedure." 24 */ 25 create procedure sp_reptostandby 26 @dbname varchar(255) = NULL, 27 @setflag varchar(20) = NULL, 28 @use_index varchar(10) = NULL /* Use index for off-row columns */ 29 as 30 declare @all varchar(20) 31 declare @none varchar(20) 32 declare @l1 varchar(20) 33 declare @msg varchar(1024) 34 declare @off varchar(20) 35 declare @procval int 36 declare @dbuid int 37 declare @curdb varchar(255) 38 declare @dbid int 39 declare @objid int 40 declare @rep_constant smallint 41 declare @tmpstr varchar(200) 42 declare @curstat int 43 declare @newstat int 44 declare @sptlang int 45 declare @curindstat int 46 declare @lt_rep_all int 47 declare @lt_rep_l1 int 48 declare @lt_setrep_tipsa_index 49 int 50 declare @lt_rep_get_failed 51 int 52 declare @lt_sqlrep_upd 53 int 54 declare @lt_sqlrep_del 55 int 56 declare @lt_sqlrep_inssel 57 int 58 declare @lt_sqlrep_selinto 59 int 60 declare @sqlrepmask 61 int 62 63 64 /* 65 ** sp_reptostandby cannot be executed inside a transaction 66 */ 67 68 if @@trancount > 0 69 begin 70 /* 71 ** 17260, "Can't run %1! from within a transaction." 72 */ 73 raiserror 17260, "sp_reptostandby" 74 return (1) 75 end 76 77 set chained off 78 set transaction isolation level 1 79 80 /* 81 ** Initialize the parameter settings 82 */ 83 exec sp_getmessage 18415, @all out 84 exec sp_getmessage 18416, @none out 85 exec sp_getmessage 18417, @l1 out 86 exec sp_getmessage 19895, @off out 87 88 89 /* 90 ** Set up the constants 91 */ 92 select @rep_constant = - 32768, 93 @lt_rep_get_failed = - 2, /* LT_REP_GET_FAILED */ 94 @lt_rep_all = 2048, /* LT_REP_ALL */ 95 @lt_rep_l1 = 4096, /* LT_REP_L1 */ 96 @lt_setrep_tipsa_index = 8, /* LT_SETREP_TIPSA_INDEX */ 97 @lt_sqlrep_upd = 32, /* LT_SQLREP_UPD */ 98 @lt_sqlrep_del = 64, /* LT_SQLREP_DEL */ 99 @lt_sqlrep_inssel = 128, /* LT_SQLREP_INSSEL */ 100 @lt_sqlrep_selinto = 256 /* LT_SQLREP_SELINTO */ 101 102 103 /* 104 ** Set 'sptlang' for proper printing of object information. Used mainly 105 ** for the 'select' statement which is executed when we are invoked with 106 ** no parameters. Copied from similar code in 'sp_help' 107 */ 108 select @sptlang = @@langid 109 if @@langid != 0 110 begin 111 if not exists ( 112 select * from master.dbo.sysmessages where error 113 between 17100 and 17109 114 and langid = @@langid) 115 select @sptlang = 0 116 end 117 118 select @setflag = upper(@setflag), 119 @use_index = lower(@use_index) 120 121 /* check the syntax */ 122 123 if ((@dbname is NULL) or 124 (@setflag is not NULL and @setflag not in (@all, @l1, @none)) or 125 (@use_index is not NULL and @use_index not in ("use_index"))) 126 begin 127 /* 128 ** 18407, "Usage: sp_reptostandby dbname [ , { ALL | L1 | NONE } 129 ** [, use_index ] ] 130 */ 131 raiserror 18407 132 return (1) 133 end 134 135 select @newstat = 0 136 137 if (@use_index = ("use_index")) 138 begin 139 /* Set LT_SETREP_TIPSA_INDEX */ 140 select @newstat = 8 141 end 142 143 /* Determine the database */ 144 select @curdb = db_name() 145 146 /* 147 ** make sure the procedure is executed with the name of the current 148 ** database 149 */ 150 if (@dbname != @curdb) 151 begin 152 /* 153 ** 18408, "The stored procedure must be executed with the name of the 154 ** current database." 155 */ 156 raiserror 18408 157 return (1) 158 end 159 160 /* 161 ** Verify the database name and get the @dbuid 162 */ 163 select @dbuid = suid 164 from master.dbo.sysdatabases 165 where name = @dbname 166 167 /* Do security check */ 168 169 /* 170 ** You must be SA, dbo or have replication role to execute this sproc. 171 ** First check if we are the DBO. 172 */ 173 if (suser_id() != @dbuid) 174 begin 175 /* 176 ** check if we have sa_role or replication_role. If show_role() 177 ** does not find both "sa_role" and the "replication_role" 178 ** then we print out a message. 179 ** Note: show_role does not print any message. 180 */ 181 if (charindex("sa_role", show_role()) = 0 and 182 charindex("replication_role", show_role()) = 0) 183 begin 184 /* 185 ** 18418, "Only the System Administrator (SA), the Database 186 ** Owner (dbo) or a user with REPLICATION authorization 187 ** may execute this stored procedure." 188 */ 189 raiserror 18418 190 return (1) 191 end 192 else 193 begin 194 /* 195 ** Call proc_role() with each role that the user has 196 ** in order to send the success audit records. 197 ** Note that this could mean 1 or 2 audit records. 198 */ 199 if (charindex("sa_role", show_role()) > 0) 200 select @procval = proc_role("sa_role") 201 if (charindex("replication_role", show_role()) > 0) 202 select @procval = proc_role("replication_role") 203 end 204 end 205 206 /* 207 ** First, determine the current replication status of the database. 208 */ 209 select @curstat = getdbrepstat() 210 if (@curstat = @lt_rep_get_failed) 211 begin 212 /* 213 ** "The built-in function getdbrepstat() failed. Please see the 214 ** other messages printed along with this message." 215 */ 216 raiserror 18409, "getdbrepstat" 217 return (1) 218 end 219 220 /* 221 ** Get current dbid 222 */ 223 select @dbid = db_id() 224 225 /* 226 ** Process the case where we simply return the replication status 227 */ 228 if (@setflag is NULL) 229 begin 230 if (@curstat & @lt_rep_all = @lt_rep_all) 231 begin 232 select @tmpstr = @all 233 end 234 else if (@curstat & @lt_rep_l1 = @lt_rep_l1) 235 begin 236 select @tmpstr = @l1 237 end 238 else 239 begin 240 select @tmpstr = @none 241 end 242 243 if (@curstat & @lt_setrep_tipsa_index = @lt_setrep_tipsa_index) 244 begin 245 select @tmpstr = @tmpstr + ", using index" 246 end 247 248 select @sqlrepmask = 249 @lt_sqlrep_upd | @lt_sqlrep_del | @lt_sqlrep_inssel | @lt_sqlrep_selinto 250 251 /* 252 ** 18411, "The replication status for database '%1!' is '%2!'" 253 */ 254 exec sp_getmessage 18411, @msg output 255 print @msg, @dbname, @tmpstr 256 257 if ((@curstat & @sqlrepmask) != 0) 258 begin 259 select @tmpstr = " " 260 if ((@curstat & @lt_sqlrep_upd) = @lt_sqlrep_upd) 261 select @tmpstr = @tmpstr + "u" 262 if ((@curstat & @lt_sqlrep_del) = @lt_sqlrep_del) 263 select @tmpstr = @tmpstr + "d" 264 if ((@curstat & @lt_sqlrep_inssel) = @lt_sqlrep_inssel) 265 select @tmpstr = @tmpstr + "i" 266 if ((@curstat & @lt_sqlrep_selinto) = @lt_sqlrep_selinto) 267 select @tmpstr = @tmpstr + "s" 268 end 269 else 270 select @tmpstr = @off 271 272 /* 273 ** 19893, "The replication mode for database '%1!' is '%2!'" 274 */ 275 exec sp_getmessage 19893, @msg output 276 print @msg, @dbname, @tmpstr 277 278 return (0) 279 end 280 281 /* 282 ** We are setting the replication status; figure out the new status 283 */ 284 if (@setflag = @all) 285 begin 286 select @newstat = @newstat | @lt_rep_all 287 end 288 else if (@setflag = @l1) 289 begin 290 select @newstat = @newstat | @lt_rep_l1 291 end 292 293 /* 294 ** If there is no change in status, simply return 295 */ 296 if (((@curstat & @lt_rep_all) = (@newstat & @lt_rep_all)) and 297 ((@curstat & @lt_rep_l1) = (@newstat & @lt_rep_l1))) 298 299 begin 300 /* 301 ** 18412, "The replication status for database '%1!' is already 302 ** set to '%2!'. The replication status is not changed." 303 */ 304 exec sp_getmessage 18412, @msg output 305 print @msg, @dbname, @setflag 306 307 return (0) 308 end 309 310 /* Do set */ 311 if (setdbrepstat(@newstat, 0) != 1) 312 begin 313 /* 314 ** 18413, "Due to a system failure, the replication status 315 ** for '%1!' has not been changed." 316 */ 317 raiserror 18413, @dbname 318 return (1) 319 end 320 321 /* Display status message */ 322 /* 323 ** 18414, "The replication status for database '%1!' has been set to '%2!'." 324 */ 325 exec sp_getmessage 18414, @msg output 326 print @msg, @dbname, @setflag 327 328 return (0) 329 330
exec sp_procxmode 'sp_reptostandby', 'AnyMode' go Grant Execute on sp_reptostandby to public go
| DEPENDENCIES |
| PROCS AND TABLES USED 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) reads table master..sysdatabases (1) reads table master..sysmessages (1) |
