DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropserver  31 Aug 14Defects 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
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysremotelogins master..sysremotelogins
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscoordinations.csyscoordinations unique clustered
(xactkey, participant, owner)
Intersection: {owner}
241
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 170
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 241
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 273
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 275
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 319
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 320
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 397
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysremotelogins  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemdb..syscoordinations  
 MGTP 3 Grant to public sybsystemprocs..sp_dropserver  
 MNER 3 No Error Check should check @@error after delete 294
 MNER 3 No Error Check should check return value of exec 308
 MNER 3 No Error Check should check @@error after delete 315
 MNER 3 No Error Check should check return value of exec 330
 MNER 3 No Error Check should check @@error after delete 396
 MNER 3 No Error Check should check return value of exec 457
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 387
 MUCO 3 Useless Code Useless Brackets 395
 MUCO 3 Useless Code Useless Brackets 408
 MUCO 3 Useless Code Useless Brackets 412
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 430
 MUCO 3 Useless Code Useless Brackets 439
 MUCO 3 Useless Code Useless Brackets 441
 MUCO 3 Useless Code Useless Brackets 460
 MUCO 3 Useless Code Useless Brackets 464
 QISO 3 Set isolation level 140
 QNAJ 3 Not using ANSI Inner Join 238
 QNAJ 3 Not using ANSI Inner Join 256
 QNAJ 3 Not using ANSI Inner Join 271
 QNAJ 3 Not using ANSI Inner Join 295
 QNAJ 3 Not using ANSI Inner Join 316
 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_cinfo, attribute, class}
170
 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: {class, attribute}
275
 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: {class, attribute}
320
 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_cinfo, attribute, class}
397
 QTJ1 3 Table only appears in inner join clause 294
 MSUB 2 Subquery Marker 152
 MSUB 2 Subquery Marker 169
 MSUB 2 Subquery Marker 200
 MSUB 2 Subquery Marker 238
 MSUB 2 Subquery Marker 255
 MSUB 2 Subquery Marker 271
 MTR1 2 Metrics: Comments Ratio Comments: 56% 62
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 32 = 37dec - 7exi + 2 62
 MTR3 2 Metrics: Query Complexity Complexity: 183 62
 PRED_QUERY_COLLECTION 2 {c=sybsystemdb..syscoordinations, s=master..sysservers} 0 238
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 255
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, s=master..sysservers} 0 271
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 294
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, s=master..sysservers} 0 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)