Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dboption ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/a_values */ 4 5 /* 6 ** Messages for "sp_dboption" 17420 7 ** Use "langid" when looking at spt_values ??? 8 ** 9 ** 17260, "Can't run %1! from within a transaction." 10 ** 17420, "Settable database options." 11 ** 17421, "No such database -- run sp_helpdb to list databases." 12 ** 17422, "The 'master' database's options cannot be changed." 13 ** 17423, "Usage: sp_dboption [dbname, optname, {true | false}]" 14 ** 17424, "The database option does not exist or you cannot set the option." 15 ** 17425, "Run sp_dboption with no parameters to see options." 16 ** 17426, "Database option is not unique." 17 ** 17428, "You must be in the 'master' database in order to change 18 ** database options." 19 ** 17430, "Run the CHECKPOINT command in the database that was changed." 20 ** 17431, "true" 21 ** 17432, "false" 22 ** 17433, "Database option '%1!' turned ON for database '%2!'." 23 ** 17434, "Database option '%1!' turned OFF for database '%2!'." 24 ** 17289, "Set your curwrite to the hurdle of current database." 25 ** 17436, "You cannot set the '%1!' option for a temporary database" 26 ** 17439, "You cannot turn on ''%1!' for '%2!' because it is an HA server 27 ** that has been configured with the proxy_db option." 28 ** 18974, "Warning: Attempting to change database options for a 29 ** temporary database. Database options must be kept consistent 30 ** across all temporary databases." 31 ** 19015, "The 'async log service' option is only valid for configurations 32 ** with more than 4 engines online." 33 ** 19112, "Running CHECKPOINT on database '%1!' for option '%2!' to take effect." 34 ** 19113, "CHECKPOINT command failed. Run the CHECKPOINT command in database '%1!' for the 35 ** change to take effect." 36 ** 19424, "Cannot run '%1!' on an archive database." 37 ** 19884, "You cannot set the '%1!' option to '%2!' for a temporary database." 38 ** 19518, "Warning: Attempting to change database options for a local 39 ** temporary database. Database options must be kept consistent 40 ** across all local temporary databases." 41 ** 19886, "Please execute DUMP DATABASE before executing DUMP TRANSACTION." 42 ** 19892, "DUMP TRANSACTION is not allowed when '%1!' is ON. Use DUMP DATABASE 43 ** or DUMP TRANSACTION ... WITH TRUNCATE_ONLY or WITH NO_LOG instead." 44 ** 19987, "You cannot set the '%1!' option for temporary databases or for 45 ** databases with durability %2!" 46 ** 17938, "You cannot set the '%1!' database option for the in-memory database '%2!'." 47 ** 17939, "The option '%1!' has been set in database '%2!' but will be ignored 48 until you change the durability of the database to FULL." 49 ** 17952, "The option '%1!' applies only to databases with a segregated log. 50 ** The option has been set in database '%2!' but will be ignored while 51 ** the log and data segments remain mixed." 52 ** 17954, "You cannot set the '%1!' database option to true when the '%2!' 53 ** database option is on for database '%3!'." 54 ** 17955, "You cannot set the '%1!' database option for the read-only 55 ** database '%2!'." 56 ** 17956, "You cannot set the '%1!' database option for database '%2!' 57 ** because it has been brought online for standby access." 58 ** 17957, "You cannot set the '%1!' database option because the database 59 ** '%2!' is either marked as needing a database dump, an unlogged 60 ** operation was performed or the transaction log was truncated. Dump 61 ** the database, then retry setting the option." 62 ** 17958, "The database options 'select into/bulkcopy/pllsort' or 'trunc log 63 ** on chkpt' are invalid when the '%1!' database option is set to 64 ** true. These options will be turned off for database '%2!'." 65 */ 66 67 create procedure sp_dboption 68 @dbname varchar(30) = NULL, /* database name to change */ 69 @optname varchar(36) = NULL, /* option name to turn on/off */ 70 @optvalue varchar(10) = NULL, /* true or false */ 71 @dockpt tinyint = 1 /* 72 ** 0 indicates don't run checkpoint 73 ** else run checkpoint automatically 74 */ 75 as 76 77 declare @dbid int, /* dbid of the database */ 78 @dbuid int, /* id of the owner of the database */ 79 @statvalue int, /* number of option */ 80 @stattype char(2), /* status field flag */ 81 @statopt smallint, /* option mask, part 1 */ 82 @stat2opt smallint, /* option mask, part 2 */ 83 @stat3opt int, /* option mask, part 3 */ 84 @stat4opt int, /* option mask, part 4 */ 85 @optcount int, /* number of options like @optname */ 86 @success_msg varchar(1024), /* success status message */ 87 @msg varchar(1024), 88 @sptlang int, 89 @retstat int, 90 @true varchar(10), 91 @false varchar(10), 92 @whichone int, /* which language? */ 93 @optmsgnum int, /* identify one msgnum to compare */ 94 @msgcnt int, /* count distinct dups */ 95 @tempdb_mask int, /* all tempdb status bits */ 96 @local_tempdb_mask int, /* all local tempdb status bits */ 97 @isatempdb int, /* changing options on a temp. db ? */ 98 @isinmemdb int, /* changing options on inmemory db ? */ 99 @issystemdb int, /* changing options on system db ? */ 100 @ismixedlog int, /* does database have mixed log,data */ 101 @engines int, /* Get the number of engines */ 102 @isarchivedb int, /* Is this an archive database? */ 103 @DMPX_NOTOK_NONLOGGED_WRITES int, /* Same definition as in dmpld.h */ 104 @DMPX_NOTOK_TRUNCATED_ONLY int, /* Same definition as in dmpld.h */ 105 @durability varchar(30), 106 @dur int, 107 @tran_status int, /* status returned from 108 ** tran_dumpable_status(). 109 */ 110 @selbulk int, /* status of 111 ** "select into/bulkcopy/pllsort" 112 ** option. 113 */ 114 @enforcedumptranseq 115 int, /* status of "enforce dump tran 116 ** sequence" option. 117 */ 118 119 @on int, 120 @error int, 121 @msgno int, 122 @max_msg_len int, 123 @sqlstr varchar(1000), 124 @flstr char(17) 125 126 if @@trancount = 0 127 begin 128 set chained off 129 end 130 131 select @sptlang = @@langid, @whichone = 0, @isatempdb = 0, @isarchivedb = 0, 132 @issystemdb = 0, @isinmemdb = 0, @retstat = 0, @dur = 1, 133 @flstr = "full logging for " 134 135 if @@langid != 0 136 begin 137 if not exists ( 138 select * from master.dbo.sysmessages where error 139 between 17050 and 17069 140 and langid = @@langid) 141 select @sptlang = 0 142 end 143 144 /* 145 ** If no @dbname given, just list the possible dboptions. 146 ** Only certain status bits may be set or cleared. 147 ** settable not settable 148 ** ------------------------------ -------------------------- 149 ** allow select into/bulkcopy (4) don't recover (32) 150 ** truncate log on checkpoint (8) not recovered (256) 151 ** no checkpoint on recovery (16) 152 ** ddl in tran (512) 153 ** read only (1024) 154 ** dbo use only (2048) 155 ** single user (4096) 156 ** allow null (8192) 157 ** ALL SETTABLE OPTIONS (15900) 158 ** abort xact on log full (1, type='D2') 159 ** no space accounting (2, type='D2') 160 ** auto identity(4, type='D2') 161 ** identity in nonunique index(8, type='D2') 162 ** auto identity unique index(64, type='D2') 163 ** async log service (1024 type='D3') 164 ** delayed commit (2048 type='D3') 165 ** scratch database (16777216 type='D3') 166 ** 167 ** off-sysdatabases options (type = 'D') 168 ** ------------------------------------ 169 ** select into (0x00000001) 170 ** alter table (0x00000004) 171 ** reorg rebuild (0x00000008) 172 */ 173 174 /* 175 ** Look for the "settable options" mask in spt_values 176 */ 177 select @statopt = number 178 from master.dbo.spt_values 179 where type = "D" 180 and name = "ALL SETTABLE OPTIONS" 181 182 select @stat2opt = number 183 from master.dbo.spt_values 184 where type = "D2" 185 and name = "ALL SETTABLE OPTIONS" 186 187 select @stat3opt = number 188 from master.dbo.spt_values 189 where type = "D3" 190 and name = "ALL SETTABLE OPTIONS" 191 192 select @stat4opt = number 193 from master.dbo.spt_values 194 where type = "D4" 195 and name = "ALL SETTABLE OPTIONS" 196 197 /* 198 ** If we can't find the option masks, guess at them 199 */ 200 if @statopt is null 201 select @statopt = 4 | 8 | 16 | 512 | 1024 | 2048 | 4096 | 8192 202 if @stat2opt is null 203 select @stat2opt = 1 | 2 | 4 | 8 | 64 204 if @stat3opt is null 205 select @stat3opt = 1024 | 2048 | 16777216 206 if @stat4opt is null 207 select @stat4opt = 32768 208 209 if @dbname is null 210 begin 211 exec sp_getmessage 17420, @msg output 212 print @msg 213 214 if @sptlang = 0 215 begin 216 select database_options = name 217 from master.dbo.spt_values 218 where ((type = "D" 219 and number & @statopt = number 220 and number & @statopt != @statopt) 221 or (type = "D2" 222 and number & @stat2opt = number 223 and number & @stat2opt != @stat2opt) 224 or (type = "D3" 225 and number & @stat3opt = number 226 and name != "ALL SETTABLE OPTIONS") 227 or (type = "D4" 228 and number & @stat4opt = number 229 and name != "ALL SETTABLE OPTIONS")) 230 union 231 select database_options = convert(char(32), @flstr + char_value) 232 from master.dbo.sysattributes 233 where class = 38 and object = 1 234 order by 1 235 end 236 else 237 begin 238 /* Compute the maximum length of the dboption message text */ 239 select @max_msg_len = max(datalength(description)) 240 from master.dbo.spt_values, master.dbo.sysmessages 241 where ((type = "D" 242 and number & @statopt = number 243 and number & @statopt != @statopt) 244 or (type = "D2" 245 and number & @stat2opt = number 246 and number & @stat2opt != @stat2opt) 247 or (type = "D3" 248 and number & @stat3opt = number 249 and name != "ALL SETTABLE OPTIONS") 250 or (type = "D4" 251 and number & @stat4opt = number 252 and name != "ALL SETTABLE OPTIONS")) 253 and msgnum = error 254 and langid = @sptlang 255 256 /* Use the above computed length in our select query */ 257 select @sqlstr = 258 'select database_options = name, convert(char(' 259 + convert(varchar, @max_msg_len) 260 + '), description) 261 from master.dbo.spt_values, master.dbo.sysmessages 262 where ((type = "D" 263 and number & @statopt = number 264 and number & @statopt != @statopt) 265 or (type = "D2" 266 and number & @stat2opt = number 267 and number & @stat2opt != @stat2opt) 268 or (type = "D3" 269 and number & @stat3opt = number 270 and name != "ALL SETTABLE OPTIONS") 271 or (type = "D4" 272 and number & @stat4opt = number 273 and name != "ALL SETTABLE OPTIONS")) 274 and msgnum = error 275 and langid = @sptlang 276 union 277 select database_options = convert(char(32), @flstr + char_value), "" 278 from master.dbo.sysattributes 279 where class = 38 and object = 1 280 order by 1' 281 /* Now execute the formulated SQL statement */ 282 exec (@sqlstr) 283 end 284 285 return (0) 286 end 287 288 set transaction isolation level 1 289 290 /* 291 ** Verify the database name and get @dbid, @dbuid and @ismixedlog 292 ** status. 293 */ 294 select @dbid = dbid, @dbuid = suid, 295 @ismixedlog = status2 & 32768, 296 @isinmemdb = status4 & 4096, 297 @dur = durability 298 from master.dbo.sysdatabases 299 where name = @dbname 300 301 /* If @dbname not found, say so. */ 302 if @dbid is NULL 303 begin 304 raiserror 17421 305 return (1) 306 end 307 308 /* 309 ** You can not change any of the options in master. If the user tries to 310 ** do so tell them they can't. 311 */ 312 if @dbid = 1 313 begin 314 raiserror 17422 315 return (1) 316 end 317 318 /* 319 ** To update master tables we should be in the master database, 320 ** otherwise, if the database where the transaction starts becomes 321 ** unrecoverable, we could have problems in the recovery of master itself. 322 */ 323 if db_name() != "master" 324 begin 325 raiserror 17428 326 return (1) 327 end 328 329 exec sp_getmessage 17431, @true out 330 exec sp_getmessage 17432, @false out 331 332 if @optname is NULL or lower(@optvalue) not in 333 ("true", "false", @true, @false) or @optvalue is null 334 begin 335 raiserror 17423 336 return (1) 337 end 338 339 if lower(@optvalue) in ("true", @true) 340 select @on = 1, @msgno = 17433 341 else 342 select @on = 0, @msgno = 17434 343 344 if lower(@optname) like "full logging for%" 345 begin 346 select @success_msg = @optname 347 348 if (@isinmemdb != 0) 349 begin 350 raiserror 17938, @success_msg, @dbname 351 return 1 352 end 353 354 /* 355 ** Trim the 'full logging for' prefix to pass 356 ** the actual option to sp_dboption_flmode 357 */ 358 select @optname = ltrim(substring(@optname, 18, datalength(@optname) - 17)) 359 exec @retstat = sp_dboption_flmode @dbname, @optname, @optvalue 360 361 if @retstat = 0 362 begin 363 if (@on = 1) 364 begin 365 if (@dur > 1) 366 select @error = 17939 367 else if (@ismixedlog = 32768) 368 select @error = 17952 369 else 370 select @error = 0 371 372 if (@error != 0) 373 begin 374 exec sp_getmessage @error, @msg output 375 print @msg, @success_msg, @dbname 376 end 377 end 378 goto print_and_ckpt 379 end 380 381 return @retstat 382 end 383 384 /* 385 ** Only the Database Owner (DBO) or 386 ** Accounts with SA role can execute it. 387 ** Call proc_role() with the required SA role. 388 */ 389 if ((suser_id() != @dbuid) and (proc_role("sa_role") < 1)) 390 return (1) 391 392 /* 393 ** Determine if we are changing options for a temporary database. 394 */ 395 select @tempdb_mask = number 396 from master.dbo.spt_values 397 where type = "D3" and name = "TEMPDB STATUS MASK" 398 399 if (@dbname in ("master", "model", "sybsystemdb", 400 "sybsystemprocs", "sybsecurity", "dbccdb", 401 "dbccalt", "sybsyntax")) 402 begin 403 select @issystemdb = 1 404 end 405 else 406 if (@dbid = 2) or exists (select * from master.dbo.sysdatabases 407 where dbid = @dbid 408 and (status3 & @tempdb_mask) != 0) 409 begin 410 select @isatempdb = 1 411 end 412 else 413 if exists (select * from master.dbo.sysdatabases 414 where dbid = @dbid 415 and (status3 & 4194304) = 4194304) 416 begin 417 select @isarchivedb = 1 418 end 419 420 select @durability = db_attr(@dbid, "durability") 421 422 /* 423 ** If attempting to change the options for a temporary database, the user 424 ** should be warned that database options across all temporary databases 425 ** should be kept consistent. 426 */ 427 select @local_tempdb_mask = 0 428 429 if (@@clustermode != "shared disk cluster" and @isatempdb = 1) 430 begin 431 exec sp_getmessage 18974, @msg output 432 print @msg 433 end 434 else if (@@clustermode = "shared disk cluster") 435 begin 436 select @local_tempdb_mask = number 437 from master.dbo.spt_values 438 where type = "D3" and name = "LOCAL TEMPDB STATUS MASK" 439 440 if exists (select * from master.dbo.sysdatabases 441 where dbid = @dbid 442 and (status3 & @local_tempdb_mask) != 0) 443 begin 444 /* 445 ** In SDC, #table appears only in local tempdbs, global tempdb 446 ** is mostly like the regular databases, dboptions doesn't 447 ** need to be same among global tempdbs. 448 ** 449 ** 19518, "Warning: Attempting to change database options for a 450 ** local temporary database. Database options must be 451 ** kept consistent across all local temporary 452 ** databases." 453 */ 454 exec sp_getmessage 19518, @msg output 455 print @msg 456 end 457 end 458 459 /* 460 ** Use @optname and try to find the right option. 461 ** If there isn't just one, print appropriate diagnostics and return. 462 */ 463 select @optcount = count(*) 464 from master.dbo.spt_values 465 where name like "%" + @optname + "%" 466 and ((type = "D" 467 and number & @statopt = number) 468 or (type = "D2" 469 and number & @stat2opt = number) 470 or (type = "D3" 471 and number & @stat3opt = number) 472 or (type = "D4" 473 and number & @stat4opt = number)) 474 /* 475 ** If more than one option like @optname, make sure they are not the same 476 ** option ("trunc" and "trunc.", for example) 477 */ 478 if @optcount > 1 479 begin 480 select @optmsgnum = msgnum 481 from master.dbo.spt_values 482 where name like "%" + @optname + "%" 483 and ((type = "D" 484 and number & @statopt = number) 485 or (type = "D2" 486 and number & @stat2opt = number) 487 or (type = "D3" 488 and number & @stat3opt = number) 489 or (type = "D4" 490 and number & @stat4opt = number)) 491 492 select @msgcnt = count(msgnum) 493 from master.dbo.spt_values 494 where name like "%" + @optname + "%" 495 and ((type = "D" 496 and number & @statopt = number) 497 or (type = "D2" 498 and number & @stat2opt = number) 499 or (type = "D3" 500 and number & @stat3opt = number) 501 or (type = "D4" 502 and number & @stat4opt = number)) 503 and msgnum != @optmsgnum 504 505 /* 506 ** msgcnt of 0 indicates we really have just 1 unique dboption, 507 ** probably due to alternate spelling. 508 */ 509 if (@msgcnt = 0) 510 select @optcount = 1 511 end 512 /* 513 ** If no option, and alternate language is set, use other language 514 */ 515 if @optcount = 0 and @sptlang != 0 516 begin 517 select @optcount = count(*) 518 from master.dbo.spt_values, master.dbo.sysmessages 519 where description like "%" + @optname + "%" 520 and ((type = "D" 521 and number & @statopt = number) 522 or (type = "D2" 523 and number & @stat2opt = number) 524 or (type = "D3" 525 and number & @stat3opt = number) 526 or (type = "D4" 527 and number & @stat4opt = number)) 528 and msgnum = error 529 and langid = @sptlang 530 select @whichone = 1 531 /* 532 ** If more than one option like @optname, make sure they are not the same 533 ** option ("trunc" and "trunc.", for example) 534 */ 535 if @optcount > 1 536 begin 537 select @optmsgnum = msgnum 538 from master.dbo.spt_values, master.dbo.sysmessages 539 where description like "%" + @optname + "%" 540 and ((type = "D" 541 and number & @statopt = number) 542 or (type = "D2" 543 and number & @stat2opt = number) 544 or (type = "D3" 545 and number & @stat3opt = number) 546 or (type = "D4" 547 and number & @stat4opt = number)) 548 and msgnum = error 549 and langid = @sptlang 550 551 select @msgcnt = count(msgnum) 552 from master.dbo.spt_values, master.dbo.sysmessages 553 where description like "%" + @optname + "%" 554 and ((type = "D" 555 and number & @statopt = number) 556 or (type = "D2" 557 and number & @stat2opt = number) 558 or (type = "D3" 559 and number & @stat3opt = number) 560 or (type = "D4" 561 and number & @stat4opt = number)) 562 and msgnum = error 563 and langid = @sptlang 564 and msgnum != @optmsgnum 565 566 /* 567 ** msgcnt of 0 indicates we really have just 1 unique dboption, 568 ** probably due to alternate spelling. 569 */ 570 if (@msgcnt = 0) 571 select @optcount = 1 572 end 573 end 574 575 /* 576 ** If no option, show the user what the options are. 577 */ 578 if @optcount = 0 579 begin 580 raiserror 17424 581 582 exec sp_getmessage 17425, @msg output 583 print @msg 584 return (1) 585 end 586 587 /* 588 ** If more than one option like @optname, show the duplicates and return. 589 */ 590 if @optcount > 1 591 begin 592 raiserror 17426 593 594 if @sptlang = 0 595 select duplicate_options = name 596 from master.dbo.spt_values 597 where name like "%" + @optname + "%" 598 and ((type = "D" 599 and number & @statopt = number) 600 or (type = "D2" 601 and number & @stat2opt = number) 602 or (type = "D3" 603 and number & @stat3opt = number) 604 or (type = "D4" 605 and number & @stat4opt = number)) 606 else 607 select duplicate_options = name, convert(char(22), description) 608 from master.dbo.spt_values, master.dbo.sysmessages 609 where (name like "%" + @optname + "%" 610 or description like "%" + @optname + "%") 611 and ((type = "D" 612 and number & @statopt = number) 613 or (type = "D2" 614 and number & @stat2opt = number) 615 or (type = "D3" 616 and number & @stat3opt = number) 617 or (type = "D4" 618 and number & @stat4opt = number)) 619 and msgnum = error 620 and langid = @sptlang 621 622 return (1) 623 end 624 625 /* 626 ** User cannot set a temporary database in single user mode or as a scratch 627 ** database. 628 */ 629 select @statvalue = number 630 from master.dbo.spt_values 631 where name like "%" + @optname + "%" 632 and ((type = "D" 633 and number & @statopt = number) 634 or (type = "D2" 635 and number & @stat2opt = number) 636 or (type = "D3" 637 and number & @stat3opt = number) 638 or (type = "D4" 639 and number & @stat4opt = number)) 640 641 if (@isatempdb = 1) and ((@statvalue & (4096 | 16777216)) != 0) 642 begin 643 raiserror 17436, @optname 644 return (1) 645 end 646 647 /* disallow turning off the 'select into' option for a temporary database. */ 648 if (@isatempdb = 1) 649 and (@statvalue = 4) 650 and (lower(@optvalue) in ('false', @false)) 651 begin 652 raiserror 19884, @optname, @false 653 return (1) 654 end 655 /* 656 ** Only the 'single user option' may be changed in an archive database. 657 */ 658 if (@isarchivedb = 1) and (@statvalue != 4096) 659 begin 660 raiserror 19424, "sp_dboption" 661 return (1) 662 end 663 664 /* 665 ** If we're in a transaction, disallow this since it might make recovery 666 ** impossible. 667 */ 668 if @@trancount > 0 669 begin 670 raiserror 17260, "sp_dboption" 671 return (1) 672 end 673 else 674 begin 675 set chained off 676 end 677 678 set transaction isolation level 1 679 680 /* 681 ** Get the number which is the bit value to set 682 */ 683 if @whichone = 0 684 select @statvalue = number, @stattype = type, @success_msg = name 685 from master.dbo.spt_values 686 where name like "%" + @optname + "%" 687 and ((type = "D" 688 and number & @statopt = number) 689 or (type = "D2" 690 and number & @stat2opt = number) 691 or (type = "D3" 692 and number & @stat3opt = number) 693 or (type = "D4" 694 and number & @stat4opt = number)) 695 else 696 select @statvalue = number, @stattype = type, @success_msg = name 697 from master.dbo.spt_values, master.dbo.sysmessages 698 where description like "%" + @optname + "%" 699 and ((type = "D" 700 and number & @statopt = number) 701 or (type = "D2" 702 and number & @stat2opt = number) 703 or (type = "D3" 704 and number & @stat3opt = number) 705 or (type = "D4" 706 and number & @stat4opt = number)) 707 and msgnum = error 708 and langid = @sptlang 709 710 /* 711 ** We do not allow 'sybsecurity' to be set to 'single user' since, 712 ** if auditing is enabled and we try to set 'sybsecurity' database to 713 ** 'single user' then, the audit process is killed because audit process 714 ** tries to do 'usedb' and it fails (look at utils/auditing.c). 715 */ 716 if (@dbname = "sybsecurity") and (@statvalue = 4096) 717 begin 718 /* 719 ** 17435, "The 'single user' option is not valid for the 720 ** 'sybsecurity' database. 721 */ 722 raiserror 17435 723 return (1) 724 end 725 726 /* 727 ** We do not allow Async logging services on system databases, MLD databases 728 ** or for configurations with less than 4 engines online. 729 */ 730 if (@statvalue = 1024) and (@stattype = "D3") 731 begin 732 /* 733 ** Not valid for master, model and systemp tempdb, user define tempdbs and 734 ** the sybsecurity database 735 */ 736 if (@issystemdb = 1) or (@isatempdb = 1) 737 begin 738 /* 739 ** 18984, The 'async log service' option is not valid 740 ** for system databases 741 */ 742 raiserror 18984 743 return (1) 744 end 745 746 /* We don't allow this option for databases with mixed log and data */ 747 if (@ismixedlog = 32768) 748 begin 749 /* 750 ** 18986, The 'async log service' option is not valid for 751 ** databases with log and data on the same device. 752 */ 753 raiserror 18986, @optname 754 return (1) 755 end 756 757 select @engines = count(engine) 758 from master.dbo.sysengines 759 where status != "offline" 760 761 if (@engines < 4) 762 begin 763 /* 764 ** 19015, The 'async log service' option is only valid for 765 ** configurations with at least 4 engines online. 766 */ 767 raiserror 19015 768 return (1) 769 end 770 end 771 772 /* 773 ** For IMDB/RDDB databases disallow 'delayed commit', 'async log service' 774 ** and 'scratch database'. 775 */ 776 if ((@durability in ('at_shutdown', 'no_recovery')) 777 and (@statvalue in (1024, 2048, 16777216)) 778 and (@stattype = "D3") 779 and (lower(@optvalue) in ("true", @true)) 780 ) 781 begin 782 select @durability = upper(@durability) 783 raiserror 19987, @optname, @durability 784 return (1) 785 end 786 787 /* 788 ** If "enforce dump tran sequence" is ON then disallow turning ON 789 ** "trunc log on chkpt", "select into/bulkcopy/pllsort" and 790 ** "read only" options. 791 */ 792 if (((@statvalue = 4) or (@statvalue = 8) or (@statvalue = 1024)) 793 and (@stattype = "D") 794 and (lower(@optvalue) in ('true', @true))) 795 begin 796 select @enforcedumptranseq = status4 & 32768 797 from master.dbo.sysdatabases 798 where dbid = @dbid 799 800 if (@enforcedumptranseq = 32768) 801 begin 802 raiserror 17954, @optname, "enforce dump tran sequence", @dbname 803 return (1) 804 end 805 end 806 807 /* 808 ** We do not allow "enforce dump tran sequence" option if... 809 */ 810 if (@statvalue = 32768) and (@stattype = "D4") 811 begin 812 /* 813 ** ... database is a temporary database. 814 */ 815 if (@isatempdb = 1) 816 begin 817 raiserror 17436, @optname 818 return (1) 819 end 820 821 select @tran_status = tran_dumpable_status(@dbname) 822 823 /* 824 ** ... database is a MLD database. 825 */ 826 if ((@tran_status & 2 = 2) or (@tran_status & 4 = 4)) 827 begin 828 raiserror 18986, @optname 829 return (1) 830 end 831 832 /* 833 ** ... database has "at_shutdown" or "no_recovery" durability. 834 */ 835 if (@tran_status & 128 = 128) 836 begin 837 select @durability = upper(@durability) 838 raiserror 19987, @optname, @durability 839 return (1) 840 end 841 842 /* 843 ** ... database is a read-only database. 844 */ 845 if (@tran_status & 256 = 256) 846 begin 847 raiserror 17955, @optname, @dbname 848 return (1) 849 end 850 851 /* 852 ** ... database has been brought onlinne for standby access. 853 */ 854 if (@tran_status & 512 = 512) 855 begin 856 raiserror 17956, @optname, @dbname 857 return (1) 858 end 859 860 /* 861 ** ... database is an archive database. 862 */ 863 if (@tran_status & 1024 = 1024) 864 begin 865 raiserror 19424, "sp_dboption" 866 return (1) 867 end 868 869 /* 870 ** If this database is either marked as needing a database dump, or an 871 ** unlogged operation was performed in this database, or the database 872 ** was truncated then ask the user to first perform a dump database 873 ** before turning on this option. 874 */ 875 if ((@tran_status & 16 = 16) or (@tran_status & 32 = 32) or 876 (@tran_status & 64 = 64)) 877 begin 878 raiserror 17957, @optname, @dbname 879 return (1) 880 end 881 882 /* 883 ** Options 'select into/bulkcopy/pllsort' or 'trunc log on chkpt' are 884 ** not valid with "enforce dump tran sequence" option. Therefore, 885 ** turn OFF these options. 886 */ 887 select @selbulk = status & 4 888 from master.dbo.sysdatabases 889 where dbid = @dbid 890 891 if ((@tran_status & 8 = 8) or (@selbulk = 4)) 892 begin 893 update master.dbo.sysdatabases 894 set status = status & ~ (4 | 8) 895 where dbid = @dbid 896 raiserror 17958, @optname, @dbname 897 end 898 end 899 900 /* 901 ** Now update sysdatabases. 902 */ 903 if (@on = 1) 904 begin 905 /* 906 ** If this is the option to set 'abort tran on log full' to 907 ** true for sybsecurity database, then don't allow. 908 */ 909 if (db_name(@dbid) = "sybsecurity" 910 and @stattype = "D2" and @statvalue = 1) 911 begin 912 /* 913 ** AUDIT_CHANGE: New error message needs to be reserved and 914 ** the print statement needs to be removed. 915 */ 916 print "You cannot set 'abort tran on log full' to true for sybsecurity database." 917 return (1) 918 end 919 920 /* 921 ** Disallow DDL IN TRAN 922 ** if proxydb option is set (@@crthaproxy = 1) 923 ** if this server is a HA server (@@cmpstate >= 0) 924 ** if database is not a tempdb 925 */ 926 if ((@statvalue = 512) and (@@crthaproxy = 1) and 927 (@@cmpstate >= 0) and (@isatempdb = 0)) 928 begin 929 /* 930 ** Cannot set DDL_IN_TRAN option for HA servers 931 ** configured with proxy_db option. 932 */ 933 exec sp_getmessage 17439, @msg output 934 print @msg, @success_msg, @dbname 935 return (1) 936 end 937 938 if (@stattype = "D") 939 update master.dbo.sysdatabases 940 set status = status | @statvalue 941 where dbid = @dbid 942 else if (@stattype = "D2") 943 update master.dbo.sysdatabases 944 set status2 = status2 | @statvalue 945 where dbid = @dbid 946 else if (@stattype = "D3") 947 update master.dbo.sysdatabases 948 set status3 = status3 | @statvalue 949 where dbid = @dbid 950 else 951 update master.dbo.sysdatabases 952 set status4 = status4 | @statvalue 953 where dbid = @dbid 954 end 955 956 /* 957 ** We want to turn it off. 958 */ 959 else 960 begin 961 962 if (@stattype = "D") 963 update master.dbo.sysdatabases 964 set status = status & ~ @statvalue 965 where dbid = @dbid 966 else if (@stattype = "D2") 967 update master.dbo.sysdatabases 968 set status2 = status2 & ~ @statvalue 969 where dbid = @dbid 970 else if (@stattype = "D3") 971 update master.dbo.sysdatabases 972 set status3 = status3 & ~ @statvalue 973 where dbid = @dbid 974 else 975 update master.dbo.sysdatabases 976 set status4 = status4 & ~ @statvalue 977 where dbid = @dbid 978 end 979 980 print_and_ckpt: 981 982 exec sp_getmessage @msgno, @msg output 983 print @msg, @success_msg, @dbname 984 985 if (@dockpt != 0) 986 begin 987 exec sp_getmessage 19112, @msg output 988 print @msg, @dbname, @success_msg 989 990 /* 991 ** Run the CHECKPOINT command on the database that was changed. If 992 ** that fails advise the user to run the CHECKPOINT command in the database 993 ** that was changed. 994 */ 995 checkpoint @dbname 996 997 if (@@error != 0) 998 begin 999 exec sp_getmessage 19113, @msg output 1000 print @msg, @dbname 1001 end 1002 end 1003 else 1004 begin 1005 exec sp_getmessage 17430, @msg output 1006 print @msg 1007 end 1008 1009 return (0) 1010 1011
exec sp_procxmode 'sp_dboption', 'AnyMode' go Grant Execute on sp_dboption to public go
RESULT SETS | |
sp_dboption_rset_003 | |
sp_dboption_rset_002 | |
sp_dboption_rset_001 |
DEFECTS | |
![]() | master..sysattributes |
![]() | master..sysmessages |
![]() | 177 |
![]() | 182 |
![]() | 359 |
![]() (number, type) Intersection: {type} | 179 |
![]() (number, type) Intersection: {type} | 184 |
![]() (number, type) Intersection: {type} | 189 |
![]() (number, type) Intersection: {type} | 194 |
![]() (number, type) Intersection: {type} | 397 |
![]() (number, type) Intersection: {type} | 438 |
![]() (number, type) Intersection: {type} | 465 |
![]() (number, type) Intersection: {type} | 482 |
![]() (number, type) Intersection: {type} | 494 |
![]() (number, type) Intersection: {type} | 520 |
![]() (number, type) Intersection: {type} | 540 |
![]() (number, type) Intersection: {type} | 554 |
![]() (number, type) Intersection: {type} | 597 |
![]() (number, type) Intersection: {type} | 609 |
![]() (number, type) Intersection: {type} | 631 |
![]() (number, type) Intersection: {type} | 686 |
![]() (number, type) Intersection: {type} | 699 |
![]() | 140 |
![]() | 140 |
![]() | 233 |
![]() | 254 |
![]() | 254 |
![]() | 407 |
![]() | 407 |
![]() | 414 |
![]() | 414 |
![]() | 441 |
![]() | 441 |
![]() | 529 |
![]() | 529 |
![]() | 549 |
![]() | 549 |
![]() | 563 |
![]() | 563 |
![]() | 620 |
![]() | 620 |
![]() | 708 |
![]() | 708 |
![]() | 798 |
![]() | 798 |
![]() | 889 |
![]() | 889 |
![]() | 895 |
![]() | 895 |
![]() | 941 |
![]() | 941 |
![]() | 945 |
![]() | 945 |
![]() | 949 |
![]() | 949 |
![]() | 953 |
![]() | 953 |
![]() | 965 |
![]() | 965 |
![]() | 969 |
![]() | 969 |
![]() | 973 |
![]() | 973 |
![]() | 977 |
![]() | 977 |
![]() | master..sysengines |
![]() | master..spt_values |
![]() | 67 |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 231 |
![]() | 211 |
![]() | 329 |
![]() | 330 |
![]() | 374 |
![]() | 431 |
![]() | 454 |
![]() | 582 |
![]() | 893 |
![]() | 933 |
![]() | 939 |
![]() | 943 |
![]() | 947 |
![]() | 951 |
![]() | 963 |
![]() | 967 |
![]() | 971 |
![]() | 975 |
![]() | 982 |
![]() | 987 |
![]() | 999 |
![]() | 1005 |
![]() | 218 |
![]() | 285 |
![]() | 305 |
![]() | 315 |
![]() | 326 |
![]() | 336 |
![]() | 348 |
![]() | 363 |
![]() | 365 |
![]() | 367 |
![]() | 372 |
![]() | 389 |
![]() | 390 |
![]() | 399 |
![]() | 429 |
![]() | 434 |
![]() | 509 |
![]() | 570 |
![]() | 584 |
![]() | 622 |
![]() | 644 |
![]() | 653 |
![]() | 661 |
![]() | 671 |
![]() | 723 |
![]() | 743 |
![]() | 747 |
![]() | 754 |
![]() | 761 |
![]() | 768 |
![]() | 776 |
![]() | 784 |
![]() | 792 |
![]() | 800 |
![]() | 803 |
![]() | 815 |
![]() | 818 |
![]() | 826 |
![]() | 829 |
![]() | 835 |
![]() | 839 |
![]() | 845 |
![]() | 848 |
![]() | 854 |
![]() | 857 |
![]() | 863 |
![]() | 866 |
![]() | 875 |
![]() | 879 |
![]() | 891 |
![]() | 903 |
![]() | 909 |
![]() | 917 |
![]() | 926 |
![]() | 935 |
![]() | 938 |
![]() | 942 |
![]() | 946 |
![]() | 962 |
![]() | 966 |
![]() | 970 |
![]() | 985 |
![]() | 997 |
![]() | 1009 |
![]() | 975 |
![]() | 177 |
![]() | 182 |
![]() | 187 |
![]() | 192 |
![]() | 395 |
![]() | 436 |
![]() | 480 |
![]() | 537 |
![]() | 629 |
![]() | 684 |
![]() | 696 |
![]() | 216 |
![]() | 595 |
![]() | 607 |
![]() | 288 |
![]() | 678 |
![]() | 240 |
![]() | 518 |
![]() | 538 |
![]() | 552 |
![]() | 608 |
![]() | 697 |
![]() | 607 |
![]() | 239 |
![]() | 240 |
![]() | 240 |
![]() | 241 |
![]() | 242 |
![]() | 243 |
![]() | 244 |
![]() | 245 |
![]() | 246 |
![]() | 247 |
![]() | 248 |
![]() | 249 |
![]() | 250 |
![]() | 251 |
![]() | 252 |
![]() | 253 |
![]() | 253 |
![]() | 254 |
![]() | 518 |
![]() | 518 |
![]() | 519 |
![]() | 520 |
![]() | 521 |
![]() | 522 |
![]() | 523 |
![]() | 524 |
![]() | 525 |
![]() | 526 |
![]() | 527 |
![]() | 528 |
![]() | 528 |
![]() | 529 |
![]() | 537 |
![]() | 538 |
![]() | 538 |
![]() | 539 |
![]() | 540 |
![]() | 541 |
![]() | 542 |
![]() | 543 |
![]() | 544 |
![]() | 545 |
![]() | 546 |
![]() | 547 |
![]() | 548 |
![]() | 548 |
![]() | 549 |
![]() | 551 |
![]() | 552 |
![]() | 552 |
![]() | 553 |
![]() | 554 |
![]() | 555 |
![]() | 556 |
![]() | 557 |
![]() | 558 |
![]() | 559 |
![]() | 560 |
![]() | 561 |
![]() | 562 |
![]() | 562 |
![]() | 563 |
![]() | 564 |
![]() | 607 |
![]() | 607 |
![]() | 608 |
![]() | 608 |
![]() | 609 |
![]() | 610 |
![]() | 611 |
![]() | 612 |
![]() | 613 |
![]() | 614 |
![]() | 615 |
![]() | 616 |
![]() | 617 |
![]() | 618 |
![]() | 619 |
![]() | 619 |
![]() | 620 |
![]() | 696 |
![]() | 696 |
![]() | 696 |
![]() | 697 |
![]() | 697 |
![]() | 698 |
![]() | 699 |
![]() | 700 |
![]() | 701 |
![]() | 702 |
![]() | 703 |
![]() | 704 |
![]() | 705 |
![]() | 706 |
![]() | 707 |
![]() | 707 |
![]() | 708 |
![]() (error, dlevel, langid) Intersection: {error, langid} | 138 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object} | 233 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 253 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 528 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 548 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 562 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 619 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 707 |
![]() | 220 |
![]() | 223 |
![]() | 243 |
![]() | 246 |
![]() | 503 |
![]() | 564 |
![]() | 216 |
![]() | 103 |
![]() | 104 |
![]() | 282 |
![]() | 216 |
![]() | 595 |
![]() | 607 |
![]() | 137 |
![]() | 406 |
![]() | 413 |
![]() | 440 |
![]() | 67 |
![]() | 67 |
![]() | 67 |
![]() | 239 |
![]() | 517 |
![]() | 537 |
![]() | 551 |
![]() | 607 |
![]() | 696 |