| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_bindcache | | 31 Aug 14 | Defects Dependencies |
1 2 /* Stored procedure for binding entites to named caches. Entity must 3 ** be the name of a database, table or index. 4 ** 5 ** Databases can only be bound when using Master. 6 ** The Master database cannot be bound to a named cache, but individual 7 ** tables in Master can. 8 */ 9 10 /* 11 ** Messages for "sp_bindcache" 18086 12 ** 13 ** 17260, "Can't run %1! from within a transaction." 14 ** 18086, "Specified named cache does not exist." 15 ** 18087, "You must be in Master to bind or unbind a database." 16 ** 18089, "The Master database cannot be bound to or unbound from 17 ** a named cache." 18 ** 18092, "Only logs may be bound to this cache." 19 ** 18098, "Individual tables in a temporary database cannot be bound to or 20 ** unbound from named caches. However, all of a temporary database 21 ** may be bound or unbound." 22 ** 18156, "Usage: sp_unbindcache dbname [, [ownername.]tablename 23 ** [, indexname | `text only']]" 24 ** 18170, "Usage: sp_bindcache cachename, dbname [, [ownername.]tablename 25 ** [, indexname | `text only']]" 26 ** 18175, "Specified named cache does not exist. The SQL Server must be 27 ** rebooted to activate the named cahce." 28 ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly." 29 ** 19120, "Cache binding changes for 'tempdb' are not dynamic. Adaptive Server 30 ** must be restarted for the change to take effect." 31 ** 19686, "Warning! Binding a local temporary database to a local named cache 32 ** that doesn't exist on the instance that owns the temporary 33 ** database." 34 ** 19776, "Cache binding changes for local system temporary databases are not 35 ** dynamic. The owner instance must be rebooted for the change to take 36 ** effect." 37 ** 19777, "Cache binding change for local temporary database '%1!' failed due 38 ** to owner instance '%2!' status change. Please try again after the 39 ** owner instance change completes." 40 ** 19828, "Cache binding failed for database '%1!'" 41 ** 19974, "Individual object or database cannot be bound to or unbound from 42 ** cache, '%1!', which is an in-memory storage cache." 43 ** 19979, "Cannot run '%1!' for in-memory database '%2!'." 44 ** 19991, "You cannot bind an in-memory database, or individual objects in an 45 ** in-memory database, to cache '%1!', which is a regular named cache. 46 */ 47 create procedure sp_bindcache 48 @cachename varchar(255), 49 @dbname varchar(30), 50 @tablename varchar(512) = NULL, 51 @indexname varchar(255) = NULL 52 as 53 54 declare @dbid int 55 declare @ownerid int 56 declare @objid int 57 declare @indid int 58 declare @entitytype char(1) 59 declare @aiobject int 60 declare @status int 61 declare @separator int 62 declare @mask int 63 declare @cachecount int 64 declare @class smallint 65 declare @attribute smallint 66 declare @action smallint 67 declare @objtype char(1) 68 declare @sysattrtype char(2) 69 declare @tempdb_mask int /* all tempdb status bits */ 70 declare @local_tempdb_mask int /* all local tempdb status bits */ 71 declare @localsystdbbit int /* local system tempdb status3 bit */ 72 declare @tdb_instanceid int /* local tempdb instanceid */ 73 declare @tdb_instancename varchar(255) 74 declare @sqlbuf varchar(255) 75 declare @islocalsystdb int /* It is a local system tempdb */ 76 declare @is_others_ltdb int /* It is a local tempdb on remote instance */ 77 declare @dbstat int 78 declare @dbstat3 int 79 declare @msg varchar(1024) 80 declare @is_inmemcache int 81 82 /* 83 ** Don't allow this in a transaction because we can't undo what 84 ** the bind_cache() builtin has done. 85 */ 86 if @@trancount > 0 87 begin 88 /* 89 ** 17260, "Can't run %1! from within a transaction." 90 */ 91 raiserror 17260, "sp_bindcache" 92 return (1) 93 end 94 else 95 begin 96 set transaction isolation level 1 97 set chained off 98 end 99 100 /* check if user has sa role, proc_role will also do auditing 101 ** if required. proc_role will also print error message if required. 102 */ 103 if (proc_role("sa_role") = 0) 104 return (1) 105 106 /* Make sure the target cache exists. */ 107 if (@cachename is not NULL) 108 begin 109 select @status = 0 110 select @is_inmemcache = 0 111 112 /* Don't get deleted entries. We may have valid entry */ 113 114 select @status = status from master.dbo.sysconfigures 115 where name = @cachename and parent = 19 and config = 19 116 and status != 16384 117 /* if the named cache does not exist */ 118 if (@status = 0) 119 begin 120 /* 18086, "Specified named cache does not exist." */ 121 raiserror 18086 122 return 1 123 end 124 /* if the named cache is not active */ 125 if (@status & 32) != 32 126 begin 127 /* 128 ** 18175, "Specified named cache does not exist. The SQL Server must be 129 ** rebooted to activate the named cache." 130 */ 131 raiserror 18175 132 return 1 133 end 134 135 /* if the named cache is an in-memory storage cache */ 136 if (@status & 65536 = 65536) 137 begin 138 select @is_inmemcache = 1 139 raiserror 19974, @cachename 140 return 1 141 end 142 end 143 144 if @dbname is NULL 145 begin 146 /* 147 ** Dbname is required for both sp_bindcache and sp_unbindcache. 148 */ 149 if @cachename is NULL 150 begin 151 /* 152 ** Raise the sp_unbindcache usage message. 153 */ 154 raiserror 18156 155 return 1 156 end 157 else 158 begin 159 /* 160 ** Raise the sp_bindcache usage message. 161 */ 162 raiserror 18170 163 return 1 164 end 165 end 166 167 /* 168 ** Retrieve the id's required to identify the object the cache is being 169 ** bound to. 170 */ 171 select @status = 0 172 select @dbid = NULL 173 select @ownerid = NULL 174 select @objid = NULL 175 select @indid = NULL 176 select @entitytype = NULL 177 exec @status = sp_aux_parse_entity @dbname, @tablename, @indexname, 178 @dbid output, @ownerid output, 179 @objid output, @indid output, 180 @entitytype output 181 182 if @status = 2 183 begin 184 /* 185 ** A syntax error was encountered. Raise the appropriate usage 186 ** message. 187 */ 188 if @cachename is NULL 189 begin 190 /* 191 ** Raise the sp_unbindcache usage message. 192 */ 193 raiserror 18156 194 return 1 195 end 196 else 197 begin 198 /* 199 ** Raise the sp_bindcache usage message. 200 */ 201 raiserror 18170 202 return 1 203 end 204 end 205 else if @status != 0 206 begin 207 return @status 208 end 209 210 /* Do not allow sp_bindcache/sp_unbindcache to be run on an archive database. */ 211 if exists (select * from master.dbo.sysdatabases 212 where dbid = @dbid 213 and (status3 & 4194304) = 4194304) 214 begin 215 /* Cannot run '%1!' on an archive database." */ 216 if @cachename = NULL 217 raiserror 19424, "sp_unbindcache" 218 else 219 raiserror 19424, "sp_bindcache" 220 return (1) 221 end 222 223 /* Do not allow sp_unbindcache to be run on an in-memory database. */ 224 if exists (select * from master.dbo.sysdatabases 225 where dbid = @dbid 226 and (status4 & 4096 = 4096)) 227 begin 228 if @cachename = NULL 229 begin 230 raiserror 19979, "sp_unbindcache", @dbname 231 return (1) 232 end 233 else if @is_inmemcache = 0 234 begin 235 /* Inmemory database or any object in inmemory database 236 ** cannot be bound to normal named cache. 237 */ 238 raiserror 19991, @cachename 239 return (1) 240 end 241 end 242 243 select @is_others_ltdb = 0 244 select @islocalsystdb = 0 245 246 select @dbstat = status, @dbstat3 = status3 247 248 from master.dbo.sysdatabases 249 where dbid = @dbid 250 251 /* 252 ** If we are [un]binding a database... 253 */ 254 if @entitytype = 'D' 255 begin 256 /* 257 ** Make sure we are currently in the master database. 258 ** If not display an error message and exit. 259 */ 260 if db_id() != 1 261 begin 262 raiserror 18087 263 return 1 264 end 265 /* 266 ** If the cache type is "logonly", make sure we are binding only 267 ** syslogs to it. Binding a database to a "logonly" cache is illegal. 268 */ 269 select @status = 0 270 select @status = status 271 from master.dbo.sysconfigures 272 where name = @cachename and parent = 19 and config = 19 273 select @mask = @status & 4 274 if @mask > 0 275 begin 276 /* 277 ** 18092, "Only logs may be bound to this cache." 278 */ 279 raiserror 18092 280 return 1 281 end 282 283 284 285 /* Master itself cannot be bound */ 286 if @dbid = 1 287 begin 288 raiserror 18089 289 return 1 290 end 291 292 select @aiobject = @dbid 293 end 294 else 295 begin 296 select @tempdb_mask = number 297 from master.dbo.spt_values 298 where type = "D3" and name = "TEMPDB STATUS MASK" 299 300 /* Individual tables in temporary databases cannot be bound. */ 301 if ((@dbid = 2) OR (@dbstat3 & @tempdb_mask) != 0) 302 begin 303 raiserror 18098 304 return 1 305 end 306 307 select @aiobject = @objid 308 309 /* if the log-only bit is set, make sure we're binding the log. */ 310 select @status = 0 311 select @status = status from master.dbo.sysconfigures 312 where name = @cachename and parent = 19 and config = 19 313 select @mask = @status & 4 314 if @mask > 0 315 begin 316 if (@objid != 8) 317 begin 318 raiserror 18092 319 return 1 320 end 321 end 322 end 323 324 /* 325 ** Map entity type to attribute type. Note that the attribute manager 326 ** needs a two-byte string... 327 */ 328 select @sysattrtype = @entitytype + " " 329 330 /* Figure out if we need to insert, update or delete from sysattributes. */ 331 begin tran bindcache 332 if @cachename is not NULL 333 BEGIN 334 if exists (select * from sysattributes where 335 class = 3 AND 336 attribute = 0 AND 337 object_type = @sysattrtype AND 338 object = @aiobject AND 339 object_info1 = @indid) 340 BEGIN 341 select @action = 2 /* ATTR_CHANGE */ 342 END 343 ELSE 344 BEGIN 345 select @action = 1 /* ATTR_ADD */ 346 END 347 END 348 ELSE 349 BEGIN 350 select @action = 3 /* ATTR_DROP */ 351 END 352 353 /* 354 ** Call the notification routine before modifying sysattributes. This 355 ** is necessary because cm_bind_cache() needs to look up the OLD binding 356 ** for the entity. 357 */ 358 select @status = 0 359 select @class = 3 360 select @attribute = 0 361 362 /* 363 ** For system 'tempdb' all (un)bindings are delayed untill the 364 ** next reboot. The changes on the systemtables are done anyway. 365 ** 366 ** In SDC, same goes with local system tempdb. However, 367 ** This restriction on dynamic binding doesn't apply to the global 368 ** system 'tempdb' since it is not used for worktable or #temp tables 369 ** unless in configuration mode. 370 */ 371 if @@clustermode = "shared disk cluster" 372 begin 373 select @localsystdbbit = number 374 from master.dbo.spt_values 375 where type = "D3" and name = "local system temp db" 376 if ((@dbstat3 & @localsystdbbit) = @localsystdbbit) 377 begin 378 select @islocalsystdb = 1 379 end 380 end 381 382 if ((@dbid != 2 or (@@clustermode = "shared disk cluster" and 383 sdc_intempdbconfig() = 0)) 384 and @islocalsystdb = 0 385 and @is_others_ltdb = 0) 386 BEGIN 387 select @status = attrib_notify(@class, @attribute, @sysattrtype, @aiobject, 388 @indid, NULL, NULL, NULL, NULL, @cachename, NULL, 389 NULL, NULL, @action) 390 391 if @status = 1 392 BEGIN 393 rollback tran bindcache 394 raiserror 19828, @dbname 395 return @status 396 END 397 END 398 399 if @action = 1 400 BEGIN 401 insert into sysattributes 402 (class, attribute, object_type, object, 403 object_info1, char_value, int_value) 404 VALUES 405 (3, /* BUFFER MANAGER */ 406 0, /* CACHE BINDING */ 407 @sysattrtype, 408 @aiobject, 409 @indid, 410 @cachename, 411 1 /* Binding is valid */ 412 ) 413 END 414 415 if @action = 2 416 BEGIN 417 update sysattributes 418 set char_value = @cachename, 419 int_value = 1 /* binding is valid */ 420 where class = 3 AND 421 attribute = 0 AND 422 object_type = @sysattrtype AND 423 object = @aiobject AND 424 object_info1 = @indid 425 END 426 427 if @action = 3 428 BEGIN 429 delete from sysattributes 430 where class = 3 AND 431 attribute = 0 AND 432 object_type = @sysattrtype AND 433 object = @aiobject AND 434 object_info1 = @indid 435 END 436 437 /* 438 ** If we come here for bindings of local tempdb owned by remote instance, 439 ** we have assumed that the local tempdb is not recovered and we can 440 ** safely update master..sysattributes only. Verify that the local tempdb 441 ** is still not recovered, otherwise, rollback the transaction. 442 */ 443 if (@@clustermode = "shared disk cluster" and @is_others_ltdb = 1) 444 begin 445 select @dbstat = status 446 from master.dbo.sysdatabases 447 where dbid = @dbid 448 if ((@dbstat & 64) = 0) 449 begin 450 /* 451 ** 19777, "Cache binding change for local tempdb '%1!' 452 ** failed due to owning instance '%2!' status change. 453 ** Please try again after the owning instance change 454 ** completes." 455 */ 456 raiserror 19777, @dbname, @tdb_instancename 457 rollback tran bindcache 458 return 1 459 end 460 end 461 commit tran bindcache 462 463 if (@dbid = 2 and (@@clustermode != "shared disk cluster" or 464 sdc_intempdbconfig() = 1)) 465 BEGIN 466 /* 467 ** 19120, "Cache binding changes for 'tempdb' are not dynamic. 468 ** Adaptive Server must be restarted for the change to take effect." 469 ** 470 ** This message should be printed as a warning/informational message 471 ** and not user error but it is not changed to avoid any impact to 472 ** user applications. 473 */ 474 raiserror 19120 475 END 476 477 if (@islocalsystdb = 1) 478 begin 479 /* 480 ** 19776, "Cache binding changes for local system temporary databases 481 ** are not dynamic. The owner instance must be rebooted for the change 482 ** to take effect." 483 */ 484 exec sp_getmessage 19776, @msg output 485 print @msg 486 end 487 488 return @status 489 490
exec sp_procxmode 'sp_bindcache', 'AnyMode' go Grant Execute on sp_bindcache to public go
| DEFECTS | |
 MINU 4 Unique Index with nullable columns master..sysconfigures | master..sysconfigures |
| MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes | sybsystemprocs..sysattributes |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} | 298 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} | 375 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 115 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 212 |
| QTYP 4 Comparison type mismatch smallint = int | 212 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 225 |
| QTYP 4 Comparison type mismatch smallint = int | 225 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 249 |
| QTYP 4 Comparison type mismatch smallint = int | 249 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 272 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 312 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 335 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 336 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 420 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 421 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 430 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 431 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 447 |
| QTYP 4 Comparison type mismatch smallint = int | 447 |
| TNOU 4 Table with no unique index master..spt_values | master..spt_values |
| VRUN 4 Variable is read and not initialized @tdb_instancename | 456 |
 MGTP 3 Grant to public master..spt_values | |
| MGTP 3 Grant to public master..sysconfigures | |
| MGTP 3 Grant to public master..sysdatabases | |
| MGTP 3 Grant to public sybsystemprocs..sp_bindcache | |
| MGTP 3 Grant to public sybsystemprocs..sysattributes | |
| MNAC 3 Not using ANSI 'is null' | 216 |
| MNAC 3 Not using ANSI 'is null' | 228 |
| MNER 3 No Error Check should check @@error after insert | 401 |
| MNER 3 No Error Check should check @@error after update | 417 |
| MNER 3 No Error Check should check @@error after delete | 429 |
| MNER 3 No Error Check should check return value of exec | 484 |
| MUCO 3 Useless Code Useless Brackets | 92 |
| MUCO 3 Useless Code Useless Brackets | 103 |
| MUCO 3 Useless Code Useless Brackets | 104 |
| MUCO 3 Useless Code Useless Brackets | 107 |
| MUCO 3 Useless Code Useless Brackets | 118 |
| MUCO 3 Useless Code Useless Brackets | 136 |
| MUCO 3 Useless Code Useless Brackets | 220 |
| MUCO 3 Useless Code Useless Brackets | 231 |
| MUCO 3 Useless Code Useless Brackets | 239 |
| MUCO 3 Useless Code Useless Brackets | 301 |
| MUCO 3 Useless Code Useless Brackets | 316 |
| MUCO 3 Useless Code Useless Brackets | 376 |
| MUCO 3 Useless Code Useless Brackets | 382 |
| MUCO 3 Useless Code Useless Brackets | 443 |
| MUCO 3 Useless Code Useless Brackets | 448 |
| MUCO 3 Useless Code Useless Brackets | 463 |
| MUCO 3 Useless Code Useless Brackets | 477 |
| QAFM 3 Var Assignment from potentially many rows | 296 |
| QAFM 3 Var Assignment from potentially many rows | 373 |
| QISO 3 Set isolation level | 96 |
| QIWC 3 Insert with not all columns specified missing 8 columns out of 15 | 402 |
| 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_info1, object_type, object, attribute, class} | 335 |
| 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_info1, object_type, object, attribute, class} | 420 |
| 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_info1, object_type, object, attribute, class} | 430 |
| VNRD 3 Variable is not read @ownerid | 178 |
| VUNU 3 Variable is not used @separator | 61 |
| VUNU 3 Variable is not used @cachecount | 63 |
| VUNU 3 Variable is not used @objtype | 67 |
| VUNU 3 Variable is not used @local_tempdb_mask | 70 |
| VUNU 3 Variable is not used @tdb_instanceid | 72 |
| VUNU 3 Variable is not used @sqlbuf | 74 |
 MSUB 2 Subquery Marker | 211 |
| MSUB 2 Subquery Marker | 224 |
| MSUB 2 Subquery Marker | 334 |
| MTR1 2 Metrics: Comments Ratio Comments: 49% | 47 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 41 = 59dec - 20exi + 2 | 47 |
| MTR3 2 Metrics: Query Complexity Complexity: 242 | 47 |
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_aux_parse_entity reads table sybsystemprocs..sysindexes reads table sybsystemprocs..sysobjects reads table master..spt_values (1)  reads table master..sysdatabases (1) read_writes table sybsystemprocs..sysattributes calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..sysconfigures (1) CALLERS called by proc sybsystemprocs..sp_unbindcache |