DatabaseProcApplicationCreatedLinks
sybsystemprocssp_defaultloc  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Omni only
6     **
7     ** Messages for "sp_defaultloc"	 18322
8     **
9     ** 17240, "'%1!' is not a valid name."
10    ** 17800, "No such server -- run sp_helpserver to list servers."
11    ** 18322, "There is not a database named '%1!'."
12    ** 18323, "There is not an object type named '%1!'."
13    ** 18324, "An RMS filespec for a directory must be specified."
14    ** 18325, "Storage location syntax must end in '.'."
15    ** 18326, "Created default location for database '%1!' at '%2!'."
16    ** 18327, "You cannot remap objects in the master, model, or tempdb databases."
17    ** 18328, "There is no default storage location in effect for database '%1!'."
18    ** 18329, "Default storage location deleted for database '%1!'."
19    ** 18330, "Database '%1!' already has a default location '%2!'."
20    */
21    
22    create procedure sp_defaultloc
23        @dbname varchar(30), /* server name */
24        @defaultloc varchar(255), /* default storage location */
25        @defaulttype varchar(30) = "table" /* default object type */
26    as
27        begin
28            declare @dbid smallint,
29                @type int,
30                @last_char char(1),
31                @msg varchar(1024),
32                @oldloc varchar(255)
33            declare @site varchar(30),
34                @db varchar(30),
35                @own varchar(30),
36                @obj varchar(30),
37                @status int,
38                @isatempdb int
39            declare @tempdb_mask int /* All database status bit for a tempdb */
40    
41            /*
42            ** Get the database id.
43            */
44            select @dbid = 0
45            select @dbid = db_id(@dbname)
46            if @dbid is null
47            begin
48                /* 
49                ** 18322, "There is not a database named '%1!'."
50                */
51                raiserror 18322, @dbname
52                return (1)
53            end
54    
55            /*
56            ** Determine if we are mapping a temporary database.
57            */
58            select @tempdb_mask = number
59            from master.dbo.spt_values
60            where type = "D3" and name = "TEMPDB STATUS MASK"
61    
62            if (@dbid = 2) or exists (select * from master.dbo.sysdatabases
63                    where dbid = @dbid
64                        and (status3 & @tempdb_mask) != 0)
65            begin
66                select @isatempdb = 1
67            end
68    
69            /*
70            ** If the database is master, model or tempdb, disallow this.
71            */
72            if @dbname = "master" OR @dbname = "model" OR @isatempdb = 1
73            begin
74                /* 
75                ** 18327, "You cannot remap objects in the master, model, or tempdb databases."
76                */
77                raiserror 18327
78                return (1)
79            end
80    
81            /*
82            ** If the database is an IQ catalog database, disallow this.
83            */
84            exec @status = sp_iqdbcheck @dbname
85            if @status = 1
86            begin
87                return 1
88            end
89    
90            /*
91            ** Get a value @type from @objecttype
92            */
93            select @type = number from master.dbo.spt_values
94            where type = 'Y' and name = lower(@defaulttype)
95    
96            if @@rowcount = 0
97            begin
98                /* 
99                ** 18323, "There is not an object type named '%1!'."
100               */
101               raiserror 18323, @defaulttype
102               return (1)
103           end
104   
105           /*
106           ** If the defaultloc is NULL, then delete any default location that
107           ** may exist for this database.
108           */
109           if @defaultloc is NULL
110           begin
111               if exists (select * from master.dbo.sysdatabases
112                       where dbid = @dbid
113                           and def_remote_loc is null)
114               begin
115                   /* 
116                   ** 18328, "There is no default storage location in effect for database '%1!'."
117                   */
118                   raiserror 18328, @dbname
119                   return (1)
120               end
121               else
122               begin
123                   update master.dbo.sysdatabases
124                   set def_remote_loc = NULL,
125                       def_remote_type = NULL
126                   where dbid = @dbid
127   
128                   /* 
129                   ** 18329, "Default storage location deleted for database '%1!'."
130                   */
131                   exec sp_getmessage 18329, @msg output
132                   print @msg, @dbname
133                   return (0)
134               end
135           end
136   
137           /*
138           ** If the object type is not a file, the syntax of the location
139           ** field must be 'server.dbname.owner.', where server must be
140           ** provided, and dbname and/or owner may be omitted.
141           */
142           select @last_char = SUBSTRING(@defaultloc, CHAR_LENGTH(@defaultloc), 1)
143   
144           /*
145           ** If the type is FILE (2), then the last character must be a
146           ** ']', since the default loc must be an RMS filespec for a 
147           ** directory.
148           */
149   
150           if exists (select * from master.dbo.spt_values where type = 'E' and name = 'vms')
151           begin
152               if @type = 2
153               begin
154                   if @last_char != ']' AND @last_char != ':'
155                   begin
156                       /* 
157                       ** 18324, "An RMS filespec for a directory must be specified."
158                       */
159                       raiserror 18324
160                       return (1)
161                   end
162               end
163           end
164           /*
165           ** If the type is FILE (2), and we are on unix then we allow
166           ** anything and validate inside the omni code
167           */
168   
169           if @type != 2
170           begin
171               if @last_char != '.'
172               begin
173                   /* 
174                   ** 18325, "Storage location syntax must end in '.'."
175                   */
176                   raiserror 18325
177                   return (1)
178               end
179   
180               /*
181               ** Make sure the format is correct: "SERVER.db.owner.",
182               */
183               exec @status = sp_namecrack @defaultloc,
184                   @site output, @db output, @own output, @obj output
185   
186               if @status != 0 OR @obj IS NOT NULL OR @site IS NULL
187               begin
188                   /*
189                   ** 17240, "'%1!' is not a valid name."
190                   */
191                   raiserror 17240, @defaultloc
192                   return (1)
193               end
194               if not exists (select * from master.dbo.sysservers
195                       where srvname = @site)
196               begin
197                   /*
198                   ** 17800, "No such server -- run sp_helpserver to
199                   ** list servers."
200                   */
201                   raiserror 17800
202                   return (1)
203   
204               end
205           end
206   
207           if exists (select * from master.dbo.sysdatabases
208                   where dbid = @dbid and def_remote_loc is not null)
209           begin
210               select @oldloc = def_remote_loc from master.dbo.sysdatabases
211               where dbid = @dbid
212               /* 
213               ** 18330, "Database '%1!' already has a default location '%2!'."
214               */
215               raiserror 18330, @dbname, @oldloc
216               return (1)
217           end
218           else
219           begin
220               update master.dbo.sysdatabases
221               set def_remote_loc = @defaultloc,
222                   def_remote_type = @type
223               where dbid = @dbid
224               /* 
225               ** 18326, "Created default location for database '%1!' at '%2!'." 
226               */
227               exec sp_getmessage 18326, @msg output
228               print @msg, @dbname, @defaultloc
229           end
230   
231           return (0)
232       end
233   
234   
235   
236   


exec sp_procxmode 'sp_defaultloc', 'AnyMode'
go

Grant Execute on sp_defaultloc to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @oldloc: varchar(255) = varchar(349) 210
 MTYP 4 Assignment type mismatch def_remote_type: smallint = int 222
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
60
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
94
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
150
 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..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_defaultloc  
 MNER 3 No Error Check should check @@error after update 123
 MNER 3 No Error Check should check return value of exec 131
 MNER 3 No Error Check should check @@error after update 220
 MNER 3 No Error Check should check return value of exec 227
 MUCO 3 Useless Code Useless Begin-End Pair 27
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 231
 MUOT 3 Updates outside transaction 220
 QAFM 3 Var Assignment from potentially many rows 58
 QAFM 3 Var Assignment from potentially many rows 93
 VNRD 3 Variable is not read @db 184
 VNRD 3 Variable is not read @own 184
 MSUB 2 Subquery Marker 62
 MSUB 2 Subquery Marker 111
 MSUB 2 Subquery Marker 150
 MSUB 2 Subquery Marker 194
 MSUB 2 Subquery Marker 207
 MTR1 2 Metrics: Comments Ratio Comments: 45% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 27dec - 12exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 102 22

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_iqdbcheck  
reads table master..sysservers (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)  
reads table master..spt_values (1)  
read_writes table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_namecrack