Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dropserver ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/defaultlanguage */ 4 5 /* 6 ** Messages for "sp_dropserver" 17530 7 ** 8 ** 17260, "Can't run %1! from within a transaction." 9 ** 17270, "There is not a server named '%1!'." 10 ** 17530, "There are still remote logins for the server '%1!'." 11 ** 17531, "Remote logins for remote server '%1!' have been dropped." 12 ** 17532, "Usage: sp_dropserver server [, droplogins]" 13 ** 17533, "Server dropped." 14 ** 17534, "There are still external logins for the server '%1!'." 15 ** 17535, "External logins for remote server '%1!' have been dropped." 16 ** 17536, "Unable to drop server '%1!' because it is referenced in master.dbo.sysdatabases." 17 ** 17537, "Unable to drop server '%1!' because it is referenced by 18 ** transaction coordinator." 19 ** 17869, "Stored procedure %1 failed because %2 failed in database %3." 20 ** 18388, "You must be in the master database in order to run '%1'!." 21 ** 18409, "The built-in function getdbrepstat() failed." 22 ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'." 23 ** 18782, "Unable to find a server with name '%1!' and id '%2!'." 24 ** 18783, "You cannot drop the companion server '%1!' in the companion mode because it is configured as a node of HA cluster." 25 ** 18785, "Unable to drop the local server '%1!' because it is in HA companion mode." 26 ** 19660, "Unable to drop server '%1!' because it stores information of existing cluster instance '%2!'." 27 */ 28 29 /* 30 ** IMPORTANT: Please read the following instructions before 31 ** making changes to this stored procedure. 32 ** 33 ** To make this stored procedure compatible with High Availability (HA), 34 ** changes to certain system tables must be propagated 35 ** to the companion server under some conditions. 36 ** The tables include (but are not limited to): 37 ** syslogins, sysservers, sysattributes, systimeranges, 38 ** sysresourcelimits, sysalternates, sysdatabases, 39 ** syslanguages, sysremotelogins, sysloginroles, 40 ** sysalternates (master DB only), systypes (master DB only), 41 ** sysusers (master DB only), sysprotects (master DB only) 42 ** please refer to the HA documentation for detail. 43 ** 44 ** Here is what you need to do: 45 ** For each insert/update/delete statement, add three sections to 46 ** -- start HA transaction prior to the statement 47 ** -- add the statement 48 ** -- add HA synchronization code to propagate the change to the companion 49 ** 50 ** For example, if you are adding 51 ** insert master.dbo.syslogins ...... 52 ** the code should look like: 53 ** 1. Now, the SQL statement: 54 ** insert master.dbo.syslogins ...... 55 ** 2. Add a HA synchronization section right after the SQL statement: 56 ** 57 ** 58 ** You may need to do similar change for each built-in function you 59 ** want to add. 60 */ 61 62 create procedure sp_dropserver 63 @server varchar(255), /* server name */ 64 @droplogins char(10) = NULL /* drop all related logins? */ 65 as 66 67 declare @msg varchar(1024), 68 @srvid smallint, 69 @retstat int 70 71 declare @srvclass smallint /* Class id of the server */ 72 declare @command varchar(30) /* Command passed to sp_extengine */ 73 declare @physname varchar(255) /* Class name of the external engine class */ 74 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 75 declare @inst_id int /* Cluster instance id */ 76 declare @status2 unsigned int /* To store 'srvstatus2' column value */ 77 declare @insysattributes int /* Is the rtds provider in sysattributes ? */ 78 declare @outstr varchar(255), /* for SDC dbcc set_scope */ 79 @scope varchar(16) /* for SDC dbcc set_scope */ 80 declare @dbname varchar(255) /* for logexec failure message */ 81 declare @master_is_rep int /* whether master db is replicated */ 82 83 select @HA_CERTIFIED = 0 84 select @insysattributes = 0 85 86 87 88 89 /* check to see if we are using HA specific SP for a HA enabled server */ 90 exec @retstat = sp_ha_check_certified 'sp_dropserver', @HA_CERTIFIED 91 if (@retstat != 0) 92 return (1) 93 94 /* 95 ** If we are logging this system procedure for replication, 96 ** we must be in the 'master' database to avoid creating a 97 ** multi-database transaction which could make recovery of 98 ** the 'master' database impossible. 99 */ 100 select @master_is_rep = getdbrepstat(1) 101 102 if (@master_is_rep < 0) 103 begin 104 /* 105 ** 18409, "The built-in function getdbrepstat() failed." 106 */ 107 raiserror 18409, "getdbrepstat" 108 return (1) 109 end 110 111 select @dbname = db_name() 112 113 if (@master_is_rep > 0) and (@dbname != "master") 114 begin 115 /* 116 ** 18388, "You must be in the master database in order 117 ** to run '%1!'." 118 */ 119 raiserror 18388, "sp_dropserver" 120 return (1) 121 end 122 123 /* 124 ** If we're in a transaction, disallow this since it might make recovery 125 ** impossible. 126 */ 127 if @@trancount > 0 128 begin 129 /* 130 ** 17260, "Can't run %1! from within a transaction." 131 */ 132 raiserror 17260, "sp_dropserver" 133 return (1) 134 end 135 else 136 begin 137 set chained off 138 end 139 140 set transaction isolation level 1 141 142 /* check if user has sso role, proc_role will also do auditing 143 ** if required. proc_role will also print error message if required. 144 */ 145 146 if (proc_role("sso_role") = 0) 147 return (1) 148 149 /* 150 ** Check to see if the server exists. 151 */ 152 if not exists (select * from master.dbo.sysservers 153 where srvname = @server) 154 begin 155 /* 156 ** 17270, "There is not a server named '%1!'." 157 */ 158 raiserror 17270, @server 159 return (1) 160 end 161 162 /* 163 ** Check to see if this is a rtds provider which 164 ** is registered in sysattributes. 165 */ 166 select @srvclass = srvclass from master.dbo.sysservers 167 where srvname = @server 168 if (@srvclass in (12, 13, 14, 15)) 169 and (exists (select 1 from master.dbo.sysattributes 170 where class = 21 and attribute = 10 171 and object_type = 'PR' 172 and object_cinfo = @server)) 173 select @insysattributes = 1 174 else 175 select @insysattributes = 0 176 177 178 /* 179 ** Check to see if server stores cluster instance information 180 */ 181 select @status2 = srvstatus2 from master.dbo.sysservers where srvname = @server 182 if ((@status2 & 4) != 0) 183 begin 184 select @inst_id = instance_id(@server) 185 if @inst_id is not NULL 186 begin 187 /* 188 ** 19660, "Unable to drop server '%1!' 189 ** because it stores information of 190 ** existing cluster instance '%2!'." 191 */ 192 raiserror 19660, @server, @inst_id 193 return (1) 194 end 195 end 196 197 /* 198 ** OMNI: check to see if there is a default location referenced in sysdatabases 199 */ 200 if exists (select * from master.dbo.sysdatabases where 201 substring(def_remote_loc, 1, 202 charindex('.', def_remote_loc) - 1) = @server) 203 begin 204 /* 205 ** 17536, "Unable to drop server '%1!' because it is referenced 206 ** in master.dbo.sysdatabases." 207 */ 208 raiserror 17536, @server 209 return (1) 210 end 211 212 213 214 /* 215 ** This transaction also writes a log record for replicating the 216 ** invocation of this procedure. If logexec() fails, the transaction 217 ** is aborted. 218 ** 219 ** IMPORTANT: The name rs_logexec is significant and is used by 220 ** Adaptive Server. 221 */ 222 begin tran rs_logexec 223 224 /* 225 ** Do more consistency checks to ensure that transaction coordinator 226 ** is not using the server entry being dropped. 227 */ 228 229 /* 230 ** Lock syscoordinations to synchronize with dtm service. This type of 231 ** locking on syscoordinations should be done only in extreme case like 232 ** dropserver as this will potentially block dtm services. 233 */ 234 lock table sybsystemdb.dbo.syscoordinations in share mode 235 236 237 238 if exists (select * from sybsystemdb.dbo.syscoordinations c, 239 master.dbo.sysservers s 240 where s.srvname = @server and 241 s.srvid = c.participant and c.owner = 1) 242 begin 243 /* 244 ** 17537, "Unable to drop server '%1!' because it is 245 ** referenced by transaction coordinator." 246 */ 247 raiserror 17537, @server 248 goto clean_all 249 end 250 251 252 /* Check to see if there are any related logins in sysremotelogins. */ 253 if (@droplogins is NULL) 254 begin 255 if exists (select * 256 from master.dbo.sysremotelogins l, 257 master.dbo.sysservers s 258 where s.srvid = l.remoteserverid 259 and s.srvname = @server) 260 begin 261 /* 262 ** 17530, "There are still remote logins for the server '%1!'." 263 */ 264 raiserror 17530, @server 265 goto clean_all 266 end 267 268 /* 269 ** OMNI: Check to see if there are any related external logins 270 */ 271 if exists (select * from master.dbo.sysattributes a, 272 master.dbo.sysservers s 273 where s.srvid = a.object_info1 and 274 s.srvname = @server and 275 a.class = 9 and a.attribute = 0) 276 begin 277 /* 278 ** 17534, "There are still external logins for the 279 ** server '%1!'." 280 */ 281 raiserror 17534, @server 282 goto clean_all 283 end 284 285 286 287 end 288 289 /* 290 ** If @droplogins is true then drop any associated logins 291 */ 292 else if @droplogins = "droplogins" 293 begin 294 delete master.dbo.sysremotelogins 295 from master.dbo.sysremotelogins l, 296 master.dbo.sysservers s 297 where s.srvid = l.remoteserverid 298 and s.srvname = @server 299 300 if (@@rowcount > 0) 301 begin 302 303 304 /* 305 ** 17531, "Remote logins for remote server '%1!' 306 ** have been dropped." 307 */ 308 exec sp_getmessage 17531, @msg output 309 print @msg, @server 310 end 311 312 /* 313 ** OMNI: Drop any external logins as well 314 */ 315 delete master.dbo.sysattributes 316 from master.dbo.sysattributes a, 317 master.dbo.sysservers s 318 where s.srvname = @server 319 and s.srvid = a.object_info1 320 and a.class = 9 and a.attribute = 0 321 322 if (@@rowcount > 0) 323 begin 324 325 326 /* 327 ** 17535, "External logins for remote server '%1!' 328 ** have been dropped." 329 */ 330 exec sp_getmessage 17535, @msg output 331 print @msg, @server 332 end 333 334 /* Continue below and drop the server */ 335 end 336 337 /* 338 ** Bad argument to @droplogins. 339 */ 340 else 341 begin 342 /* 343 ** 17532, "Usage: sp_dropserver server [, droplogins]" 344 */ 345 raiserror 17532 346 goto clean_all 347 end 348 349 /* 350 ** Hang up the connection to the server if there is one 351 */ 352 dbcc connection_hangup(@server) 353 354 355 356 /* 357 ** Fetch the srvid for this server 358 */ 359 select @srvid = srvid 360 from master.dbo.sysservers 361 where srvname = @server 362 363 /* 364 ** If the server belongs to the ejb class make sure the server is stopped 365 ** before the entry from sysserver is deleted 366 */ 367 368 select @srvclass = srvclass, @physname = srvnetname 369 from master.dbo.sysservers 370 where srvname = @server 371 372 if @srvclass = 10 373 begin 374 select @command = "STOP" 375 /* 376 ** We go ahead and stop the ejb server when the sp_dropserver command 377 ** is executed on an ejb server 378 */ 379 dbcc extengine(@physname, @srvclass, @command) 380 end 381 382 /* 383 ** Drop the server. 384 */ 385 delete master.dbo.sysservers 386 where srvname = @server 387 if (@@error != 0) 388 begin 389 goto clean_all 390 end 391 392 /* 393 ** Drop the rtds provider registered in sysattributes 394 */ 395 if (@insysattributes = 1) 396 delete master.dbo.sysattributes 397 where class = 21 and attribute = 10 398 and object_type = 'PR' 399 and object_cinfo = @server 400 401 402 403 /* 404 ** For SDC, update cluster-wide in-memory SRVDES with data from 405 ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc command scope 406 ** needs to be set to cluster. 407 */ 408 if (@@clustermode = "shared disk cluster") 409 begin 410 select @scope = NULL 411 select @outstr = "dbcc set_scope_in_cluster('scope')" 412 if (charindex("instance", @outstr) != 0) 413 begin 414 /* save the scope to be restored later */ 415 select @scope = "instance" 416 dbcc set_scope_in_cluster('cluster') 417 end 418 end 419 420 /* 421 ** Tag in-memory copy as 422 ** unusable. 423 */ 424 425 dbcc cis("srvdes", @srvid) 426 427 /* restore dbcc command scope */ 428 if (@@clustermode = "shared disk cluster") 429 begin 430 if (@scope = "instance") 431 begin 432 dbcc set_scope_in_cluster('instance') 433 end 434 end 435 436 /* 437 ** log the command for replication support 438 */ 439 if (@master_is_rep > 0) 440 begin 441 if (logexec(1) != 1) 442 begin 443 /* 444 ** , "17869 Stored procedure %1 failed' 445 ** because '%2' failed in database '%3'." 446 */ 447 raiserror 17869, "sp_addserver", "logexec()", @dbname 448 goto clean_all 449 end 450 end 451 452 commit tran rs_logexec 453 454 /* 455 ** 17533, "Server dropped." 456 */ 457 exec sp_getmessage 17533, @msg output 458 print @msg 459 460 return (0) 461 462 clean_all: 463 rollback tran rs_logexec 464 return (1) 465 466
exec sp_procxmode 'sp_dropserver', 'AnyMode' go Grant Execute on sp_dropserver to public go
DEFECTS | |
![]() | master..sysattributes |
![]() | master..sysremotelogins |
![]() (xactkey, participant, owner) Intersection: {owner} | 241 |
![]() | 170 |
![]() | 241 |
![]() | 273 |
![]() | 275 |
![]() | 319 |
![]() | 320 |
![]() | 397 |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 294 |
![]() | 308 |
![]() | 315 |
![]() | 330 |
![]() | 396 |
![]() | 457 |
![]() | 91 |
![]() | 92 |
![]() | 102 |
![]() | 108 |
![]() | 120 |
![]() | 133 |
![]() | 146 |
![]() | 147 |
![]() | 159 |
![]() | 182 |
![]() | 193 |
![]() | 209 |
![]() | 253 |
![]() | 300 |
![]() | 322 |
![]() | 387 |
![]() | 395 |
![]() | 408 |
![]() | 412 |
![]() | 428 |
![]() | 430 |
![]() | 439 |
![]() | 441 |
![]() | 460 |
![]() | 464 |
![]() | 140 |
![]() | 238 |
![]() | 256 |
![]() | 271 |
![]() | 295 |
![]() | 316 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 170 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 275 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 320 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 397 |
![]() | 294 |
![]() | 152 |
![]() | 169 |
![]() | 200 |
![]() | 238 |
![]() | 255 |
![]() | 271 |
![]() | 62 |
![]() | 62 |
![]() | 62 |
![]() | 238 |
![]() | 255 |
![]() | 271 |
![]() | 294 |
![]() | 315 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysremotelogins (1) ![]() calls proc sybsystemprocs..sp_ha_check_certified ![]() reads table tempdb..sysobjects (1) ![]() 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) ![]() read_writes table master..sysservers (1) ![]() reads table master..sysdatabases (1) ![]() read_writes table master..sysattributes (1) ![]() read_writes table sybsystemdb..syscoordinations (1) ![]() |