DatabaseProcApplicationCreatedLinks
sybsystemprocssp_reptostandby  31 Aug 14Defects Dependencies

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


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 114
 QTYP 4 Comparison type mismatch smallint = int 114
 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 83
 MNER 3 No Error Check should check return value of exec 84
 MNER 3 No Error Check should check return value of exec 85
 MNER 3 No Error Check should check return value of exec 86
 MNER 3 No Error Check should check return value of exec 254
 MNER 3 No Error Check should check return value of exec 275
 MNER 3 No Error Check should check return value of exec 304
 MNER 3 No Error Check should check return value of exec 325
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 260
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 307
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 318
 MUCO 3 Useless Code Useless Brackets 328
 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}
112
 VNRD 3 Variable is not read @rep_constant 92
 VNRD 3 Variable is not read @sptlang 115
 VNRD 3 Variable is not read @procval 202
 VNRD 3 Variable is not read @dbid 223
 VUNU 3 Variable is not used @objid 39
 VUNU 3 Variable is not used @curindstat 45
 MSUB 2 Subquery Marker 111
 MTR1 2 Metrics: Comments Ratio Comments: 44% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 32dec - 9exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 139 25

DEPENDENCIES
PROCS AND TABLES USED
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)  
reads table master..sysdatabases (1)  
reads table master..sysmessages (1)