DatabaseProcApplicationCreatedLinks
sybsystemprocssp_forceonline_db  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** 17260, "Can't run %1! from within a transaction." 
6     ** 17421, "No such database -- run sp_helpdb to list databases."
7     ** 17422, "The 'master' database's options can not be changed."
8     ** 17428, "You must be in the 'master' database in order to change
9     **	database options."                                
10    */
11    
12    create or replace procedure sp_forceonline_db
13        @dbname varchar(30),
14        @option varchar(9)
15    as
16        declare @dbid int, /* dbid of the database */
17            @class int,
18            @attrib_id int,
19            @action int,
20            @object_type varchar(2),
21            @msg varchar(1024),
22            @sptlang int,
23            @state int,
24            @old_state int,
25            @susgran int,
26            @num int,
27            @pgid int,
28            @whichone int, /* which language? */
29            @nullarg char(1),
30            @dummy int,
31            @status int,
32            @gp_enabled int,
33            @issystemdb int
34    
35        select @class = 10 /* for suspect granularity */
36        select @action = 2 /* modify entry */
37        select @attrib_id = 2 /* attribute is SUSPECT PAGES */
38        select @object_type = 'D'
39        select @issystemdb = 0
40    
41    
42        select @sptlang = @@langid, @whichone = 0
43    
44        if @@langid != 0
45        begin
46            if not exists (
47                    select * from master.dbo.sysmessages where error
48                        between 17050 and 17069
49                        and langid = @@langid)
50                select @sptlang = 0
51        end
52        /*
53        ** Check for input params - dbname, pageid, and options
54        */
55    
56    
57        /*
58        **  Verify the database name and get the @dbid 
59        */
60        select @dbid = db_id(@dbname)
61    
62        /*
63        **  If @dbname not found, say so.
64        */
65        if @dbid is NULL
66        begin
67            /*
68            ** 17421, "No such database -- run sp_helpdb to list databases."
69            */
70            exec sp_getmessage 17421, @msg output
71            print @msg
72            return (1)
73        end
74    
75        /* 
76        ** If granular permissions is not enabled then sa_role is required.
77        ** If granular permissions is enabled then the permission 'own database' is
78        ** required. proc_role and proc_auditperm will also do auditing
79        ** if required. Both will also print error message if required.
80        */
81    
82        select @nullarg = NULL
83        execute @status = sp_aux_checkroleperm "sa_role", "own database",
84            @dbname, @gp_enabled output
85        /* For Auditing */
86        if (@gp_enabled = 0)
87            select @dummy = proc_role("sa_role")
88        else
89        begin
90            select @dummy = proc_auditperm("own database", @status, @dbname)
91        end
92    
93        if (@status != 0)
94            return (1)
95    
96        if db_name() != "master"
97        begin
98            /*
99            ** 17428, "You must be in the 'master' database in order to change database options."                                
100           */
101           exec sp_getmessage 17428, @msg output
102           print "sp_forceonline_db: %1!", @msg
103           return (1)
104       end
105   
106       /*
107       **  If we're in a transaction, disallow this since it might make recovery
108       **  impossible.
109       */
110       if @@trancount > 0
111       begin
112           /*
113           ** 17260, "Can't run %1! from within a transaction." 
114           */
115           exec sp_getmessage 17260, @msg output
116           print @msg, "sp_forceonline_db"
117           return (1)
118       end
119       else
120       begin
121           set chained off
122       end
123   
124       set transaction isolation level 1
125   
126       /*
127       ** Check to see that the input params are correct and then hook up with
128       ** Sysattributes table to enter data.
129       */
130   
131   
132       select @susgran = object_info1 from master.dbo.sysattributes
133       where class = @class AND
134           attribute = 0 AND
135           object_type = @object_type AND
136           object = @dbid
137   
138   
139       if ((@option is NULL) OR ((@option != "sa_on") AND (@option != "sa_off")
140                   AND (@option != "all_users")))
141       begin
142           /*
143           ** "%1!: Invalid option '%2!'. Use 'sa_on', 'sa_off', or 'all_users'."
144           */
145           exec sp_getmessage 18440, @msg output
146           print @msg, "sp_forceonline_db", @option
147           return (1)
148       end
149   
150       select @num = count(*) from master.dbo.sysattributes
151       where class = @class AND
152           attribute = @attrib_id AND
153           object_type = @object_type AND
154           object = @dbid
155   
156       if (@num = 0)
157       begin
158           /*
159           ** "%1!: No suspect pages in database '%2!'. Use sp_listsuspect_page to list suspect pages."
160           */
161           exec sp_getmessage 18448, @msg output
162           print @msg, "sp_forceonline_db", @dbname
163           return (1)
164       end
165   
166       declare suspect_page cursor for
167       select object_info3 from master.dbo.sysattributes
168       where class = @class AND
169           attribute = @attrib_id AND
170           object_type = @object_type AND
171           object = @dbid
172       open suspect_page
173       fetch suspect_page into @pgid
174   
175       while (@@sqlstatus = 0)
176       begin
177           exec sp_forceonline_page @dbname, @pgid, @option, "no_print"
178           fetch suspect_page into @pgid
179       end
180       close suspect_page
181       deallocate cursor suspect_page
182   
183   
184       return (0)
185   
186   


exec sp_procxmode 'sp_forceonline_db', 'AnyMode'
go

Grant Execute on sp_forceonline_db to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 49
 QTYP 4 Comparison type mismatch smallint = int 49
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 133
 QTYP 4 Comparison type mismatch smallint = int 133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 134
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 151
 QTYP 4 Comparison type mismatch smallint = int 151
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 152
 QTYP 4 Comparison type mismatch smallint = int 152
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 168
 QTYP 4 Comparison type mismatch smallint = int 168
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch smallint = int 169
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause suspect_page 167
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_forceonline_db  
 MNER 3 No Error Check should check return value of exec 70
 MNER 3 No Error Check should check return value of exec 101
 MNER 3 No Error Check should check return value of exec 115
 MNER 3 No Error Check should check return value of exec 145
 MNER 3 No Error Check should check return value of exec 161
 MNER 3 No Error Check should check return value of exec 177
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 184
 QAFM 3 Var Assignment from potentially many rows 132
 QISO 3 Set isolation level 124
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
47
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
133
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
151
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
168
 VNRD 3 Variable is not read @action 36
 VNRD 3 Variable is not read @issystemdb 39
 VNRD 3 Variable is not read @whichone 42
 VNRD 3 Variable is not read @sptlang 50
 VNRD 3 Variable is not read @nullarg 82
 VNRD 3 Variable is not read @dummy 90
 VNRD 3 Variable is not read @susgran 132
 VUNU 3 Variable is not used @state 23
 VUNU 3 Variable is not used @old_state 24
 CUPD 2 Updatable Cursor Marker (updatable by default) 167
 MSUB 2 Subquery Marker 46
 MTR1 2 Metrics: Comments Ratio Comments: 35% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 25dec - 6exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 83 12

DATA PROPAGATION detailed
ColumnWritten To
@dbnamesp_forceonline_page_rset_001.DBName sp_forceonline_page_rset_002.DBName

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
reads table master..sysattributes (1)  
reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_forceonline_page  
   read_writes table master..sysattributes (1)  
   writes table sybsystemprocs..sp_forceonline_page_rset_002 
   calls proc sybsystemprocs..sp_getmessage  
   writes table sybsystemprocs..sp_forceonline_page_rset_001 
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
      reads table master..syscurconfigs (1)  
      reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm