DatabaseProcApplicationCreatedLinks
sybsystemprocssp_forceonline_db  31 Aug 14Defects 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 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    
30        select @class = 10 /* for suspect granularity */
31        select @action = 2 /* modify entry */
32        select @attrib_id = 2 /* attribute is SUSPECT PAGES */
33        select @object_type = 'D'
34    
35        select @sptlang = @@langid, @whichone = 0
36    
37        if @@langid != 0
38        begin
39            if not exists (
40                    select * from master.dbo.sysmessages where error
41                        between 17050 and 17069
42                        and langid = @@langid)
43                select @sptlang = 0
44        end
45        /*
46        ** Check for input params - dbname, pageid, and options
47        */
48    
49    
50        /*
51        **  Verify the database name and get the @dbid 
52        */
53        select @dbid = db_id(@dbname)
54    
55        /*
56        **  If @dbname not found, say so.
57        */
58        if @dbid is NULL
59        begin
60            /*
61            ** 17421, "No such database -- run sp_helpdb to list databases."
62            */
63            exec sp_getmessage 17421, @msg output
64            print @msg
65            return (1)
66        end
67    
68        /*
69        **  Only the Accounts with SA role can execute it.
70        **  Call proc_role() with the required SA role.
71        */
72        if (proc_role("sa_role") < 1)
73        begin
74            /*
75            ** , "'%1!':Permission denied. This operation requires System Administrator (sa_role) role."                                
76            */
77            exec sp_getmessage 18524, @msg output
78            print @msg, "sp_forceonline_db"
79            return (1)
80        end
81    
82    
83        if db_name() != "master"
84        begin
85            /*
86            ** 17428, "You must be in the 'master' database in order to change database options."                                
87            */
88            exec sp_getmessage 17428, @msg output
89            print "sp_forceonline_db: %1!", @msg
90            return (1)
91        end
92    
93        /*
94        **  If we're in a transaction, disallow this since it might make recovery
95        **  impossible.
96        */
97        if @@trancount > 0
98        begin
99            /*
100           ** 17260, "Can't run %1! from within a transaction." 
101           */
102           exec sp_getmessage 17260, @msg output
103           print @msg, "sp_forceonline_db"
104           return (1)
105       end
106       else
107       begin
108           set chained off
109       end
110   
111       set transaction isolation level 1
112   
113       /*
114       ** Check to see that the input params are correct and then hook up with
115       ** Sysattributes table to enter data.
116       */
117   
118   
119       select @susgran = object_info1 from master.dbo.sysattributes
120       where class = @class AND
121           attribute = 0 AND
122           object_type = @object_type AND
123           object = @dbid
124   
125   
126       if ((@option is NULL) OR ((@option != "sa_on") AND (@option != "sa_off")
127                   AND (@option != "all_users")))
128       begin
129           /*
130           ** "%1!: Invalid option '%2!'. Use 'sa_on', 'sa_off', or 'all_users'."
131           */
132           exec sp_getmessage 18440, @msg output
133           print @msg, "sp_forceonline_db", @option
134           return (1)
135       end
136   
137       select @num = count(*) from master.dbo.sysattributes
138       where class = @class AND
139           attribute = @attrib_id AND
140           object_type = @object_type AND
141           object = @dbid
142   
143       if (@num = 0)
144       begin
145           /*
146           ** "%1!: No suspect pages in database '%2!'. Use sp_listsuspect_page to list suspect pages."
147           */
148           exec sp_getmessage 18448, @msg output
149           print @msg, "sp_forceonline_db", @dbname
150           return (1)
151       end
152   
153       declare suspect_page cursor for
154       select object_info3 from master.dbo.sysattributes
155       where class = @class AND
156           attribute = @attrib_id AND
157           object_type = @object_type AND
158           object = @dbid
159       open suspect_page
160       fetch suspect_page into @pgid
161   
162       while (@@sqlstatus = 0)
163       begin
164           exec sp_forceonline_page @dbname, @pgid, @option, "no_print"
165           fetch suspect_page into @pgid
166       end
167       close suspect_page
168       deallocate cursor suspect_page
169   
170   
171       return (0)
172   
173   


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 42
 QTYP 4 Comparison type mismatch smallint = int 42
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 120
 QTYP 4 Comparison type mismatch smallint = int 120
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 121
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch smallint = int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 139
 QTYP 4 Comparison type mismatch smallint = int 139
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch smallint = int 155
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch smallint = int 156
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause suspect_page 154
 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 63
 MNER 3 No Error Check should check return value of exec 77
 MNER 3 No Error Check should check return value of exec 88
 MNER 3 No Error Check should check return value of exec 102
 MNER 3 No Error Check should check return value of exec 132
 MNER 3 No Error Check should check return value of exec 148
 MNER 3 No Error Check should check return value of exec 164
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 126
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 171
 QAFM 3 Var Assignment from potentially many rows 119
 QISO 3 Set isolation level 111
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}		 40
 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}		 120
 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}		 138
 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}		 155
 VNRD 3 Variable is not read @action 31
 VNRD 3 Variable is not read @whichone 35
 VNRD 3 Variable is not read @sptlang 43
 VNRD 3 Variable is not read @susgran 119
 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) 154
 MSUB 2 Subquery Marker 39
 MTR1 2 Metrics: Comments Ratio Comments: 36% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 23dec - 7exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 78 12

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)  
reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_forceonline_page  
   reads table master..sysmessages (1)  
   read_writes table master..sysattributes (1)  
   calls proc sybsystemprocs..sp_getmessage