DatabaseProcApplicationCreatedLinks
sybsystemprocssp_reptostandby  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Message for "sp_reptostandby"
4     ** 17259, "Warning: The execution of the stored procedure sp_reptostandby 
5     **         will take time to process all the tables and all the text/image 
6     **         columns in database '%1!'."
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18407, "Usage: sp_reptostandby dbname [ , { L1 | ALL | NONE } ,[use_index] ]"
9     ** 18408, "The stored procedure must be executed with the name of
10    **         the current database."
11    ** 18409, "The built-in function '%1!' failed. Please see the 
12    **         other messages printed along with this message."
13    ** 18411, "The replication status for database '%1!' is '%2!'."
14    ** 18412, "The replication status for database '%1!' is already set to 
15    **         '%2!'. The replication status is not changed."
16    ** 18413, "Due to a system failure, the replication status for '%1!' 
17    **         has not been changed."
18    ** 18414, "The replication status for database '%1!' has been set to '%2!'."
19    ** 18415, "ALL"
20    ** 18416, "NONE"
21    ** 18417, "L1"
22    ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) 
23    **         or a user with REPLICATION authorization may execute this 
24    **         stored procedure."
25    */
26    create or replace procedure sp_reptostandby
27        @dbname varchar(255) = NULL,
28        @setflag varchar(20) = NULL,
29        @use_index varchar(10) = NULL /* Use index for off-row columns */
30    as
31        declare @all varchar(20)
32        declare @none varchar(20)
33        declare @l1 varchar(20)
34        declare @msg varchar(1024)
35        declare @off varchar(20)
36        declare @procval int
37        declare @dbuid int
38        declare @curdb varchar(255)
39        declare @dbid int
40        declare @objid int
41        declare @rep_constant smallint
42        declare @tmpstr varchar(200)
43        declare @curstat int
44        declare @newstat int
45        declare @sptlang int
46        declare @curindstat int
47        declare @lt_rep_all int
48        declare @lt_rep_l1 int
49        declare @lt_setrep_tipsa_index
50            int
51        declare @lt_rep_get_failed
52            int
53        declare @lt_sqlrep_upd
54            int
55        declare @lt_sqlrep_del
56            int
57        declare @lt_sqlrep_inssel
58            int
59        declare @lt_sqlrep_selinto
60            int
61        declare @sqlrepmask
62            int
63        declare @dummy int
64        declare @nullarg char(1)
65        declare @gp_enabled int
66        declare @status1 int
67        declare @status2 int
68        declare @status3 int
69    
70        select @status1 = 1
71        select @status2 = 1
72        select @status3 = 1
73    
74    
75        /*
76        ** sp_reptostandby cannot be executed inside a transaction
77        */
78    
79        if @@trancount > 0
80        begin
81            /*
82            ** 17260, "Can't run %1! from within a transaction."
83            */
84            raiserror 17260, "sp_reptostandby"
85            return (1)
86        end
87    
88        set chained off
89        set transaction isolation level 1
90        set flushmessage on
91    
92        /*
93        ** Initialize the parameter settings 
94        */
95        exec sp_getmessage 18415, @all out
96        exec sp_getmessage 18416, @none out
97        exec sp_getmessage 18417, @l1 out
98        exec sp_getmessage 19895, @off out
99    
100       /*
101       ** Set up the constants 
102       */
103       select @rep_constant = - 32768,
104           @lt_rep_get_failed = - 2, /* LT_REP_GET_FAILED */
105           @lt_rep_all = 2048, /* LT_REP_ALL */
106           @lt_rep_l1 = 4096, /* LT_REP_L1 */
107           @lt_setrep_tipsa_index = 8, /* LT_SETREP_TIPSA_INDEX */
108           @lt_sqlrep_upd = 32, /* LT_SQLREP_UPD */
109           @lt_sqlrep_del = 64, /* LT_SQLREP_DEL */
110           @lt_sqlrep_inssel = 128, /* LT_SQLREP_INSSEL */
111           @lt_sqlrep_selinto = 256 /* LT_SQLREP_SELINTO */
112   
113   
114       /*
115       ** Set 'sptlang' for proper printing of object information.  Used mainly
116       ** for the 'select' statement which is executed when we are invoked with
117       ** no parameters.  Copied from similar code in 'sp_help'
118       */
119       select @sptlang = @@langid
120       if @@langid != 0
121       begin
122           if not exists (
123                   select * from master.dbo.sysmessages where error
124                       between 17100 and 17109
125                       and langid = @@langid)
126               select @sptlang = 0
127       end
128   
129       select @setflag = upper(@setflag),
130           @use_index = lower(@use_index)
131   
132       /* check the syntax */
133   
134       if ((@dbname is NULL) or
135               (@setflag is not NULL and @setflag not in (@all, @l1, @none)) or
136               (@use_index is not NULL and @use_index not in ("use_index")))
137       begin
138           /*
139           ** 18407, 
140           ** Usage: sp_reptostandby dbname [, { L1 | ALL | NONE } [, use_index ] ]
141           */
142           raiserror 18407
143           return (1)
144       end
145   
146       select @newstat = 0
147   
148       if (@use_index = ("use_index"))
149       begin
150           /* Set LT_SETREP_TIPSA_INDEX */
151           select @newstat = 8
152       end
153   
154       /* Determine the database */
155       select @curdb = db_name()
156   
157       /*
158       ** make sure the procedure is executed with the name of the current
159       ** database
160       */
161       if (@dbname != @curdb)
162       begin
163           /*
164           ** 18408, "The stored procedure must be executed with the name of the
165           **         current database."
166           */
167           raiserror 18408
168           return (1)
169       end
170   
171       /*
172       ** Verify the database name and get the @dbuid
173       */
174       select @dbuid = suid
175       from master.dbo.sysdatabases
176       where name = @dbname
177   
178       /* Do security check */
179   
180       /*
181       ** If granular permissions is not enabled you must be SA, dbo or have 
182       ** replication role to execute this sproc.
183       ** First check if we are the DBO. 
184       **
185       **  If granular permissions is enabled then users with 
186       ** 'manage replication' permission can execute it.
187       */
188       select @nullarg = NULL
189       execute @status1 = sp_aux_checkroleperm "dbo", "manage replication",
190           @dbname, @gp_enabled output
191   
192       if (@gp_enabled = 0)
193       begin
194           if (@status1 != 0)
195           begin
196               execute @status2 = sp_aux_checkroleperm "sa_role",
197                   @nullarg, @nullarg, @gp_enabled output
198   
199               if (@status2 != 0)
200                   execute @status3 = sp_aux_checkroleperm
201                       "replication_role", @nullarg, @nullarg,
202                       @gp_enabled output
203   
204               if (@status3 != 0)
205               begin
206                   /*
207                   ** 18418, "Only the System Administrator (SA), the 
208                   ** Database Owner (dbo) or a user with REPLICATION 
209                   ** authorization may execute this stored procedure."
210                   */
211                   raiserror 18418
212                   return (1)
213               end
214           end
215           if (@status2 = 0)
216               select @dummy = proc_role("sa_role")
217   
218           if (@status3 = 0)
219               select @dummy = proc_role("replication_role")
220       end
221       else
222       begin
223           select @dummy = proc_auditperm("manage replication",
224                   @status1, @dbname)
225           if (@status1 != 0)
226               return 1
227       end
228   
229       /*
230       ** First, determine the current replication status of the database.
231       */
232       select @curstat = getdbrepstat()
233       if (@curstat = @lt_rep_get_failed)
234       begin
235           /*
236           ** "The built-in function getdbrepstat() failed. Please see the
237           **  other messages printed along with this message."
238           */
239           raiserror 18409, "getdbrepstat"
240           return (1)
241       end
242   
243       /*
244       ** Get current dbid
245       */
246       select @dbid = db_id()
247   
248       /*
249       ** Process the case where we simply return the replication status
250       */
251       if (@setflag is NULL)
252       begin
253           if (@curstat & @lt_rep_all = @lt_rep_all)
254           begin
255               select @tmpstr = @all
256           end
257           else if (@curstat & @lt_rep_l1 = @lt_rep_l1)
258           begin
259               select @tmpstr = @l1
260           end
261           else
262           begin
263               select @tmpstr = @none
264           end
265   
266           if (@curstat & @lt_setrep_tipsa_index = @lt_setrep_tipsa_index)
267           begin
268               select @tmpstr = @tmpstr + ", using index"
269           end
270   
271           select @sqlrepmask =
272               @lt_sqlrep_upd | @lt_sqlrep_del | @lt_sqlrep_inssel | @lt_sqlrep_selinto
273   
274           /*
275           ** 18411, "The replication status for database '%1!' is '%2!'"
276           */
277           exec sp_getmessage 18411, @msg output
278           print @msg, @dbname, @tmpstr
279   
280           if ((@curstat & @sqlrepmask) != 0)
281           begin
282               select @tmpstr = " "
283               if ((@curstat & @lt_sqlrep_upd) = @lt_sqlrep_upd)
284                   select @tmpstr = @tmpstr + "u"
285               if ((@curstat & @lt_sqlrep_del) = @lt_sqlrep_del)
286                   select @tmpstr = @tmpstr + "d"
287               if ((@curstat & @lt_sqlrep_inssel) = @lt_sqlrep_inssel)
288                   select @tmpstr = @tmpstr + "i"
289               if ((@curstat & @lt_sqlrep_selinto) = @lt_sqlrep_selinto)
290                   select @tmpstr = @tmpstr + "s"
291           end
292           else
293               select @tmpstr = @off
294   
295           /*
296           ** 19893, "The replication mode for database '%1!' is '%2!'"
297           */
298           exec sp_getmessage 19893, @msg output
299           print @msg, @dbname, @tmpstr
300   
301           return (0)
302       end
303   
304       /*
305       ** We are setting the replication status; figure out the new status
306       */
307       if (@setflag = @all)
308       begin
309           select @newstat = @newstat | @lt_rep_all
310       end
311       else if (@setflag = @l1)
312       begin
313           select @newstat = @newstat | @lt_rep_l1
314       end
315   
316       /*
317       ** If there is no change in status, simply return
318       */
319       if (((@curstat & @lt_rep_all) = (@newstat & @lt_rep_all)) and
320               ((@curstat & @lt_rep_l1) = (@newstat & @lt_rep_l1)))
321   
322       begin
323           /*
324           ** 18412, "The replication status for database '%1!' is already 
325           ** set to '%2!'. The replication status is not changed."
326           */
327           exec sp_getmessage 18412, @msg output
328           print @msg, @dbname, @setflag
329   
330           return (0)
331       end
332       /*
333       ** 17259, "Warning: The execution of the stored procedure 
334       ** sp_reptostandby will take time to process all the tables  
335       ** and all the text/image columns in database '%1!'."
336       */
337       exec sp_getmessage 17259, @msg output
338       print @msg, @dbname
339   
340       /* Do set */
341       if (setdbrepstat(@newstat, 0) != 1)
342       begin
343           /*
344           ** 18413, "Due to a system failure, the replication status
345           ** for '%1!' has not been changed."
346           */
347           raiserror 18413, @dbname
348           return (1)
349       end
350   
351       /* Display status message */
352       /*
353       ** 18414, "The replication status for database '%1!' has been set to '%2!'."
354       */
355       exec sp_getmessage 18414, @msg output
356       print @msg, @dbname, @setflag
357   
358       return (0)
359   
360   


exec sp_procxmode 'sp_reptostandby', 'AnyMode'
go

Grant Execute on sp_reptostandby to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch smallint = int 125
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_reptostandby  
 MNER 3 No Error Check should check return value of exec 95
 MNER 3 No Error Check should check return value of exec 96
 MNER 3 No Error Check should check return value of exec 97
 MNER 3 No Error Check should check return value of exec 98
 MNER 3 No Error Check should check return value of exec 277
 MNER 3 No Error Check should check return value of exec 298
 MNER 3 No Error Check should check return value of exec 327
 MNER 3 No Error Check should check return value of exec 337
 MNER 3 No Error Check should check return value of exec 355
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 280
 MUCO 3 Useless Code Useless Brackets 283
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 301
 MUCO 3 Useless Code Useless Brackets 307
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 348
 MUCO 3 Useless Code Useless Brackets 358
 QISO 3 Set isolation level 89
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
123
 VNRD 3 Variable is not read @rep_constant 103
 VNRD 3 Variable is not read @sptlang 126
 VNRD 3 Variable is not read @dbuid 174
 VNRD 3 Variable is not read @gp_enabled 202
 VNRD 3 Variable is not read @dummy 223
 VNRD 3 Variable is not read @dbid 246
 VUNU 3 Variable is not used @procval 36
 VUNU 3 Variable is not used @objid 40
 VUNU 3 Variable is not used @curindstat 46
 MSUB 2 Subquery Marker 122
 MTR1 2 Metrics: Comments Ratio Comments: 42% 26
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 27 = 35dec - 10exi + 2 26
 MTR3 2 Metrics: Query Complexity Complexity: 162 26

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