DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setrepdbmode  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Message for "sp_setrepdbmode"
4     ** 17260, "Can't run %1! from within a transaction."
5     ** 18408, "The stored procedure must be executed with the name of
6     **         the current database."
7     ** 18409, "The built-in function '%1!' failed. Please see any 
8     **         other messages printed along with this message."
9     ** 18410, "The replication status of '%1!' is corrupt. Please contact 
10    **         Sybase Technical Support."
11    ** 18411, "The replication status for database '%1!' is '%2!'."
12    ** 18412, "The replication status for database '%1!' is already set to 
13    **         '%2!'. The replication status is not changed."
14    ** 18413, "Due to a system failure, the replication status for '%1!' 
15    **         has not been changed."
16    ** 18414, "The replication status for database '%1!' has been set to '%2!'."
17    ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) 
18    **         or a user with REPLICATION authorization may execute this 
19    **         stored procedure."
20    */
21    create procedure sp_setrepdbmode
22        @dbname varchar(255) = NULL,
23        @setflag varchar(20) = NULL,
24        @setmode varchar(20) = NULL
25    as
26        declare @msg varchar(1024)
27        declare @off varchar(20)
28        declare @on varchar(20)
29        declare @procval int
30        declare @dbuid int
31        declare @curdb varchar(255)
32        declare @dbid int
33        declare @objid int
34        declare @rep_constant smallint
35        declare @tmpstr varchar(200)
36        declare @curstat int
37        declare @newstat int
38        declare @sptlang int
39        declare @setrep_status int
40        declare @lt_sqlrep_upd int
41        declare @lt_sqlrep_del int
42        declare @lt_sqlrep_inssel int
43        declare @lt_sqlrep_selinto int
44        declare @sqlrepmask int
45        declare @lt_rep_all int
46        declare @lt_rep_l1 int
47        declare @lt_rep_get_failed int
48    
49        declare @update char(1) /* 'u' string used for sql replication */
50        declare @delete char(1) /* 'd' string used for sql replication */
51        declare @inserts char(1) /* 'i' string used for sql replication */
52        declare @selinto char(1) /* 's' string used for sql replication */
53        declare @sqlrep varchar(4) /* 'udis' string used for sql replication */
54        declare @sqlrep_syntax int /* Are we using a valid substring of 'udis'? */
55    
56        /* SQL replication threshold */
57        declare @threshold varchar(20) /* Used to check stored proc syntax */
58        declare @current_threshold int /* Current database threshold */
59        declare @threshold_value int /* Integer value from @setflag */
60        declare @threshold_syntax smallint /* Used to check stored proc syntax */
61        declare @threshold_max int /* Max value for sql threshold */
62        declare @outvalue varchar(10) /* Used to stored tmp values */
63    
64        /*
65        ** sp_setrepdbmode cannot be executed inside a transaction
66        */
67    
68        if @@trancount > 0
69        begin
70            /*
71            ** 17260, "Can't run %1! from within a transaction."
72            */
73            raiserror 17260, "sp_reptostandby"
74            return (1)
75        end
76    
77        set chained off
78        set transaction isolation level 1
79    
80        /*
81        ** Set up the constants 
82        */
83        select @rep_constant = - 32768,
84            @setrep_status = 0,
85            @lt_rep_get_failed = - 2, /* LT_GET_REP_FAILED */
86            @lt_sqlrep_upd = 32, /* LT_SQLREP_UPD */
87            @lt_sqlrep_del = 64, /* LT_SQLREP_DEL */
88            @lt_sqlrep_inssel = 128, /* LT_SQLREP_INSSEL */
89            @lt_sqlrep_selinto = 256, /* LT_SQLREP_SELINTO */
90            @lt_rep_all = 2048, /* LT_REP_ALL */
91            @lt_rep_l1 = 4096, /* LT_REP_L1 */
92            @threshold_max = 10000 /* LT_SQLREP_MAX_THRESHOLD */
93    
94        select @sqlrep = name
95        from master.dbo.spt_values
96        where type = 'SQ'
97            and number = 1
98    
99        /*
100       ** get localized parameters for "on","off" and "threshold"
101       */
102       exec sp_getmessage 19895, @off out
103       exec sp_getmessage 19897, @on out
104       exec sp_getmessage 19931, @threshold out
105   
106       if (@sqlrep is NULL or @on is NULL or @off is NULL or @threshold is NULL)
107       begin
108           /*
109           ** 18413, "Due to a system failure, the replication status
110           ** for '%1!' has not been changed."
111           */
112           raiserror 18413, @dbname
113           return (1)
114       end
115   
116       select @update = substring(@sqlrep, 1, 1)
117       select @delete = substring(@sqlrep, 2, 1)
118       select @inserts = substring(@sqlrep, 3, 1)
119       select @selinto = substring(@sqlrep, 4, 1)
120   
121       /*
122       ** Set 'sptlang' for proper printing of object information.  Used mainly
123       ** for the 'select' statement which is executed when we are invoked with
124       ** no parameters.  Copied from similar code in 'sp_help'
125       */
126       select @sptlang = @@langid
127       if @@langid != 0
128       begin
129           if not exists (
130                   select * from master.dbo.sysmessages where error
131                       between 17100 and 17109
132                       and langid = @@langid)
133               select @sptlang = 0
134       end
135   
136       select @setflag = lower(@setflag)
137       select @setmode = lower(@setmode)
138   
139       /* check the syntax */
140       if (@dbname is NULL)
141       begin
142           /*
143           ** 19887, "Usage: sp_setrepdbmode dbname [, 'UDIS' [, 'on' | 'off']
144           **				| 'threshold' [,'value']] 
145           */
146           raiserror 19887
147           return (1)
148       end
149   
150   
151       /* Determine the database */
152       select @curdb = db_name()
153   
154       /*
155       ** make sure the procedure is executed with the name of the current
156       ** database
157       */
158       if (@dbname != @curdb)
159       begin
160           /*
161           ** 18408, "The stored procedure must be executed with the name of the
162           **         current database."
163           */
164           raiserror 18408
165           return (1)
166       end
167   
168       /*
169       ** Verify the database name and get the @dbuid
170       */
171       select @dbuid = suid
172       from master.dbo.sysdatabases
173       where name = @dbname
174   
175       /* Do security check */
176   
177       /*
178       ** You must be SA, dbo or have replication role to execute this sproc.
179       ** First check if we are the DBO. 
180       */
181       if (suser_id() != @dbuid)
182       begin
183           /*
184           ** check if we have sa_role or replication_role. If show_role()
185           ** does not find both "sa_role" and the "replication_role"
186           ** then we print out a message. 
187           ** Note: show_role does not print any message.
188           */
189           if (charindex("sa_role", show_role()) = 0 and
190                   charindex("replication_role", show_role()) = 0)
191           begin
192               /*
193               ** 18418, "Only the System Administrator (SA), the Database
194               **         Owner (dbo) or a user with REPLICATION authorization
195               **         may execute this stored procedure."
196               */
197               raiserror 18418
198               return (1)
199           end
200           else
201           begin
202               /*
203               ** Call proc_role() with each role that the user has
204               ** in order to send the success audit records.
205               ** Note that this could mean 1 or 2 audit records.
206               */
207               if (charindex("sa_role", show_role()) > 0)
208                   select @procval = proc_role("sa_role")
209               if (charindex("replication_role", show_role()) > 0)
210                   select @procval = proc_role("replication_role")
211           end
212       end
213   
214       /*
215       ** Get current dbid
216       */
217       select @dbid = db_id()
218   
219       select @sqlrepmask =
220           @lt_sqlrep_upd | @lt_sqlrep_del | @lt_sqlrep_inssel | @lt_sqlrep_selinto
221   
222       /*
223       ** Check that the arguments to this procedure are acceptable for
224       ** further processing. The arguments are @setflag and @setmode.
225       */
226       select @sqlrep_syntax = 0,
227           @threshold_syntax = 0
228   
229       /*
230       ** @sqlrep_syntax is set to 1 if @setflag is a valid substring of 
231       ** 'udis', and to 0 otherwise.
232       */
233       exec sp_sqlrep_check_syntax @setflag,
234           @sqlrep,
235           @sqlrep_syntax output
236   
237   
238       /*
239       ** We display sp_setrepdbmode status in the following cases
240       **
241       **	1.- 'sp_setrepdbmode '
242       **	2.- 'sp_setrepdbmode ', '
243       ** 	3.- 'sp_setrepdbmode ', 'threshold'
244       **
245       */
246       if ((@setflag is null) or
247               (@setmode is null and @sqlrep_syntax = 1) or
248               (@setmode is null and @setflag in ("threshold", @threshold)))
249       begin
250           /*
251           ** First, determine the current replication status of the database.
252           */
253           select @curstat = getdbrepstat()
254           if (@curstat = @lt_rep_get_failed)
255           begin
256               /*
257               ** "The built-in function getdbrepstat() failed. Please see any
258               **  other messages printed along with this message."
259               */
260               raiserror 18409, "getdbrepstat"
261               return (1)
262           end
263   
264           if ((@curstat & @sqlrepmask) != 0)
265           begin
266               select @tmpstr = " "
267               if ((@curstat & @lt_sqlrep_upd) = @lt_sqlrep_upd)
268                   select @tmpstr = @tmpstr + "u"
269               if ((@curstat & @lt_sqlrep_del) = @lt_sqlrep_del)
270                   select @tmpstr = @tmpstr + "d"
271               if ((@curstat & @lt_sqlrep_inssel) = @lt_sqlrep_inssel)
272                   select @tmpstr = @tmpstr + "i"
273               if ((@curstat & @lt_sqlrep_selinto) = @lt_sqlrep_selinto)
274                   select @tmpstr = @tmpstr + "s"
275           end
276           else
277               select @tmpstr = @off
278   
279           /* Get the current replication threshold */
280           select @current_threshold = getrepdbthreshold(@dbid)
281           if (@current_threshold < 0)
282           begin
283               /*
284               ** "The built-in function getrepthreshold() failed.
285               ** Please see any other messages printed along with
286               ** this message."
287               */
288               raiserror 18409, "getrepdbthreshold"
289               return (1)
290           end
291   
292           /*
293           ** 19893, "The replication mode for database '%1!' is '%2!'"
294           */
295           exec sp_getmessage 19893, @msg output
296           print @msg, @dbname, @tmpstr
297   
298           /*
299           ** 19932, "The replication threshold for '%1!' is '%2!'"
300           */
301           exec sp_getmessage 19932, @msg output
302           select @outvalue = convert(varchar(10), @current_threshold)
303           print @msg, @dbname, @outvalue
304           return (0)
305       end
306   
307       select @setflag = ltrim(rtrim(@setflag))
308   
309       if (@setflag in ("threshold", @threshold))
310       begin
311           select @threshold_syntax = 1
312       end
313       else
314       if (@sqlrep_syntax = 1 and @setmode not in ("on", "off", @on, @off, NULL))
315       begin
316           select @sqlrep_syntax = 0
317       end
318   
319       if (@sqlrep_syntax = 0 and @threshold_syntax = 0)
320       begin
321           /*
322           ** 19887, "Usage: sp_setrepdbmode dbname [, 'UDIS' [, 'on' | 'off']
323           **				| 'threshold' [,'value']] 
324           */
325           raiserror 19887
326           return (1)
327       end
328   
329       if (@sqlrep_syntax = 1)
330       begin
331           /*
332           ** First, determine the current replication status of the database.
333           */
334           select @curstat = getdbrepstat()
335           if (@curstat = @lt_rep_get_failed)
336           begin
337               /*
338               ** "The built-in function getdbrepstat() failed. Please see any
339               **  other messages printed along with this message."
340               */
341               raiserror 18409, "getdbrepstat"
342               return (1)
343           end
344   
345           /* Database replication level has to be set */
346           if ((@curstat & @lt_rep_all != @lt_rep_all) and
347                   (@curstat & @lt_rep_l1 != @lt_rep_l1))
348           begin
349               /*
350               ** 19888: The attempt to set the replication mode failed. Please,
351               ** set the database replication level to 'ALL' or to 'L1' using
352               ** the stored procedure sp_reptostandby prior to setting the
353               ** replication mode.
354               */
355               raiserror 19888
356               return (1)
357           end
358   
359           /* Interpret the input string: "udis" */
360           if (charindex("u", @setflag) > 0)
361           begin
362               select @setrep_status = @setrep_status | @lt_sqlrep_upd
363           end
364           if (charindex("d", @setflag) > 0)
365           begin
366               select @setrep_status = @setrep_status | @lt_sqlrep_del
367           end
368           if (charindex("i", @setflag) > 0)
369           begin
370               select @setrep_status = @setrep_status | @lt_sqlrep_inssel
371           end
372           if (charindex("s", @setflag) > 0)
373           begin
374               select @setrep_status = @setrep_status | @lt_sqlrep_selinto
375           end
376   
377           if (@setmode = @off)
378           begin
379               select @setrep_status = 0
380           end
381   
382           /* No Change; we're done. */
383           if ((@setrep_status & @sqlrepmask) = (@curstat & @sqlrepmask))
384           begin
385               /*
386               ** 18412, "The replication status for database '%1!' is already 
387               ** set to '%2!'. The replication status is not changed."
388               */
389               exec sp_getmessage 18412, @msg output
390               if (@setmode = @off)
391                   print @msg, @dbname, @setmode
392               else
393                   print @msg, @dbname, @setflag
394               return (0)
395           end
396   
397           /* Do set */
398           if (setdbrepstat(@setrep_status, 1) != 1)
399           begin
400               /*
401               ** 18413, "Due to a system failure, the replication status
402               ** for '%1!' has not been changed."
403               */
404               raiserror 18413, @dbname
405               return (1)
406           end
407   
408           /* Let user know what we did. */
409           if (@setmode = @off)
410           begin
411               /*
412               ** 19893, "The replication mode for database '%1!' is '%2!'."
413               */
414               exec sp_getmessage 19893, @msg output
415               print @msg, @dbname, @off
416           end
417           else
418           begin
419               /* Display status message */
420               /*
421               ** 19893, "The replication mode for database '%1!' is '%2!'."
422               */
423               exec sp_getmessage 19893, @msg output
424               print @msg, @dbname, @setflag
425           end
426           return (0)
427       end
428       else
429       /* We are changing the sql replication threshold */
430       if (@threshold_syntax = 1)
431       begin
432           if (@setmode is null)
433           begin
434               /* Get the current replication threshold */
435               select @current_threshold = getrepdbthreshold(@dbid)
436   
437               if (@current_threshold < 0)
438               begin
439                   /*
440                   ** "The built-in function getrepthreshold() failed.
441                   ** Please see any other messages printed along with
442                   ** this message."
443                   */
444                   raiserror 18409, "getrepdbthreshold"
445                   return (1)
446               end
447   
448               /*
449               ** 19932, "The replication threshold for '%1!' is '%2!'."
450               */
451               exec sp_getmessage 19932, @msg output
452               select @outvalue = convert(varchar(10), @current_threshold)
453               print @msg, @dbname, @outvalue
454               return (0)
455           end
456   
457           select @threshold_value = convert(int, @setmode)
458   
459           if ((@threshold_value < 0) or (@threshold_value > @threshold_max))
460           begin
461               /*
462               ** 19934, "The replication threshold provided for
463               **        '%1!' is '%2!'. Please choose a
464               **        threshold larger than 0 and smaller than '%3!'."
465               */
466               select @outvalue = convert(varchar(10), @threshold_max)
467               raiserror 19934, @dbname, @setmode, @outvalue
468               return (1)
469           end
470   
471           if (setrepdbthreshold(@dbid, @threshold_value) != 1)
472           begin
473               /*
474               ** 18409, "The built-in funtion '%1!' failed. Please see
475               **         any other messages printed along with this
476               **         message."
477               */
478               raiserror 18409, "setrepdbthreshold"
479               return (1)
480           end
481   
482           /* 
483           ** We have requested to reset the threshold to the default value.
484           */
485           if (@threshold_value = 0)
486           begin
487               select @current_threshold = getrepdbthreshold(@dbid)
488               /*
489               ** 19950, " The replication threshold for '%1!' has been reset 
490               **	    to '%2!'."
491               */
492               exec sp_getmessage 19950, @msg output
493               select @outvalue = convert(varchar(10), @current_threshold)
494               print @msg, @dbname, @outvalue
495               return (0)
496           end
497   
498           /*
499           ** 19932, 'The replication threshold for '%1!' is '%2!'."
500           */
501           exec sp_getmessage 19932, @msg output
502           print @msg, @dbname, @setmode
503           return (0)
504   
505   
506       end
507   


exec sp_procxmode 'sp_setrepdbmode', 'AnyMode'
go

Grant Execute on sp_setrepdbmode to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @sqlrep: varchar(4) = varchar(28) 94
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 132
 QTYP 4 Comparison type mismatch smallint = int 132
 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..sysdatabases  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_setrepdbmode  
 MNER 3 No Error Check should check return value of exec 102
 MNER 3 No Error Check should check return value of exec 103
 MNER 3 No Error Check should check return value of exec 104
 MNER 3 No Error Check should check return value of exec 233
 MNER 3 No Error Check should check return value of exec 295
 MNER 3 No Error Check should check return value of exec 301
 MNER 3 No Error Check should check return value of exec 389
 MNER 3 No Error Check should check return value of exec 414
 MNER 3 No Error Check should check return value of exec 423
 MNER 3 No Error Check should check return value of exec 451
 MNER 3 No Error Check should check return value of exec 492
 MNER 3 No Error Check should check return value of exec 501
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 246
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 281
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 329
 MUCO 3 Useless Code Useless Brackets 335
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 356
 MUCO 3 Useless Code Useless Brackets 360
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 368
 MUCO 3 Useless Code Useless Brackets 372
 MUCO 3 Useless Code Useless Brackets 377
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 390
 MUCO 3 Useless Code Useless Brackets 394
 MUCO 3 Useless Code Useless Brackets 398
 MUCO 3 Useless Code Useless Brackets 405
 MUCO 3 Useless Code Useless Brackets 409
 MUCO 3 Useless Code Useless Brackets 426
 MUCO 3 Useless Code Useless Brackets 430
 MUCO 3 Useless Code Useless Brackets 432
 MUCO 3 Useless Code Useless Brackets 437
 MUCO 3 Useless Code Useless Brackets 445
 MUCO 3 Useless Code Useless Brackets 454
 MUCO 3 Useless Code Useless Brackets 459
 MUCO 3 Useless Code Useless Brackets 468
 MUCO 3 Useless Code Useless Brackets 471
 MUCO 3 Useless Code Useless Brackets 479
 MUCO 3 Useless Code Useless Brackets 485
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 503
 QAFM 3 Var Assignment from potentially many rows 94
 QISO 3 Set isolation level 78
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
130
 VNRD 3 Variable is not read @rep_constant 83
 VNRD 3 Variable is not read @update 116
 VNRD 3 Variable is not read @delete 117
 VNRD 3 Variable is not read @inserts 118
 VNRD 3 Variable is not read @selinto 119
 VNRD 3 Variable is not read @sptlang 133
 VNRD 3 Variable is not read @procval 210
 VUNU 3 Variable is not used @objid 33
 VUNU 3 Variable is not used @newstat 37
 MSUB 2 Subquery Marker 129
 MTR1 2 Metrics: Comments Ratio Comments: 44% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 35 = 54dec - 21exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 231 21

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_values (1)  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_sqlrep_check_syntax  
reads table master..sysmessages (1)