1
2
3 /*
4 ** Messages for "sp_multdb_bind"
5 **
6 ** 17260, "Can't run %1! from within a transaction."
7 ** 18252, "%1!' value '%2!' is not valid."
8 ** 18255, "%1! cannot be NULL."
9 ** 18276, "%1! is not a valid object type."
10 ** 18314, "Login '%1!' does not exist in syslogins table."
11 ** 18609, "%1!: Update row of master.dbo.sysattributes failed. Command aborted."
12 ** 18610, "%1!: Insert row to master.dbo.sysattributes failed. Command aborted."
13 ** 18946, "Either the database '%1!' does not exist or is not a user
14 ** created temporary database."
15 ** 18947, "Group '%1!' does not exist."
16 ** 18951, "'%1!' is not currently supported."
17 ** 18953, "Internal Error: failed to add binding."
18 ** 18976, "The '%1!' login can not have a hard binding."
19 ** 19580, "Either the database '%1!' does not exist or is not a local
20 ** user temporary database."
21 ** 19581, "The %1! '%2!' is already bound to a group. To bind it to a
22 ** database, first remove existing group binding to it."
23 ** 19582, "The %1! '%2!' already has one or more database bindings. To
24 ** bind it to a group, first remove existing database bindings to it."
25 ** 19594, "Unable to determine owner instance of the database '%1!'. An
26 ** internal inconsistency is encountered in row for the database '%1!'
27 ** in master..sysdatabases. Please contact Sybase Technical Support."
28 */
29
30 /*
31 ** Procedure sp_multdb_bind
32 **
33 ** This procedure adds a new multiple tempdb related binding
34 ** in SYSATTRIBUTES
35 **
36 ** SMP
37 ** Triplet could be either bound to a
38 ** temporary database group or to a temporary database i.e. there
39 ** could be only one binding entry for the given triplet.
40 **
41 ** SDC
42 ** Triplet could be either bound to a
43 ** temporary database group or to local temporary databases. If the
44 ** binding is to a temporary database group, then there could be
45 ** only one binding entry. However, if binding is to local temporary
46 ** databases then there could be multiple binding entries, one for
47 ** each cluster instance.
48 */
49 create procedure sp_multdb_bind
50 @obj_name varchar(255), /* object to be bound */
51 @obj_type varchar(30), /* Type of object 'AP' or 'LG' */
52 @scope varchar(255), /* Application name or login name */
53 @tdb_type varchar(30), /* 'DB' for db or "GR" for group */
54 @tdb varchar(255), /* temporary db/group to be bound to*/
55 @hardness varchar(30) /* hard of soft binding */
56 as
57
58 declare @class_id smallint, /* class in SYSATTRIBUTES */
59 @attrib_id smallint, /* attribute in SYSATTRIBUTES */
60 @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */
61 @new_bind_type int, /* New binding type. Corresponds to
62 ** int_value in SYSATTRIBUTES
63 */
64 @existing_bind_type int, /* Existing binding type. Corresponds
65 ** to int_value in SYSATTRIBUTES
66 */
67 @group_id int, /* group id */
68 @action int, /* action for built in */
69 @upcase_str varchar(30),
70 @app_name varchar(255), /* application name */
71 @user_id int, /* user id of login */
72 @hardorsoft int, /* hard or soft */
73 @instanceid int, /* (SDC only) instance id of the owner
74 ** of local user tempdb.
75 */
76 @obj_type_str varchar(30), /* To hold object type "login" or
77 ** "application" string constants
78 ** for message printing.
79 */
80 @svrmode int, /* Indicates the SMP or SDC mode */
81 @notify_status int, /* return value from attrib_notify() */
82 @MULTDB_HARDBINDING int, /* hard binding */
83 @MULTDB_SOFTBINDING int, /* soft binding */
84 @MULTDB_BINDGRP int, /* binding is to a group */
85 @MULTDB_BINDDB int, /* binding is to a temporary database */
86 @SMP int, /* Indicates SMP Server */
87 @SDC int, /* Indicates SDC Server */
88 @DBT3_USER_TEMPDB int, /* (SMP only) Bit value corresponding
89 ** to DBT3_USER_TEMPDB
90 */
91 @DBT3_LOCAL_USER_TEMPDB int, /* (SDC only) Bit value corresponding to
92 ** DBT3_LOCAL_USER_TEMPDB
93 */
94 @ATTR_ADD int, /* indicates a new binding */
95 @ATTR_CHANGE int /* indicates a binding update */
96
97 select @SMP = 0,
98 @SDC = 1
99
100 if @@clustermode != "shared disk cluster"
101 begin
102 select @svrmode = @SMP
103
104 select @DBT3_USER_TEMPDB = number
105 from master.dbo.spt_values
106 where type = "D3" and name = "user created temp db"
107
108 /* instanceid is not relevant in SMP, set it to NULL */
109 select @instanceid = NULL
110 end
111
112
113 /*
114 ** if we're in a transaction, disallow this since it might make recovery
115 ** impossible.
116 */
117 if @@trancount > 0
118 begin
119 /*
120 ** 17260, "Can't run %1! from within a transaction."
121 */
122 raiserror 17260, "sp_multdb_bind"
123 return (1)
124 end
125 else
126 begin
127 /* Use TSQL mode of unchained transactions */
128 set chained off
129 end
130
131 /* Don't do "Dirty Reads" */
132 set transaction isolation level 1
133
134 select @class_id = 16 /* class is MULTEMPDB_CLASS */
135 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
136
137 /*
138 ** Following constants must be consistent with their definition in
139 ** multempdb.c
140 */
141 select @MULTDB_HARDBINDING = 1,
142 @MULTDB_SOFTBINDING = 0,
143 @MULTDB_BINDGRP = 1,
144 @MULTDB_BINDDB = 0
145
146 /* keep in sync with sysattr.h */
147 select @ATTR_ADD = 1,
148 @ATTR_CHANGE = 2
149
150 /*
151 ** Make sure that object type is valid
152 ** Can only be 'AP' or 'LG' or 'APPLICATION_NAME' or 'LOGIN_NAME'
153 */
154 select @upcase_str = upper(@obj_type)
155 if (@upcase_str = "LG") OR (@upcase_str = "AP")
156 OR (@upcase_str = "APPLICATION_NAME") OR (@upcase_str = "LOGIN_NAME")
157 begin
158 if (@upcase_str = "APPLICATION_NAME") OR (@upcase_str = "AP")
159 begin
160 select @obj_type = "AP"
161 select @obj_type_str = "application"
162 end
163 else
164 begin
165 select @obj_type = "LG"
166 select @obj_type_str = "login"
167 end
168 end
169 else
170 begin
171 /*
172 ** 18276, "%1! is not a valid object type."
173 */
174 raiserror 18276, @obj_type
175 return (1)
176 end
177
178 /*
179 ** Make sure that tdb type is valid
180 ** Can only be 'GR' (binding to a group) or 'GROUP'
181 ** 'DB' (binding to specific database) or 'DATABASE'
182 */
183 select @upcase_str = upper(@tdb_type)
184 if (@upcase_str = "DB") OR (@upcase_str = "GR")
185 OR (@upcase_str = "DATABASE") OR (@upcase_str = "GROUP")
186 begin
187 if (@upcase_str = "DATABASE") OR (@upcase_str = "DB")
188 begin
189 select @tdb_type = "DB"
190 end
191 else
192 begin
193 select @tdb_type = "GR"
194 end
195 end
196 else
197 begin
198 /*
199 ** 18252, "%1!' value '%2!' is not valid."
200 */
201 raiserror 18252, "Bindtype", @tdb_type
202 return (1)
203 end
204
205 /*
206 ** Object name can't be null
207 */
208 if (@obj_name is NULL)
209 begin
210 /*
211 ** 18255, "%1! cannot be NULL."
212 */
213 raiserror 18255, "Object name"
214 return (1)
215 end
216
217 /*
218 ** Check to see that the hardness is valid.
219 ** Either "hard" or "soft" or null (in which case
220 ** we use the default of "soft"
221 */
222 select @upcase_str = upper(@hardness)
223 if (@upcase_str = "HARD")
224 begin
225 select @hardorsoft = @MULTDB_HARDBINDING
226 end
227 else if (@upcase_str = "SOFT") OR (@upcase_str is NULL)
228 begin
229 select @hardorsoft = @MULTDB_SOFTBINDING
230 end
231 else
232 begin
233 /*
234 ** 18252, "%1!' value '%2!' is not valid."
235 */
236 raiserror 18252, "Hardness", @hardness
237 return (1)
238 end
239
240
241 /*
242 ** Temp db or group name can't be null
243 */
244 if (@tdb is NULL)
245 begin
246 /*
247 ** 18255, "%1! cannot be NULL."
248 */
249 raiserror 18255, "bindobj"
250 return (1)
251 end
252
253 select @action = @ATTR_ADD /* new binding */
254 select @user_id = NULL /* id of user from syslogins */
255 select @app_name = NULL /* application name */
256
257 /*
258 ** We don't currently support a non null scope.
259 */
260 if (@scope is not NULL)
261 begin
262 /*
263 ** 18951, "'%1!' is not currently supported."
264 */
265 raiserror 18951, "Scope"
266 return (1)
267 end
268
269 /*
270 ** If binding to temporary database then
271 ** check to see that the database being bound to
272 ** is indeed a temporary database. We do not allow the
273 ** binding to a non temporary database.
274 **
275 ** For SDC, check to see if the database being bound is a local user
276 ** temporary database. Bindings to non temporary databases, global
277 ** temporary databases and local system temporary databases are not allowed.
278 */
279 if (@tdb_type = "DB")
280 begin
281
282 if not exists (select * from master..sysdatabases
283 where name = @tdb
284 AND ((status3 & @DBT3_USER_TEMPDB) =
285 @DBT3_USER_TEMPDB))
286 begin
287 /*
288 ** 18946, "Either the database '%1!' does not exist
289 ** or is not a user created temporary database."
290 */
291 raiserror 18946, @tdb
292 return (1)
293 end
294
295 /*
296 ** Specified (local) temporary database exists. Indicate that
297 ** binding is to a temporary database.
298 */
299 select @new_bind_type = @MULTDB_BINDDB
300
301 end
302 else
303 /*
304 ** Binding is to a group.
305 ** Check to see if the group name exists.
306 */
307 begin
308 select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */
309 if not exists (select * from master..sysattributes
310 where class = @class_id
311 AND attribute = @attrib_id
312 AND object_type = 'GR'
313 AND object_cinfo = @tdb)
314 begin
315 /*
316 ** 18947, "Group '%1!' does not exist."
317 */
318 raiserror 18947, @tdb
319 return (1)
320 end
321 else
322 begin
323 select @new_bind_type = @MULTDB_BINDGRP /* indicates group */
324
325 end
326 end
327
328 /*
329 ** convert user name to user id after checking its existence
330 */
331 if (@obj_type = "LG")
332 begin
333 /* 'sa' cannot have a hard binding */
334 if ((@hardorsoft = @MULTDB_HARDBINDING) and (@obj_name = "sa"))
335 begin
336 /*
337 ** 18976, "The '%1!' login can not have a hard binding."
338 */
339 raiserror 18976, @obj_name
340 return (1)
341 end
342
343 if not exists (select suid from master..syslogins
344 where (name = @obj_name)
345 and ((status & 512) != 512)) /* not LOGIN PROFILE */
346 begin
347 /*
348 ** 18314, "Login '%1!' does not exist in syslogins table."
349 */
350 raiserror 18314, @obj_name
351 return (1)
352 end
353
354 select @user_id = (select suid from master..syslogins
355 where (name = @obj_name))
356
357 if (@scope is not NULL)
358 begin
359 select @app_name = @scope
360 end
361 end
362
363 /*
364 ** For "AP" type bindings, obj_name is an application name.
365 */
366 else if (@obj_type = "AP")
367 begin
368 select @app_name = @obj_name
369
370 /*
371 ** if obj_type is "AP", then a non null scope
372 ** would have the login name. If user name is specified
373 ** then convert user name to user id after checking its existence
374 */
375 if (@scope is not NULL)
376 begin
377 if not exists (select suid from master..syslogins
378 where (name = @scope)
379 and ((status & 512) != 512)) /* not LOGIN PROFILE */
380 begin
381 /*
382 ** 18314, "Login '%1!' does not exist in syslogins
383 ** table."
384 */
385 raiserror 18314, @scope
386 return (1)
387 end
388
389 select @user_id = (select suid from master..syslogins
390 where (name = @scope))
391 end
392 end
393
394 /* Attribute if for binding */
395 select @attrib_id = 1
396
397 begin transaction tempdb_bind
398
399 /*
400 ** If binding already exists then update it,
401 ** else insert the new binding
402 */
403 if exists (select * from master..sysattributes
404 where class = @class_id
405 AND attribute = @attrib_id
406 AND object_type = @obj_type
407 AND object_cinfo = @app_name
408 AND object = @user_id)
409 begin
410
411
412 select @action = @ATTR_CHANGE
413
414 /* Update the existing binding */
415 update master..sysattributes
416 set char_value = @tdb, int_value = @new_bind_type,
417 object_info1 = @hardorsoft
418 where class = @class_id
419 AND attribute = @attrib_id
420 AND object_type = @obj_type
421 AND object_cinfo = @app_name
422 AND object = @user_id
423
424 if (@@error != 0)
425 begin
426 /*
427 ** 18609, "%1!: Update row of master.dbo.sysattributes
428 ** failed. Command aborted."
429 */
430 raiserror 18609, "sp_multdb_bind"
431 goto error_exit
432 end
433
434 end
435 else
436 begin
437
438 select @action = @ATTR_ADD
439
440 /* Insert the new binding */
441 insert into master..sysattributes
442 (class, attribute, object_type, object_cinfo, object,
443 object_info1, int_value, char_value)
444 values
445 (@class_id, @attrib_id, @obj_type, @app_name, @user_id,
446 @hardorsoft, @new_bind_type, @tdb)
447
448 if (@@error != 0)
449 begin
450 /*
451 ** 18610, "%1!: Insert row to master.dbo.sysattributes
452 ** failed. Command aborted."
453 */
454 raiserror 18610, "sp_multdb_bind"
455 goto error_exit
456 end
457
458 end
459
460 /*
461 ** Notify the server about the new/updated binding.
462 ** Note that for SMP, @instanceid is always NULL and should be ignored
463 ** by the notification handler routine.
464 **
465 ** Currently, no action is taken upon notification of a new binding.
466 ** Uncomment the following notification when some action needs to be
467 ** implemented.
468 */
469 --select @notify_status =
470 -- attrib_notify (@class_id, @attrib_id, @obj_type, @user_id,
471 -- @hardorsoft, @instanceid, NULL, @app_name,
472 -- @new_bind_type, @tdb, NULL, NULL, "", @action)
473 --
474 --if (@notify_status = 0)
475 --begin
476 -- /*
477 -- ** Server responded with failure, rollback the transaction.
478 -- **
479 -- ** 18953, "Internal Error: failed to add binding."
480 -- */
481 -- raiserror 18953
482 -- goto error_exit
483 --end
484
485 /* Commit the transaction */
486 commit transaction tempdb_bind
487 return (0)
488
489 error_exit:
490 rollback transaction tempdb_bind
491 return (1)
492
| DEFECTS |
 MINU 4 Unique Index with nullable columns master..sysattributes |
master..sysattributes |
| MTYP 4 Assignment type mismatch object_type: char(2) = varchar(30) |
445 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
106 |
| 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..syslogins |
|
| MUCO 3 Useless Code Useless Brackets |
123 |
| MUCO 3 Useless Code Useless Brackets |
175 |
| MUCO 3 Useless Code Useless Brackets |
202 |
| MUCO 3 Useless Code Useless Brackets |
208 |
| MUCO 3 Useless Code Useless Brackets |
214 |
| MUCO 3 Useless Code Useless Brackets |
223 |
| MUCO 3 Useless Code Useless Brackets |
237 |
| MUCO 3 Useless Code Useless Brackets |
244 |
| MUCO 3 Useless Code Useless Brackets |
250 |
| MUCO 3 Useless Code Useless Brackets |
260 |
| MUCO 3 Useless Code Useless Brackets |
266 |
| MUCO 3 Useless Code Useless Brackets |
279 |
| MUCO 3 Useless Code Useless Brackets |
292 |
| MUCO 3 Useless Code Useless Brackets |
319 |
| MUCO 3 Useless Code Useless Brackets |
331 |
| MUCO 3 Useless Code Useless Brackets |
334 |
| MUCO 3 Useless Code Useless Brackets |
340 |
| MUCO 3 Useless Code Useless Brackets |
351 |
| MUCO 3 Useless Code Useless Brackets |
355 |
| MUCO 3 Useless Code Useless Brackets |
357 |
| MUCO 3 Useless Code Useless Brackets |
366 |
| MUCO 3 Useless Code Useless Brackets |
375 |
| MUCO 3 Useless Code Useless Brackets |
386 |
| MUCO 3 Useless Code Useless Brackets |
390 |
| MUCO 3 Useless Code Useless Brackets |
424 |
| MUCO 3 Useless Code Useless Brackets |
448 |
| MUCO 3 Useless Code Useless Brackets |
487 |
| MUCO 3 Useless Code Useless Brackets |
491 |
| MUPK 3 Update column which is part of a PK or unique index object_info1 |
416 |
| QAFM 3 Var Assignment from potentially many rows |
104 |
| QISO 3 Set isolation level |
132 |
| QIWC 3 Insert with not all columns specified missing 7 columns out of 15 |
442 |
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} |
310 |
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, object_cinfo, attribute, class} |
404 |
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, object_cinfo, attribute, class} |
418 |
| VNRD 3 Variable is not read @SDC |
98 |
| VNRD 3 Variable is not read @svrmode |
102 |
| VNRD 3 Variable is not read @instanceid |
109 |
| VNRD 3 Variable is not read @obj_type_str |
166 |
| VNRD 3 Variable is not read @action |
438 |
| VUNU 3 Variable is not used @object_cinfo |
60 |
| VUNU 3 Variable is not used @existing_bind_type |
64 |
| VUNU 3 Variable is not used @group_id |
67 |
| VUNU 3 Variable is not used @notify_status |
81 |
| VUNU 3 Variable is not used @DBT3_LOCAL_USER_TEMPDB |
91 |
 MSUB 2 Subquery Marker |
282 |
| MSUB 2 Subquery Marker |
309 |
| MSUB 2 Subquery Marker |
343 |
| MSUB 2 Subquery Marker |
354 |
| MSUB 2 Subquery Marker |
377 |
| MSUB 2 Subquery Marker |
389 |
| MSUB 2 Subquery Marker |
403 |
| MTR1 2 Metrics: Comments Ratio Comments: 54% |
49 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 34 = 45dec - 13exi + 2 |
49 |
| MTR3 2 Metrics: Query Complexity Complexity: 164 |
49 |
