DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setrepdefmode  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_setrepdefmode"	
4     **
5     ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) or
6     **	   a user with REPLICATION authorization may execute this stored
7     **	   procedure."
8     ** 18101, "Table must be in the current database."
9     ** 18102, "Table '%1!' does not exist in this database."
10    ** 18110, "The table '%1!' is not marked for replication."
11    ** 18409, "The built-in function '%1!' failed. Please see any other messages printed along with this message."
12    ** 18537, "Usage: sp_setrepdefmode table_name, {owner_on | owner_off}
13    ** 18538, "owner_on"
14    ** 18539, "owner_off"
15    ** 18540, "The replication definition mode for '%1!' is already set to
16    **	   '%2!'. Replication definition mode for '%3!' does not change."
17    ** 18541, "An object with the same name but owned by a different user is
18    **	   already being replicated with the replication definition mode
19    **	   'owner_off'. The replication definition mode for '%1!' is not
20    **	   changed."
21    ** 18542, "Failed to change the replication definition mode for '%1!'
22    **	   because of an internal error."
23    ** 18543, "The replication definition mode for '%1!' is set to '%2!'."
24    */
25    create or replace procedure sp_setrepdefmode
26        @replicate_name varchar(767) = NULL, /* obj we want to mark as replicate */
27        @setflag varchar(20) = NULL, /* set or unset the replicate status. */
28        @setmode varchar(20) = NULL
29    as
30    
31        declare @is_replicated smallint /* The object is marked for replication */
32        declare @current_status int /* current sysstat value for the object. */
33        declare @new_status int /* new sysstat value for the object. */
34        declare @rep_constant smallint /* bit which indicates a replicated object. */
35        declare @setrep_owner int /* bit indicating the owner bit */
36        declare @setrep_never int /* bit indicating the rep status 'never' */
37        declare @owner_bit smallint
38        declare @db varchar(255) /* db of object. */
39        declare @owner varchar(255) /* owner of object. */
40        declare @object varchar(255) /* object's name. */
41        declare @msg varchar(1024)
42        declare @tmpstr varchar(255) /* Use to display current status */
43        declare @sqlstr varchar(20) /* Use to display sql current status */
44        declare @sptlang int
45        declare @procval int
46        declare @objid int
47        declare @mode int
48        declare @flagval int
49        declare @dbname varchar(255)
50        declare @user_tran int /* are we inside a user tran? */
51        declare @after_image int /* log the after image of the schema */
52        declare @mod_versionts int /* modify version timestamp after logging
53        ** the schema
54        */
55        declare @owner_syntax smallint /* Use to check stored proc syntax */
56        declare @sqlrep_syntax smallint /* Use to check stored proc syntax */
57        declare @threshold_syntax smallint /* Use to check strored proc syntax */
58        declare @sqlrep_mask int /* Holds the mask of bits for sql rep. */
59        declare @set int
60        declare @c1 char(1)
61        declare @c2 char(1)
62        declare @c3 char(1)
63    
64        /* Following variables hold constant strings retrieved from spt_values */
65        declare @owner_on varchar(10)
66        declare @owner_off varchar(10)
67        declare @update char(1)
68        declare @delete char(1)
69        declare @inserts char(1)
70        declare @on varchar(20)
71        declare @off varchar(20)
72        declare @never varchar(20)
73        declare @sqlrep varchar(4)
74    
75        /* Sql replication log threshold management */
76        declare @threshold varchar(20)
77        declare @threshold_value int
78        declare @current_threshold int
79        declare @threshold_max int
80        declare @outvalue varchar(10)
81        declare @outvalue1 varchar(10)
82    
83        /* Variables used in checking permissions. */
84        declare @dummy int
85        declare @nullarg char(1)
86        declare @gp_enabled int
87        declare @status1 int
88        declare @status2 int
89        declare @status3 int
90    
91        select @status1 = 1
92        select @status2 = 1
93        select @status3 = 1
94    
95        if @@trancount = 0
96        begin
97            set transaction isolation level 1
98            set chained off
99        end
100   
101       if (@@trancount > 0)
102           select @user_tran = 1
103       else
104           select @user_tran = 0
105   
106       /*
107       ** Replication enabled flag is 8000H (which is -32768D)
108       */
109       select @rep_constant = - 32768, /* 0x8000 in sysstat  */
110           @owner_bit = 4096, /* 0x1000 in sysstat2 */
111           @setrep_owner = 16, /* LT_SETREP_OWNER */
112           @setrep_never = 1024 /* LT_REP_NEVER */
113   
114   
115       /*
116       ** Initialize constant strings.
117       */
118       select @sqlrep = name
119       from master.dbo.spt_values
120       where type = 'SQ'
121           and number = 1
122   
123       select @sqlrep = substring(@sqlrep, 1, 3)
124       select @update = substring(@sqlrep, 1, 1)
125       select @delete = substring(@sqlrep, 2, 1)
126       select @inserts = substring(@sqlrep, 3, 1)
127   
128       exec sp_getmessage 18538, @owner_on out
129       exec sp_getmessage 18539, @owner_off out
130       exec sp_getmessage 19895, @off out
131       exec sp_getmessage 19896, @never out
132       exec sp_getmessage 19897, @on out
133       exec sp_getmessage 19931, @threshold out
134   
135       /* Create the temporary table for printing the values */
136       create table #repdefmode(val int, str varchar(10))
137   
138       insert #repdefmode values (0, @owner_off)
139       insert #repdefmode values (@owner_bit, @owner_on)
140       insert #repdefmode values (32, @update) /* LT_SQLREP_UPD */
141       insert #repdefmode values (64, @delete) /* LT_SQLREP_DEL */
142       insert #repdefmode values (128, @inserts) /* LT_SQLREP_INSSEL */
143       insert #repdefmode values (512, @never) /* LT_SQLREP_NEVER */
144   
145       select @threshold_max = 10000 /* LT_SQLREP_MAX_THRESHOLD */
146       select @sqlrep_mask = (32 | 64 | 128 | 512)
147   
148       /*
149       ** Set 'sptlang' for proper printing of object information.  Used mainly
150       ** for the 'select' statement which is executed when we are invoked with
151       ** no parameters.  Copied from similar code in 'sp_help'
152       */
153       select @sptlang = @@langid
154       if @@langid != 0
155       begin
156           if not exists (
157                   select * from master.dbo.sysmessages where error
158                       between 17100 and 17109
159                       and langid = @@langid)
160               select @sptlang = 0
161       end
162   
163       /*
164       ** If we are invoked with no parameters or fewer than two parameters,
165       ** print the usage message. 
166       */
167       if (@replicate_name is null)
168       begin
169           /*
170           ** 18537 "Usage: sp_setrepdefmode table_name 
171           **	  [, owner_on | owner_off | 'udi' [, 'on' | 'off' | 'never']
172           **		| 'threshold' [, value]]"
173           */
174           raiserror 18537
175           return (1)
176       end
177   
178       /*
179       ** Crack the name into its corresponding pieces.
180       */
181       execute sp_namecrack @replicate_name,
182           @db = @db output,
183           @owner = @owner output,
184           @object = @object output
185   
186       /*
187       ** Make sure that the object is in the current database.
188       */
189       if (@db is not NULL and @db != db_name())
190       begin
191           /*
192           ** 18101, "Table must be in the current database."
193           */
194           raiserror 18101
195           return (1)
196       end
197   
198       /*
199       **  Make sure that the object actually exists.
200       */
201       select @objid = object_id(@replicate_name)
202   
203       if (@objid is NULL) or
204           (not exists (select name from sysobjects where
205                       id = @objid and
206                       type = "U"
207                   ))
208       begin
209           /*
210           ** 18102, "Table '%1!' does not exist in this database."
211           */
212           raiserror 18102, @replicate_name
213           return (1)
214       end
215   
216       /*
217       ** If granular permissions is not enabled, 
218       ** you must be SA, dbo or have REPLICATION role to execute this
219       ** sproc.
220       ** If granular permissions is enabled then users with 
221       ** 'manage replication' permission can execute it.
222       */
223       select @dbname = db_name()
224       select @nullarg = NULL
225       execute @status1 = sp_aux_checkroleperm "dbo", "manage replication",
226           @dbname, @gp_enabled output
227   
228       if (@gp_enabled = 0)
229       begin
230           if (@status1 != 0)
231           begin
232               execute @status2 = sp_aux_checkroleperm "sa_role",
233                   @nullarg, @nullarg, @gp_enabled output
234   
235               if (@status2 != 0)
236                   execute @status3 = sp_aux_checkroleperm
237                       "replication_role", @nullarg, @nullarg,
238                       @gp_enabled output
239   
240               if (@status3 != 0)
241               begin
242                   /*
243                   ** 18418, "Only the System Administrator (SA), the
244                   **	   Database Owner (dbo) or a user with 
245                   **	   REPLICATION authorization may execute this 
246                   **	   stored procedure."
247                   */
248                   raiserror 18418
249                   return (1)
250               end
251           end
252           if (@status2 = 0)
253               select @dummy = proc_role("sa_role")
254           if (@status3 = 0)
255               select @dummy = proc_role("replication_role")
256       end
257       else
258       begin
259           select @dummy = proc_auditperm("manage replication", @status1, @dbname)
260           if (@status1 != 0)
261               return 1
262       end
263   
264       /* Initialize variables to control correct syntax */
265       select @owner_syntax = 0,
266           @sqlrep_syntax = 0,
267           @threshold_syntax = 0
268   
269       select @setflag = lower(@setflag)
270   
271       /*
272       ** We check whether @setflag is a valid
273       ** 'udi' substring. If it is, then
274       ** we know that we are dealing with a sqlrep 
275       ** mode setting command. @sqlrep_syntax will
276       ** then be set to 1, and to 0 otherwise.
277       */
278       exec sp_sqlrep_check_syntax @setflag,
279           @sqlrep,
280           @sqlrep_syntax output
281   
282       /*
283       ** Check whether the table is marked to be never replicated 
284       ** regardless of the database replication setting.
285       ** In that case, no replication setting may be changed.
286       */
287       select @current_status = getrepdefmode(@objid)
288       if (@current_status < 0)
289       begin
290           /*
291           ** 18409, "The built-in function '%1!' failed. Please see any 
292           ** other messages printed along with this message."
293           */
294           raiserror 18409, "getrepdefmode"
295           return (1)
296       end
297       if ((@current_status & @setrep_never) != 0)
298       begin
299           /*
300           ** 17965 "The replication status for '%1!' is currently %2!."
301           */
302           raiserror 17965, @replicate_name, @never
303           return (1)
304       end
305   
306       /*
307       ** Check whether the table is explicitely marked for replication.
308       */
309       if (exists
310                   (select * from sysobjects
311                   where
312                       id = @objid
313                       and (sysstat & @rep_constant) = @rep_constant
314                       and type = "U"))
315       begin
316           select @is_replicated = 1
317       end
318       else
319       begin
320           select @is_replicated = 0
321       end
322   
323       /*
324       ** We solely display setrepdefmode status in the
325       ** following cases:
326       **
327       **	1. 'sp_setrepdefmode '
328       **
329       **	2. 'sp_setrepdefmode 
, ' 330 ** 331 ** 3. 'sp_setrepdefmode
, 'threshold' 332 ** 333 */334 if((@setflagisnull)or335 (@setmodeisnulland@sqlrep_syntax= 1)or336 (@setmodeisnulland@setflagin("threshold",@threshold)))337 begin338 /* 339 ** Display the replication status only when the table 340 ** is marked for replication. 341 */342 if(@is_replicated= 1)343 begin344 select@current_status= getrepdefmode(@objid)345 if(@current_status< 0)346 begin347 /* 348 ** 18409, "The built-in funtion '%1!' failed. 349 ** Please see any other messages printed along 350 ** with this message." 351 */352 raiserror 18409, "getrepdefmode" 353 return(1)354 end355 select@tmpstr= "" 356 select@sqlstr= "" 357 if((@current_status&@setrep_owner)!= 0)358 select@tmpstr=@tmpstr+@owner_on359 else360 select@tmpstr=@tmpstr+@owner_off361 362 /* Build the string with the sql rep status */363 select@mode= val 364 from #repdefmodewhere str =@update365 if((@current_status&@mode)!= 0)366 select@sqlstr=@sqlstr+@update367 368 select@mode= val 369 from #repdefmodewhere str =@delete370 if((@current_status&@mode)!= 0)371 select@sqlstr=@sqlstr+@delete372 373 select@mode= val 374 from #repdefmodewhere str =@inserts375 if((@current_status&@mode)!= 0)376 select@sqlstr=@sqlstr+@inserts377 378 select@mode= val 379 from #repdefmodewhere str =@never380 if((@current_status&@mode)!= 0)381 select@sqlstr=@sqlstr+@never382 383 /* Get rid of empty string if necessary */384 select@sqlstr= ltrim(@sqlstr)385 386 if(datalength(@sqlstr)> 0)387 select@tmpstr=@tmpstr+ ", '" +@sqlstr+ "'" 388 389 /* 390 ** 17965 "The replication status for '%1!' is currently %2!." 391 */392 execsp_getmessage 17965,@msgoutput393 print@msg,@replicate_name,@tmpstr394 end395 396 /* Get the current replication threshold */397 select@current_threshold= getrepthreshold(@objid)398 399 if(@current_threshold< 0)400 begin401 /* 402 ** "The built-in function getrepthreshold() failed. 403 ** Please see any other messages printed along with 404 ** this message." 405 */406 raiserror 18409, "getrepthreshold" 407 return(1)408 end409 410 /* Check first if there is not a threshold defined */411 if(@current_threshold= 0)412 begin413 /* 414 ** 19949, "There is no replication threshold defined 415 ** for '%1!'." 416 */417 execsp_getmessage 19949,@msgoutput418 print@msg,@replicate_name419 return(0)420 end421 422 /* 423 ** 19932, "The replication threshold for table '%1!' is '%2!'." 424 */425 execsp_getmessage 19932,@msgoutput426 select@outvalue=convert(varchar(10),@current_threshold)427 print@msg,@replicate_name,@outvalue428 429 return(0)430 end431 432 select@setflag= ltrim(rtrim(@setflag))433 434 /* 435 ** If the table is not marked for replication, 436 ** then only a threshold operation is allowed. 437 */438 if((@is_replicated= 0)and(@setflag!=@threshold))439 begin440 /* 441 ** 18110, "The table '%1!' is not marked for replication." 442 */443 raiserror 18110,@replicate_name444 return(1)445 end446 447 /* check for valid threshold syntax. */448 if(@setflagin("threshold",@threshold))449 begin450 select@threshold_syntax= 1 451 end452 453 /* Check for valid owner mode syntax. */454 elseif(@setflagin(@owner_on,@owner_off))455 begin456 select@owner_syntax= 1 457 end458 459 /* Check for a valid sql replication syntax */460 else461 begin462 /* 463 ** Remember we have checked above whether @setflag 464 ** is a valid substring of 'udi'. If it is, then 465 ** @sqlrep_syntax has been set to 1, and we need 466 ** to check that @setmode is also correct. If 467 ** @setflag is not a valid substring of 'udi', 468 ** then @sqlrep_syntax is already 0. 469 */470 if(@setmodeisnotnull)471 begin472 /* 473 ** when checking the mode we need to account for either us_english 474 ** or the local language for the parameters. 475 */476 if(lower(@setmode)notin("on", "off", "never",@on,@off,@never))477 begin478 select@sqlrep_syntax= 0 479 end480 elseif(lower(@setmode)in("on", "never",@on,@never))481 begin482 select@set= 1 483 end484 elseif(lower(@setmode)in("off",@off))485 begin486 select@set= 0 487 end488 end489 else490 begin491 select@sqlrep_syntax= 0 492 end493 end494 495 /* The current syntax is invalid */496 if(@owner_syntax= 0)and(@sqlrep_syntax= 0)and(@threshold_syntax= 0)497 begin498 /* 499 ** 18537 "Usage: sp_setrepdefmode table_name 500 ** [, owner_on | owner_off | 'udi' [, 'on' | 'off' | 'never'] 501 ** | 'threshold' [, 'value']]" 502 */503 raiserror 18537 504 return(1)505 end506 507 /* We are changing the owner status */508 if(@owner_syntax= 1)509 begin510 /* Get the object's current status. */511 select@current_status= getrepdefmode(@objid)512 513 /* 514 ** Set the mode we want to change. For now only owner mode can be set. 515 */516 select@mode=@setrep_owner517 518 /* 519 ** Perform the requested operation on the object. 520 */521 if@setflagin(@owner_on, "owner_on")522 begin523 if(@current_status&@setrep_owner)=@setrep_owner524 begin525 /* 526 ** 18540: "The replication definition mode for '%1!' is 527 ** already set to '%2!'. Replication definition mode 528 ** for '%3!' does not change." 529 */530 raiserror 18540,@replicate_name,@setflag,@replicate_name531 return(1)532 end533 /* Set the action */534 select@set= 1 535 end536 else537 begin538 539 /* 540 ** Make sure that no like object with the same name, but a 541 ** different owner, exists. We need to do this because 542 ** the SQL Server does not send owner information along 543 ** with the object to the Replication Server. This 544 ** restriction may be lifted in future versions. 545 */546 ifexists(select*fromsysobjects547 wherename=@object548 and(549 (type= "U ")/* user table */550 or551 (type= "P ")/* stored procedure */552 )553 and(sysstat&@rep_constant)!= 0 554 and(sysstat2&@owner_bit)= 0)555 begin556 /* 557 ** 18541, "An object with the same name but owned by a 558 ** different user is already being replicated 559 ** with the replication definition mode 'owner_off'. 560 ** The replication definition mode for '%1!' is not 561 ** changed." 562 */563 raiserror 18541,@replicate_name564 return(1)565 end566 567 /* 568 ** We are attempting to remove the owner status but it is 569 ** not set. 570 */571 if((@current_status&@setrep_owner)= 0)572 begin573 /* 574 ** 18540: "The replication definition mode for '%1!' is 575 ** already set to '%2!'. Replication definition mode 576 ** for '%3!' does not change." 577 */578 raiserror 18540,@replicate_name,@setflag,@replicate_name579 return(1)580 end581 582 /* Set the action */583 select@set= 0 584 end585 586 /* 587 ** Update the object's sysstat column 588 ** 589 ** IMPORTANT: The name "rs_logexec is significant and is used by 590 ** Replication Server 591 */592 begintransaction rs_logexec 593 594 /* log the schema first before we update sysobjects */595 select@after_image= 0 596 select@mod_versionts= 1 597 if(logschema(@objid,@user_tran,@after_image,598 @mod_versionts)!= 1)599 begin600 /* 601 ** 17968 "The built-in function logschema() failed 602 ** for '%1!'." 603 */604 execsp_getmessage 17968,@msgoutput605 print@msg,@replicate_name606 607 rollbacktransaction608 return(1)609 end610 611 /* 612 ** Hold a read lock so that the status cannot be 613 ** changed till we have done. 614 */615 select@current_status=sysstat2616 fromsysobjectsholdlock617 whereid=@objid618 619 /* Determine the new status depending on the setting */620 if(@set= 0)621 begin622 select@new_status=@current_status&~@owner_bit623 end624 else625 if(@set= 1)626 begin627 select@new_status=@current_status|@owner_bit628 end629 630 /* Set the new status */631 updatesysobjectssetsysstat2=@new_status632 where633 id=@objid634 635 /* log the schema with the after image if we were in a user 636 ** transaction 637 */638 if(@user_tran= 1)639 begin640 select@after_image= 1 641 select@mod_versionts= 0 642 if(logschema(@objid,@user_tran,@after_image,643 @mod_versionts)!= 1)644 begin645 /* 646 ** 17968 "The built-in function logschema() failed 647 ** for '%1!'." 648 */649 execsp_getmessage 17968,@msgoutput650 print@msg,@replicate_name651 652 rollbacktransaction653 return(1)654 end655 end656 657 /* 658 ** Update the object's status in cache. 659 */660 if(setrepdefmode(@objid,@mode,@set)!= 1)661 begin662 /* 663 ** 18542 "Failed to change the replication definition 664 ** mode for '%1!' because of an internal error." 665 */666 raiserror 18542,@replicate_name667 668 rollbacktransaction669 670 return(1)671 end672 673 /* 674 ** Write the log record to replicate this invocation 675 ** of the stored procedure. 676 */677 if(logexec()!= 1)678 begin679 /* 680 ** 17756, "The execution of the stored procedure 681 ** '%1!' in database '%2!' was aborted 682 ** because there was an error in writing 683 ** the replication log record." 684 */685 select@dbname= db_name()686 raiserror 17756, "sp_setrepdefmode",@dbname687 688 rollbacktransaction rs_logexec 689 return(1)690 end691 692 committransaction693 /* 694 ** 18543 ""The replication definition mode for '%1!' is set to '%2!'." 695 */696 execsp_getmessage 18543,@msgoutput697 print@msg,@replicate_name,@setflag698 return(0)699 end700 else/* We are changing the sql replication status */701 if(@sqlrep_syntax= 1)702 begin703 if(@setmodein("never",@never))704 begin705 select@mode= val 706 from #repdefmode707 where str =@never708 end709 else710 begin711 select@mode= 0 712 select@c1= substring(@setflag, 1, 1)713 select@c2= substring(@setflag, 2, 1)714 select@c3= substring(@setflag, 3, 1)715 716 select@mode=@mode| val 717 from #repdefmode718 where str =@c1and@c1isnotnull719 720 select@mode=@mode| val 721 from #repdefmode722 where str =@c2and@c2isnotnull723 724 select@mode=@mode| val 725 from #repdefmode726 where str =@c3and@c3isnotnull727 end728 729 /* Get the current sql statement setting */730 select@current_status= getrepdefmode(@objid)731 732 /* 733 ** If we are attempting to set but we have already the status 734 ** just return. 735 */736 if((@mode=(@current_status&@sqlrep_mask))and(@set= 1))737 begin738 /* 739 ** 18540: "The replication definition mode for '%1!' is 740 ** already set to '%2!'. Replication definition mode 741 ** for '%3!' does not change." 742 */743 execsp_getmessage 18540,@msgoutput744 if(@setmodein("never",@never))745 print@msg,@replicate_name,@setmode,@replicate_name746 else747 print@msg,@replicate_name,@setflag,@replicate_name748 return(0)749 end750 751 /* 752 ** IMPORTANT: The name "rs_logexec is significant and is used by 753 ** Replication Server 754 */755 begintransaction rs_logexec 756 757 if(setrepdefmode(@objid,@mode,@set)!= 1)758 begin759 /* 760 ** 18542 "Failed to change the replication definition 761 ** mode for '%1!' because of an internal error." 762 */763 raiserror 18542,@replicate_name764 765 rollbacktransaction766 767 return(1)768 end769 770 /* 771 ** Write the log record to replicate this invocation 772 ** of the stored procedure. 773 */774 if(logexec()!= 1)775 begin776 /* 777 ** 17756, "The execution of the stored procedure 778 ** '%1!' in database '%2!' was aborted 779 ** because there was an error in writing 780 ** the replication log record." 781 */782 select@dbname= db_name()783 raiserror 17756, "sp_setrepdefmode",@dbname784 785 rollbacktransaction rs_logexec 786 return(1)787 end788 789 committransaction790 /* 791 ** 18543 ""The replication definition mode for '%1!' is set to '%2!'." 792 */793 execsp_getmessage 18543,@msgoutput794 if(@setmodein("never",@never))or(@set= 0)795 print@msg,@replicate_name,@setmode796 else797 print@msg,@replicate_name,@setflag798 return(0)799 end800 else801 /* We are changing the sql replication log threshold */802 if(@threshold_syntax= 1)803 begin804 if(@setmodeisnull)805 begin806 /* Get the current replication threshold */807 select@current_threshold= getrepthreshold(@objid)808 809 if(@current_threshold< 0)810 begin811 /* 812 ** "The built-in function getrepthreshold() failed. 813 ** Please see any other messages printed along with 814 ** this message." 815 */816 raiserror 18409, "getrepthreshold" 817 return(1)818 end819 820 /* 821 ** 19932, "The replication threshold for table '%1!' 822 ** is '%2!'." 823 */824 execsp_getmessage 19932,@msgoutput825 select@outvalue=convert(varchar(10),@current_threshold)826 print@msg,@replicate_name,@outvalue827 return(0)828 end829 830 select@threshold_value=convert(int,@setmode)831 832 if((@threshold_value< 0)or(@threshold_value>@threshold_max))833 begin834 /* 835 ** 19934, "The replication threshold provided for 836 ** table '%1!' is '%2!'. Please choose a 837 ** threshold larger than 0 and smaller than '%3!'." 838 */839 select@outvalue=convert(varchar(10),@threshold_value)840 select@outvalue1=convert(varchar(10),@threshold_max)841 raiserror 19934,@replicate_name,@outvalue,@outvalue1842 end843 844 /* Now the threshold value exists and is legal */845 846 /* 847 ** IMPORTANT: The name "rs_logexec is significant and is used by 848 ** Replication Server 849 */850 begintransaction rs_logexec 851 852 if(setrepthreshold(@objid,@threshold_value)!= 1)853 begin854 /* 855 ** 18409, "The built-in funtion '%1!' failed. Please see 856 ** any other messages printed along with this 857 ** message." 858 */859 raiserror 18409, "setrepthreshold" 860 return(1)861 862 return(1)863 end864 865 /* 866 ** Write the log record to replicate this invocation 867 ** of the stored procedure. 868 */869 if(logexec()!= 1)870 begin871 /* 872 ** 17756, "The execution of the stored procedure 873 ** '%1!' in database '%2!' was aborted 874 ** because there was an error in writing 875 ** the replication log record." 876 */877 select@dbname= db_name()878 raiserror 17756, "sp_setrepdefmode",@dbname879 880 rollbacktransaction rs_logexec 881 return(1)882 end883 884 committransaction885 886 /* We requested to clear the threshold */887 if(@threshold_value= 0)888 begin889 /* 890 ** 19948, "The replication threshold for '%1!' has been cleared" 891 */892 execsp_getmessage 19948,@msgoutput893 print@msg,@replicate_name894 return(0)895 end896 897 /* 898 ** 19932, 'The replication threshold for '%1!' is '%2!'." 899 */900 execsp_getmessage 19932,@msgoutput901 select@outvalue=convert(varchar(10),@threshold_value)902 print@msg,@replicate_name,@outvalue903 return(0)904 end905


exec sp_procxmode 'sp_setrepdefmode', 'AnyMode'
go

Grant Execute on sp_setrepdefmode to public
go
DEFECTS
 MURC 6 Unreachable Code 862
 MRIT 5 Return in Transaction trancount is 1 860
 MRIT 5 Return in Transaction trancount is 1 862
 MCTR 4 Conditional Begin Tran or Commit Tran 592
 MCTR 4 Conditional Begin Tran or Commit Tran 692
 MCTR 4 Conditional Begin Tran or Commit Tran 755
 MCTR 4 Conditional Begin Tran or Commit Tran 789
 MCTR 4 Conditional Begin Tran or Commit Tran 850
 MCTR 4 Conditional Begin Tran or Commit Tran 884
 MEST 4 Empty String will be replaced by Single Space 355
 MEST 4 Empty String will be replaced by Single Space 356
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @sqlrep: varchar(4) = varchar(255) 118
 MTYP 4 Assignment type mismatch str: varchar(10) = varchar(20) 143
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 159
 QTYP 4 Comparison type mismatch smallint = int 159
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 205
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 312
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 617
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 633
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_setrepdefmode  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 128
 MNER 3 No Error Check should check return value of exec 129
 MNER 3 No Error Check should check return value of exec 130
 MNER 3 No Error Check should check return value of exec 131
 MNER 3 No Error Check should check return value of exec 132
 MNER 3 No Error Check should check return value of exec 133
 MNER 3 No Error Check should check @@error after insert 138
 MNER 3 No Error Check should check @@error after insert 139
 MNER 3 No Error Check should check @@error after insert 140
 MNER 3 No Error Check should check @@error after insert 141
 MNER 3 No Error Check should check @@error after insert 142
 MNER 3 No Error Check should check @@error after insert 143
 MNER 3 No Error Check should check return value of exec 181
 MNER 3 No Error Check should check return value of exec 278
 MNER 3 No Error Check should check return value of exec 392
 MNER 3 No Error Check should check return value of exec 417
 MNER 3 No Error Check should check return value of exec 425
 MNER 3 No Error Check should check return value of exec 604
 MNER 3 No Error Check should check @@error after update 631
 MNER 3 No Error Check should check return value of exec 649
 MNER 3 No Error Check should check return value of exec 696
 MNER 3 No Error Check should check return value of exec 743
 MNER 3 No Error Check should check return value of exec 793
 MNER 3 No Error Check should check return value of exec 824
 MNER 3 No Error Check should check return value of exec 892
 MNER 3 No Error Check should check return value of exec 900
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 260
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 303
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 357
 MUCO 3 Useless Code Useless Brackets 365
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 375
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 386
 MUCO 3 Useless Code Useless Brackets 399
 MUCO 3 Useless Code Useless Brackets 407
 MUCO 3 Useless Code Useless Brackets 411
 MUCO 3 Useless Code Useless Brackets 419
 MUCO 3 Useless Code Useless Brackets 429
 MUCO 3 Useless Code Useless Brackets 438
 MUCO 3 Useless Code Useless Brackets 444
 MUCO 3 Useless Code Useless Brackets 448
 MUCO 3 Useless Code Useless Brackets 454
 MUCO 3 Useless Code Useless Brackets 470
 MUCO 3 Useless Code Useless Brackets 476
 MUCO 3 Useless Code Useless Brackets 480
 MUCO 3 Useless Code Useless Brackets 484
 MUCO 3 Useless Code Useless Brackets 504
 MUCO 3 Useless Code Useless Brackets 508
 MUCO 3 Useless Code Useless Brackets 531
 MUCO 3 Useless Code Useless Brackets 564
 MUCO 3 Useless Code Useless Brackets 571
 MUCO 3 Useless Code Useless Brackets 579
 MUCO 3 Useless Code Useless Brackets 597
 MUCO 3 Useless Code Useless Brackets 608
 MUCO 3 Useless Code Useless Brackets 620
 MUCO 3 Useless Code Useless Brackets 625
 MUCO 3 Useless Code Useless Brackets 638
 MUCO 3 Useless Code Useless Brackets 642
 MUCO 3 Useless Code Useless Brackets 653
 MUCO 3 Useless Code Useless Brackets 660
 MUCO 3 Useless Code Useless Brackets 670
 MUCO 3 Useless Code Useless Brackets 677
 MUCO 3 Useless Code Useless Brackets 689
 MUCO 3 Useless Code Useless Brackets 698
 MUCO 3 Useless Code Useless Brackets 701
 MUCO 3 Useless Code Useless Brackets 703
 MUCO 3 Useless Code Useless Brackets 736
 MUCO 3 Useless Code Useless Brackets 744
 MUCO 3 Useless Code Useless Brackets 748
 MUCO 3 Useless Code Useless Brackets 757
 MUCO 3 Useless Code Useless Brackets 767
 MUCO 3 Useless Code Useless Brackets 774
 MUCO 3 Useless Code Useless Brackets 786
 MUCO 3 Useless Code Useless Brackets 798
 MUCO 3 Useless Code Useless Brackets 802
 MUCO 3 Useless Code Useless Brackets 804
 MUCO 3 Useless Code Useless Brackets 809
 MUCO 3 Useless Code Useless Brackets 817
 MUCO 3 Useless Code Useless Brackets 827
 MUCO 3 Useless Code Useless Brackets 832
 MUCO 3 Useless Code Useless Brackets 852
 MUCO 3 Useless Code Useless Brackets 860
 MUCO 3 Useless Code Useless Brackets 862
 MUCO 3 Useless Code Useless Brackets 869
 MUCO 3 Useless Code Useless Brackets 881
 MUCO 3 Useless Code Useless Brackets 887
 MUCO 3 Useless Code Useless Brackets 894
 MUCO 3 Useless Code Useless Brackets 903
 MUIN 3 Column created using implicit nullability 136
 QAFM 3 Var Assignment from potentially many rows 118
 QAFM 3 Var Assignment from potentially many rows 363
 QAFM 3 Var Assignment from potentially many rows 368
 QAFM 3 Var Assignment from potentially many rows 373
 QAFM 3 Var Assignment from potentially many rows 378
 QAFM 3 Var Assignment from potentially many rows 705
 QAFM 3 Var Assignment from potentially many rows 716
 QAFM 3 Var Assignment from potentially many rows 720
 QAFM 3 Var Assignment from potentially many rows 724
 QISO 3 Set isolation level 97
 QPNC 3 No column in condition 718
 QPNC 3 No column in condition 722
 QPNC 3 No column in condition 726
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
157
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
547
 VNRD 3 Variable is not read @sptlang 160
 VNRD 3 Variable is not read @owner 183
 VNRD 3 Variable is not read @gp_enabled 238
 VNRD 3 Variable is not read @dummy 259
 VUNU 3 Variable is not used @procval 45
 VUNU 3 Variable is not used @flagval 48
 MSUB 2 Subquery Marker 156
 MSUB 2 Subquery Marker 204
 MSUB 2 Subquery Marker 310
 MSUB 2 Subquery Marker 546
 MTR1 2 Metrics: Comments Ratio Comments: 45% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 57 = 88dec - 33exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 427 25

DATA PROPAGATION detailed
ColumnWritten To
@replicate_namesysobjects.sysstat2   sp_helpconstraint_rset_002.definition sp_helpconstraint_rset_003.definition sp_helpconstraint_rset_004.definition

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table tempdb..#repdefmode (1) 
calls proc sybsystemprocs..sp_sqlrep_check_syntax  
reads table master..sysmessages (1)  
read_writes table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
calls proc sybsystemprocs..sp_namecrack