Database | Proc | Application | Created | Links |
sybsystemprocs | sp_helpserver ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/fixindex */ 4 /* 5 ** Messages for "sp_helpserver" 6 ** 7 ** 17270, "There is not a server named '%1!'." 8 ** 17680, "There are no remote servers defined." 9 ** 19958, "Backup Server policy is set to '%1!'." 10 */ 11 create procedure sp_helpserver 12 @server varchar(255) = NULL /* server name */ 13 as 14 15 declare @msg varchar(1024) 16 declare @len1 int, @len2 int 17 declare @bs_policy varchar(255) 18 19 begin 20 21 if @@trancount = 0 22 begin 23 set chained off 24 end 25 26 set transaction isolation level 1 27 28 set nocount on 29 30 /* 31 ** If no server name given, get 'em all. 32 */ 33 if @server is null 34 begin 35 select @server = "%" 36 end 37 38 if @server = "SYB_BACKUP" 39 begin 40 select @bs_policy = srvnetname from master..sysservers where srvname = "SYB_BACKUP" 41 42 if ((lower(@bs_policy) = "$dedicated") or (lower(@bs_policy) = "$roundrobin")) 43 begin 44 select @server = "%_BS" 45 46 /* 19958, "Backup Server policy is set to '%1!'." */ 47 exec sp_getmessage 19958, @msg output 48 print @msg, @bs_policy 49 end 50 end 51 52 /* 53 ** Does the server exist? 54 */ 55 if not exists (select * 56 from master.dbo.sysservers 57 where srvname like @server 58 or srvnetname like @server) 59 begin 60 if @server = "%" 61 begin 62 /* 17680, "There are no remote servers defined." */ 63 exec sp_getmessage 17680, @msg output 64 print @msg 65 end 66 else 67 /* 17270, "There is not a server named '%1!'." */ 68 raiserror 17270, @server 69 return (1) 70 end 71 72 /* 73 ** Display server information. 74 ** First put it into a temp table so we can examine the status 75 ** bits and turn them into english. 76 */ 77 create table #spt_server 78 ( 79 name varchar(255), 80 network varchar(32), 81 secmech varchar(255) NULL, 82 principal varchar(255) NULL, 83 class varchar(15) NULL, 84 status varchar(255), 85 id smallint, 86 cost smallint NULL 87 ) 88 89 /* 90 ** Initialize #spt_server from sysservers. 91 */ 92 insert into #spt_server 93 select s.srvname, s.srvnetname, s.srvsecmech, s.srvprincipal, v.name, "", 94 id = s.srvid, s.srvcost 95 from master.dbo.sysservers s, master.dbo.spt_values v 96 where (s.srvname like @server 97 or s.srvnetname like @server) 98 and s.srvclass *= v.number 99 and v.type = 'X' and v.name != 'access_server' 100 101 /* 102 ** Now for each nonlocal server in #spt_server, build the server 103 ** status description. Logic patterned after helpdb. 104 */ 105 106 declare @cursrvid smallint /* the current server we're working on. */ 107 declare @srvdesc varchar(255) /* the server description string. */ 108 declare @bitdesc varchar(30) /* the bit description for the server */ 109 110 /* 111 ** Set @cursrvid to the first server id. 112 */ 113 select @cursrvid = min(id) 114 from #spt_server 115 where id != 0 116 117 /* 118 ** Parse the status. 119 */ 120 while @cursrvid is not NULL 121 begin 122 /* 123 ** Initialize @srvdesc. 124 */ 125 select @srvdesc = "" 126 127 /* 128 ** Check timeout bit. Note special kludgy handling 129 ** due to previous definition of timeout and no timeout 130 ** in spt_values. 131 */ 132 select @bitdesc = null 133 select @bitdesc = v.name 134 from master.dbo.spt_values v 135 where v.type = "A" 136 and v.number = 0 137 138 if exists (select * 139 from master.dbo.spt_values v, master.dbo.sysservers s 140 where s.srvid = @cursrvid 141 and s.srvstatus & v.number = 1) 142 begin 143 select @srvdesc = "no " + @bitdesc 144 end 145 else 146 begin 147 select @srvdesc = @bitdesc 148 end 149 150 /* 151 ** Check network password encryption status. 152 */ 153 select @bitdesc = null 154 select @bitdesc = v.name 155 from master.dbo.spt_values v 156 where v.type = "A" 157 and v.number = 2 158 159 if exists (select * 160 from master.dbo.spt_values v, master.dbo.sysservers s 161 where s.srvid = @cursrvid 162 and s.srvstatus & v.number = 2) 163 begin 164 if @srvdesc != "" 165 select @srvdesc = @srvdesc + ", " + @bitdesc 166 else 167 select @srvdesc = @bitdesc 168 end 169 else 170 begin 171 if @srvdesc != "" 172 select @srvdesc = @srvdesc + ", no " + @bitdesc 173 else 174 select @srvdesc = "no " + @bitdesc 175 end 176 177 /* 178 ** if OMNI is configured, handle the readonly status 179 */ 180 if exists (select * from master.dbo.sysconfigures cf, 181 master.dbo.syscurconfigs cc 182 where cf.config = cc.config 183 and cc.value = 1 184 and cf.name = 'enable cis') 185 begin 186 select @bitdesc = null 187 select @bitdesc = v.name 188 from master.dbo.spt_values v 189 where v.type = "A" 190 and v.number = 4 191 if exists (select * 192 from master.dbo.spt_values v, master.dbo.sysservers s 193 where s.srvid = @cursrvid 194 and s.srvstatus & v.number = 4) 195 begin 196 if @srvdesc != "" 197 select @srvdesc = @srvdesc + ", " + @bitdesc 198 else 199 select @srvdesc = @bitdesc 200 end 201 else 202 begin 203 if @srvdesc != "" 204 select @srvdesc = @srvdesc + ", writable " 205 else 206 select @srvdesc = "writable " 207 end 208 end 209 210 /* 211 ** Further check security options namely, 212 ** "use confidentiality", "use message integrity", or 213 ** "mutual authentication". Notice that these three options 214 ** can co-exist for a given remote server. 215 */ 216 select @bitdesc = null 217 select @bitdesc = v.name 218 from master.dbo.spt_values v 219 where v.type = "A" 220 and v.number = 64 221 222 if exists (select * 223 from master.dbo.spt_values v, master.dbo.sysservers s 224 where s.srvid = @cursrvid 225 and s.srvstatus & v.number = 64) 226 227 begin 228 /* 229 ** We are sure that @srvdesc is not empty at this point. 230 */ 231 select @srvdesc = @srvdesc + ", " + @bitdesc 232 end 233 234 select @bitdesc = null 235 select @bitdesc = v.name 236 from master.dbo.spt_values v 237 where v.type = "A" 238 and v.number = 128 239 240 if exists (select * 241 from master.dbo.spt_values v, master.dbo.sysservers s 242 where s.srvid = @cursrvid 243 and s.srvstatus & v.number = 128) 244 245 begin 246 /* 247 ** We are sure that @srvdesc is not empty at this point. 248 */ 249 select @srvdesc = @srvdesc + ", " + @bitdesc 250 end 251 252 select @bitdesc = null 253 select @bitdesc = v.name 254 from master.dbo.spt_values v 255 where v.type = "A" 256 and v.number = 256 257 258 if exists (select * 259 from master.dbo.spt_values v, master.dbo.sysservers s 260 where s.srvid = @cursrvid 261 and s.srvstatus & v.number = 256) 262 263 begin 264 /* 265 ** We are sure that @srvdesc is not empty at this point. 266 */ 267 select @srvdesc = @srvdesc + ", " + @bitdesc 268 end 269 270 /* 271 ** Check "cis hafailover". 272 */ 273 select @bitdesc = null 274 select @bitdesc = v.name 275 from master.dbo.spt_values v 276 where v.type = "A" 277 and v.number = 32 278 279 if exists (select * 280 from master.dbo.spt_values v, master.dbo.sysservers s 281 where s.srvid = @cursrvid 282 and s.srvstatus & v.number = 32) 283 begin 284 if @srvdesc != "" 285 select @srvdesc = @srvdesc + ", " + @bitdesc 286 else 287 select @srvdesc = @bitdesc 288 end 289 290 /* 291 ** Check for 'server logins' 292 */ 293 select @bitdesc = null 294 select @bitdesc = v.name 295 from master.dbo.spt_values v 296 where v.type = "A" 297 and v.number = 512 298 299 if exists (select * 300 from master.dbo.spt_values v, master.dbo.sysservers s 301 where s.srvid = @cursrvid 302 and s.srvstatus & v.number = 512) 303 begin 304 if @srvdesc != "" 305 select @srvdesc = @srvdesc + ", " + @bitdesc 306 else 307 select @srvdesc = @bitdesc 308 end 309 310 /* 311 ** Check for 'external engine auto start', this option is for the 312 ** EJB Class of servers only hence when reporting the various servers 313 ** for the ejb server only this option should be shown in the status 314 */ 315 select @bitdesc = null 316 select @bitdesc = v.name 317 from master.dbo.spt_values v 318 where v.type = "A" 319 and v.number = 1024 320 321 if exists (select * 322 from master.dbo.spt_values v, master.dbo.sysservers s 323 where s.srvid = @cursrvid 324 and s.srvstatus & v.number = 1024) 325 begin 326 select @srvdesc = @bitdesc 327 end 328 else 329 begin 330 if exists (select * from master.dbo.sysservers s 331 where s.srvid = @cursrvid and s.srvclass = 10) 332 begin 333 select @srvdesc = "" 334 end 335 end 336 337 /* 338 ** Check for 'negotiated logins' 339 */ 340 select @bitdesc = null 341 select @bitdesc = v.name from master.dbo.spt_values v 342 where v.type = 'A' and v.number = 4096 343 if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s 344 where s.srvid = @cursrvid and s.srvstatus & v.number = 4096) 345 begin 346 if @srvdesc != "" 347 select @srvdesc = @srvdesc + ", " + @bitdesc 348 else 349 select @srvdesc = @bitdesc 350 end 351 352 /* 353 ** Check for 'relocated joins' 354 */ 355 select @bitdesc = null 356 select @bitdesc = v.name from master.dbo.spt_values v 357 where v.type = 'A2' and v.number = 1 358 if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s 359 where s.srvid = @cursrvid and s.srvstatus2 & v.number = 1) 360 begin 361 if @srvdesc != "" 362 select @srvdesc = @srvdesc + ", " + @bitdesc 363 else 364 select @srvdesc = @bitdesc 365 end 366 367 /* 368 ** Check for 'incompatible sort order' 369 */ 370 select @bitdesc = null 371 select @bitdesc = v.name from master.dbo.spt_values v 372 where v.type = 'A2' and v.number = 8 373 if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s 374 where s.srvid = @cursrvid and s.srvstatus2 & v.number = 8) 375 begin 376 if @srvdesc != "" 377 select @srvdesc = @srvdesc + ", " + @bitdesc 378 else 379 select @srvdesc = @bitdesc 380 end 381 382 /* 383 ** Check for 'enable login redirection' 384 */ 385 select @bitdesc = null 386 select @bitdesc = v.name from master.dbo.spt_values v 387 where v.type = 'A2' and v.number = 2 388 if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s 389 where s.srvid = @cursrvid and s.srvstatus2 & v.number = 2) 390 begin 391 if @srvdesc != "" 392 select @srvdesc = @srvdesc + ", " + @bitdesc 393 else 394 select @srvdesc = @bitdesc 395 end 396 /* 397 ** Check for 'cluster instance' 398 */ 399 select @bitdesc = null 400 select @bitdesc = v.name from master.dbo.spt_values v 401 where v.type = 'A2' and v.number = 4 402 if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s 403 where s.srvid = @cursrvid and s.srvstatus2 & v.number = 4) 404 begin 405 if @srvdesc != "" 406 select @srvdesc = @srvdesc + ", " + @bitdesc 407 else 408 select @srvdesc = @bitdesc 409 end 410 411 /* 412 ** Save the description. 413 */ 414 update #spt_server 415 set status = @srvdesc 416 from #spt_server 417 where id = @cursrvid 418 419 /* 420 ** Now get the next, if any, server id. 421 */ 422 select @cursrvid = min(id) 423 from #spt_server 424 where id > @cursrvid 425 end 426 427 /* 428 ** Display the information we got. 429 */ 430 if exists (select * from master.dbo.sysconfigures cf, 431 master.dbo.syscurconfigs cc 432 where cf.config = cc.config 433 and cc.value = 1 434 and cf.name = 'enable cis') 435 begin 436 /* 437 ** OMNI is configured, display the server class and cost as well 438 */ 439 440 exec sp_autoformat @fulltabname = #spt_server, 441 @selectlist = " name, 'network_name' = network, 'security_mechanism' = secmech, 'server_principal' = principal, class, status,id, cost", 442 @orderby = "order by name" 443 end 444 else 445 begin 446 447 exec sp_autoformat @fulltabname = #spt_server, 448 @selectlist = " name, 'network_name' = network, 'security_mechanism' = secmech, status,id", 449 @orderby = "order by name" 450 end 451 452 return (0) 453 end 454
exec sp_procxmode 'sp_helpserver', 'AnyMode' go Grant Execute on sp_helpserver to public go
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 master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() calls proc sybsystemprocs..sp_autoformat ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..syscolumns (1) ![]() reads table tempdb..syscolumns (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() reads table master..systypes (1) ![]() read_writes table tempdb..#colinfo_af (1) reads table tempdb..systypes (1) ![]() reads table master..spt_values (1) ![]() reads table master..sysservers (1) ![]() reads table master..sysconfigures (1) ![]() read_writes table tempdb..#spt_server (1) reads table master..syscurconfigs (1) ![]() |