Database | Proc | Application | Created | Links |
sybsystemprocs | sp_setrepcol ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Generated by spgenmsgs.pl on Wed Feb 8 14:55:57 2006 4 */ 5 /* 6 ** raiserror Messages for setrepcol [Total 3] 7 ** 8 ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record." 9 ** 17966, "Due to system failure, the replication status for '%1!' has not been changed." 10 ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) or a user with REPLICATION authorization may execute this stored procedure." 11 */ 12 /* 13 ** sp_getmessage Messages for setrepcol [Total 15] 14 ** 15 ** 17142, "You need to be able to set curwrite label to data_high. This script will continue in spite of failure to set curwrite. Please rerun after setting your labels correctly." 16 ** 17460, "Object must be in the current database." 17 ** 17964, "The replication status for '%1!' is set to %2!." 18 ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly." 19 ** 18109, "Usage: sp_setrepcol table_name, column_name, {do_not_replicate | always_replicate | replicate_if_changed}" 20 ** 18110, "The table '%1!' is not marked as replicated." 21 ** 18111, "do_not_replicate" 22 ** 18112, "always_replicate" 23 ** 18113, "replicate_if_changed" 24 ** 18114, "A table name must be provided." 25 ** 18115, "No Text/Image or off-row object columns in '%1!'." 26 ** 18116, "The replication status for all Text/Image and off-row object columns in '%1!' has been set to %2!." 27 ** 18117, "Column '%1!' does not exist in '%2!' or it is not a Text/Image or off-row object column." 28 ** 18118, "Object '%1!' does not exist in this database." 29 ** 18687, "Cannot set replication status for in-row objects, use sp_setreptable to set the table's replication status instead. The replication status for '%1!.%2!' is not changed." 30 */ 31 /* 32 ** End spgenmsgs.pl output. 33 */ 34 35 create procedure sp_setrepcol 36 @replicate_name varchar(767) = NULL, /* table for the column */ 37 @column_name varchar(767) = NULL, /* column we want to mark */ 38 @setflag varchar(22) = NULL, /* the replicate status. */ 39 @use_index varchar(10) = NULL /* use index for text columns */ 40 as 41 42 declare @current_status int /* current sysstat value for the object. */ 43 declare @new_status int /* new sysstat value for the object. */ 44 declare @rep_constant smallint /* bit which indicates a replicated object. */ 45 declare @db varchar(255) /* db of object. */ 46 declare @owner varchar(255) /* owner of object. */ 47 declare @object varchar(255) /* object's name. */ 48 declare @msg varchar(1024) /* variable to get the error message */ declare @sptlang int 49 declare @procval int 50 declare @rnever smallint /* flag value for "do_not_replicate" */ 51 declare @ralways smallint /* flag value for "always_replicate" */ 52 declare @rifchanged smallint /* flag value for "replicate_if_changed" */ 53 declare @rcolbitmask smallint /* mask for the above flags */ 54 declare @repnever varchar(30) /* "do_not_replicate" */ 55 declare @repalways varchar(30) /* "always_replicate" */ 56 declare @repifchanged varchar(30) /* "replicate_if_changed" */ 57 declare @colid smallint /* Column id of the column */ 58 declare @objid int /* Id of the object */ 59 declare @lsetflag varchar(22) /* lower case value of @setflag */ 60 declare @texttype smallint /* SYB_TEXT type. */ 61 declare @imagetype smallint /* IMAGE type. */ 62 declare @xtype_type smallint /* XTYPE_TOKEN type. */ 63 declare @unitexttype smallint /* UNITEXT type. */ 64 declare @dbname varchar(255) 65 declare @xstatus int /* xstatus field. */ 66 declare @offrow smallint /* Object stored OFFROW. */ 67 declare @setrep_index int /* Bit which indicates if the replicated ** object will use internal indexes for 68 ** replication. 69 */ 70 declare @tipsa_in_index smallint 71 72 if @@trancount = 0 73 begin 74 set transaction isolation level 1 75 set chained off 76 end 77 78 /* 79 ** Replication enabled flag is 8000H (which is -32768D) 80 */ 81 select @rep_constant = - 32768, 82 @rnever = 0, 83 @ralways = 1, 84 @rifchanged = 2, 85 @rcolbitmask = 3, 86 @imagetype = 34, 87 @texttype = 35, 88 @xtype_type = 36, 89 @unitexttype = 174, 90 @offrow = 1, 91 @setrep_index = 0, 92 @tipsa_in_index = 2048 93 94 /* 95 ** Initialize strings 96 */ 97 /* 18111, "do_not_replicate" */ 98 exec sp_getmessage 18111, @repnever out 99 /* 18112, "always_replicate" */ 100 exec sp_getmessage 18112, @repalways out 101 /* 18113, "replicate_if_changed" */ 102 exec sp_getmessage 18113, @repifchanged out 103 104 /* Create a temp table for messages */ 105 create table #setrepcol(val int, str varchar(255)) 106 insert into #setrepcol values (@ralways, @repalways) 107 insert into #setrepcol values (@rnever, @repnever) 108 insert into #setrepcol values (@rifchanged, @repifchanged) 109 110 /* 111 ** Set 'sptlang' for proper printing of object information. Used mainly 112 ** for the 'select' statement which is executed when we are invoked with 113 ** no parameters. Copied from similar code in 'sp_help' 114 */ 115 select @sptlang = @@langid 116 if @@langid != 0 117 begin 118 if not exists ( 119 select * from master.dbo.sysmessages where error 120 between 17100 and 17109 121 and langid = @@langid) 122 select @sptlang = 0 123 end 124 125 if (@replicate_name is NULL) 126 begin 127 /* 128 ** 18114, "A table name must be provided." 129 */ 130 exec sp_getmessage 18114, @msg output 131 print @msg 132 return (1) 133 end 134 /* 135 ** Crack the name into its corresponding pieces. 136 */ 137 execute sp_namecrack @replicate_name, 138 @db = @db output, 139 @owner = @owner output, 140 @object = @object output 141 142 /* 143 ** Make sure that the object is in the current database. 144 */ 145 if (@db is not NULL and @db != db_name()) 146 begin 147 /* 148 ** 17460, "Object must be in the current database." 149 */ 150 exec sp_getmessage 17460, @msg output 151 print @msg 152 return (1) 153 end 154 155 /* 156 ** Make sure that the object actually exists. 157 */ 158 if (object_id(@replicate_name) is NULL) 159 begin 160 /* 161 ** 18118, "Object '%1!' does not exist in this database." 162 */ 163 exec sp_getmessage 18118, @msg output 164 print @msg, @replicate_name 165 return (1) 166 end 167 168 /* 169 ** Check if the object is a table. 170 */ 171 if not exists ( 172 select id from sysobjects 173 where 174 id = object_id(@replicate_name) and 175 type = "U") 176 begin 177 /* 178 ** 18114, "A table name must be provided." 179 */ 180 exec sp_getmessage 18114, @msg output 181 print @msg 182 return (1) 183 end 184 /* 185 ** Check if the table is marked for replication. 186 */ 187 select @current_status = sysstat 188 from sysobjects 189 where id = object_id(@replicate_name) 190 191 /* 192 ** Is the replicate status bit even set? 193 */ 194 if (@current_status & @rep_constant) = 0 195 begin 196 /* 197 ** 18110, "The object '%1!' is not marked for replication." 198 */ 199 exec sp_getmessage 18110, @msg output 200 print @msg, @replicate_name 201 return (1) 202 end 203 204 if (@column_name is not NULL) 205 begin 206 if not exists (select colid from syscolumns 207 where name = @column_name 208 and id = object_id(@replicate_name) 209 and type in (@imagetype, @texttype, 210 @xtype_type, @unitexttype)) 211 begin 212 /* 213 ** 18117 "Column '%1!' does not exist in '%2!' or is not 214 ** a Text/Image or an off-row object column." 215 */ 216 exec sp_getmessage 18117, @msg output 217 print @msg, @column_name, @replicate_name 218 return (1) 219 end 220 221 /* If object column is specified, it must be an off-row object. */ 222 if exists (select colid from syscolumns 223 where name = @column_name 224 and id = object_id(@replicate_name) 225 and type = @xtype_type 226 and (xstatus & @offrow) = 0) 227 begin 228 /* 229 ** 18687, "Cannot set replication status for in-row objects, 230 ** use sp_setreptable to set the table's replication 231 ** status instead. The replication status for '%1!.%2!' 232 ** is not changed." 233 */ 234 exec sp_getmessage 18687, @msg output 235 print @msg, @replicate_name, @column_name 236 return (1) 237 end 238 239 end 240 else 241 begin 242 if not exists (select colid from syscolumns 243 where id = object_id(@replicate_name) 244 and (type in (@imagetype, @texttype, @unitexttype) 245 or (type = @xtype_type 246 and (xstatus & @offrow) = @offrow))) 247 begin 248 /* 249 ** 18115 "No Text/Image nor an off-row object columns in '%1!'." 250 */ 251 exec sp_getmessage 18115, @msg output 252 print @msg, @replicate_name 253 return (1) 254 end 255 end 256 257 /* 258 ** Currently, marking for TEXT/IMAGE replication user table columns in the 259 ** 'master' database is not allowed. 260 ** 261 ** Therefore, if the current database is the 'master' database, reset the 262 ** '@setflag' to NULL so that the system procedure will report the current 263 ** status of the user table column. A proper error message should be 264 ** implemented at a later date. 265 */ 266 if (db_name() = "master") 267 begin 268 select @setflag = NULL 269 end 270 271 /* 272 ** If we are invoked with two parameters, then just print out status 273 */ 274 if (@setflag is NULL) 275 begin 276 if (@column_name is NULL) 277 begin 278 select 279 Name = c.name, 280 Type = t.name, 281 Status = s.str, 282 Mode = case 283 when (c.status2 & @tipsa_in_index) = @tipsa_in_index 284 then "using index" 285 when (c.status2 & @tipsa_in_index) = 0 286 then "no index" 287 when c.status2 is null 288 then "no index" 289 end 290 291 into #setrepcol1rs 292 from 293 syscolumns c holdlock, 294 systypes t, 295 #setrepcol s 296 where 297 c.id = object_id(@replicate_name) 298 and (c.type in (@imagetype, @texttype, @unitexttype) 299 or (c.type = @xtype_type 300 and (c.xstatus & @offrow) = @offrow)) 301 and c.type = t.type 302 and s.val = (c.status & @rcolbitmask) 303 exec sp_autoformat @fulltabname = #setrepcol1rs 304 drop table #setrepcol1rs 305 end 306 else 307 begin 308 select 309 Name = c.name, 310 Type = t.name, 311 Status = s.str, 312 Mode = case 313 when (c.status2 & @tipsa_in_index) = @tipsa_in_index 314 then "using index" 315 when (c.status2 & @tipsa_in_index) = 0 316 then "no index" 317 when c.status2 is null 318 then "no index" 319 end 320 321 into #setrepcol2rs 322 from 323 syscolumns c holdlock, 324 systypes t, 325 #setrepcol s 326 where 327 c.id = object_id(@replicate_name) 328 and c.name = @column_name 329 and (c.type in (@imagetype, @texttype, @unitexttype) 330 or (c.type = @xtype_type 331 and (c.xstatus & @offrow) = @offrow)) 332 and c.type = t.type 333 and s.val = (c.status & @rcolbitmask) 334 exec sp_autoformat @fulltabname = #setrepcol2rs 335 drop table #setrepcol2rs 336 end 337 return (0) 338 end 339 340 /* 341 ** Check for a valid setname parameter 342 */ 343 if (lower(@setflag) not in ("do_not_replicate", "always_replicate", 344 "replicate_if_changed", @repnever, 345 @repalways, @repifchanged)) 346 begin 347 /* 348 ** 18109, "Usage: sp_setrepcol table_name, column_name, 349 ** {do_not_replicate | 350 ** always_replicate | 351 ** replicate_if_changed} 352 ** [, use_index ] " 353 */ 354 exec sp_getmessage 18109, @msg output 355 print @msg 356 return (1) 357 end 358 359 select @use_index = lower(@use_index) 360 361 /* Check for valid use_index parameter */ 362 if (@use_index is not null and @use_index != "use_index") 363 begin 364 /* 365 ** 18109, "Usage: sp_setrepcol table_name, column_name, 366 ** {do_not_replicate | 367 ** always_replicate | 368 ** replicate_if_changed}" 369 ** [, use_index ] 370 */ 371 exec sp_getmessage 18109, @msg output 372 print @msg 373 return (1) 374 end 375 376 if (@use_index in ("use_index")) 377 begin 378 /* Set LT_SETREP_TIPSA_INDEX */ 379 select @setrep_index = 8 380 end 381 382 /* 383 ** You must be SA, dbo or have REPLICATION role to execute this 384 ** sproc. 385 */ 386 if (user_id() != 1) 387 begin 388 if (charindex("sa_role", show_role()) = 0 and 389 charindex("replication_role", show_role()) = 0) 390 begin 391 /* 392 ** 18418, "Only the System Administrator (SA), the 393 ** Database Owner (dbo) or a user with REPLICATION 394 ** authorization may execute this stored 395 ** procedure." 396 */ 397 raiserror 18418 398 return (1) 399 end 400 else 401 begin 402 /* 403 ** Call proc_role() with each role that the user has 404 ** in order to send the success audit records. 405 ** Note that this could mean 1 or 2 audit records. 406 */ 407 if (charindex("sa_role", show_role()) > 0) 408 select @procval = proc_role("sa_role") 409 if (charindex("replication_role", show_role()) > 0) 410 select @procval = proc_role("replication_role") 411 end 412 end 413 414 /* 415 ** Perform the requested operation on the object. 416 */ 417 select @objid = object_id(@replicate_name) 418 select @lsetflag = lower(@setflag) 419 420 421 422 /* Start the transaction to log the execution of this procedure. 423 ** 424 ** IMPORTANT: The name "rs_logexec is significant and is used by 425 ** Replication Server 426 */ 427 begin transaction rs_logexec 428 429 if @lsetflag in ("always_replicate", @repalways) 430 begin 431 if (@column_name is NULL) 432 begin 433 434 declare syscol_cursor cursor for 435 select colid from syscolumns 436 where 437 id = @objid 438 and (type in (@imagetype, @texttype, @unitexttype) 439 or (type = @xtype_type 440 and (xstatus & @offrow) = @offrow)) 441 order by colid 442 443 open syscol_cursor 444 fetch syscol_cursor into @colid 445 while (@@sqlstatus = 0) 446 begin 447 dbcc replicate_txtcol(@objid, @colid, @lsetflag, @setrep_index) 448 if @@error != 0 449 begin 450 /* 451 ** 17966 "Due to system failure, the 452 ** replication status for '%1!' has 453 ** not been changed." 454 */ 455 raiserror 17966, @replicate_name 456 457 /* 458 ** The DBCC command may have already 459 ** rolled back the transaction 460 */ 461 if @@trancount > 0 462 rollback transaction rs_logexec 463 464 return (1) 465 end 466 467 fetch syscol_cursor into @colid 468 end 469 close syscol_cursor 470 deallocate cursor syscol_cursor 471 end 472 else 473 begin 474 /* A column name was provided */ 475 select @colid = colid from syscolumns 476 where 477 name = @column_name 478 and id = @objid 479 480 dbcc replicate_txtcol(@objid, @colid, @lsetflag, @setrep_index) 481 if @@error != 0 482 begin 483 /* 484 ** 17966 "Due to system failure, the 485 ** replication status for '%1!' has 486 ** not been changed." 487 */ 488 raiserror 17966, @replicate_name 489 490 /* 491 ** The DBCC command may have already rolled back 492 ** the transaction 493 */ 494 if @@trancount > 0 495 rollback transaction rs_logexec 496 497 return (1) 498 end 499 end 500 end 501 else 502 /* flag is either repnever or repifchanged */ 503 begin 504 if (@column_name is NULL) 505 begin 506 /* 507 ** Set the status for all 508 ** text, image, and off-row-object columns. 509 */ 510 declare syscol_cursor cursor for 511 select colid from syscolumns 512 where 513 id = @objid 514 and (type in (@imagetype, @texttype, @unitexttype) 515 or (type = @xtype_type 516 and (xstatus & @offrow) = @offrow)) 517 order by colid 518 519 open syscol_cursor 520 fetch syscol_cursor into @colid 521 while (@@sqlstatus = 0) 522 begin 523 dbcc replicate_txtcol(@objid, @colid, @lsetflag, @setrep_index) 524 if @@error != 0 525 begin 526 /* 527 ** 17966 "Due to system failure, the 528 ** replication status for '%1!' has 529 ** not been changed." 530 */ 531 raiserror 17966, @replicate_name 532 533 /* 534 ** The DBCC command may have already rolled 535 ** back the transaction 536 */ 537 if @@trancount > 0 538 rollback transaction rs_logexec 539 540 return (1) 541 end 542 fetch syscol_cursor into @colid 543 end 544 close syscol_cursor 545 deallocate cursor syscol_cursor 546 end 547 else 548 begin 549 /* Set the status for the given column */ 550 select @colid = colid from syscolumns 551 where 552 id = @objid and 553 name = @column_name 554 555 dbcc replicate_txtcol(@objid, @colid, @lsetflag, @setrep_index) 556 if @@error != 0 557 begin 558 /* 559 ** 17966 "Due to system failure, the 560 ** replication status for '%1!' has 561 ** not been changed." 562 */ 563 raiserror 17966, @replicate_name 564 565 /* 566 ** The DBCC command may have already rolled back 567 ** the transaction 568 */ 569 if @@trancount > 0 570 rollback transaction rs_logexec 571 572 return (1) 573 end 574 end 575 end 576 577 /* 578 ** Write the log record to replicate this invocation 579 ** of the stored procedure. 580 */ 581 if (logexec() != 1) 582 begin 583 /* 584 ** 17756, "The execution of the stored procedure '%1!' in 585 ** database '%2!' was aborted because there was an 586 ** error in writing the replication log record." 587 */ 588 select @dbname = db_name() 589 raiserror 17756, "sp_setrepcol", @dbname 590 591 rollback transaction rs_logexec 592 return (1) 593 end 594 595 commit transaction rs_logexec 596 597 if (@column_name is NULL) 598 begin 599 /* 600 ** 18116, "The replication status for all Text/Image and off-row 601 ** objectcolumns in '%1!' is set to %2!." 602 */ 603 exec sp_getmessage 18116, @msg output 604 print @msg, @replicate_name, @setflag 605 return (0) 606 end 607 else 608 begin 609 /* 610 ** 17964 "The replication status for '%1!' is set to %2!." 611 */ 612 exec sp_getmessage 17964, @msg output 613 print @msg, @column_name, @setflag 614 return (0) 615 end 616
exec sp_procxmode 'sp_setrepcol', 'AnyMode' go Grant Execute on sp_setrepcol to public go
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_namecrack ![]() calls proc sybsystemprocs..sp_autoformat ![]() read_writes table tempdb..#colinfo_af (1) reads table master..syscolumns (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() reads table tempdb..systypes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..systypes (1) ![]() reads table tempdb..syscolumns (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..systypes ![]() writes table tempdb..#setrepcol2rs (1) read_writes table tempdb..#setrepcol (1) reads table master..sysmessages (1) ![]() writes table tempdb..#setrepcol1rs (1) reads table sybsystemprocs..syscolumns ![]() |