DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropserver  14 déc. 14Defects Propagation 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 or replace 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        declare @log_for_rep int /* whether the sproc should be replicated */
83        declare @retstat1 int
84        declare @retstat2 int
85        declare @nullarg varchar(1) /* Null argument */
86        declare @dummy int
87        declare @gp_enabled int
88    
89    
90        select @HA_CERTIFIED = 0
91        select @insysattributes = 0
92        select @retstat2 = 0
93    
94    
95    
96    
97    
98        /* check to see if we are using HA specific SP for a HA enabled server */
99        exec @retstat = sp_ha_check_certified 'sp_dropserver', @HA_CERTIFIED
100       if (@retstat != 0)
101           return (1)
102   
103       /*
104       ** If we are logging this system procedure for replication,
105       ** we must be in the 'master' database to avoid creating a
106       ** multi-database transaction which could make recovery of
107       ** the 'master' database impossible.
108       */
109       select @master_is_rep = getdbrepstat(1)
110   
111       if (@master_is_rep < 0)
112       begin
113           /*
114           ** 18409, "The built-in function getdbrepstat() failed."
115           */
116           raiserror 18409, "getdbrepstat"
117           return (1)
118       end
119   
120       select @dbname = db_name()
121   
122       if (@master_is_rep > 0) and (@dbname != "master")
123       begin
124           /*
125           ** 18388, "You must be in the master database in order
126           ** to run '%1!'."
127           */
128           raiserror 18388, "sp_dropserver"
129           return (1)
130       end
131   
132       /*
133       ** The execution of sp_dropserver should not be replicated when 
134       ** the server is an HADR member.
135       */
136       if exists (select * from master.dbo.sysservers
137               where (srvname = @server or srvname = @server + "DR")
138                   and srvclass = (select number from master.dbo.spt_values
139                       where type = 'X' and
140                           lower(name) = lower("HADR_MEMBER")))
141       begin
142           select @log_for_rep = 0
143       end
144       else
145       begin
146           select @log_for_rep = 1
147       end
148   
149       /*
150       **  If we're in a transaction, disallow this since it might make recovery
151       **  impossible.
152       */
153       if @@trancount > 0
154       begin
155           /*
156           ** 17260, "Can't run %1! from within a transaction." 
157           */
158           raiserror 17260, "sp_dropserver"
159           return (1)
160       end
161       else
162       begin
163           set chained off
164       end
165   
166       set transaction isolation level 1
167   
168       /*
169       ** If granular permissions is not enabled, the user must have sso_role.
170       ** If granular permissions is enabled then the user must have 'manage
171       ** server' permission and if the cluster mode is "shared disk
172       ** cluster" then the user must also have 'manage cluster' permission.
173       ** proc_role() and proc_auditperm() will do auditing.
174       */
175   
176       select @nullarg = NULL
177       select @retstat = 0
178       select @retstat1 = 0
179       select @retstat2 = 0
180       execute @retstat = sp_aux_checkroleperm "sso_role",
181           "manage server", @nullarg, @gp_enabled output
182   
183       if ((@gp_enabled = 0) and (proc_role("sso_role") = 0))
184           return (1)
185   
186       if (@gp_enabled = 1)
187       begin
188           /* 
189           ** manage server (+ manage any remote login when droplogins is 
190           ** specified) (+ manage cluster when @@clustermode = "shared disk cluster".)
191           */
192           select @dummy = proc_auditperm("manage server", @retstat)
193   
194           if @retstat != 0
195           begin
196               return 1
197           end
198   
199           if (@@clustermode = "shared disk cluster")
200           begin
201               execute @retstat1 = sp_aux_checkroleperm
202                   @nullarg, "manage cluster", @nullarg, @gp_enabled output
203               /* Audit */
204               select @dummy = proc_auditperm("manage cluster", @retstat1)
205               if @retstat1 != 0
206               begin
207                   return 1
208               end
209           end
210   
211           if (@droplogins = "droplogins")
212           begin
213               /*
214               ** must have 'manage any remote login' when droplogins is
215               ** specified in the command.
216               */
217               execute @retstat2 = sp_aux_checkroleperm
218                   @nullarg, "manage any remote login", @nullarg,
219                   @gp_enabled output
220   
221               select @dummy =
222                   proc_auditperm("manage any remote login", @retstat2)
223   
224               if @retstat2 != 0
225               begin
226                   return 1
227               end
228           end
229       end
230   
231   
232       /*
233       **  Check to see if the server exists.
234       */
235       if not exists (select * from master.dbo.sysservers
236               where srvname = @server)
237       begin
238           /*
239           ** 17270, "There is not a server named '%1!'."
240           */
241           raiserror 17270, @server
242           return (1)
243       end
244   
245       /*
246       ** Check to see if this is a rtds provider which
247       ** is registered in sysattributes.
248       */
249       select @srvclass = srvclass from master.dbo.sysservers
250       where srvname = @server
251       if (@srvclass in (12, 13, 14, 15))
252           and (exists (select 1 from master.dbo.sysattributes
253                   where class = 21 and attribute = 10
254                       and object_type = 'PR'
255                       and object_cinfo = @server))
256           select @insysattributes = 1
257       else
258           select @insysattributes = 0
259   
260   
261       /*
262       ** Check to see if server stores cluster instance information 
263       */
264       select @status2 = srvstatus2 from master.dbo.sysservers where srvname = @server
265       if ((@status2 & 4) != 0)
266       begin
267           select @inst_id = instance_id(@server)
268           if @inst_id is not NULL
269           begin
270               /*
271               ** 19660, "Unable to drop server '%1!' 
272               ** because it stores information of 
273               ** existing cluster instance '%2!'."
274               */
275               raiserror 19660, @server, @inst_id
276               return (1)
277           end
278       end
279   
280       /*
281       ** OMNI: check to see if there is a default location referenced in sysdatabases
282       */
283       if exists (select * from master.dbo.sysdatabases where
284                   substring(def_remote_loc, 1,
285                       charindex('.', def_remote_loc) - 1) = @server)
286       begin
287           /*
288           ** 17536, "Unable to drop server '%1!' because it is referenced 
289           ** in master.dbo.sysdatabases."
290           */
291           raiserror 17536, @server
292           return (1)
293       end
294   
295   
296   
297       /* 
298       ** This transaction also writes a log record for replicating the
299       ** invocation of this procedure. If logexec() fails, the transaction
300       ** is aborted.
301       **
302       ** IMPORTANT: The name rs_logexec is significant and is used by
303       ** Adaptive Server.
304       */
305       begin tran rs_logexec
306   
307       /* 
308       ** Do more consistency checks to ensure that transaction coordinator
309       ** is not using the server entry being dropped.
310       */
311   
312       /*
313       ** Lock syscoordinations to synchronize with dtm service. This type of
314       ** locking on syscoordinations should be done only in extreme case like 
315       ** dropserver as this will potentially block dtm services.
316       */
317       lock table sybsystemdb.dbo.syscoordinations in share mode
318   
319   
320   
321       if exists (select * from sybsystemdb.dbo.syscoordinations c,
322                   master.dbo.sysservers s
323               where s.srvname = @server and
324                   s.srvid = c.participant and c.owner = 1)
325       begin
326           /*
327           ** 17537, "Unable to drop server '%1!' because it is 
328           ** 	   referenced by transaction coordinator."
329           */
330           raiserror 17537, @server
331           goto clean_all
332       end
333   
334   
335       /* Check to see if there are any related logins in sysremotelogins. */
336       if (@droplogins is NULL)
337       begin
338           if exists (select *
339                   from master.dbo.sysremotelogins l,
340                       master.dbo.sysservers s
341                   where s.srvid = l.remoteserverid
342                       and s.srvname = @server)
343           begin
344               /*
345               ** 17530, "There are still remote logins for the server '%1!'."
346               */
347               raiserror 17530, @server
348               goto clean_all
349           end
350   
351           /*
352           ** OMNI: Check to see if there are any related external logins
353           */
354           if exists (select * from master.dbo.sysattributes a,
355                       master.dbo.sysservers s
356                   where s.srvid = a.object_info1 and
357                       s.srvname = @server and
358                       a.class = 9 and a.attribute = 0)
359           begin
360               /*
361               ** 17534, "There are still external logins for the 
362               **	   server '%1!'."
363               */
364               raiserror 17534, @server
365               goto clean_all
366           end
367   
368   
369   
370       end
371   
372       /*
373       **  If @droplogins is true then drop any associated logins
374       */
375       else if @droplogins = "droplogins"
376       begin
377           delete master.dbo.sysremotelogins
378           from master.dbo.sysremotelogins l,
379               master.dbo.sysservers s
380           where s.srvid = l.remoteserverid
381               and s.srvname = @server
382   
383           if (@@rowcount > 0)
384           begin
385   
386   
387               /*
388               ** 17531, "Remote logins for remote server '%1!' 
389               **	  have been dropped."
390               */
391               exec sp_getmessage 17531, @msg output
392               print @msg, @server
393           end
394   
395           /*
396           ** OMNI: Drop any external logins as well
397           */
398           delete master.dbo.sysattributes
399           from master.dbo.sysattributes a,
400               master.dbo.sysservers s
401           where s.srvname = @server
402               and s.srvid = a.object_info1
403               and a.class = 9 and a.attribute = 0
404   
405           if (@@rowcount > 0)
406           begin
407   
408   
409               /*
410               ** 17535, "External logins for remote server '%1!'
411               ** have been dropped."
412               */
413               exec sp_getmessage 17535, @msg output
414               print @msg, @server
415           end
416   
417       /* Continue below and drop the server */
418       end
419   
420       /*
421       **  Bad argument to @droplogins.
422       */
423       else
424       begin
425           /*
426           ** 17532, "Usage: sp_dropserver server [, droplogins]"
427           */
428           raiserror 17532
429           goto clean_all
430       end
431   
432       /*
433       ** Hang up the connection to the server if there is one
434       */
435       dbcc connection_hangup(@server)
436   
437   
438   
439       /*
440       ** Fetch the srvid for this server
441       */
442       select @srvid = srvid
443       from master.dbo.sysservers
444       where srvname = @server
445   
446       /*
447       ** If the server belongs to the ejb class make sure the server is stopped
448       ** before the entry from sysserver is deleted
449       */
450   
451       select @srvclass = srvclass, @physname = srvnetname
452       from master.dbo.sysservers
453       where srvname = @server
454   
455       if @srvclass = 10
456       begin
457           select @command = "STOP"
458           /*
459           ** We go ahead and stop the ejb server when the sp_dropserver command
460           ** is executed on an ejb server
461           */
462           dbcc extengine(@physname, @srvclass, @command)
463       end
464   
465       /*
466       **  Drop the server.
467       */
468       delete master.dbo.sysservers
469       where srvname = @server
470       if (@@error != 0)
471       begin
472           goto clean_all
473       end
474   
475       /*
476       ** Drop the rtds provider registered in sysattributes
477       */
478       if (@insysattributes = 1)
479           delete master.dbo.sysattributes
480           where class = 21 and attribute = 10
481               and object_type = 'PR'
482               and object_cinfo = @server
483   
484   
485   
486       /*
487       ** For SDC, update cluster-wide in-memory SRVDES with data from
488       ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc command scope
489       ** needs to be set to cluster.
490       */
491       if (@@clustermode = "shared disk cluster")
492       begin
493           select @scope = NULL
494           select @outstr = "dbcc set_scope_in_cluster('scope')"
495           if (charindex("instance", @outstr) != 0)
496           begin
497               /* save the scope to be restored later */
498               select @scope = "instance"
499               dbcc set_scope_in_cluster('cluster')
500           end
501       end
502   
503       /*
504       ** Tag in-memory copy as
505       ** unusable.
506       */
507   
508       dbcc cis("srvdes", @srvid)
509   
510       /* restore dbcc command scope */
511       if (@@clustermode = "shared disk cluster")
512       begin
513           if (@scope = "instance")
514           begin
515               dbcc set_scope_in_cluster('instance')
516           end
517       end
518   
519       /*
520       ** log the command for replication support
521       */
522       if (@master_is_rep > 0) and (@log_for_rep = 1)
523       begin
524           if (logexec(1) != 1)
525           begin
526               /*
527               ** , "17869 Stored procedure %1 failed'
528               **       because '%2' failed in database '%3'."
529               */
530               raiserror 17869, "sp_addserver", "logexec()", @dbname
531               goto clean_all
532           end
533       end
534   
535       commit tran rs_logexec
536   
537       /*
538       ** 17533, "Server dropped."
539       */
540       exec sp_getmessage 17533, @msg output
541       print @msg
542   
543       return (0)
544   
545   clean_all:
546       rollback tran rs_logexec
547       return (1)
548   
549   


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: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
139
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscoordinations.csyscoordinations unique clustered
(xactkey, participant, owner)
Intersection: {owner}
324
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch smallint = int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 253
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 324
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 356
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 358
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 402
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 403
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 480
 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..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 return value of exec 180
 MNER 3 No Error Check should check return value of exec 201
 MNER 3 No Error Check should check return value of exec 217
 MNER 3 No Error Check should check @@error after delete 377
 MNER 3 No Error Check should check return value of exec 391
 MNER 3 No Error Check should check @@error after delete 398
 MNER 3 No Error Check should check return value of exec 413
 MNER 3 No Error Check should check @@error after delete 479
 MNER 3 No Error Check should check return value of exec 540
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 265
 MUCO 3 Useless Code Useless Brackets 276
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 405
 MUCO 3 Useless Code Useless Brackets 470
 MUCO 3 Useless Code Useless Brackets 478
 MUCO 3 Useless Code Useless Brackets 491
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 511
 MUCO 3 Useless Code Useless Brackets 513
 MUCO 3 Useless Code Useless Brackets 524
 MUCO 3 Useless Code Useless Brackets 543
 MUCO 3 Useless Code Useless Brackets 547
 QISO 3 Set isolation level 166
 QNAJ 3 Not using ANSI Inner Join 321
 QNAJ 3 Not using ANSI Inner Join 339
 QNAJ 3 Not using ANSI Inner Join 354
 QNAJ 3 Not using ANSI Inner Join 378
 QNAJ 3 Not using ANSI Inner Join 399
 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}
253
 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}
358
 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}
403
 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}
480
 QTJ1 3 Table only appears in inner join clause 377
 VNRD 3 Variable is not read @gp_enabled 219
 VNRD 3 Variable is not read @dummy 221
 MSUB 2 Subquery Marker 136
 MSUB 2 Subquery Marker 138
 MSUB 2 Subquery Marker 235
 MSUB 2 Subquery Marker 252
 MSUB 2 Subquery Marker 283
 MSUB 2 Subquery Marker 321
 MSUB 2 Subquery Marker 338
 MSUB 2 Subquery Marker 354
 MTR1 2 Metrics: Comments Ratio Comments: 52% 62
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 42 = 50dec - 10exi + 2 62
 MTR3 2 Metrics: Query Complexity Complexity: 228 62
 PRED_QUERY_COLLECTION 2 {c=sybsystemdb..syscoordinations, s=master..sysservers} 0 321
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 338
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, s=master..sysservers} 0 354
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 377
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, s=master..sysservers} 0 398

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
read_writes table master..sysremotelogins (1)  
read_writes table master..sysservers (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
read_writes table master..sysattributes (1)  
read_writes table sybsystemdb..syscoordinations (1)  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysdatabases (1)  

CALLERS
called by proc sybsystemprocs..sp_hadr_admin  
   called by proc sybsystemprocs..sp_hadr_update