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 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 if @@trancount = 0
84 begin
85 set transaction isolation level 1
86 set chained off
87 end
88
89 if (@@trancount > 0)
90 select @user_tran = 1
91 else
92 select @user_tran = 0
93
94 /*
95 ** Replication enabled flag is 8000H (which is -32768D)
96 */
97 select @rep_constant = - 32768, /* 0x8000 in sysstat */
98 @owner_bit = 4096, /* 0x1000 in sysstat2 */
99 @setrep_owner = 16, /* LT_SETREP_OWNER */
100 @setrep_never = 1024 /* LT_REP_NEVER */
101
102
103 /*
104 ** Initialize constant strings.
105 */
106 select @sqlrep = name
107 from master.dbo.spt_values
108 where type = 'SQ'
109 and number = 1
110
111 select @sqlrep = substring(@sqlrep, 1, 3)
112 select @update = substring(@sqlrep, 1, 1)
113 select @delete = substring(@sqlrep, 2, 1)
114 select @inserts = substring(@sqlrep, 3, 1)
115
116 exec sp_getmessage 18538, @owner_on out
117 exec sp_getmessage 18539, @owner_off out
118 exec sp_getmessage 19895, @off out
119 exec sp_getmessage 19896, @never out
120 exec sp_getmessage 19897, @on out
121 exec sp_getmessage 19931, @threshold out
122
123 /* Create the temporary table for printing the values */
124 create table #repdefmode(val int, str varchar(10))
125
126 insert #repdefmode values (0, @owner_off)
127 insert #repdefmode values (@owner_bit, @owner_on)
128 insert #repdefmode values (32, @update) /* LT_SQLREP_UPD */
129 insert #repdefmode values (64, @delete) /* LT_SQLREP_DEL */
130 insert #repdefmode values (128, @inserts) /* LT_SQLREP_INSSEL */
131 insert #repdefmode values (512, @never) /* LT_SQLREP_NEVER */
132
133 select @threshold_max = 10000 /* LT_SQLREP_MAX_THRESHOLD */
134 select @sqlrep_mask = (32 | 64 | 128 | 512)
135
136 /*
137 ** Set 'sptlang' for proper printing of object information. Used mainly
138 ** for the 'select' statement which is executed when we are invoked with
139 ** no parameters. Copied from similar code in 'sp_help'
140 */
141 select @sptlang = @@langid
142 if @@langid != 0
143 begin
144 if not exists (
145 select * from master.dbo.sysmessages where error
146 between 17100 and 17109
147 and langid = @@langid)
148 select @sptlang = 0
149 end
150
151 /*
152 ** If we are invoked with no parameters or fewer than two parameters,
153 ** print the usage message.
154 */
155 if (@replicate_name is null)
156 begin
157 /*
158 ** 18537 "Usage: sp_setrepdefmode table_name
159 ** [, owner_on | owner_off | 'udi' [, 'on' | 'off' | 'never']
160 ** | 'threshold' [, value]]"
161 */
162 raiserror 18537
163 return (1)
164 end
165
166 /*
167 ** Crack the name into its corresponding pieces.
168 */
169 execute sp_namecrack @replicate_name,
170 @db = @db output,
171 @owner = @owner output,
172 @object = @object output
173
174 /*
175 ** Make sure that the object is in the current database.
176 */
177 if (@db is not NULL and @db != db_name())
178 begin
179 /*
180 ** 18101, "Table must be in the current database."
181 */
182 raiserror 18101
183 return (1)
184 end
185
186 /*
187 ** Make sure that the object actually exists.
188 */
189 select @objid = object_id(@replicate_name)
190
191 if (@objid is NULL) or
192 (not exists (select name from sysobjects where
193 id = @objid and
194 type = "U"
195 ))
196 begin
197 /*
198 ** 18102, "Table '%1!' does not exist in this database."
199 */
200 raiserror 18102, @replicate_name
201 return (1)
202 end
203
204 /*
205 ** You must be SA, dbo or have REPLICATION role to execute this
206 ** sproc.
207 */
208 if (user_id() != 1)
209 begin
210 if (charindex("sa_role", show_role()) = 0 and
211 charindex("replication_role", show_role()) = 0)
212 begin
213 /*
214 ** 18418, "Only the System Administrator (SA), the
215 ** Database Owner (dbo) or a user with REPLICATION
216 ** authorization may execute this stored
217 ** procedure."
218 */
219 raiserror 18418
220 return (1)
221 end
222 else
223 begin
224 /*
225 ** Call proc_role() with each role that the user has
226 ** in order to send the success audit records.
227 ** Note that this could mean 1 or 2 audit records.
228 */
229 if (charindex("sa_role", show_role()) > 0)
230 select @procval = proc_role("sa_role")
231 if (charindex("replication_role", show_role()) > 0)
232 select @procval = proc_role("replication_role")
233 end
234 end
235
236 /* Initialize variables to control correct syntax */
237 select @owner_syntax = 0,
238 @sqlrep_syntax = 0,
239 @threshold_syntax = 0
240
241 select @setflag = lower(@setflag)
242
243 /*
244 ** We check whether @setflag is a valid
245 ** 'udi' substring. If it is, then
246 ** we know that we are dealing with a sqlrep
247 ** mode setting command. @sqlrep_syntax will
248 ** then be set to 1, and to 0 otherwise.
249 */
250 exec sp_sqlrep_check_syntax @setflag,
251 @sqlrep,
252 @sqlrep_syntax output
253
254 /*
255 ** Check whether the table is marked to be never replicated
256 ** regardless of the database replication setting.
257 ** In that case, no replication setting may be changed.
258 */
259 select @current_status = getrepdefmode(@objid)
260 if (@current_status < 0)
261 begin
262 /*
263 ** 18409, "The built-in function '%1!' failed. Please see any
264 ** other messages printed along with this message."
265 */
266 raiserror 18409, "getrepdefmode"
267 return (1)
268 end
269 if ((@current_status & @setrep_never) != 0)
270 begin
271 /*
272 ** 17965 "The replication status for '%1!' is currently %2!."
273 */
274 raiserror 17965, @replicate_name, @never
275 return (1)
276 end
277
278 /*
279 ** Check whether the table is explicitely marked for replication.
280 */
281 if (exists
282 (select * from sysobjects
283 where
284 id = @objid
285 and (sysstat & @rep_constant) = @rep_constant
286 and type = "U"))
287 begin
288 select @is_replicated = 1
289 end
290 else
291 begin
292 select @is_replicated = 0
293 end
294
295 /*
296 ** We solely display setrepdefmode status in the
297 ** following cases:
298 **
299 ** 1. 'sp_setrepdefmode '
300 **
301 ** 2. 'sp_setrepdefmode , '
302 **
303 ** 3. 'sp_setrepdefmode , 'threshold'
304 **
305 */
306 if ((@setflag is null) or
307 (@setmode is null and @sqlrep_syntax = 1) or
308 (@setmode is null and @setflag in ("threshold", @threshold)))
309 begin
310 /*
311 ** Display the replication status only when the table
312 ** is marked for replication.
313 */
314 if (@is_replicated = 1)
315 begin
316 select @current_status = getrepdefmode(@objid)
317 if (@current_status < 0)
318 begin
319 /*
320 ** 18409, "The built-in funtion '%1!' failed.
321 ** Please see any other messages printed along
322 ** with this message."
323 */
324 raiserror 18409, "getrepdefmode"
325 return (1)
326 end
327 select @tmpstr = ""
328 select @sqlstr = ""
329 if ((@current_status & @setrep_owner) != 0)
330 select @tmpstr = @tmpstr + @owner_on
331 else
332 select @tmpstr = @tmpstr + @owner_off
333
334 /* Build the string with the sql rep status */
335 select @mode = val
336 from #repdefmode where str = @update
337 if ((@current_status & @mode) != 0)
338 select @sqlstr = @sqlstr + @update
339
340 select @mode = val
341 from #repdefmode where str = @delete
342 if ((@current_status & @mode) != 0)
343 select @sqlstr = @sqlstr + @delete
344
345 select @mode = val
346 from #repdefmode where str = @inserts
347 if ((@current_status & @mode) != 0)
348 select @sqlstr = @sqlstr + @inserts
349
350 select @mode = val
351 from #repdefmode where str = @never
352 if ((@current_status & @mode) != 0)
353 select @sqlstr = @sqlstr + @never
354
355 /* Get rid of empty string if necessary */
356 select @sqlstr = ltrim(@sqlstr)
357
358 if (datalength(@sqlstr) > 0)
359 select @tmpstr = @tmpstr + ", '" + @sqlstr + "'"
360
361 /*
362 ** 17965 "The replication status for '%1!' is currently %2!."
363 */
364 exec sp_getmessage 17965, @msg output
365 print @msg, @replicate_name, @tmpstr
366 end
367
368 /* Get the current replication threshold */
369 select @current_threshold = getrepthreshold(@objid)
370
371 if (@current_threshold < 0)
372 begin
373 /*
374 ** "The built-in function getrepthreshold() failed.
375 ** Please see any other messages printed along with
376 ** this message."
377 */
378 raiserror 18409, "getrepthreshold"
379 return (1)
380 end
381
382 /* Check first if there is not a threshold defined */
383 if (@current_threshold = 0)
384 begin
385 /*
386 ** 19949, "There is no replication threshold defined
387 ** for '%1!'."
388 */
389 exec sp_getmessage 19949, @msg output
390 print @msg, @replicate_name
391 return (0)
392 end
393
394 /*
395 ** 19932, "The replication threshold for table '%1!' is '%2!'."
396 */
397 exec sp_getmessage 19932, @msg output
398 select @outvalue = convert(varchar(10), @current_threshold)
399 print @msg, @replicate_name, @outvalue
400
401 return (0)
402 end
403
404 select @setflag = ltrim(rtrim(@setflag))
405
406 /*
407 ** If the table is not marked for replication,
408 ** then only a threshold operation is allowed.
409 */
410 if ((@is_replicated = 0) and (@setflag != @threshold))
411 begin
412 /*
413 ** 18110, "The table '%1!' is not marked for replication."
414 */
415 raiserror 18110, @replicate_name
416 return (1)
417 end
418
419 /* check for valid threshold syntax. */
420 if (@setflag in ("threshold", @threshold))
421 begin
422 select @threshold_syntax = 1
423 end
424
425 /* Check for valid owner mode syntax. */
426 else if (@setflag in (@owner_on, @owner_off))
427 begin
428 select @owner_syntax = 1
429 end
430
431 /* Check for a valid sql replication syntax */
432 else
433 begin
434 /*
435 ** Remember we have checked above whether @setflag
436 ** is a valid substring of 'udi'. If it is, then
437 ** @sqlrep_syntax has been set to 1, and we need
438 ** to check that @setmode is also correct. If
439 ** @setflag is not a valid substring of 'udi',
440 ** then @sqlrep_syntax is already 0.
441 */
442 if (@setmode is not null)
443 begin
444 /*
445 ** when checking the mode we need to account for either us_english
446 ** or the local language for the parameters.
447 */
448 if (lower(@setmode) not in ("on", "off", "never", @on, @off, @never))
449 begin
450 select @sqlrep_syntax = 0
451 end
452 else if (lower(@setmode) in ("on", "never", @on, @never))
453 begin
454 select @set = 1
455 end
456 else if (lower(@setmode) in ("off", @off))
457 begin
458 select @set = 0
459 end
460 end
461 else
462 begin
463 select @sqlrep_syntax = 0
464 end
465 end
466
467 /* The current syntax is invalid */
468 if (@owner_syntax = 0) and (@sqlrep_syntax = 0) and (@threshold_syntax = 0)
469 begin
470 /*
471 ** 18537 "Usage: sp_setrepdefmode table_name
472 ** [, owner_on | owner_off | 'udi' [, 'on' | 'off' | 'never']
473 ** | 'threshold' [, 'value']]"
474 */
475 raiserror 18537
476 return (1)
477 end
478
479 /* We are changing the owner status */
480 if (@owner_syntax = 1)
481 begin
482 /* Get the object's current status. */
483 select @current_status = getrepdefmode(@objid)
484
485 /*
486 ** Set the mode we want to change. For now only owner mode can be set.
487 */
488 select @mode = @setrep_owner
489
490 /*
491 ** Perform the requested operation on the object.
492 */
493 if @setflag in (@owner_on, "owner_on")
494 begin
495 if (@current_status & @setrep_owner) = @setrep_owner
496 begin
497 /*
498 ** 18540: "The replication definition mode for '%1!' is
499 ** already set to '%2!'. Replication definition mode
500 ** for '%3!' does not change."
501 */
502 raiserror 18540, @replicate_name, @setflag, @replicate_name
503 return (1)
504 end
505 /* Set the action */
506 select @set = 1
507 end
508 else
509 begin
510
511 /*
512 ** Make sure that no like object with the same name, but a
513 ** different owner, exists. We need to do this because
514 ** the SQL Server does not send owner information along
515 ** with the object to the Replication Server. This
516 ** restriction may be lifted in future versions.
517 */
518 if exists (select * from sysobjects
519 where name = @object
520 and (
521 (type = "U ") /* user table */
522 or
523 (type = "P ") /* stored procedure */
524 )
525 and (sysstat & @rep_constant) != 0
526 and (sysstat2 & @owner_bit) = 0)
527 begin
528 /*
529 ** 18541, "An object with the same name but owned by a
530 ** different user is already being replicated
531 ** with the replication definition mode 'owner_off'.
532 ** The replication definition mode for '%1!' is not
533 ** changed."
534 */
535 raiserror 18541, @replicate_name
536 return (1)
537 end
538
539 /*
540 ** We are attempting to remove the owner status but it is
541 ** not set.
542 */
543 if ((@current_status & @setrep_owner) = 0)
544 begin
545 /*
546 ** 18540: "The replication definition mode for '%1!' is
547 ** already set to '%2!'. Replication definition mode
548 ** for '%3!' does not change."
549 */
550 raiserror 18540, @replicate_name, @setflag, @replicate_name
551 return (1)
552 end
553
554 /* Set the action */
555 select @set = 0
556 end
557
558 /*
559 ** Update the object's sysstat column
560 **
561 ** IMPORTANT: The name "rs_logexec is significant and is used by
562 ** Replication Server
563 */
564 begin transaction rs_logexec
565
566 /* log the schema first before we update sysobjects */
567 select @after_image = 0
568 select @mod_versionts = 1
569 if (logschema(@objid, @user_tran, @after_image,
570 @mod_versionts) != 1)
571 begin
572 /*
573 ** 17968 "The built-in function logschema() failed
574 ** for '%1!'."
575 */
576 exec sp_getmessage 17968, @msg output
577 print @msg, @replicate_name
578
579 rollback transaction
580 return (1)
581 end
582
583 /*
584 ** Hold a read lock so that the status cannot be
585 ** changed till we have done.
586 */
587 select @current_status = sysstat2
588 from sysobjects holdlock
589 where id = @objid
590
591 /* Determine the new status depending on the setting */
592 if (@set = 0)
593 begin
594 select @new_status = @current_status & ~ @owner_bit
595 end
596 else
597 if (@set = 1)
598 begin
599 select @new_status = @current_status | @owner_bit
600 end
601
602 /* Set the new status */
603 update sysobjects set sysstat2 = @new_status
604 where
605 id = @objid
606
607 /* log the schema with the after image if we were in a user
608 ** transaction
609 */
610 if (@user_tran = 1)
611 begin
612 select @after_image = 1
613 select @mod_versionts = 0
614 if (logschema(@objid, @user_tran, @after_image,
615 @mod_versionts) != 1)
616 begin
617 /*
618 ** 17968 "The built-in function logschema() failed
619 ** for '%1!'."
620 */
621 exec sp_getmessage 17968, @msg output
622 print @msg, @replicate_name
623
624 rollback transaction
625 return (1)
626 end
627 end
628
629 /*
630 ** Update the object's status in cache.
631 */
632 if (setrepdefmode(@objid, @mode, @set) != 1)
633 begin
634 /*
635 ** 18542 "Failed to change the replication definition
636 ** mode for '%1!' because of an internal error."
637 */
638 raiserror 18542, @replicate_name
639
640 rollback transaction
641
642 return (1)
643 end
644
645 /*
646 ** Write the log record to replicate this invocation
647 ** of the stored procedure.
648 */
649 if (logexec() != 1)
650 begin
651 /*
652 ** 17756, "The execution of the stored procedure
653 ** '%1!' in database '%2!' was aborted
654 ** because there was an error in writing
655 ** the replication log record."
656 */
657 select @dbname = db_name()
658 raiserror 17756, "sp_setrepdefmode", @dbname
659
660 rollback transaction rs_logexec
661 return (1)
662 end
663
664 commit transaction
665 /*
666 ** 18543 ""The replication definition mode for '%1!' is set to '%2!'."
667 */
668 exec sp_getmessage 18543, @msg output
669 print @msg, @replicate_name, @setflag
670 return (0)
671 end
672 else /* We are changing the sql replication status */
673 if (@sqlrep_syntax = 1)
674 begin
675 if (@setmode in ("never", @never))
676 begin
677 select @mode = val
678 from #repdefmode
679 where str = @never
680 end
681 else
682 begin
683 select @mode = 0
684 select @c1 = substring(@setflag, 1, 1)
685 select @c2 = substring(@setflag, 2, 1)
686 select @c3 = substring(@setflag, 3, 1)
687
688 select @mode = @mode | val
689 from #repdefmode
690 where str = @c1 and @c1 is not null
691
692 select @mode = @mode | val
693 from #repdefmode
694 where str = @c2 and @c2 is not null
695
696 select @mode = @mode | val
697 from #repdefmode
698 where str = @c3 and @c3 is not null
699 end
700
701 /* Get the current sql statement setting */
702 select @current_status = getrepdefmode(@objid)
703
704 /*
705 ** If we are attempting to set but we have already the status
706 ** just return.
707 */
708 if ((@mode = (@current_status & @sqlrep_mask)) and (@set = 1))
709 begin
710 /*
711 ** 18540: "The replication definition mode for '%1!' is
712 ** already set to '%2!'. Replication definition mode
713 ** for '%3!' does not change."
714 */
715 exec sp_getmessage 18540, @msg output
716 if (@setmode in ("never", @never))
717 print @msg, @replicate_name, @setmode, @replicate_name
718 else
719 print @msg, @replicate_name, @setflag, @replicate_name
720 return (0)
721 end
722
723 /*
724 ** IMPORTANT: The name "rs_logexec is significant and is used by
725 ** Replication Server
726 */
727 begin transaction rs_logexec
728
729 if (setrepdefmode(@objid, @mode, @set) != 1)
730 begin
731 /*
732 ** 18542 "Failed to change the replication definition
733 ** mode for '%1!' because of an internal error."
734 */
735 raiserror 18542, @replicate_name
736
737 rollback transaction
738
739 return (1)
740 end
741
742 /*
743 ** Write the log record to replicate this invocation
744 ** of the stored procedure.
745 */
746 if (logexec() != 1)
747 begin
748 /*
749 ** 17756, "The execution of the stored procedure
750 ** '%1!' in database '%2!' was aborted
751 ** because there was an error in writing
752 ** the replication log record."
753 */
754 select @dbname = db_name()
755 raiserror 17756, "sp_setrepdefmode", @dbname
756
757 rollback transaction rs_logexec
758 return (1)
759 end
760
761 commit transaction
762 /*
763 ** 18543 ""The replication definition mode for '%1!' is set to '%2!'."
764 */
765 exec sp_getmessage 18543, @msg output
766 if (@setmode in ("never", @never)) or (@set = 0)
767 print @msg, @replicate_name, @setmode
768 else
769 print @msg, @replicate_name, @setflag
770 return (0)
771 end
772 else
773 /* We are changing the sql replication log threshold */
774 if (@threshold_syntax = 1)
775 begin
776 if (@setmode is null)
777 begin
778 /* Get the current replication threshold */
779 select @current_threshold = getrepthreshold(@objid)
780
781 if (@current_threshold < 0)
782 begin
783 /*
784 ** "The built-in function getrepthreshold() failed.
785 ** Please see any other messages printed along with
786 ** this message."
787 */
788 raiserror 18409, "getrepthreshold"
789 return (1)
790 end
791
792 /*
793 ** 19932, "The replication threshold for table '%1!'
794 ** is '%2!'."
795 */
796 exec sp_getmessage 19932, @msg output
797 select @outvalue = convert(varchar(10), @current_threshold)
798 print @msg, @replicate_name, @outvalue
799 return (0)
800 end
801
802 select @threshold_value = convert(int, @setmode)
803
804 if ((@threshold_value < 0) or (@threshold_value > @threshold_max))
805 begin
806 /*
807 ** 19934, "The replication threshold provided for
808 ** table '%1!' is '%2!'. Please choose a
809 ** threshold larger than 0 and smaller than '%3!'."
810 */
811 select @outvalue = convert(varchar(10), @threshold_value)
812 select @outvalue1 = convert(varchar(10), @threshold_max)
813 raiserror 19934, @replicate_name, @outvalue, @outvalue1
814 end
815
816 /* Now the threshold value exists and is legal */
817
818 /*
819 ** IMPORTANT: The name "rs_logexec is significant and is used by
820 ** Replication Server
821 */
822 begin transaction rs_logexec
823
824 if (setrepthreshold(@objid, @threshold_value) != 1)
825 begin
826 /*
827 ** 18409, "The built-in funtion '%1!' failed. Please see
828 ** any other messages printed along with this
829 ** message."
830 */
831 raiserror 18409, "setrepthreshold"
832 return (1)
833
834 return (1)
835 end
836
837 /*
838 ** Write the log record to replicate this invocation
839 ** of the stored procedure.
840 */
841 if (logexec() != 1)
842 begin
843 /*
844 ** 17756, "The execution of the stored procedure
845 ** '%1!' in database '%2!' was aborted
846 ** because there was an error in writing
847 ** the replication log record."
848 */
849 select @dbname = db_name()
850 raiserror 17756, "sp_setrepdefmode", @dbname
851
852 rollback transaction rs_logexec
853 return (1)
854 end
855
856 commit transaction
857
858 /* We requested to clear the threshold */
859 if (@threshold_value = 0)
860 begin
861 /*
862 ** 19948, "The replication threshold for '%1!' has been cleared"
863 */
864 exec sp_getmessage 19948, @msg output
865 print @msg, @replicate_name
866 return (0)
867 end
868
869 /*
870 ** 19932, 'The replication threshold for '%1!' is '%2!'."
871 */
872 exec sp_getmessage 19932, @msg output
873 select @outvalue = convert(varchar(10), @threshold_value)
874 print @msg, @replicate_name, @outvalue
875 return (0)
876 end
877
exec sp_procxmode 'sp_setrepdefmode', 'AnyMode'
go
Grant Execute on sp_setrepdefmode to public
go