Database	Proc	Application	Created	Links
sybsystemprocs	sp_setreplicate		31 Aug 14	Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Wed Feb  8 14:55:58 2006 
4     */
5     /*
6     ** raiserror Messages for setreplicate [Total 11]
7     **
8     ** 17460, "Object must be in the current database."
9     ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
10    ** 17961, "Usage: sp_setreplicate object_name, {true | false}"
11    ** 17962, "The replication status for '%1!' is already set to %2!. Replication status for '%3!' does not change."
12    ** 17963, "An object with the same name, but owned by a different user is already being replicated.  The object '%1!' cannot be replicated."
13    ** 17966, "Due to system failure, the replication status for '%1!' has not been changed."
14    ** 17969, "No user table or procedure named '%1!' exists in the current database."
15    ** 18409, "The built-in function '%1!' failed. Please see the other messages printed along with this message."
16    ** 18410, "The replication status of '%1!' is corrupt. Please contact Sybase Technical Support."
17    ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) or a user with REPLICATION authorization may execute this stored procedure."
18    ** 18688, "Stored procedures with object parameters cannot be marked for replication yet at this time. Instead, use sp_setreptable to mark the invidual tables for replication. The replication status for '%1!' is not changed."
19    */
20    /*
21    ** sp_getmessage Messages for setreplicate [Total 5]
22    **
23    ** 17431, "true"
24    ** 17432, "false"
25    ** 17964, "The replication status for '%1!' is set to %2!."
26    ** 17965, "The replication status for '%1!' is currently %2!."
27    ** 17968, "The built-in function logschema() failed for '%1!'. See the other messages printed along with this message for more information."
28    */
29    /*
30    ** End spgenmsgs.pl output.
31    */
32    
33    create procedure sp_setreplicate
34        @replicate_name varchar(767) = NULL, /* obj we want to mark as replicate */
35        @setflag varchar(5) = NULL /* set or unset the replicate status. */
36    as
37    
38        declare @current_status int /* current sysstat value for the object. */
39        declare @new_status int /* new sysstat value for the object. */
40        declare @new_status2 int /* new sysstat2 value for the object. */
41        declare @rep_constant smallint /* bit which indicates a replicated object. */
42        declare @dbccbit int /* The "on"/"off" bit for the DBCC command */
43        declare @db varchar(255) /* db of object. */
44        declare @owner varchar(255) /* owner of object. */
45        declare @object varchar(255) /* object's name. */
46        declare @true varchar(30)
47        declare @false varchar(30)
48        declare @msg varchar(1024)
49        declare @tmpstr varchar(10)
50        declare @sptlang int
51        declare @procval int
52        declare @rep_on_schema int /* log schema when turning replication on? */
53        declare @rep_off_schema int /* log schema when turning replication off? */
54        declare @objid int
55        declare @user_tran int /* are we inside a user tran? */
56        declare @after_image int /* log the after image of the schema */
57        declare @mod_versionts int /* modify version timestamp after logging
58        ** the schema
59        */
60        declare @dbname varchar(255)
61        declare @tmpid int /* Temporary variable for check to make
62        ** sure that the object is a user table
63        ** or a procedure	
64        */
65        declare @curstat int
66        declare @reptostandbyon int /* 1:  there are standby server */
67        declare @db_rep_level_all int /* All level replication */
68        declare @db_rep_level_none int /* no replication */
69        declare @db_rep_level_l1 int /* L1 level replication */
70        declare @xtype_token int /* Type for object parameters. */
71        declare @textlocator_token int /* Type for LOB TEXT locator or 
72        ** parameter 
73        */
74        declare @imagelocator_token int /* Type for LOB IMAGE locator or 
75        ** parameter 
76        */
77        declare @unitextlocator_token int /* Type for LOB UNITEXT locator
78        ** or parameter
79        */
80        declare @owner_bit int
81        declare @index_bit int
82        declare @colrepalwys smallint /* Always replicate */
83        declare @colrepifch smallint /* Replicate if changed */
84        declare @tipsa_in_index smallint /* Use index on text columns */
85        declare @texttype smallint /* Text datatype */
86        declare @imagetype smallint /* Image datatype */
87        declare @unitexttype smallint /* UNITEXT type. */
88        declare @xtype_type smallint /* Java column */
89        declare @offrow smallint
90        declare @lt_rep_get_failed int
91        declare @lt_rep_all int
92        declare @lt_rep_l1 int
93    
94    
95        if @@trancount = 0
96        begin
97            set chained off
98        end
99    
100       if (@@trancount > 0)
101           select @user_tran = 1
102       else
103           select @user_tran = 0
104   
105       set transaction isolation level 1
106   
107       /*
108       ** Replication enabled flag is 8000H (which is -32768D)
109       */
110       select @rep_constant = - 32768
111   
112       /* set @rep_on_schema and rep_off_schema to false initially */
113       select @rep_on_schema = 0,
114           @rep_off_schema = 0,
115           @db_rep_level_all = - 1, /* -1: code for all level replication */
116           @db_rep_level_l1 = 1, /* 1:  code for L1 level replication */
117           @index_bit = 8388608, /* 0x800000 in sysstat2 */
118           @owner_bit = 4096, /* 0x1000 in sysstat2 */
119           @colrepalwys = 1,
120           @colrepifch = 2,
121           @tipsa_in_index = 2048,
122           @imagetype = 34,
123           @texttype = 35,
124           @xtype_type = 36,
125           @unitexttype = 174,
126           @offrow = 1,
127           @lt_rep_get_failed = - 2, /* LT_GET_REP_FAILED */
128           @lt_rep_all = 2048, /* LT_REP_ALL */
129           @lt_rep_l1 = 4096 /* LT_REP_L1 */
130   
131   
132       /*
133       ** Datatype value for object parameters is 36.
134       */
135       select @xtype_token = 36
136   
137       /* 
138       ** Column type value for LOB, stands for either of these datatypes:
139       ** text = 35, image = 34, unitext = 174.  
140       */
141       select @textlocator_token = 169
142       select @imagelocator_token = 170
143       select @unitextlocator_token = 171
144   
145   
146       /*
147       ** Initialize 'true' and 'false' strings
148       */
149       /* 17431, "true" */
150       exec sp_getmessage 17431, @true out
151       /* 17432, "false" */
152       exec sp_getmessage 17432, @false out
153   
154       /*
155       ** Set 'sptlang' for proper printing of object information.  Used mainly
156       ** for the 'select' statement which is executed when we are invoked with
157       ** no parameters.  Copied from similar code in 'sp_help'
158       */
159       select @sptlang = @@langid
160       if @@langid != 0
161       begin
162           if not exists (
163                   select * from master.dbo.sysmessages where error
164                       between 17100 and 17109
165                       and langid = @@langid)
166               select @sptlang = 0
167       end
168   
169       /*
170       ** If we are invoked with no parameters, then just print out all objects
171       ** which are marked for replication.  The 'select' statement is heavily
172       ** based upon the one found in 'sp_help'.
173       */
174       if (@replicate_name is NULL and @setflag is NULL)
175       begin
176           select
177               Name = o.name,
178               Type = convert(char(16), m.description + x.name)
179           into #show_replicate_obj
180           from
181               sysobjects o,
182               master.dbo.spt_values v,
183               master.dbo.spt_values x,
184               master.dbo.sysmessages m
185           where
186               o.sysstat & 2055 = v.number
187               and v.type = "O"
188               and v.msgnum = m.error
189               and isnull(m.langid, 0) = @sptlang
190               and m.error between 17100 and 17109
191               and x.type = "R"
192               and o.userstat & - 32768 = x.number
193               and (o.sysstat & @rep_constant) = @rep_constant
194   
195           exec sp_autoformat #show_replicate_obj
196   
197           drop table #show_replicate_obj
198   
199           return (0)
200       end
201   
202       /*
203       ** Crack the name into its corresponding pieces.
204       */
205       execute sp_namecrack @replicate_name,
206           @db = @db output,
207           @owner = @owner output,
208           @object = @object output
209   
210       /*
211       ** Make sure that the object is in the current database.
212       */
213       if (@db is not NULL and @db != db_name())
214       begin
215           /*
216           ** 17460, "Object must be in the current database."
217           */
218           raiserror 17460
219           return (1)
220       end
221   
222       /*
223       **  Make sure that the object actually exists.
224       */
225       select @objid = object_id(@replicate_name)
226   
227       /* Check if the object is either a procedure or user table */
228       select @tmpid = id from sysobjects
229       where id = @objid and type in ("P", "U")
230   
231       /* Error if the object does not exist or if it is not a user table or
232       ** procedure. 
233       */
234       if (@objid is NULL) or (@objid != @tmpid)
235       begin
236           /*
237           ** 17969, "No user table or procedure named '%1!' exists in the
238           ** 	   current database."
239           */
240           raiserror 17969, @object
241           return (1)
242       end
243   
244       /*
245       ** Currently, marking for replication user tables in the 'master' database
246       ** is not allowed.
247       **
248       ** Therefore, if the current database is the 'master' database and the
249       ** object is a user table, reset the '@setflag' to NULL so that the system
250       ** procedure will report the current status of the user table.  A proper
251       ** error message should be implemented at a later date.
252       */
253       if ((db_name() = "master") and
254               exists (select * from sysobjects where name = @object and type = "U"))
255       begin
256           select @setflag = NULL
257       end
258   
259       /*
260       ** If the 'setflag' parameter is NULL, then we are only interested in the
261       ** current replication status of the specified object.
262       */
263       if (@setflag is NULL)
264       begin
265           select
266               @current_status = sysstat
267           from
268               sysobjects holdlock
269           where
270               id = @objid
271   
272           if (@current_status & @rep_constant) = @rep_constant
273           begin
274               select @tmpstr = @true
275           end
276           else
277           begin
278               select @tmpstr = @false
279           end
280   
281           /*
282           ** 17965 "The replication status for '%1!' is currently %2!."
283           */
284           exec sp_getmessage 17965, @msg output
285           print @msg, @replicate_name, @tmpstr
286   
287           return (0)
288       end
289   
290       /*
291       ** You must be SA, dbo or have REPLICATION role to execute this
292       ** sproc.
293       */
294       if (user_id() != 1)
295       begin
296           if (charindex("sa_role", show_role()) = 0 and
297                   charindex("replication_role", show_role()) = 0)
298           begin
299               /*
300               ** 18418, "Only the System Administrator (SA), the
301               **	   Database Owner (dbo) or a user with REPLICATION
302               **	   authorization may execute this stored
303               **	   procedure."
304               */
305               raiserror 18418
306               return (1)
307           end
308           else
309           begin
310               /*
311               ** Call proc_role() with each role that the user has
312               ** in order to send the success audit records.
313               ** Note that this could mean 1 or 2 audit records.
314               */
315               if (charindex("sa_role", show_role()) > 0)
316                   select @procval = proc_role("sa_role")
317               if (charindex("replication_role", show_role()) > 0)
318                   select @procval = proc_role("replication_role")
319           end
320       end
321   
322       /*
323       ** Check for a valid setname parameter
324       */
325       if (lower(@setflag) not in ("true", "false", @true, @false))
326       begin
327           /*
328           ** 17961 "Usage: sp_setreplicate object_name, {true | false}"
329           */
330           raiserror 17961
331           return (1)
332       end
333   
334       select @curstat = getdbrepstat(),
335           @dbname = db_name()
336   
337       /*
338       ** Perform sanity checks on the returned value
339       ** getdbrepstat() return current status of replication server. Check returned
340       ** message, system supports only L1 and All level replication.   
341       */
342       if (@curstat = @lt_rep_get_failed)
343       begin
344           /*
345           ** 18409, "The built-in function getdbrepstat() failed. Please
346           ** see the other messages printed along with this message."
347           */
348           raiserror 18409, "getdbrepstat"
349           return (1)
350       end
351   
352       if ((@curstat & @lt_rep_all = @lt_rep_all) or
353               (@curstat & @lt_rep_l1 = @lt_rep_l1))
354       begin
355           select @reptostandbyon = 1
356       end
357       else
358       begin
359           select @reptostandbyon = 0
360       end
361   
362       /*
363       ** Get the object's current status. Hold a read lock on sysobjects so that 
364       **	the status cannot be changed until we're done.
365       */
366       select @current_status = sysstat, @new_status2 = sysstat2
367       from sysobjects holdlock
368       where id = @objid
369   
370       /*
371       ** Perform the requested operation on the object.
372       */
373       if lower(@setflag) in ("false", @false)
374       begin
375           /*
376           ** Is the replicate status bit even set?
377           */
378           if (@current_status & @rep_constant) = 0
379           begin
380               /*
381               ** 17962 "The replication status for '%1!' is already
382               **	  set to %2!.  Replication status for '%3!'
383               **	  does not change."
384               */
385               raiserror 17962, @replicate_name, @setflag, @replicate_name
386               return (1)
387           end
388   
389           select @new_status = @current_status & ~ @rep_constant
390           select @new_status2 = @new_status2 & ~ (@owner_bit | @index_bit)
391           select @dbccbit = 0
392           select @rep_off_schema = 1
393       end
394       else
395       begin
396           /*
397           ** We are turning on replication
398           **
399           ** Is the replicate status bit already set?
400           */
401           if (@current_status & @rep_constant) != 0
402           begin
403               /*
404               ** 17962 "The replication status for '%1!' is already
405               **	  set to %2!.  Replication status for '%3!'
406               **	  does not change."
407               */
408               raiserror 17962, @replicate_name, @setflag, @replicate_name
409               return (1)
410           end
411   
412           /*
413           ** Make sure that no like object with the same name, but a
414           ** different owner, exists.  We need to do this because
415           ** the SQL Server does not send owner information along
416           ** with the object to the Replication Server.  This
417           ** restriction may be lifted in future versions.
418           */
419           if exists (select * from sysobjects
420                   where name = @object
421                       and (
422                           (type = "U ") /* user table */
423                           or
424                           (type = "P ") /* stored procedure */
425                       )
426                       and sysstat & @rep_constant != 0)
427           begin
428               /*
429               ** 17963 "An object with the same name, but owned by a
430               **	  different user is already being replicated.
431               **	  The object '%1!' cannot be replicated."
432               */
433               raiserror 17963, @replicate_name
434               return (1)
435           end
436   
437           /*
438           ** Disallow procedures that have LOB or object parameters.
439           ** Replication of procedures with LOB or object parameters
440           ** is not supported yet.
441           */
442           if exists (select A.name
443                   from sysobjects A, syscolumns B
444                   where A.name = @object
445                       and A.type = "P"
446                       and A.id = B.id
447                       and (
448                           B.type = @xtype_token
449                           or
450                           B.type = @textlocator_token
451                           or
452                           B.type = @imagelocator_token
453                           or
454                           B.type = @unitextlocator_token
455                       ))
456   
457           begin
458               /*
459               ** 18688, "Stored procedures with LOB or object parameters
460               **         cannot be marked for replication yet at this time.
461               **         Instead, use sp_setreptable to mark the invidual
462               **         tables for replication. The replication status for
463               **         '%1!' is not changed."
464               */
465               raiserror 18688, @object
466               return (1)
467           end
468   
469           select @new_status = @current_status | @rep_constant
470           select @dbccbit = 1
471           select @rep_on_schema = 1
472       end
473   
474       /*
475       ** Update the object's sysstat column
476       **
477       ** IMPORTANT: This transaction name is significant and is used by
478       **            Replication Server
479       */
480       begin transaction rs_logexec
481   
482       /* log the schema first if we are turning off replication
483       ** or standby server is running
484       */
485       if ((@rep_off_schema = 1) or (@reptostandbyon = 1))
486       begin
487           select @after_image = 0
488           select @mod_versionts = 1
489           if (logschema(@objid, @user_tran, @after_image,
490                       @mod_versionts) != 1)
491           begin
492               /*
493               ** 17968 "The built-in function logschema() failed 
494               ** for '%1!'."
495               */
496               exec sp_getmessage 17968, @msg output
497               print @msg, @replicate_name
498   
499               rollback transaction
500               return (1)
501           end
502   
503           /* 
504           ** Update syscolumns status to remove any off-row replication
505           ** bit
506           */
507           update syscolumns
508           set status = status & ~ (@colrepalwys | @colrepifch)
509           where
510               id = @objid
511               and (type in (@imagetype, @texttype, @unitexttype)
512                   or (type = @xtype_type
513                       and (xstatus & @offrow) = @offrow))
514       end
515   
516       update sysobjects set sysstat = @new_status,
517           sysstat2 = @new_status2
518       where id = @objid
519   
520       /* 
521       ** Log the schema now if we are turning on replication and we are
522       ** inside a user tran
523       */
524       if ((@rep_on_schema = 1) and (@user_tran = 1))
525       begin
526           select @after_image = 1
527           select @mod_versionts = 0
528           if (logschema(@objid, @user_tran, @after_image,
529                       @mod_versionts) != 1)
530           begin
531               /*
532               ** 17968 "The built-in function logschema() failed 
533               ** for '%1!'."
534               */
535               exec sp_getmessage 17968, @msg output
536               print @msg, @replicate_name
537   
538               rollback transaction
539               return (1)
540           end
541       end
542   
543       /*
544       ** Update the object's status in cache.
545       */
546       dbcc setreplicate(@replicate_name, @dbccbit)
547   
548       if @@error != 0
549       begin
550           /*
551           ** 17966 "Due to system failure, the replication status
552           **	  for '%1!' has not been changed."
553           */
554           raiserror 17966, @replicate_name, @setflag
555   
556           rollback transaction
557   
558           return (1)
559       end
560   
561       /*
562       ** Write the log record to replicate this invocation 
563       ** of the stored procedure.
564       */
565       if (logexec() != 1)
566       begin
567           /*
568           ** 17756, "The execution of the stored procedure '%1!'
569           ** 	   in database '%2!' was aborted because there
570           ** 	   was an error in writing the replication log
571           **	   record."
572           */
573           raiserror 17756, "sp_setreplicate", @dbname
574   
575           rollback transaction rs_logexec
576           return (1)
577       end
578   
579       commit transaction
580   
581       /*
582       ** 17964 "The replication status for '%1!' is set to %2!."
583       */
584       exec sp_getmessage 17964, @msg output
585       print @msg, @replicate_name, @setflag
586       return (0)
587



exec sp_procxmode 'sp_setreplicate', 'AnyMode'
go

Grant Execute on sp_setreplicate to public
go

DEFECTS
MINU 4 Unique Index with nullable columns master..sysmessages	master..sysmessages
MTYP 4 Assignment type mismatch @tmpstr: varchar(10) = varchar(30)	274
MTYP 4 Assignment type mismatch @tmpstr: varchar(10) = varchar(30)	278
MTYP 4 Assignment type mismatch sysstat: smallint = int	516
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1	195
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	165
QTYP 4 Comparison type mismatch smallint = int	165
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	448
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	450
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	452
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	454
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs smallint	512
QTYP 4 Comparison type mismatch tinyint = smallint	512
TNOU 4 Table with no unique index master..spt_values	master..spt_values
MGTP 3 Grant to public master..spt_values
MGTP 3 Grant to public master..sysmessages
MGTP 3 Grant to public sybsystemprocs..sp_setreplicate
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	150
MNER 3 No Error Check should check return value of exec	152
MNER 3 No Error Check should check @@error after select into	176
MNER 3 No Error Check should check return value of exec	195
MNER 3 No Error Check should check return value of exec	205
MNER 3 No Error Check should check return value of exec	284
MNER 3 No Error Check should check return value of exec	496
MNER 3 No Error Check should check @@error after update	507
MNER 3 No Error Check should check @@error after update	516
MNER 3 No Error Check should check return value of exec	535
MNER 3 No Error Check should check return value of exec	584
MUCO 3 Useless Code Useless Brackets	100
MUCO 3 Useless Code Useless Brackets	174
MUCO 3 Useless Code Useless Brackets	199
MUCO 3 Useless Code Useless Brackets	213
MUCO 3 Useless Code Useless Brackets	219
MUCO 3 Useless Code Useless Brackets	241
MUCO 3 Useless Code Useless Brackets	253
MUCO 3 Useless Code Useless Brackets	263
MUCO 3 Useless Code Useless Brackets	287
MUCO 3 Useless Code Useless Brackets	294
MUCO 3 Useless Code Useless Brackets	296
MUCO 3 Useless Code Useless Brackets	306
MUCO 3 Useless Code Useless Brackets	315
MUCO 3 Useless Code Useless Brackets	317
MUCO 3 Useless Code Useless Brackets	325
MUCO 3 Useless Code Useless Brackets	331
MUCO 3 Useless Code Useless Brackets	342
MUCO 3 Useless Code Useless Brackets	349
MUCO 3 Useless Code Useless Brackets	352
MUCO 3 Useless Code Useless Brackets	386
MUCO 3 Useless Code Useless Brackets	409
MUCO 3 Useless Code Useless Brackets	434
MUCO 3 Useless Code Useless Brackets	466
MUCO 3 Useless Code Useless Brackets	485
MUCO 3 Useless Code Useless Brackets	489
MUCO 3 Useless Code Useless Brackets	500
MUCO 3 Useless Code Useless Brackets	524
MUCO 3 Useless Code Useless Brackets	528
MUCO 3 Useless Code Useless Brackets	539
MUCO 3 Useless Code Useless Brackets	558
MUCO 3 Useless Code Useless Brackets	565
MUCO 3 Useless Code Useless Brackets	576
MUCO 3 Useless Code Useless Brackets	586
QCTC 3 Conditional Table Creation	176
QISO 3 Set isolation level	105
QNAJ 3 Not using ANSI Inner Join	180
QNAJ 3 Not using ANSI Inner Join	443
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique (error, dlevel, langid) Intersection: {error, langid}	163
QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel]	188
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique (name, uid) Intersection: {name}	254
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique (name, uid) Intersection: {name}	420
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique (name, uid) Intersection: {name}	444
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	510
VNRD 3 Variable is not read @db_rep_level_all	115
VNRD 3 Variable is not read @db_rep_level_l1	116
VNRD 3 Variable is not read @tipsa_in_index	121
VNRD 3 Variable is not read @owner	207
VNRD 3 Variable is not read @procval	318
VUNU 3 Variable is not used @db_rep_level_none	68
MSUB 2 Subquery Marker	162
MSUB 2 Subquery Marker	254
MSUB 2 Subquery Marker	419
MSUB 2 Subquery Marker	442
MTR1 2 Metrics: Comments Ratio Comments: 51%	33
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 43 = 57dec - 16exi + 2	33
MTR3 2 Metrics: Query Complexity Complexity: 241	33
PRED_QUERY_COLLECTION 2 {m=master..sysmessages, o=sybsystemprocs..sysobjects, sv=master..spt_values, sv2=master..spt_values} 0	176
PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects} 0	442