DatabaseProcApplicationCreatedLinks
sybsystemprocssp_multdb_dropgroup  31 Aug 14Defects Dependencies

1     
2     
3     /*
4     ** Messages for "sp_multdb_dropgroup"
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 17240, "'%1!' is not a valid name."
8     ** 18943, "Can not drop '%1!' group."
9     ** 18944, "Internal Error: failed to drop group."
10    ** 18947, "Group '%1!' does not exist."
11    ** 18608, "%1!: Delete row from master.dbo.sysattributes failed. 
12    **	   Command aborted."
13    */
14    
15    
16    /*
17    ** Procedure sp_multdb_dropgroup
18    **
19    ** This procedure drops a group from SYSATTRIBUTES
20    ** for the MULTEMPDB_CLASS (class 16)
21    **      
22    */
23    create procedure sp_multdb_dropgroup
24        @tdbgroup varchar(255) /* temp db group */
25    as
26    
27        declare @class_id smallint, /* class in SYSATTRIBUTES */
28            @attrib_id smallint, /* attribute in SYSATTRIBUTES */
29            @object_type char(2), /* object type in SYSATTRIBUTES */
30            @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */
31            @int_val int, /* int_value in SYSATTRIBUTES */
32            @group_id int, /* group id database being added to */
33            @action int, /* action for built in */
34            @upcase_str varchar(30),
35            @errnum int, /* error during sysattribute update*/
36            @svrmode int, /* Indicates the SMP or SDC mode */
37            @SMP int, /* Indicates SMP Server */
38            @SDC int, /* Indicates SDC Server */
39            @action_code int, /* Indication action code for the op. */
40            @MULTDB_ACTION_DO int, /* Action code that operation
41            ** completed successfully
42            */
43            @MULTDB_ACTION_UNDO int /* Action code to indicate that 
44        ** operation did not complete
45        */
46    
47        select @SMP = 0,
48            @SDC = 1
49    
50        if @@clustermode != "shared disk cluster"
51        begin
52            select @svrmode = @SMP
53        end
54        else
55        begin
56            select @svrmode = @SDC
57    
58            /* 
59            ** These values must be kept in sync with their definitions in
60            ** multempdb.h.
61            */
62            select @MULTDB_ACTION_DO = 2
63            select @MULTDB_ACTION_UNDO = 3
64        end
65    
66        /*
67        **  If we're in a transaction, disallow this since it might make recovery
68        **  impossible.
69        */
70        if @@trancount > 0
71        begin
72            raiserror 17260, "sp_multdb_dropgroup"
73            return (1)
74        end
75        else
76        begin
77            /* Use TSQL mode of unchained transactions */
78            set chained off
79        end
80    
81        /* Dont do "Dirty Reads" */
82        set transaction isolation level 1
83    
84        select @class_id = 16 /* class is MULTEMPDB_CLASS */
85    
86        /*
87        ** We don't allow dropping the default group.
88        */
89        if (@tdbgroup = "default")
90        begin
91            raiserror 18943, @tdbgroup
92            return (1)
93        end
94    
95        /*
96        ** Make sure group name is valid.
97        */
98        if (@tdbgroup != "default")
99        begin
100           if valid_name(@tdbgroup) = 0
101           begin
102               raiserror 17240, @tdbgroup
103               return (1)
104           end
105       end
106   
107       /*
108       ** Check if group exists.
109       */
110       select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */
111       select @object_type = 'GR' /* Object type if 'GR' for temp. db group */
112   
113       select @group_id = (select int_value from master..sysattributes
114               where class = @class_id
115                   AND attribute = @attrib_id
116                   AND object_type = @object_type
117                   AND object_cinfo = @tdbgroup)
118   
119       /*
120       ** Group does not exist. Report error.
121       */
122       if @group_id is NULL
123       begin
124           raiserror 18947, @tdbgroup
125           return (1)
126       end
127   
128       select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
129   
130       select @errnum = 0
131   
132       begin tran drop_tdbgrp
133   
134       /*
135       ** Send the notification to indicate the beginning of the transaction
136       ** to allow cluster synchronization mechanism to be deployed.
137       */
138       if (@svrmode = @SDC)
139       begin
140           select @action = 5 /* ATTR_BEGIN */
141           select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */
142           if (attrib_notify(@class_id, @attrib_id, @object_type,
143                   NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
144                   NULL, "", @action)) = 0
145           begin
146               raiserror 18944
147               goto error_exit
148           end
149       end
150   
151       /*
152       ** Drop all bindings of type 'AP' and 'LG' (application
153       ** and login bindings) where the binding is to the group being dropped
154       **
155       ** The int_value of 1 indicates that the name stored in char_value is a
156       ** group name.
157       */
158       select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
159   
160       delete from master..sysattributes
161       where class = @class_id
162           AND attribute = @attrib_id
163           AND (object_type = 'LG' OR object_type = 'AP')
164           AND char_value = @tdbgroup
165           AND int_value = 1
166   
167       select @errnum = @@error
168       if (@errnum != 0)
169           goto error_exit
170   
171       /*
172       ** Drop all entres from the sysattributes where
173       ** databases are bound to the group that is being dropped.
174       */
175       select @object_type = 'D ' /* Object type if 'GR' for temp. db group */
176   
177       delete from master..sysattributes
178       where class = @class_id
179           AND attribute = @attrib_id
180           AND object_type = @object_type
181           AND object = @group_id
182   
183       select @errnum = @@error
184       if (@errnum != 0)
185           goto error_exit
186   
187       /*
188       ** Drop the group entry
189       */
190       select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */
191       select @object_type = 'GR' /* Object type if 'GR' for temp. db group */
192   
193       delete from master..sysattributes
194       where class = @class_id
195           AND attribute = @attrib_id
196           AND object_type = @object_type
197           AND object_cinfo = @tdbgroup
198   
199       select @errnum = @@error
200       if (@errnum != 0)
201           goto error_exit
202   
203       /*
204       ** Notify the deletion of the new group.
205       ** If this return failure, then it's an internal error.
206       ** There is no reason to undo the work we have just done.
207       */
208       select @action = 3 /* ATTR_DROP - Drop a group */
209       if (attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL,
210               NULL, @tdbgroup, @group_id, NULL, NULL, NULL, "",
211               @action)) = 0
212       begin
213           raiserror 18944
214           goto error_exit
215       end
216   
217       commit tran drop_tdbgrp
218       /*
219       ** Send notification to communicate that transaction is committed and
220       ** in-memory changes be made final.
221       */
222       if (@svrmode = @SDC)
223       begin
224           select @action = 6 /* ATTR_END */
225           select @action_code = @MULTDB_ACTION_DO
226           select attrib_notify(@class_id, @attrib_id, @object_type,
227                   NULL, NULL, NULL, NULL, NULL, @action_code, NULL, NULL,
228                   NULL, "", @action)
229       end
230       return (0)
231   
232   error_exit:
233       if @errnum != 0
234           raiserror 18608, 'sp_multdb_dropgroup'
235       /*
236       ** In SDC, the in-memory changes are undone as part of the 
237       ** abort transaction processing. So, there is no need to send
238       ** end notification with @MULTDB_ACTION_UNDO action.
239       */
240       rollback tran drop_tdbgrp
241       return (1)
242   

RESULT SETS
sp_multdb_dropgroup_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 144
 MEST 4 Empty String will be replaced by Single Space 210
 MEST 4 Empty String will be replaced by Single Space 228
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MGTP 3 Grant to public master..sysattributes  
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 241
 QCRS 3 Conditional Result Set 226
 QISO 3 Set isolation level 82
 QNAM 3 Select expression has no name attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, NULL, NULL, @action_code, NULL,... 226
 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}
114
 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: {attribute, object_type, class}
161
 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, attribute, class}
178
 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}
194
 VNRD 3 Variable is not read @MULTDB_ACTION_UNDO 63
 VUNU 3 Variable is not used @object_cinfo 30
 VUNU 3 Variable is not used @int_val 31
 VUNU 3 Variable is not used @upcase_str 34
 MRST 2 Result Set Marker 226
 MSUB 2 Subquery Marker 113
 MTR1 2 Metrics: Comments Ratio Comments: 44% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 17dec - 5exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 103 23

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  

CALLERS
called by proc sybsystemprocs..sp_tempdb