| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_defaultloc | | 31 Aug 14 | Defects 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
| 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 |
