Database | Proc | Application | Created | Links |
sybsystemprocs | sp_setrepdbmode | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Message for "sp_setrepdbmode" 4 ** 17260, "Can't run %1! from within a transaction." 5 ** 18408, "The stored procedure must be executed with the name of 6 ** the current database." 7 ** 18409, "The built-in function '%1!' failed. Please see any 8 ** other messages printed along with this message." 9 ** 18410, "The replication status of '%1!' is corrupt. Please contact 10 ** Sybase Technical Support." 11 ** 18411, "The replication status for database '%1!' is '%2!'." 12 ** 18412, "The replication status for database '%1!' is already set to 13 ** '%2!'. The replication status is not changed." 14 ** 18413, "Due to a system failure, the replication status for '%1!' 15 ** has not been changed." 16 ** 18414, "The replication status for database '%1!' has been set to '%2!'." 17 ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) 18 ** or a user with REPLICATION authorization may execute this 19 ** stored procedure." 20 */ 21 create procedure sp_setrepdbmode 22 @dbname varchar(255) = NULL, 23 @setflag varchar(20) = NULL, 24 @setmode varchar(20) = NULL 25 as 26 declare @msg varchar(1024) 27 declare @off varchar(20) 28 declare @on varchar(20) 29 declare @procval int 30 declare @dbuid int 31 declare @curdb varchar(255) 32 declare @dbid int 33 declare @objid int 34 declare @rep_constant smallint 35 declare @tmpstr varchar(200) 36 declare @curstat int 37 declare @newstat int 38 declare @sptlang int 39 declare @setrep_status int 40 declare @lt_sqlrep_upd int 41 declare @lt_sqlrep_del int 42 declare @lt_sqlrep_inssel int 43 declare @lt_sqlrep_selinto int 44 declare @sqlrepmask int 45 declare @lt_rep_all int 46 declare @lt_rep_l1 int 47 declare @lt_rep_get_failed int 48 49 declare @update char(1) /* 'u' string used for sql replication */ 50 declare @delete char(1) /* 'd' string used for sql replication */ 51 declare @inserts char(1) /* 'i' string used for sql replication */ 52 declare @selinto char(1) /* 's' string used for sql replication */ 53 declare @sqlrep varchar(4) /* 'udis' string used for sql replication */ 54 declare @sqlrep_syntax int /* Are we using a valid substring of 'udis'? */ 55 56 /* SQL replication threshold */ 57 declare @threshold varchar(20) /* Used to check stored proc syntax */ 58 declare @current_threshold int /* Current database threshold */ 59 declare @threshold_value int /* Integer value from @setflag */ 60 declare @threshold_syntax smallint /* Used to check stored proc syntax */ 61 declare @threshold_max int /* Max value for sql threshold */ 62 declare @outvalue varchar(10) /* Used to stored tmp values */ 63 64 /* 65 ** sp_setrepdbmode 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 ** Set up the constants 82 */ 83 select @rep_constant = - 32768, 84 @setrep_status = 0, 85 @lt_rep_get_failed = - 2, /* LT_GET_REP_FAILED */ 86 @lt_sqlrep_upd = 32, /* LT_SQLREP_UPD */ 87 @lt_sqlrep_del = 64, /* LT_SQLREP_DEL */ 88 @lt_sqlrep_inssel = 128, /* LT_SQLREP_INSSEL */ 89 @lt_sqlrep_selinto = 256, /* LT_SQLREP_SELINTO */ 90 @lt_rep_all = 2048, /* LT_REP_ALL */ 91 @lt_rep_l1 = 4096, /* LT_REP_L1 */ 92 @threshold_max = 10000 /* LT_SQLREP_MAX_THRESHOLD */ 93 94 select @sqlrep = name 95 from master.dbo.spt_values 96 where type = 'SQ' 97 and number = 1 98 99 /* 100 ** get localized parameters for "on","off" and "threshold" 101 */ 102 exec sp_getmessage 19895, @off out 103 exec sp_getmessage 19897, @on out 104 exec sp_getmessage 19931, @threshold out 105 106 if (@sqlrep is NULL or @on is NULL or @off is NULL or @threshold is NULL) 107 begin 108 /* 109 ** 18413, "Due to a system failure, the replication status 110 ** for '%1!' has not been changed." 111 */ 112 raiserror 18413, @dbname 113 return (1) 114 end 115 116 select @update = substring(@sqlrep, 1, 1) 117 select @delete = substring(@sqlrep, 2, 1) 118 select @inserts = substring(@sqlrep, 3, 1) 119 select @selinto = substring(@sqlrep, 4, 1) 120 121 /* 122 ** Set 'sptlang' for proper printing of object information. Used mainly 123 ** for the 'select' statement which is executed when we are invoked with 124 ** no parameters. Copied from similar code in 'sp_help' 125 */ 126 select @sptlang = @@langid 127 if @@langid != 0 128 begin 129 if not exists ( 130 select * from master.dbo.sysmessages where error 131 between 17100 and 17109 132 and langid = @@langid) 133 select @sptlang = 0 134 end 135 136 select @setflag = lower(@setflag) 137 select @setmode = lower(@setmode) 138 139 /* check the syntax */ 140 if (@dbname is NULL) 141 begin 142 /* 143 ** 19887, "Usage: sp_setrepdbmode dbname [, 'UDIS' [, 'on' | 'off'] 144 ** | 'threshold' [,'value']] 145 */ 146 raiserror 19887 147 return (1) 148 end 149 150 151 /* Determine the database */ 152 select @curdb = db_name() 153 154 /* 155 ** make sure the procedure is executed with the name of the current 156 ** database 157 */ 158 if (@dbname != @curdb) 159 begin 160 /* 161 ** 18408, "The stored procedure must be executed with the name of the 162 ** current database." 163 */ 164 raiserror 18408 165 return (1) 166 end 167 168 /* 169 ** Verify the database name and get the @dbuid 170 */ 171 select @dbuid = suid 172 from master.dbo.sysdatabases 173 where name = @dbname 174 175 /* Do security check */ 176 177 /* 178 ** You must be SA, dbo or have replication role to execute this sproc. 179 ** First check if we are the DBO. 180 */ 181 if (suser_id() != @dbuid) 182 begin 183 /* 184 ** check if we have sa_role or replication_role. If show_role() 185 ** does not find both "sa_role" and the "replication_role" 186 ** then we print out a message. 187 ** Note: show_role does not print any message. 188 */ 189 if (charindex("sa_role", show_role()) = 0 and 190 charindex("replication_role", show_role()) = 0) 191 begin 192 /* 193 ** 18418, "Only the System Administrator (SA), the Database 194 ** Owner (dbo) or a user with REPLICATION authorization 195 ** may execute this stored procedure." 196 */ 197 raiserror 18418 198 return (1) 199 end 200 else 201 begin 202 /* 203 ** Call proc_role() with each role that the user has 204 ** in order to send the success audit records. 205 ** Note that this could mean 1 or 2 audit records. 206 */ 207 if (charindex("sa_role", show_role()) > 0) 208 select @procval = proc_role("sa_role") 209 if (charindex("replication_role", show_role()) > 0) 210 select @procval = proc_role("replication_role") 211 end 212 end 213 214 /* 215 ** Get current dbid 216 */ 217 select @dbid = db_id() 218 219 select @sqlrepmask = 220 @lt_sqlrep_upd | @lt_sqlrep_del | @lt_sqlrep_inssel | @lt_sqlrep_selinto 221 222 /* 223 ** Check that the arguments to this procedure are acceptable for 224 ** further processing. The arguments are @setflag and @setmode. 225 */ 226 select @sqlrep_syntax = 0, 227 @threshold_syntax = 0 228 229 /* 230 ** @sqlrep_syntax is set to 1 if @setflag is a valid substring of 231 ** 'udis', and to 0 otherwise. 232 */ 233 exec sp_sqlrep_check_syntax @setflag, 234 @sqlrep, 235 @sqlrep_syntax output 236 237 238 /* 239 ** We display sp_setrepdbmode status in the following cases 240 ** 241 ** 1.- 'sp_setrepdbmode' 242 ** 2.- 'sp_setrepdbmode 246 if ((@setflag is null) or 247 (@setmode is null and @sqlrep_syntax = 1) or 248 (@setmode is null and @setflag in ("threshold", @threshold))) 249 begin 250 /* 251 ** First, determine the current replication status of the database. 252 */ 253 select @curstat = getdbrepstat() 254 if (@curstat = @lt_rep_get_failed) 255 begin 256 /* 257 ** "The built-in function getdbrepstat() failed. Please see any 258 ** other messages printed along with this message." 259 */ 260 raiserror 18409, "getdbrepstat" 261 return (1) 262 end 263 264 if ((@curstat & @sqlrepmask) != 0) 265 begin 266 select @tmpstr = " " 267 if ((@curstat & @lt_sqlrep_upd) = @lt_sqlrep_upd) 268 select @tmpstr = @tmpstr + "u" 269 if ((@curstat & @lt_sqlrep_del) = @lt_sqlrep_del) 270 select @tmpstr = @tmpstr + "d" 271 if ((@curstat & @lt_sqlrep_inssel) = @lt_sqlrep_inssel) 272 select @tmpstr = @tmpstr + "i" 273 if ((@curstat & @lt_sqlrep_selinto) = @lt_sqlrep_selinto) 274 select @tmpstr = @tmpstr + "s" 275 end 276 else 277 select @tmpstr = @off 278 279 /* Get the current replication threshold */ 280 select @current_threshold = getrepdbthreshold(@dbid) 281 if (@current_threshold < 0) 282 begin 283 /* 284 ** "The built-in function getrepthreshold() failed. 285 ** Please see any other messages printed along with 286 ** this message." 287 */ 288 raiserror 18409, "getrepdbthreshold" 289 return (1) 290 end 291 292 /* 293 ** 19893, "The replication mode for database '%1!' is '%2!'" 294 */ 295 exec sp_getmessage 19893, @msg output 296 print @msg, @dbname, @tmpstr 297 298 /* 299 ** 19932, "The replication threshold for '%1!' is '%2!'" 300 */ 301 exec sp_getmessage 19932, @msg output 302 select @outvalue = convert(varchar(10), @current_threshold) 303 print @msg, @dbname, @outvalue 304 return (0) 305 end 306 307 select @setflag = ltrim(rtrim(@setflag)) 308 309 if (@setflag in ("threshold", @threshold)) 310 begin 311 select @threshold_syntax = 1 312 end 313 else 314 if (@sqlrep_syntax = 1 and @setmode not in ("on", "off", @on, @off, NULL)) 315 begin 316 select @sqlrep_syntax = 0 317 end 318 319 if (@sqlrep_syntax = 0 and @threshold_syntax = 0) 320 begin 321 /* 322 ** 19887, "Usage: sp_setrepdbmode dbname [, 'UDIS' [, 'on' | 'off'] 323 ** | 'threshold' [,'value']] 324 */ 325 raiserror 19887 326 return (1) 327 end 328 329 if (@sqlrep_syntax = 1) 330 begin 331 /* 332 ** First, determine the current replication status of the database. 333 */ 334 select @curstat = getdbrepstat() 335 if (@curstat = @lt_rep_get_failed) 336 begin 337 /* 338 ** "The built-in function getdbrepstat() failed. Please see any 339 ** other messages printed along with this message." 340 */ 341 raiserror 18409, "getdbrepstat" 342 return (1) 343 end 344 345 /* Database replication level has to be set */ 346 if ((@curstat & @lt_rep_all != @lt_rep_all) and 347 (@curstat & @lt_rep_l1 != @lt_rep_l1)) 348 begin 349 /* 350 ** 19888: The attempt to set the replication mode failed. Please, 351 ** set the database replication level to 'ALL' or to 'L1' using 352 ** the stored procedure sp_reptostandby prior to setting the 353 ** replication mode. 354 */ 355 raiserror 19888 356 return (1) 357 end 358 359 /* Interpret the input string: "udis" */ 360 if (charindex("u", @setflag) > 0) 361 begin 362 select @setrep_status = @setrep_status | @lt_sqlrep_upd 363 end 364 if (charindex("d", @setflag) > 0) 365 begin 366 select @setrep_status = @setrep_status | @lt_sqlrep_del 367 end 368 if (charindex("i", @setflag) > 0) 369 begin 370 select @setrep_status = @setrep_status | @lt_sqlrep_inssel 371 end 372 if (charindex("s", @setflag) > 0) 373 begin 374 select @setrep_status = @setrep_status | @lt_sqlrep_selinto 375 end 376 377 if (@setmode = @off) 378 begin 379 select @setrep_status = 0 380 end 381 382 /* No Change; we're done. */ 383 if ((@setrep_status & @sqlrepmask) = (@curstat & @sqlrepmask)) 384 begin 385 /* 386 ** 18412, "The replication status for database '%1!' is already 387 ** set to '%2!'. The replication status is not changed." 388 */ 389 exec sp_getmessage 18412, @msg output 390 if (@setmode = @off) 391 print @msg, @dbname, @setmode 392 else 393 print @msg, @dbname, @setflag 394 return (0) 395 end 396 397 /* Do set */ 398 if (setdbrepstat(@setrep_status, 1) != 1) 399 begin 400 /* 401 ** 18413, "Due to a system failure, the replication status 402 ** for '%1!' has not been changed." 403 */ 404 raiserror 18413, @dbname 405 return (1) 406 end 407 408 /* Let user know what we did. */ 409 if (@setmode = @off) 410 begin 411 /* 412 ** 19893, "The replication mode for database '%1!' is '%2!'." 413 */ 414 exec sp_getmessage 19893, @msg output 415 print @msg, @dbname, @off 416 end 417 else 418 begin 419 /* Display status message */ 420 /* 421 ** 19893, "The replication mode for database '%1!' is '%2!'." 422 */ 423 exec sp_getmessage 19893, @msg output 424 print @msg, @dbname, @setflag 425 end 426 return (0) 427 end 428 else 429 /* We are changing the sql replication threshold */ 430 if (@threshold_syntax = 1) 431 begin 432 if (@setmode is null) 433 begin 434 /* Get the current replication threshold */ 435 select @current_threshold = getrepdbthreshold(@dbid) 436 437 if (@current_threshold < 0) 438 begin 439 /* 440 ** "The built-in function getrepthreshold() failed. 441 ** Please see any other messages printed along with 442 ** this message." 443 */ 444 raiserror 18409, "getrepdbthreshold" 445 return (1) 446 end 447 448 /* 449 ** 19932, "The replication threshold for '%1!' is '%2!'." 450 */ 451 exec sp_getmessage 19932, @msg output 452 select @outvalue = convert(varchar(10), @current_threshold) 453 print @msg, @dbname, @outvalue 454 return (0) 455 end 456 457 select @threshold_value = convert(int, @setmode) 458 459 if ((@threshold_value < 0) or (@threshold_value > @threshold_max)) 460 begin 461 /* 462 ** 19934, "The replication threshold provided for 463 ** '%1!' is '%2!'. Please choose a 464 ** threshold larger than 0 and smaller than '%3!'." 465 */ 466 select @outvalue = convert(varchar(10), @threshold_max) 467 raiserror 19934, @dbname, @setmode, @outvalue 468 return (1) 469 end 470 471 if (setrepdbthreshold(@dbid, @threshold_value) != 1) 472 begin 473 /* 474 ** 18409, "The built-in funtion '%1!' failed. Please see 475 ** any other messages printed along with this 476 ** message." 477 */ 478 raiserror 18409, "setrepdbthreshold" 479 return (1) 480 end 481 482 /* 483 ** We have requested to reset the threshold to the default value. 484 */ 485 if (@threshold_value = 0) 486 begin 487 select @current_threshold = getrepdbthreshold(@dbid) 488 /* 489 ** 19950, " The replication threshold for '%1!' has been reset 490 ** to '%2!'." 491 */ 492 exec sp_getmessage 19950, @msg output 493 select @outvalue = convert(varchar(10), @current_threshold) 494 print @msg, @dbname, @outvalue 495 return (0) 496 end 497 498 /* 499 ** 19932, 'The replication threshold for '%1!' is '%2!'." 500 */ 501 exec sp_getmessage 19932, @msg output 502 print @msg, @dbname, @setmode 503 return (0) 504 505 506 end 507', ' 243 ** 3.- 'sp_setrepdbmode ', 'threshold' 244 ** 245 */
exec sp_procxmode 'sp_setrepdbmode', 'AnyMode' go Grant Execute on sp_setrepdbmode to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table master..spt_values (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) calls proc sybsystemprocs..sp_sqlrep_check_syntax reads table master..sysmessages (1) |