DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setrepproc  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "@(#) generic/sproc/setreplicate 155.1 11/10/94" */
3     /*
4     ** Messages for "sp_setreplicate"	17960
5     **
6     ** 17230, "Only the System Administrator (SA) or the Database Owner
7     **	   (dbo) may execute this stored procedure."
8     ** 17432, "false"
9     ** 17756, "The execution of the stored procedure '%1!' in database
10    **         '%2!' was aborted because there was an error in writing the
11    **         replication log record."
12    ** 17962, "The replication status for '%1!' is already set to %2!.
13    **	   Replication status for '%3!' does not change."
14    ** 17963, "A object with the same name, but owned by a different user
15    **	   is already being replicated.  The object '%1!' cannot be
16    **	   replicated."
17    ** 17965, "The replication status for '%1!' is currently %2!."
18    ** 17966, "Due to system failure, the replication status for '%1!' has
19    **	   not been changed."
20    ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) or
21    **	   a user with REPLICATION authorization may execute this stored
22    **	   procedure."
23    ** 17968, "The built-in function logschema() failed for '%1!'."
24    ** 18104, "table"
25    ** 18105, "function"
26    ** 18106, "Stored Procedure must be in the current database."
27    ** 18107, "Stored Procedure '%1!' does not exist in this database."
28    ** 18108, "Usage: sp_setrepproc [proc_name [, {'false' | 'table' | 
29    			{'function' [,{'log_current' | 'log_sproc'}]}}]]
30    ** 18688, "Stored procedures with object parameters cannot be marked for
31    **	   replication yet at this time. Instead, use sp_setreptable to
32    **	   mark the invidual tables for replication. The replication status
33    **	   for '%1!' is not changed."
34    ** 18846, "Object name cannot be null when other parameters are non null.
35    ** 18847, "The log mode for '%1!' is currently %2!."
36    ** 18848, "The replication status for '%1!' is already set to %2!,
37    **	   with log mode set to %3!. Replication status for '%4!'
38    **	   does not change.
39    ** 18849, "The log mode is set to %1!."
40    */
41    
42    create or replace procedure sp_setrepproc
43        @replicate_name varchar(767) = NULL, /* obj we want to mark as replicate */
44        @setflag varchar(20) = NULL, /* set or unset the replicate status. */
45        @logflag varchar(20) = NULL /* log EXECBEGIN in the current
46    					** database or in the sproc's database.
47    					*/
48    as
49    
50        declare @sysstat1_curr int /* current sysobjects.sysstat.	*/
51        declare @sysstat1_new int /* new sysobjects.sysstat.	*/
52        declare @sysstat1_mask int /* mask for sysobjects.sysstat.	*/
53        declare @sysstat2_curr int /* current sysobjects.sysstat2.	*/
54        declare @sysstat2_new int /* new sysobjects.sysstat2.	*/
55        declare @sysstat2_mask int /* mask for sysobjects.sysstat2.*/
56        declare @rep_constant int /* O_REPLICATED (0x8000) bit in
57        ** bit in sysobjects.sysstat.	*/
58        declare @sub_constant int /* O_PROC_SUBSCRIBABLE (0x40)
59        ** bit in sysobjects.sysstat.	*/
60        declare @log_constant int /* O2_REP_LOG_SPROC (0x800000)
61        ** bit in sysobjects.sysstat2.	*/
62        declare @setrep_flags int /* repflags parameter passed to
63        ** setrepstatus().		*/
64        declare @db varchar(255) /* db of object. */
65        declare @owner varchar(255) /* owner of object. */
66        declare @object varchar(255) /* object's name. */
67        declare @true varchar(30) /* "TRUE" */
68        declare @table varchar(30) /* "TABLE" */
69        declare @function varchar(30) /* "FUNCTION" */
70        declare @false varchar(30) /* "FALSE" */
71        declare @log_current varchar(30) /* "LOG_CURRENT" */
72        declare @log_sproc varchar(30) /* "LOG_SPROC" */
73        declare @msg varchar(1024) /* Used to get Error messages */
74        declare @tmpstr varchar(20) /* Used to display replication type
75        ** (table/function) in messages.
76        */
77        declare @logstr varchar(20) /* Used to display logmode in messages.
78        */
79        declare @procval int
80        declare @objid int /* Id of the procedure to be marked */
81        declare @log_aft_img int /* log after image schema when replication
82        ** status is turned on, or the replication
83        ** status is already on, but the sproc
84        ** replication type (function/table), or
85        ** logging mode (sproc/current) changes.
86        */
87        declare @log_bef_img int /* log before image schema when replication
88        ** status is turned off.
89        */
90        declare @user_tran int /* are we inside a user tran? */
91        declare @after_image int /* log the after image of the schema */
92        declare @mod_versionts int /* modify version timestamp after logging
93        ** the schema
94        */
95        declare @dbname varchar(255)
96        declare @xtype_token int /* Type for object parameters. */
97        declare @textlocator_token int /* Type for LOB TEXT locator or
98        ** parameter 
99        */
100       declare @imagelocator_token int /* Type for LOB IMAGE locator or
101       ** parameter 
102       */
103       declare @unitextlocator_token int /* Type for LOB UNITEXT locator or
104       ** parameter 
105       */
106       declare @setrep_replicate int /* setrepstatus() LT_SETREP_REPLICATE
107       ** flag.   */
108       declare @setrep_subscribable int /* setrepstatus() LT_SETREP_SUBSCRIBABLE
109       ** flag.*/
110       declare @setrep_log_sproc int /* setrepstatus() LT_SETREP_LOG_SPROC
111       ** flag. */
112       declare @dummy int
113       declare @nullarg char(1)
114       declare @gp_enabled int
115       declare @status1 int
116       declare @status2 int
117       declare @status3 int
118   
119       select @status1 = 1
120       select @status2 = 1
121       select @status3 = 1
122   
123       if @@trancount = 0
124       begin
125           set transaction isolation level 1
126           set chained off
127       end
128   
129       if (@@trancount > 0)
130           select @user_tran = 1
131       else
132           select @user_tran = 0
133   
134       /*
135       ** Define sysobject sysstat and sysstat2 constants.
136       **
137       ** O_REPLICATED bit flag in sysstat is 0x8000 (-32768)
138       ** O_PROC_SUBSCRIBABLE bit flag in sysstat flag is 0x0040 (64)
139       ** O2_REP_LOG_SPROC bit flag in sysstat2 is 0x800000 (8388608).
140       ** See objects.h for bit flag defines.
141       */
142       select @rep_constant = - 32768,
143           @sub_constant = 64,
144           @log_constant = 8388608
145   
146   
147       /* set @log_aft_img and @log_bef_img to false initially */
148       select @log_aft_img = 0,
149           @log_bef_img = 0
150   
151       /* Datatype value for objects. */
152       select @xtype_token = 36
153   
154       /* 
155       ** Column type value for LOB, stands for either of these datatypes:
156       ** text = 35, image = 34, unitext = 174.  
157       */
158       select @textlocator_token = 169
159       select @imagelocator_token = 170
160       select @unitextlocator_token = 171
161   
162       /*
163       ** Initialize ssetrepstatus() repflags constants.
164       ** See logtrans.h for bit flag defines.
165       */
166       select @setrep_replicate = 1, /* LT_SETREP_REPLICATE	  0x00000001 */
167           @setrep_subscribable = 2, /* LT_SETREP_SUBSCRIBABLE 0x00000002 */
168           @setrep_log_sproc = 4 /* LT_SETREP_LOG_SPROC    0x00000004 */
169   
170       /*
171       ** Initialize 'true' and 'false' strings
172       */
173       /* 18104, "table" */
174       exec sp_getmessage 18104, @table out
175       /* 18105, "function" */
176       exec sp_getmessage 18105, @function out
177       /* 17432, "false" */
178       exec sp_getmessage 17432, @false out
179       /* 18844, "log_current" */
180       exec sp_getmessage 18844, @log_current out
181       /* 18845, "log_sproc" */
182       exec sp_getmessage 18845, @log_sproc out
183   
184       /*
185       ** If we are invoked with no parameters, then just print out all procedures
186       ** which are marked for replication.  The 'select' statement is heavily
187       ** based upon the one found in 'sp_help'.
188       */
189       if (@replicate_name is NULL and @setflag is NULL and @logflag is NULL)
190       begin
191           select
192               Name = o.name,
193               Type = convert(char(14), m1.description),
194               Mode = convert(char(14), m2.description)
195           into #setrepproc1rs
196           from
197               sysobjects o,
198               master.dbo.sysmessages m1,
199               master.dbo.sysmessages m2
200           where
201               o.type = "P"
202               and isnull(m1.langid, 0) = @@langid
203               and isnull(m2.langid, 0) = @@langid
204               and (o.sysstat & @rep_constant) = @rep_constant
205               and (((o.sysstat & @sub_constant) = @sub_constant
206                       and m1.error = 18105) /* function */
207                   or ((o.sysstat & @sub_constant) = 0
208                       and m1.error = 18104)) /* table */
209               and (((o.sysstat2 & @log_constant) = @log_constant
210                       and m2.error = 18845) /* log_sproc */
211                   or ((o.sysstat2 & @log_constant) = 0
212                       and m2.error = 18844)) /* log_current */
213           exec sp_autoformat @fulltabname = #setrepproc1rs,
214               @selectlist = "Name, Type,'Log Mode'= Mode"
215           drop table #setrepproc1rs
216           return (0)
217       end
218   
219       /*
220       ** Don't allow replicate name to be null, with non-null setflag and/or logflag.
221       */
222       if (@replicate_name is NULL) and (@setflag is not NULL or @logflag is not NULL)
223       begin
224           /*
225           ** 18846, "Object name cannot be null when other parameters
226           **	   are non null."
227           */
228           raiserror 18846
229           return (1)
230       end
231   
232       /*
233       ** Crack the name into its corresponding pieces.
234       */
235       execute sp_namecrack @replicate_name,
236           @db = @db output,
237           @owner = @owner output,
238           @object = @object output
239   
240       /*
241       ** Make sure that the object is in the current database.
242       */
243       if (@db is not NULL and @db != db_name())
244       begin
245           /*
246           ** 18106, "Procedure must be in the current database."
247           */
248           raiserror 18106
249           return (1)
250       end
251   
252       /*
253       **  Make sure that the object actually exists.
254       */
255       select @objid = object_id(@replicate_name)
256   
257       if (@objid is NULL)
258           or (not exists (select name from sysobjects where
259                       id = @objid and
260                       type = "P"
261                   ))
262       begin
263           /*
264           ** 18107, "Procedure does not exist in this database."
265           */
266           raiserror 18107, @replicate_name
267           return (1)
268       end
269   
270       /*
271       ** If the @setflag and @logflag are NULL, then we are only interested in the
272       ** current replication status of the specified object.
273       */
274       if (@setflag is NULL and @logflag is NULL)
275       begin
276           /*
277           ** Get the sproc's current sysstat and sysstat2 from sysobjects
278           ** Hold a read lock on sysobjects so that the statuses cannot be
279           ** changed until we're done.
280           */
281           select
282               @sysstat1_curr = sysstat,
283               @sysstat2_curr = sysstat2
284           from
285               sysobjects holdlock
286           where
287               id = @objid
288   
289           /*
290           ** If sproc is replicated, get replication type and log mode.
291           */
292           if (@sysstat1_curr & @rep_constant) = @rep_constant
293           begin
294               /* Is this a subscribable function?. */
295               if (@sysstat1_curr & @sub_constant) = @sub_constant
296               begin
297                   select @tmpstr = @function
298               end
299               else
300               begin
301                   select @tmpstr = @table
302               end
303   
304               /* Is log mode log_sproc?. */
305               if (@sysstat2_curr & @log_constant) = @log_constant
306               begin
307                   select @logstr = @log_sproc
308               end
309               else
310               begin
311                   select @logstr = @log_current
312               end
313           end
314   
315           /* Otherwise, sproc is not replicated. */
316           else
317           begin
318               select @tmpstr = @false
319           end
320   
321   
322           /*
323           ** 17965 "The replication status for '%1!' is currently %2!."
324           */
325           exec sp_getmessage 17965, @msg output
326           print @msg, @replicate_name, @tmpstr
327   
328           /* If the sproc is replicated, display the log mode. */
329           if (@tmpstr != @false)
330           begin
331               /*
332               ** 18847 "The log mode for '%1!' is currently %2!"
333               */
334               exec sp_getmessage 18847, @msg output
335               print @msg, @replicate_name, @logstr
336           end
337           return (0)
338       end
339   
340       /*
341       ** If granular permissions is not enabled, 
342       ** you must be SA, dbo or have REPLICATION role to execute this
343       ** sproc.
344       **  If granular permissions is enabled then users with 
345       ** 'manage replication' permission can execute it.
346       */
347       select @dbname = db_name()
348       select @nullarg = NULL
349       execute @status1 = sp_aux_checkroleperm "dbo", "manage replication",
350           @dbname, @gp_enabled output
351   
352       if (@gp_enabled = 0)
353       begin
354           if (@status1 != 0)
355           begin
356               execute @status2 = sp_aux_checkroleperm "sa_role",
357                   @nullarg, @nullarg, @gp_enabled output
358   
359               if (@status2 != 0)
360                   execute @status3 = sp_aux_checkroleperm
361                       "replication_role", @nullarg, @nullarg,
362                       @gp_enabled output
363   
364               if (@status3 != 0)
365               begin
366                   /*
367                   ** 18418, "Only the System Administrator (SA), the
368                   **         Database Owner (dbo) or a user with
369                   **         REPLICATION authorization may execute this
370                   **         stored procedure."
371                   */
372                   raiserror 18418
373                   return (1)
374               end
375           end
376           if (@status2 = 0)
377               select @dummy = proc_role("sa_role")
378           if (@status3 = 0)
379               select @dummy = proc_role("replication_role")
380       end
381       else
382       begin
383           select @dummy = proc_auditperm("manage replication", @status1, @dbname)
384           if (@status1 != 0)
385               return 1
386       end
387   
388       /*
389       ** At this point, we know that the sproc is not being invoked for
390       ** informational purposes. 
391       */
392   
393       /*
394       ** Disallow any log mode when the replication status is not "function".
395       ** The default value will be applied.
396       */
397       if (lower(@setflag) not in ("function", @function)) and (@logflag is not NULL)
398       begin
399           /*
400           ** 18108 "Usage: sp_setrepproc [proc_name [, {'false' | 'table' |
401           {'function' [,{'log_current' | 'log_sproc'}]}}]]
402           */
403           raiserror 18108
404           return (1)
405       end
406   
407       /*
408       ** Replace any null parameters by the default value for that parameter.
409       */
410   
411       /* If @setflag is null, default is function. */
412       if (@setflag is NULL)
413       begin
414           select @setflag = @function
415       end
416   
417       /* If @logflag is null, default is log_sproc. */
418       if (@logflag is NULL)
419       begin
420           select @logflag = @log_sproc
421       end
422   
423       /*
424       ** Check for a valid setname parameter
425       */
426       if (lower(@setflag) not in ("function", "table", "false",
427                   @function, @table, @false))
428       begin
429           /*
430           ** 18108 "Usage: sp_setrepproc [proc_name [, {'false' | 'table' |
431           {'function' [,{'log_current' | 'log_sproc'}]}}]]
432           */
433           raiserror 18108
434           return (1)
435       end
436   
437       /*
438       ** Check for a valid logflag parameter
439       */
440       if (lower(@logflag) not in ("log_sproc", "log_current",
441                   @log_sproc, @log_current))
442       begin
443           /*
444           ** 18108 "Usage: sp_setrepproc [proc_name [, {'false' | 'table' |
445           {'function' [,{'log_current' | 'log_sproc'}]}}]]
446           */
447           raiserror 18108
448           return (1)
449       end
450   
451       /*
452       ** Get the object's current status. Hold a read lock on sysobjects so that 
453       **	the statuses cannot be changed until we're done.
454       */
455       select
456           @sysstat1_curr = sysstat,
457           @sysstat2_curr = sysstat2
458       from
459           sysobjects holdlock
460       where
461           id = @objid
462   
463       /*
464       ** Perform the requested operation on the object.
465       */
466       if lower(@setflag) in ("false", @false)
467       begin
468           /* Is the replicate status bit even set? */
469           if (@sysstat1_curr & @rep_constant) = 0
470           begin
471               /*
472               ** 17962 "The replication status for '%1!' is already
473               **	  set to %2!.  Replication status for '%3!'
474               **	  does not change."
475               */
476               raiserror 17962, @replicate_name, @setflag, @replicate_name
477               return (1)
478           end
479   
480           /*
481           ** Initialize new values for sysstat, sysstat2 with all replication
482           ** related bit cleared. Initialize @setrep_flags to 0 to have
483           ** setrepstatus() clear all replication related bits.
484           ** Since the replication status is being turned off, we need to
485           ** set @log_bef_img to log the before image.
486           */
487           select @sysstat1_new = @sysstat1_curr &
488               ~ (@rep_constant | @sub_constant),
489               @sysstat2_new = @sysstat2_curr & ~ @log_constant,
490               @setrep_flags = 0,
491               @log_bef_img = 1
492       end
493   
494       /*
495       ** If @setflag is table or function, check the following..
496       **	- not already marked for replication
497       **	- another object with same name but different owner is
498       **	  already marked  for replication
499       **	- has no parameters of type object.
500       */
501       else if lower(@setflag) in ("table", @table, "function", @function)
502       begin
503           /* The @sysstat1 mask has all replication related bits set. */
504           select @sysstat1_mask = (@rep_constant | @sub_constant)
505   
506           /* Calculate the new the sysstat value based on @setflag. */
507           if lower(@setflag) in ("table", @table)
508           begin
509               /* New sysstat will have the replicate bit set. */
510               select @sysstat1_new = @rep_constant
511           end
512           else
513           begin
514               /*
515               ** New sysstat will have the replicate and the subscribable
516               ** bits set.
517               */
518               select @sysstat1_new = @rep_constant | @sub_constant
519           end
520   
521           /* The @sysstat2 mask has all replication related bits set. */
522           select @sysstat2_mask = @log_constant
523   
524           /* Calculate the new sysstat2 value. */
525           if lower(@logflag) in ("log_sproc", @log_sproc)
526           begin
527               /* New sysstat2 value will have the log_sproc bit set. */
528               select @sysstat2_new = @log_constant
529           end
530           else
531           begin
532               /* New sysstat2 value will have the log_sproc bit cleared. */
533               select @sysstat2_new = 0
534           end
535   
536           /*
537           ** If sysstat and sysstat2 are not going to change, raise 18848
538           */
539           if ((@sysstat1_curr & @sysstat1_mask) = @sysstat1_new)
540               and ((@sysstat2_curr & @sysstat2_mask) = @sysstat2_new)
541           begin
542               /*
543               ** 18848 "The replication status for '%1!' is already
544               **	  set to %2!, with log mode set to %3!. Replication
545               **	  status for '%4!' does not change."
546               */
547               raiserror 18848, @replicate_name, @setflag, @logflag,
548                   @replicate_name
549               return (1)
550           end
551   
552           /*
553           ** Make sure that no like object with the same name, but a
554           ** different owner, exists.  We need to do this because
555           ** the SQL Server does not send owner information along
556           ** with the object to the Replication Server.  This
557           ** restriction may be lifted in future versions.
558           */
559           if exists (select * from sysobjects
560                   where name = @object
561                       and id != @objid
562                       and (
563                           (type = "U ") /* user table */
564                           or
565                           (type = "P ") /* stored procedure */
566                       )
567                       and sysstat & @rep_constant != 0)
568           begin
569               /*
570               ** 17963 "An object with the same name, but owned by a
571               **	  different user is already being replicated.
572               **	  The object '%1!' cannot be replicated."
573               */
574               raiserror 17963, @replicate_name
575               return (1)
576           end
577   
578           /* Disallow sprocs with LOB or object parameters. */
579           if exists (select * from syscolumns
580                   where id = @objid
581                       and (
582                           type = @xtype_token
583                           or
584                           type = @textlocator_token
585                           or
586                           type = @imagelocator_token
587                           or
588                           type = @unitextlocator_token
589                       ))
590   
591           begin
592               /*
593               ** 18688, "Stored procedures with LOB or object parameters
594               **	   cannot be marked for replication yet at this time.
595               **	   Instead, use sp_setreptable to mark the invidual
596               **	   tables for replication. The replication status for
597               **	   '%1!' is not changed."
598               */
599               raiserror 18688, @replicate_name
600               return (1)
601           end
602   
603           /*
604           ** Calculate @sysstat1_new and @sysstat2_new values.
605           ** The new value is calculated by first clearing all replication
606           ** related bits, and then setting the desired bits.
607           */
608           select @sysstat1_new = (@sysstat1_curr & ~ @sysstat1_mask)
609               | @sysstat1_new
610           select @sysstat2_new = (@sysstat2_curr & ~ @sysstat2_mask)
611               | @sysstat2_new
612   
613           /*
614           ** Calcuate @setrep_flags based on the new sysstat1 and sysstat2 values.
615           ** This variable is the repflags parameter to be passed to
616           ** setrepstatus(), which will update the replication status of
617           ** an object in cache. Whatever bits that are cleared or set in
618           ** the object's sysobjects row, must also be cleared or set in the
619           ** cached version.
620           **
621           ** First initialize @setrep_flags to 0.
622           */
623           select @setrep_flags = 0
624   
625           /* Is the replication bit set?. */
626           if ((@sysstat1_new & @rep_constant) != 0)
627           begin
628               select @setrep_flags = @setrep_flags | @setrep_replicate
629           end
630   
631           /* Is the subscribable bit set?. */
632           if ((@sysstat1_new & @sub_constant) != 0)
633           begin
634               select @setrep_flags = @setrep_flags | @setrep_subscribable
635           end
636   
637           /* Is the log_sproc bit set?. */
638           if ((@sysstat2_new & @log_constant) != 0)
639           begin
640               select @setrep_flags = @setrep_flags | @setrep_log_sproc
641           end
642   
643           /*
644           ** The after image needs to be logged because replication is being
645           ** turned from off to on, or the sproc's replication type or
646           ** logging mode is being changed. Any of these changes require the
647           ** after image to be logged.
648           */
649           select @log_aft_img = 1
650       end
651   
652       /*
653       ** Update the object's sysstat and sysstat2 columns
654       **
655       ** IMPORTANT: This transaction name is significant and is used by
656       **            Replication Server
657       */
658       begin transaction rs_logexec
659   
660       select @objid = @objid
661   
662       /* log the schema first if we are turning off replication */
663       if (@log_bef_img = 1)
664       begin
665           /*
666           ** Initialize flags passed to logschema().
667           **   @after_image is 0 because this is not an after image.
668           **   @mod_versionts is 1 to modify the object's log version
669           **		timestamp.
670           **
671           ** logschema() will log the objects schema, before image in
672           ** this case.
673           */
674           select @after_image = 0,
675               @mod_versionts = 1
676           if (logschema(@objid, @user_tran, @after_image, @mod_versionts)
677                   != 1)
678           begin
679               /*
680               ** 17968 "The built-in function logschema() failed 
681               ** for '%1!'."
682               */
683               exec sp_getmessage 17968, @msg output
684               print @msg, @replicate_name
685   
686               rollback transaction rs_logexec
687               return (1)
688           end
689       end
690   
691       /* Update the sysobjects row. */
692       update sysobjects
693       set sysstat = @sysstat1_new,
694           sysstat2 = @sysstat2_new
695       where id = @objid
696   
697       /*
698       ** Log the after image only if we are in a user initiated transaction.
699       ** After images are needed to rebuild the object's schema in the case
700       ** or rollbacks and ddl-in-tran is on.
701       */
702       if ((@log_aft_img = 1) and (@user_tran = 1))
703       begin
704           /*
705           ** Initialize flags passed to logschema().
706           **    @after_image is 1 because this is an after image.
707           **    @mod_versionts is 0 because we don't want to change
708           **	the object's log version timestamp.
709           */
710           select @after_image = 1,
711               @mod_versionts = 0
712           if (logschema(@objid, @user_tran, @after_image, @mod_versionts)
713                   != 1)
714           begin
715               /*
716               ** 17968 "The built-in function logschema() failed 
717               ** for '%1!'."
718               */
719               exec sp_getmessage 17968, @msg output
720               print @msg, @replicate_name
721   
722               rollback transaction rs_logexec
723               return (1)
724           end
725       end
726   
727       /*
728       ** Update the object's status in cache.
729       */
730       if (setrepstatus(@objid, @setrep_flags) != 1)
731       begin
732           /*
733           ** 17966 "Due to system failure, the replication status
734           **	  for '%1!' has not been changed."
735           */
736           raiserror 17966, @replicate_name
737   
738           rollback transaction rs_logexec
739   
740           return (1)
741       end
742   
743       /*
744       ** Write the log record to replicate this invocation 
745       ** of the stored procedure.
746       */
747       if (logexec() != 1)
748       begin
749           /*
750           ** 17756, "The execution of the stored procedure '%1!'
751           ** 	   in database '%2!' was aborted because there
752           ** 	   was an error in writing the replication log
753           **	   record."
754           */
755           select @dbname = db_name()
756           raiserror 17756, "sp_setrepproc", @dbname
757   
758           rollback transaction rs_logexec
759           return (1)
760       end
761   
762       commit transaction
763   
764       /*
765       ** 17964 "The replication status for '%1!' is set to %2!."
766       */
767       exec sp_getmessage 17964, @msg output
768       print @msg, @replicate_name, @setflag
769   
770       /*
771       ** If status is not being turned off, also display log_mode.
772       ** Detect the status not being turned off by @setrep_flags not being 0.
773       */
774       if (@setrep_flags != 0)
775       begin
776           /*
777           ** 18849 "The log mode is set to %1!."
778           */
779           exec sp_getmessage 18849, @msg output
780           print @msg, @logflag
781       end
782   
783       return (0)
784   


exec sp_procxmode 'sp_setrepproc', 'AnyMode'
go

Grant Execute on sp_setrepproc to public
go
DEFECTS
 QCAR 6 Cartesian product between tables sybsystemprocs..sysobjects o and [master..sysmessages m1, master..sysmessages m2] 196
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @tmpstr: varchar(20) = varchar(30) 297
 MTYP 4 Assignment type mismatch @tmpstr: varchar(20) = varchar(30) 301
 MTYP 4 Assignment type mismatch @logstr: varchar(20) = varchar(30) 307
 MTYP 4 Assignment type mismatch @logstr: varchar(20) = varchar(30) 311
 MTYP 4 Assignment type mismatch @tmpstr: varchar(20) = varchar(30) 318
 MTYP 4 Assignment type mismatch @setflag: varchar(20) = varchar(30) 414
 MTYP 4 Assignment type mismatch @logflag: varchar(20) = varchar(30) 420
 MTYP 4 Assignment type mismatch sysstat: smallint = int 693
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 213
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 582
 QTYP 4 Comparison type mismatch tinyint = int 582
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 584
 QTYP 4 Comparison type mismatch tinyint = int 584
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 586
 QTYP 4 Comparison type mismatch tinyint = int 586
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 588
 QTYP 4 Comparison type mismatch tinyint = int 588
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 259
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 287
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 461
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 561
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 580
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 695
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_setrepproc  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 174
 MNER 3 No Error Check should check return value of exec 176
 MNER 3 No Error Check should check return value of exec 178
 MNER 3 No Error Check should check return value of exec 180
 MNER 3 No Error Check should check return value of exec 182
 MNER 3 No Error Check should check @@error after select into 191
 MNER 3 No Error Check should check return value of exec 213
 MNER 3 No Error Check should check return value of exec 235
 MNER 3 No Error Check should check return value of exec 325
 MNER 3 No Error Check should check return value of exec 334
 MNER 3 No Error Check should check return value of exec 683
 MNER 3 No Error Check should check @@error after update 692
 MNER 3 No Error Check should check return value of exec 719
 MNER 3 No Error Check should check return value of exec 767
 MNER 3 No Error Check should check return value of exec 779
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 329
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 352
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 373
 MUCO 3 Useless Code Useless Brackets 376
 MUCO 3 Useless Code Useless Brackets 378
 MUCO 3 Useless Code Useless Brackets 384
 MUCO 3 Useless Code Useless Brackets 404
 MUCO 3 Useless Code Useless Brackets 412
 MUCO 3 Useless Code Useless Brackets 418
 MUCO 3 Useless Code Useless Brackets 426
 MUCO 3 Useless Code Useless Brackets 434
 MUCO 3 Useless Code Useless Brackets 440
 MUCO 3 Useless Code Useless Brackets 448
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 549
 MUCO 3 Useless Code Useless Brackets 575
 MUCO 3 Useless Code Useless Brackets 600
 MUCO 3 Useless Code Useless Brackets 626
 MUCO 3 Useless Code Useless Brackets 632
 MUCO 3 Useless Code Useless Brackets 638
 MUCO 3 Useless Code Useless Brackets 663
 MUCO 3 Useless Code Useless Brackets 676
 MUCO 3 Useless Code Useless Brackets 687
 MUCO 3 Useless Code Useless Brackets 702
 MUCO 3 Useless Code Useless Brackets 712
 MUCO 3 Useless Code Useless Brackets 723
 MUCO 3 Useless Code Useless Brackets 730
 MUCO 3 Useless Code Useless Brackets 740
 MUCO 3 Useless Code Useless Brackets 747
 MUCO 3 Useless Code Useless Brackets 759
 MUCO 3 Useless Code Useless Brackets 774
 MUCO 3 Useless Code Useless Brackets 783
 QCTC 3 Conditional Table Creation 191
 QISO 3 Set isolation level 125
 QNAJ 3 Not using ANSI Inner Join 196
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
202
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
203
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
580
 VNRD 3 Variable is not read @owner 237
 VNRD 3 Variable is not read @gp_enabled 362
 VNRD 3 Variable is not read @dummy 383
 VUNU 3 Variable is not used @true 67
 VUNU 3 Variable is not used @procval 79
 MSUB 2 Subquery Marker 258
 MSUB 2 Subquery Marker 559
 MSUB 2 Subquery Marker 579
 MTR1 2 Metrics: Comments Ratio Comments: 55% 42
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 55 = 72dec - 19exi + 2 42
 MTR3 2 Metrics: Query Complexity Complexity: 283 42

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_namecrack  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
reads table master..sysmessages (1)  
reads table sybsystemprocs..syscolumns  
writes table tempdb..#setrepproc1rs (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005