1
2 /*
3 ** SP_DOWNGRADE
4 **
5 ** Description
6 ** This stored procedure must be executed to downgrade a server
7 ** from version to a previous one.
8 **
9 ** Parameters
10 ** @cmd - 'help', 'prepare' or 'downgrade'. If null, 'help'
11 ** is the default.
12 ** @toversion - Target version in char format
13 ** @verbose - 0 or 1, to control verbosity
14 ** @override - 0 or 1: if 1, skip databases that are not writable
15 ** @show_cmd - if 1 and verbose=1, print command text before executing it
16 ** Returns
17 ** 0 - success
18 ** 1 - error
19 */
20 create procedure sp_downgrade
21 @cmd varchar(10) = 'help',
22 @toversion varchar(10) = null,
23 @verbose int = 0,
24 @override int = 0,
25 @show_cmd int = 0
26 as
27 begin -- {
28 declare @dbid int,
29 @dbname sysname,
30 @devname sysname,
31 @cachename varchar(255),
32 @alldbs int,
33 @allbutmasterdb int,
34 @masterdb int,
35 @sybsecuritydb int,
36 @whatdbid int,
37 @fromversion varchar(10),
38 @fromversid int,
39 @toversid int,
40 @any15versid int,
41 @actionid int,
42 @baddbstat int,
43 @baddbstat2 int,
44 @baddbstat3 int,
45 @imdbstat int,
46 @cachedevstat int,
47 @imcachestat int,
48 @auinitstat int,
49 @dbstat4 int,
50 @retval int,
51 @prepare_error_count int,
52 @exec_error_count int,
53 @lob_comp_error int,
54 @usage varchar(128),
55 @sqlbuf varchar(1024),
56 @sqlcmd varchar(2048),
57 @sqlclause varchar(1024),
58 @dbnameoffset int,
59 @downgrade_sysconfigures_15015_cmd varchar(1024),
60 @mark_dbprocs_sql varchar(128),
61 @drop_1502_sysoptions_sql varchar(512),
62 @dbinfo_reset_asevers_sql varchar(100),
63 @dbinfo_turnon_downgradeneed_sql varchar(512),
64 @dbinfo_update_1550_master_sql varchar(128),
65 @dbinfo_update_1550_userdb_sql varchar(128),
66 @dbinfo_update_1502_master_sql varchar(128),
67 @dbinfo_update_1502_userdb_sql varchar(128),
68 @dbinfo_update_15015_alldbs_sql varchar(128),
69 @dbinfo_update_1500_master_sql varchar(128),
70 @dbinfo_update_1500_userdb_sql varchar(128),
71 @errorlog_msg varchar(128),
72 @downgrade_passwords_sql varchar(128),
73 @downgrade_sysauditoptions_sql varchar(256),
74 @downgrade_sysconfigures_15015_sql1 varchar(512),
75 @downgrade_sysconfigures_15015_sql2 varchar(512),
76 @downgrade_sysconfigures_15015_sql3 varchar(512),
77 @downgrade_15015_config varchar(30),
78 @reset_sysservers_srvnetname_len varchar(128),
79 @downgrade_15015_msg varchar(30),
80 @sysobjstat3_sql varchar(20),
81 @durability_sql varchar(256),
82 @soid int,
83 @soname varchar(30),
84 @objname sysname,
85 @objstat3 smallint,
86 @csid int,
87 @wide_rows int,
88 @page_comp int,
89 @row_comp int,
90 @v char(1),
91 @c int,
92 @cct int,
93 @lp_status int,
94 @exempt_lock int,
95 @lockrole int
96
97 set nocount on
98
99 select @usage = 'sp_downgrade @cmd = {''prepare'' | ''downgrade'' | ''help''}, @toversion = ''n'' [, @verbose = 0 | 1 ] [, @override = 0 | 1]'
100
101 select @cmd = lower(isnull(@cmd, 'downgrade'))
102
103 if ((@toversion is null) or (@cmd = 'help'))
104 begin
105 print @usage
106 return 0
107 end
108
109 if (@cmd not in ('prepare', 'downgrade'))
110 begin
111 print @usage
112 return 1
113 end
114
115 /*
116 ** Because this procedure could update security tables,
117 ** SSO role is required and because it is an administration
118 ** tool SA role is also required. The sybase_ts_role is
119 ** needed to execute the command dbcc markprocs.
120 */
121 if (proc_role('sa_role') = 0 or
122 proc_role('sso_role') = 0 or
123 proc_role('sybase_ts_role') = 0)
124 begin
125 return 1
126 end
127
128 if db_name() != "master"
129 begin
130 /* 17428, "You must be in the 'master' database in order to change database options." */
131 raiserror 17428
132 return 1
133 end
134
135 /* Initialize */
136 select @prepare_error_count = 0,
137 @any15versid = 0,
138 @lp_status = 512,
139 @exempt_lock = 8,
140 @lockrole = 2
141
142 /* Determine which version we are downgrading from */
143 select @fromversid = coalesce (nullif (convert(smallint,
144 dbinfo_get('master', 'ASEvers')), 0),
145 @@version_number)
146 select @fromversion = convert(varchar(2), @fromversid / 1000)
147 + '.' + convert(varchar(1), ((@fromversid / 100) % 10))
148 + '.' + convert(varchar(1), ((@fromversid / 10) % 10))
149 + '.' + convert(varchar(1), (@fromversid % 10))
150
151 /*
152 ** Valid 'to' versions: any version whose major number equals
153 ** the @fromversid major number, and is < @fromversid.
154 */
155 select @toversid = 0
156 select @cct = datalength(@toversion)
157 select @c = 1
158 while @c <= @cct
159 begin
160 select @v = substring(@toversion, @c, 1)
161 if (@v between '0' and '9')
162 begin
163 select @toversid = (@toversid * 10) + convert(int, @v)
164 end
165 else if (@v != '.')
166 begin
167 -- Not a valid version ... stop
168 print "'%1!' is not a valid downgrade version.", @toversion
169 return 1
170 end
171 select @c = @c + 1
172 end
173 if (@toversid = 0)
174 begin
175 print "'%1!' is not a valid downgrade version.", @toversion
176 return 1
177 end
178 while (@toversid < 10000)
179 select @toversid = @toversid * 10
180
181 select @toversion = convert(varchar(2), @toversid / 1000)
182 + '.' + convert(varchar(1), ((@toversid / 100) % 10))
183 + '.' + convert(varchar(1), ((@toversid / 10) % 10))
184 + '.' + convert(varchar(1), (@toversid % 10))
185
186 /*
187 ** Major-version downgrade is not allowed.
188 ** Cannot "downgrade" to a higher release than current.
189 ** Downgrade between SDC and SMP is disallowed; these are
190 ** denoted by SDC=5 in the subminor number, SMP=0.
191 ** 15035 server must only be downgraded to 15015, but
192 ** not existing 15005 nor 15025, there is no need
193 ** for an extra check.
194 */
195 if (((@toversid / 1000) != (@fromversid / 1000))
196 or (@fromversid < @toversid)
197 or ((@fromversid % 10) != (@toversid % 10)))
198 begin
199 print "'%1!' is not a valid downgrade version from %2!.",
200 @toversion, @fromversion
201 return 1
202 end
203
204 print "Downgrade from %1! to %2! (command: '%3!')", @fromversion,
205 @toversion, @cmd
206
207 /*
208 ** Downgrade can only be performed when server is under single
209 ** user mode.
210 */
211 if (is_singleusermode() = 0)
212 begin
213 print 'You cannot run sp_downgrade; server must be in single-user mode.'
214 print 'Try restarting the server using the -m option.'
215 return 1
216 end
217
218 /*
219 ** Downgrade only those databases that are writable
220 **
221 ** sysdatabases.status not in 0x20 (in load), 0x40 (not rec),
222 ** 0x80 (bypass), 0x100 (suspect), 0x400 (rdonly), 0x8000 (emergency)
223 **
224 ** sysdatabases.status2 not in 0x10 (offline), 0x400 (online for
225 ** standby access)
226 **
227 ** sysdatabases.status3 not in 0x1 (user proxy), 0x2 (ha proxy),
228 ** 0x8 (shutdown), 0x10 (failedover), 0x80 (quiesced), 0x400000
229 ** (archive)
230 */
231 select @baddbstat = 34272, @baddbstat2 = 1040,
232 @baddbstat3 = 4194459
233
234 select @imdbstat = number from master..spt_values
235 where name = "in-memory database"
236
237 select dbid
238 into #sysdatabases
239 from master.dbo.sysdatabases
240 where status & @baddbstat = 0
241 and status2 & @baddbstat2 = 0
242 and status3 & @baddbstat3 = 0
243 and status4 & @imdbstat = 0
244 order by dbid
245
246 select a.dbid, a.name
247 into #baddb
248 from master.dbo.sysdatabases a
249 where not exists (select 1 from #sysdatabases b
250 where a.dbid = b.dbid)
251
252 if (@@rowcount > 0)
253 begin
254 /*
255 ** If there is any database that we know in advance that cannot
256 ** be downgraded, skip it only if WITH OVERRIDE was specified.
257 */
258 if (@override = 0)
259 begin
260 print 'sp_downgrade cannot be run because the following databases are not writable or they cannot be downgraded.'
261 print 'Use sp_downgrade ... @override = 1 to skip these databases.'
262 end
263 else
264 begin
265 print 'sp_downgrade will skip the following databases because they are not writable or cannot be downgraded.'
266 end
267
268 select dbid, convert(char(30), name) as name
269 from #baddb
270
271 if (@override = 0)
272 begin
273 return 1
274 end
275 end
276
277 -- If we are at the requested downgrade level, check whether all
278 -- databases have been downgraded. If so, stop with success;
279 -- else declare an error.
280 if (@fromversid = @toversid)
281 begin
282 print "This installation is already at version %1!", @toversion
283 select @dbid = 2
284 while @dbid is not null
285 begin
286 if (not exists (select dbid from #baddb where dbid = @dbid))
287 and (@toversid != (select convert(smallint,
288 dbinfo_get(@dbid, 'asevers'))))
289 begin
290 print "Database ID %1! is not at the correct version.",
291 @dbid
292 select @prepare_error_count = @prepare_error_count + 1
293 end
294 select @dbid = min(dbid)
295 from master.dbo.sysdatabases
296 where dbid > @dbid
297 end
298 if @prepare_error_count != 0
299 begin
300 print "sp_downgrade cannot continue. Run 'online database' for all databases to bring them back up to ASE's current release level, then retry sp_downgrade."
301 return 1
302 end
303 return 0
304 end
305
306 select @errorlog_msg = 'Preparing ASE downgrade from ' + @fromversion
307 + ' to ' + @toversion + '.'
308 dbcc printolog(@errorlog_msg)
309
310 /*
311 ** We will verify encryption downgrade and prepare for
312 ** password downgrade for both prepare mode and downgrade mode.
313 ** If either fail, we return fail.
314 ** Initally set retval to 0. It will be set by sp_encryption if
315 ** sp_encryption fails or by sp_passwordpolicy if sp_passwordpolicy
316 ** fails.
317 */
318 select @retval = 0
319
320 /*
321 ** verify_downgrade, 15.5, 15.5SDC, 15.0.3, 15.0.2 are at the
322 ** same level of the encrypted column feature, 15.0.1 and 15.0.1SDC
323 ** are at the same level of the encrypted column feature
324 */
325 if (not exists (select name from syscolumns
326 where id = object_id('syscomments') and name = 'encrkeyid'))
327 begin
328 -- This is a re-run of sp_downgrade; skip sp_encryption
329 print "This is a re-run of sp_downgrade; skip sp_encryption"
330 select @retval = 0
331 end
332 else if (@toversid >= 15020)
333 begin
334 exec @retval = sp_encryption 'verify_downgrade', '15.5'
335 end
336 else if (@toversid = 15010 or @toversid = 15015)
337 begin
338 exec @retval = sp_encryption 'verify_downgrade', '15.0.1'
339 end
340 else if (@toversid = 15000)
341 begin
342 exec @retval = sp_encryption 'verify_downgrade', '15.0'
343 end
344
345 if @retval != 0
346 begin
347 select @prepare_error_count = @prepare_error_count + 1
348 end
349
350 /*
351 ** Starting with 15.0.2, new password encryption algorithm is used to
352 ** encrypt login passwords.
353 ** On downgrade to SMP 15.0.1 or prior and SDC version 15.0.1.5,
354 ** the login password is reset for logins with password encrypted
355 ** only using the new algorithm.
356 */
357 if (@toversid <= 15015)
358 begin
359 exec @retval = sp_passwordpolicy 'prepare_downgrade'
360
361 if @retval != 0
362 begin
363 select @prepare_error_count = @prepare_error_count + 1
364 end
365 end
366
367 if (@toversid < 15500)
368 begin
369 -- Some datatypes don't exist before 15.5. Assure they are unused.
370 print "Checking datatypes in preparation for downgrade"
371
372 select @retval = 0
373
374 select @sqlbuf = 'select @c = count(1) from :DBNAME:..syscolumns ' +
375 'where type in (select type from :DBNAME:..systypes ' +
376 'where name in (''bigdatetime'', ''bigtime'', ' +
377 '''bigdatetimen'', ''bigtimen''))'
378
379 select @dbid = db_id('master')
380 while (@dbid is not null)
381 begin
382 select @dbname = name
383 from master..sysdatabases
384 where dbid = @dbid
385
386 select @sqlcmd = @sqlbuf
387 select @dbnameoffset = patindex('%:DBNAME:%', @sqlcmd)
388 while (@dbnameoffset > 0)
389 begin
390 select @sqlcmd = stuff(@sqlcmd,
391 @dbnameoffset, 8,
392 @dbname)
393 select @dbnameoffset = patindex('%:DBNAME:%',
394 @sqlcmd)
395 end
396
397 exec (@sqlcmd)
398 if (@c > 0)
399 begin
400 raiserror 19985, @toversion
401 select @retval = 1
402 end
403 else
404 if @verbose = 1
405 begin
406 print 'Database %1! does not use new datatypes',
407 @dbname
408 end
409
410 select @dbid = min(dbid)
411 from master..sysdatabases
412 where dbid > @dbid
413 end
414
415 /* Block downgrade unless users remove usages of new datatypes */
416 if @retval != 0
417 begin
418 select @prepare_error_count = @prepare_error_count + 1
419 end
420
421 /*
422 ** Before 15.5 stored procedures cannot be created with
423 ** deferred name resolution. If we have procedures created
424 ** with deferred name resolution the downgrade machinery
425 ** will mark them to be recreated from text so they can
426 ** be executed in the downgraded server. But the text in
427 ** syscomments will have 'select *' not expanded, for
428 ** getting the 'select *' correctly expanded the procedures
429 ** would have to be recreated from SQL script.
430 **
431 ** So during 'prepare' we warn if there are databases having
432 ** stored procedures created with deferred name resolution
433 ** and in 'verbose' mode we print out the names for the
434 ** procedures in each database.
435 */
436 print "Checking procedures in preparation for downgrade"
437
438 /* Cursor for traversing databases */
439 declare sysdbc2 cursor for
440 select dbid from #sysdatabases
441
442 /* Initializations */
443 set @retval = 0
444 set @c = 0
445
446 select Name = name, Owner = user_name(uid)
447 into #sprocDNR
448 from sysobjects
449 where 1 = 2
450
451 select @sqlbuf = " truncate table #sprocDNR
452 insert #sprocDNR
453 select Name=o.name,
454 Owner=user_name(o.uid)
455 from :DBNAME:..sysobjects o
456 where exists (select 1 from
457 :DBNAME:..sysprocedures p
458 where p.id = o.id and
459 p.type = 2048)
460 order by o.name"
461
462 /* Scan sysprocedures in each database */
463 open sysdbc2
464 fetch sysdbc2 into @dbid
465
466 while (@@sqlstatus = 0)
467 begin -- {
468
469 select @dbname = db_name(@dbid)
470 select @sqlcmd = "select @c = count(1) from "
471 + @dbname + "..sysprocedures " +
472 "where type = 2048"
473
474 exec (@sqlcmd)
475
476 if (@c != 0)
477 begin
478 set @retval = 1
479 print "Warning: There are procedures in database: %1! (dbid: %2!) created with deferred name resolution", @dbname, @dbid
480
481 if (@verbose = 1)
482 begin
483 print "List of procedures created with deferred name resolution in database: %1! (dbid: %2!)", @dbname, @dbid
484
485 select @sqlcmd = @sqlbuf
486 select @dbnameoffset =
487 patindex('%:DBNAME:%', @sqlcmd)
488
489 while (@dbnameoffset > 0)
490 begin
491 select @sqlcmd =
492 stuff(@sqlcmd,
493 @dbnameoffset,
494 8, @dbname)
495
496 select @dbnameoffset =
497 patindex('%:DBNAME:%',
498 @sqlcmd)
499 end
500
501 exec (@sqlcmd)
502
503 exec sp_autoformat #sprocDNR
504
505 print ""
506 end
507 end
508
509 fetch sysdbc2 into @dbid
510
511 end -- }
512
513 if (@retval = 1)
514 begin
515 /*
516 ** At least one database has procedures created with
517 ** deferred name resolution. Print a warning with
518 ** the advice for recreating manually those procedures
519 ** for getting the right "select *" expanded text in
520 ** syscomments.
521 */
522 print ""
523 print "Warning: Procedures created with deferred name resolution that contain statements with 'select *' syntax would need to be recreated manually from text. If they are not recreated they will still execute but the text in syscomments will remain with 'select *' unexpanded."
524 print ""
525 end
526 else if (@verbose = 1)
527 begin
528 /*
529 ** We didn't find any procedure created with deferred
530 ** name resolution.
531 */
532 print "There are no procedures created with deferred name resolution in any database"
533 end
534
535 close sysdbc2
536 deallocate cursor sysdbc2
537 end
538
539 if (@toversid < 15030 and @toversid != 15015)
540 begin -- {
541 /*
542 ** Starting at 15.0.3, the size of sysservers.srvnetname
543 ** changed from 32 bytes to 255 bytes. If any srvnetname
544 ** is greater than 32 bytes do not let downgrade happen.
545 ** (exception: 15015 was original version for ssl=CN changes
546 ** and increase of srvnetname column.)
547 */
548 print "Checking sysservers in preparation for downgrade"
549 if exists (select 1 from master.dbo.sysservers
550 where
551 lower(srvnetname) like "%ssl=%"
552 or datalength(srvnetname) > 32)
553 begin
554 raiserror 19891, @fromversion, @toversion
555 select srvname, srvnetname into #newformat
556 from master.dbo.sysservers
557 where
558 lower(srvnetname) like "%ssl=%"
559 or datalength(srvnetname) > 32
560
561 exec sp_autoformat
562 @fulltabname = "#newformat",
563 @selectlist = "'Server Name' = srvname,
564 'Server Net Name' = srvnetname"
565
566 select @prepare_error_count = @prepare_error_count + 1
567 end
568 else
569 begin
570 print "There were no errors which involve the sysservers table."
571 end
572
573 /*
574 ** Starting at 15.0.3, the directory service entry may contain
575 ** CN=. If any row in syslisteners has such a
576 ** string, ssl was configured for this listener. State that
577 ** ssl needs to be disabled and list the listeners.
578 */
579 print "Checking syslisteners in preparation for downgrade"
580 if exists (select 1 from master.dbo.syslisteners
581 where
582 lower(net_type) like "%ssl%"
583 or lower(address_info) like "%cn=%")
584 begin
585 raiserror 19943, @fromversion, @toversion
586 select net_type, address_info into #newformat1
587 from master.dbo.syslisteners
588 where
589 lower(net_type) like "%ssl%"
590 or lower(address_info) like "%cn=%"
591
592 exec sp_autoformat
593 @fulltabname = "#newformat1",
594 @selectlist = "'Net Type' = net_type,
595 'Address Info' = address_info"
596
597 select @prepare_error_count = @prepare_error_count + 1
598 end
599 else
600 begin
601 print "There were no errors which involve the syslisteners table."
602 end
603
604 /*
605 ** Starting at 15.0.3, sysprocedures has additional
606 ** qp_setting column that is used to associate optimization
607 ** goal and criteria with stored procedures. If the
608 ** qp_setting column is not null, print an warning to user.
609 */
610 print "Checking sysprocedures in preparation for downgrade"
611
612 /* A cursor to traverse sysdatabases */
613 declare sysdbc1 cursor for
614 select dbid from #sysdatabases
615
616 set @retval = 0
617 open sysdbc1
618 fetch sysdbc1 into @dbid
619 while (@@sqlstatus = 0)
620 begin -- {
621 select @dbname = db_name(@dbid)
622 select @sqlcmd = 'select @retval = count(1) from '
623 + @dbname + '..sysprocedures ' +
624 'where qp_setting is not null'
625 exec (@sqlcmd)
626 if @retval != 0
627 begin
628 print "Warning: There are non-NULL qp_setting rows in sysprocedures of database: %1! (dbid: %2!).", @dbname, @dbid
629 print "The qp_setting column in sysprocedures will be set to NULL and dropped during downgrade."
630 end
631 fetch sysdbc1 into @dbid
632 end --}
633 close sysdbc1
634 deallocate cursor sysdbc1
635
636
637 /*
638 ** Starting at 15.0.3, sysqueryplans has additional columns:
639 ** dbid, qpdate, sprocid, hashkey2, key1, key2, key3, key4.
640 ** If anyone of them is not null, print a warning message.
641 */
642 print "Preparing sysqueryplans for downgrade"
643
644 /* A cursor to traverse sysdatabases */
645 declare sysdbc1 cursor for
646 select dbid from #sysdatabases
647
648 set @retval = 0
649 open sysdbc1
650 fetch sysdbc1 into @dbid
651 while (@@sqlstatus = 0)
652 begin -- {
653 select @dbname = db_name(@dbid)
654 select @sqlcmd = 'select @retval = count(1) from '
655 + @dbname + '..sysqueryplans ' +
656 'where dbid is not null ' +
657 'or qpdate is not null ' +
658 'or sprocid is not null ' +
659 'or hashkey2 is not null ' +
660 'or key1 is not null ' +
661 'or key2 is not null ' +
662 'or key3 is not null ' +
663 'or key4 is not null'
664 exec (@sqlcmd)
665 if @retval != 0
666 begin
667 print "Warning: There are non-NULL rows for dbid or qpdate or sprocid or hashkey2 or key1 or key2 or key3 or key4 in sysqueryplans of database: %1! (dbid: %2!).", @dbname, @dbid
668 print "The respective column in sysqueryplans will be set to NULL and dropped during downgrade."
669 end
670 fetch sysdbc1 into @dbid
671 end --}
672 close sysdbc1
673 deallocate cursor sysdbc1
674
675 /*
676 ** In 15.0.3 ESD#1, a new charset KZ1048 is added. Do not allow
677 ** downgrade to happen if server is using this charset.
678 */
679 select @csid = value
680 from master.dbo.syscurconfigs
681 where config = 131
682 if (@csid = 87)
683 begin
684 print ""
685 raiserror 19954, @toversion, 'kz1048'
686 select @prepare_error_count = @prepare_error_count + 1
687 end
688
689 end --}
690
691 /*
692 ** In 15.0.2, new nocase sortorders (id 52) are added for
693 ** Chinese and Japanese character sets: eucgb, gb18030, cp936,
694 ** eucjis, sjis and deckanji. If server is using these sortorder
695 ** as default, do not allow downgrade to happen.
696 */
697 if (@toversid < 15020)
698 begin -- {
699 select @soid = value
700 from master.dbo.syscurconfigs
701 where config = 123
702
703 select @csid = value
704 from master.dbo.syscurconfigs
705 where config = 131
706
707 select @soname = name
708 from master.dbo.syscharsets
709 where id = @soid and csid = @csid and type between 2000 and 2999
710
711 if (@soid = 52) and (@csid in (170, 171, 173, 140, 141, 142))
712 begin
713 print ""
714 raiserror 19779, @toversion, @soname
715 select @prepare_error_count = @prepare_error_count + 1
716 end
717 end -- }
718
719 /*
720 ** In 15.5, status4 column of sysdatabases has new values indicating
721 ** MINIMAL logging mode and in-memory database. Downgrade will report
722 ** those databases which are using these values.
723 */
724 if (@toversid < 15500)
725 begin
726 print "Checking sysdatabases in preparation for downgrade"
727
728 set @retval = 0
729
730 select @imdbstat = 4096
731
732 select @retval = count(1)
733 from master..sysdatabases
734 where (status4 & (@imdbstat | 256)) != 0
735
736 if @retval != 0
737 begin
738 declare sysdbc0 cursor for
739 select dbid, status4 from master..sysdatabases
740 where (status4 & (@imdbstat | 256)) != 0
741
742 open sysdbc0
743 fetch sysdbc0 into @dbid, @dbstat4
744
745 while (@@sqlstatus = 0)
746 begin
747 select @dbname = db_name(@dbid)
748 if (@dbstat4 & 256 = 256)
749 begin
750 print "Error: Database '%1!' with MINIMAL logging mode should be altered back to FULL logging mode before downgrade.", @dbname
751 end
752 if (@dbstat4 & @imdbstat = @imdbstat)
753 begin
754 print "Error: In-memory database '%1!' should be dropped before downgrade.", @dbname
755 end
756 fetch sysdbc0 into @dbid, @dbstat4
757 end
758
759 close sysdbc0
760 deallocate cursor sysdbc0
761
762 select @prepare_error_count = @prepare_error_count + 1
763 end
764
765 /*
766 ** In 15.5, a new kind of device "cache device" is introduced.
767 ** Downgrade will advise users to drop these cache devices.
768 */
769 print "Checking sysdevices in preparation for downgrade"
770
771 set @retval = 0
772
773 select @cachedevstat = 8
774
775 select @retval = count(1)
776 from master..sysdevices
777 where status2 & @cachedevstat = @cachedevstat
778
779 if @retval != 0
780 begin
781 declare sysdevc cursor for
782 select name from master..sysdevices
783 where status2 & @cachedevstat = @cachedevstat
784
785 open sysdevc
786 fetch sysdevc into @devname
787
788 while (@@sqlstatus = 0)
789 begin
790 print "Error: Device '%1!' is a cache device, which should be dropped via sp_dropdevice before downgrade.", @devname
791 fetch sysdevc into @devname
792 end
793
794 close sysdevc
795 deallocate cursor sysdevc
796
797 select @prepare_error_count = @prepare_error_count + 1
798 end
799
800 /*
801 ** In 15.5, a new cache type "inmemory_storage" is introduced.
802 ** Downgrade will advise users to drop the user-defined caches
803 ** whose type is "inmemory_storage".
804 */
805 print "Checking user-defined caches in preparation for downgrade"
806
807 set @retval = 0
808
809 select @imcachestat = 65536
810
811 select @retval = count(1)
812 from master..sysconfigures
813 where (parent = 19) and (status & @imcachestat = @imcachestat)
814
815 if @retval != 0
816 begin
817 declare usercachec cursor for
818 select name from master..sysconfigures
819 where (parent = 19) and (status & @imcachestat = @imcachestat)
820
821 open usercachec
822 fetch usercachec into @cachename
823
824 while (@@sqlstatus = 0)
825 begin
826 print "Error: Cache '%1!' is a inmemory_storage cache, which should be dropped via sp_cacheconfig before downgrade.", @cachename
827 fetch usercachec into @cachename
828 end
829
830 close usercachec
831 deallocate cursor usercachec
832
833 select @prepare_error_count = @prepare_error_count + 1
834 end
835
836 /*
837 ** In 15.5, sysstat3 column of sysobjects is introduced.
838 ** Downgrade will clear the values of the column, and remove
839 ** it from syscolumns.
840 */
841 set @retval = 0
842 exec @retval = sp_downgrade_sysobjects "display"
843 if @retval != 0
844 begin
845 select @prepare_error_count = @prepare_error_count + 1
846 end
847 end
848
849 -- Look for databases having "allow wide dol rows" enabled
850 if (@toversid < 15700 and @@maxpagesize > 8192)
851 begin
852 select @wide_rows = number
853 from master.dbo.spt_values
854 where type = "D4" and name = "allow wide dol rows"
855 if (@wide_rows is null)
856 begin
857 print 'Information is missing from spt_values.'
858 print 'Please re-run installmaster before continuing.'
859 select @prepare_error_count = @prepare_error_count + 1
860 end
861 else
862 begin
863 -- Turn this off arbitrarily in temp databases
864 update master.dbo.sysdatabases
865 set status4 = status4 & ~ @wide_rows
866 where name = 'tempdb'
867 or status3 & 256 = 256
868
869 -- Now look for other databases having it on
870 if exists (select name
871 from master.dbo.sysdatabases
872 where status4 & @wide_rows = @wide_rows)
873 begin
874 print "One or more databases still permit wide DOL rows."
875 select name as 'Investigate these databases:'
876 from master.dbo.sysdatabases
877 where status4 & @wide_rows = @wide_rows
878 select @prepare_error_count = @prepare_error_count + 1
879 end
880 else if (@verbose = 1)
881 begin
882 print "No databases allow wide DOL rows."
883 end
884 end
885 end
886
887 -- Look for databases having compression enabled
888 -- Look for databases having LOB compression enabled
889 -- Look for databases using in-row as default storage for LOBs
890 if (@toversid < 15700)
891 begin
892 select @page_comp = number
893 from master.dbo.spt_values
894 where type = 'D4' and name = 'page compressed'
895 select @row_comp = number
896 from master.dbo.spt_values
897 where type = 'D4' and name = 'row compressed'
898 if (@page_comp is null or @row_comp is null)
899 begin
900 print 'Information is missing from spt_values.'
901 print 'Please re-run installmaster before continuing.'
902 select @prepare_error_count = @prepare_error_count + 1
903 end
904 else
905 begin
906 -- look for databases configured for compression
907 if exists (select name
908 from master.dbo.sysdatabases
909 where status4 & (@page_comp | @row_comp) != 0)
910 begin
911 print "One or more databases is configured for compression."
912 select name as 'Investigate these databases:'
913 from master.dbo.sysdatabases
914 where status4 & (@page_comp | @row_comp) != 0
915 select @prepare_error_count = @prepare_error_count + 1
916 end
917 else if (@verbose = 1)
918 begin
919 print "No databases are configured for compression."
920 end
921 end
922
923 if exists (select 1
924 from master.dbo.sysdatabases
925 where (lobcomp_lvl is not null)
926 and (lobcomp_lvl > 0))
927 begin
928 print '*** Some databases are configured for LOB compression.'
929 select distinct name as 'check these databases'
930 from master.dbo.sysdatabases where lobcomp_lvl > 0
931 select @prepare_error_count = @prepare_error_count + 1
932 end
933 else if (@verbose = 1)
934 begin
935 print 'No databases are configured for LOB compression.'
936 end
937
938 if exists (select 1
939 from master.dbo.sysdatabases
940 where (inrowlen is not null)
941 and (inrowlen > 0))
942 begin
943 print '*** Some databases are configured to use in-row LOBs.'
944 select distinct name as 'check these databases'
945 from master.dbo.sysdatabases where inrowlen > 0
946 select @prepare_error_count = @prepare_error_count + 1
947 end
948 else if (@verbose = 1)
949 begin
950 print 'No databases are configured to use in-row LOBs.'
951 end
952 end
953
954 /*
955 ** If downgrading to a version prior to 15.7, check:
956 ** - There are no log records of the type PAGEIMAGE or PAGE_COMPRESS
957 ** - There are no databases that are being initialized by the
958 ** Allocation Unit Initializer.
959 ** - There are no tables having compression enabled
960 ** - There are no tables having in-row LOBs
961 */
962 if (@toversid < 15700)
963 begin --{
964 select @retval = 0
965
966 select @auinitstat = 67108864, @lob_comp_error = 0
967
968 declare sysdbc0 cursor for
969 select dbid, name, status4 from master..sysdatabases
970
971 open sysdbc0
972 fetch sysdbc0 into @dbid, @dbname, @dbstat4
973
974 while (@@sqlstatus = 0)
975 begin --{
976 if (@verbose = 1)
977 begin
978 print 'Checking database ''%1!'' for downgrade readiness.', @dbname
979 end
980 if (@dbstat4 & @auinitstat != 0)
981 begin --{
982 print "Error: Database '%1!' is not fully initialized. You must wait for the Allocation Unit Initializer to complete.", @dbname
983 select @prepare_error_count = @prepare_error_count + 1
984 end --}
985
986 select @sqlcmd = 'select @retval=count(*) from '
987 + @dbname + '..syslogs '
988 + 'where op > 84'
989 exec (@sqlcmd)
990 if (@retval > 0)
991 begin --{
992 print "Error: Database '%1!' contains log records that will not be understood by the target server. Please, truncate the log and retry.", @dbname
993 select @prepare_error_count = @prepare_error_count + 1
994 end --}
995
996 select @sqlcmd = 'select @retval=sum(size) from '
997 + 'master..sysusages where dbid=@dbid '
998 + 'and vdevno < 0'
999 exec (@sqlcmd)
1000 if (@retval > 0)
1001 begin --{
1002 print "Error: Database '%1!' contains %2! hidden pages that have to be filled. Please, use ALTER DATABASE LOG ON command to extend the log %3! pages.", @dbname, @retval, @retval
1003 select @prepare_error_count = @prepare_error_count + 1
1004 end --}
1005
1006 select @sqlcmd = 'select @retval = count(1) from ' + @dbname
1007 + '..syscolumns where id = object_id(''sysobjects'') and name = ''lobcomp_lvl'''
1008 exec (@sqlcmd)
1009 if (@retval > 0)
1010 begin --{
1011 /*
1012 ** This block checks for LOB compressed columns.
1013 ** syscolumns.status2 value 131072 is 0x20000,
1014 ** "column should not be compressed".
1015 ** syscolumns.sysstat3 value 2048 is 0x800,
1016 ** "table contains compressed LOB data".
1017 */
1018 select @sqlclause = ' from '
1019 + @dbname + '..syscolumns c,'
1020 + @dbname + '..sysobjects o,'
1021 + @dbname + '..syspartitions p'
1022 + ' where o.id = c.id and o.id = p.id'
1023 + ' and ((c.lobcomp_lvl is not null and c.status2 & 131072 = 0)'
1024 + ' or ((o.lobcomp_lvl is not null and o.lobcomp_lvl > 0) or (sysstat3 & 2048 != 0))'
1025 + ' or (p.lobcomp_lvl is not null and p.lobcomp_lvl > 0))'
1026 select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
1027 exec (@sqlcmd)
1028 if (@retval > 0)
1029 begin --{
1030 print "Error: Database '%1!' has tables configured for LOB compression.", @dbname
1031 -- Determine how wide the table name display must be.
1032 select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1033 exec (@sqlcmd)
1034 -- Display the affected table names.
1035 select @sqlcmd = 'select distinct convert(varchar('
1036 + convert(varchar(10), @retval)
1037 + '),o.name) as ''Check these tables:'''
1038 + @sqlclause
1039 exec (@sqlcmd)
1040 select @prepare_error_count = @prepare_error_count + 1,
1041 @lob_comp_error = 1
1042 end --}
1043 /*
1044 ** This block checks for compressed tables.
1045 ** sysstat3 value 28672 is 0x7000, the three compression
1046 ** status bits:
1047 ** - 0x1000 = Table contains compressed data
1048 ** - 0x2000 = Table is page level compressed
1049 ** - 0x4000 = Table is row level compressed
1050 */
1051 select @sqlclause = ' from '
1052 + @dbname + '..sysobjects o,'
1053 + @dbname + '..syspartitions p'
1054 + ' where o.id = p.id'
1055 + ' and (o.sysstat3 & 28672 != 0'
1056 + ' or (p.ptndcompver is not null and p.ptndcompver > 0))'
1057 select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
1058 exec (@sqlcmd)
1059 if (@retval > 0)
1060 begin --{
1061 print "Error: Database '%1!' has tables configured for compression.", @dbname
1062 -- Determine how wide the table name display must be.
1063 select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1064 exec (@sqlcmd)
1065 -- Display the affected table names.
1066 select @sqlcmd = 'select distinct convert(varchar('
1067 + convert(varchar(10), @retval)
1068 + '),o.name) as ''Check these tables:'''
1069 + @sqlclause
1070 exec (@sqlcmd)
1071 print "For each table, set compression = none, then use 'reorg rebuild' on that table."
1072 select @prepare_error_count = @prepare_error_count + 1
1073 end --}
1074 end --}
1075
1076 -- Check for 15.7 tables containing in-row LOBs
1077 select @sqlclause = ' from '
1078 + @dbname + '..sysobjects o,'
1079 + @dbname + '..syscolumns c '
1080 + 'where o.id = c.id and (inrowlen is not null) and (inrowlen > 0)'
1081 select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
1082 exec (@sqlcmd)
1083 if (@retval > 0)
1084 begin --{
1085 print "Error: Database '%1!' has tables containing in-row LOBs.", @dbname
1086 select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1087 exec (@sqlcmd)
1088 select @sqlcmd = 'select distinct convert(varchar('
1089 + convert(varchar(10), @retval)
1090 + '), o.name) as ''check these tables'''
1091 + @sqlclause
1092 exec (@sqlcmd)
1093 select @prepare_error_count = @prepare_error_count + 1
1094 end --}
1095
1096 fetch sysdbc0 into @dbid, @dbname, @dbstat4
1097 end --}
1098 close sysdbc0
1099 deallocate cursor sysdbc0
1100
1101 if (@lob_comp_error != 0)
1102 begin --{
1103 print "*** NOTE about tables using LOB compression:"
1104 print "To reset table LOB compression status indicators, copy data from the indicated tables into new tables, then drop the old tables."
1105 print "***"
1106 end --}
1107 end --}
1108
1109 if @prepare_error_count != 0
1110 begin
1111 print " "
1112 print "sp_downgrade '%1!' failed.", @cmd
1113 print "Re-run sp_downgrade after addressing the items listed above."
1114 return @prepare_error_count
1115 end
1116
1117 if (@cmd = 'prepare')
1118 begin
1119 /*
1120 ** In prepare mode the rest of non password/encryption
1121 ** processing is done, as there is nothing else to verify.
1122 */
1123 print 'sp_downgrade ''prepare'' completed.'
1124 return 0
1125 end
1126
1127 select @alldbs = 0,
1128 @allbutmasterdb = - 1,
1129 @masterdb = db_id('master'),
1130 @sybsecuritydb = db_id('sybsecurity'),
1131 @downgrade_passwords_sql = 'sp_passwordpolicy ''downgrade''',
1132 @mark_dbprocs_sql =
1133 'dbcc markprocs(@dbid)',
1134 @drop_1502_sysoptions_sql =
1135 'declare @d int ' +
1136 'delete syscolumns where id = 57 ' +
1137 'delete sysindexes where id = 57 ' +
1138 'delete sysobjects where id = 57 ' +
1139 'delete syspartitions where id = 57 ' +
1140 'delete systabstats where id = 57 ' +
1141 'select @d=rm_anchor(''master'',''table'', 57)',
1142 @dbinfo_reset_asevers_sql =
1143 'declare @d int ' +
1144 'select @d=dbinfo_update(@dbid, ''ASEvers'', ' +
1145 convert(varchar(10), @toversid) + ')',
1146 /* 32 as 0x00000002 indicates downgrade is needed */
1147 @dbinfo_turnon_downgradeneed_sql =
1148 'declare @d int, ' +
1149 '@stat4 int ' +
1150 'select @stat4=convert(int, dbinfo_get(''master'',''status4'')) ' +
1151 'select @d=dbinfo_update(1, ''status4'', 32 | @stat4)',
1152 @dbinfo_update_1550_master_sql =
1153 'declare @d int ' +
1154 'select @d=dbinfo_update(1, ''upgd_minor'', 1620)',
1155 @dbinfo_update_1502_master_sql =
1156 'declare @d int ' +
1157 'select @d=dbinfo_update(1, ''upgd_minor'', 1560)',
1158 @dbinfo_update_15015_alldbs_sql =
1159 'declare @d int ' +
1160 'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1700)',
1161 @downgrade_sysconfigures_15015_sql1 =
1162 'delete sysconfigures where name in (' +
1163 '''config file version'',''max query parallel degree'',' +
1164 '''cost of a logical io'',''cost of a physical io'',' +
1165 '''cost of a cpu unit'',''auto query tuning'',' +
1166 '''enable query tuning mem limit'',' +
1167 '''query tuning plan executions'',' +
1168 '''enable query tuning time limit'',' +
1169 '''max buffers per lava operator'',' +
1170 '''enable merge join'',''mnc_full_index_filter'')',
1171 @downgrade_sysconfigures_15015_sql2 =
1172 'delete sysconfigures where name in (' +
1173 '''engine memory log size'',''compression memory size'',' +
1174 '''enable sql debugger'',''identity reservation size'',' +
1175 '''session tempdb log cache size'',''max nesting level'',' +
1176 '''enable pci'',''max pci slots'',' +
1177 '''pci memory size'',' +
1178 '''restricted decrypt permission'',''net password encryption reqd'')',
1179 @downgrade_sysconfigures_15015_sql3 =
1180 'delete sysconfigures where name in (' +
1181 '''start xp server during boot'',''enable stmt cache monitoring'')',
1182 @downgrade_sysconfigures_15015_cmd =
1183 'if not exists (select 1 from sysconfigures where config = 392) ' +
1184 'begin ' +
1185 'insert sysconfigures(config,value,comment,status,name,parent) values(' +
1186 '392,0,''enable enterprise java beans'',262153,''enable enterprise java beans'',39) ' +
1187 'delete master..sysattributes where class = 1 and attribute = 1560 ' +
1188 'end',
1189 /* 1502 upgd_minor for user tables is still 1554 */
1190 @dbinfo_update_1550_userdb_sql =
1191 'declare @d int ' +
1192 'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1554)',
1193 @dbinfo_update_1502_userdb_sql =
1194 'declare @d int ' +
1195 'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1554)',
1196 @dbinfo_update_1500_master_sql =
1197 'declare @d int ' +
1198 'select @d=dbinfo_update(1, ''upgd_minor'', 1550)',
1199 @dbinfo_update_1500_userdb_sql =
1200 'declare @d int ' +
1201 'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1554)',
1202 @reset_sysservers_srvnetname_len =
1203 'update syscolumns ' +
1204 'set length=32 ' +
1205 'where id=40 and colid=4 and length>32',
1206 @downgrade_15015_config = "downgrade_15015_config",
1207 @downgrade_15015_msg = "@downgrade_15015_msg",
1208 @sysobjstat3_sql = "sysobjstat3_sql",
1209 @durability_sql = "UNUSED",
1210 @downgrade_sysauditoptions_sql =
1211 'delete from sybsecurity..sysauditoptions where ' +
1212 'name in (''login_locked'', ''password'', ' +
1213 '''keycustodian_role'', ''errorlog'')'
1214
1215
1216 /*
1217 ** The following is a table which will contain various actions
1218 ** to perform on downgrade. The 'toversion' is either "any
1219 ** version" or the highest ASE version at which the given action
1220 ** is needed during downgrade.
1221 */
1222 create table #actions
1223 (
1224 actionid int,
1225 dbid int,
1226 toversion int,
1227 action varchar(1024),
1228 errorlog_msg varchar(128)
1229 )
1230
1231 -- Downgrade login passwords if going to SMP version <= 15.0.1 or SDC version 15.0.1.5
1232 insert #actions values (1, @masterdb, 15015,
1233 @downgrade_passwords_sql,
1234 'Downgrade : Downgrading login passwords.')
1235
1236 -- Mark stored procs for recompile on any downgrade
1237 insert #actions values (2, @alldbs, @any15versid,
1238 @mark_dbprocs_sql,
1239 'Downgrade : Marking stored procedures to be recreated from text.')
1240
1241 -- Remove sysoptions if going to <= 15.0.1
1242 insert #actions values (3, @masterdb, 15015,
1243 @drop_1502_sysoptions_sql,
1244 'Downgrade : Dropping Sysoptions system table.')
1245
1246 -- Downgrade systabstats if going to <= 15.0.1
1247 insert #actions values (4, @alldbs, 15015,
1248 'if exists (select 1 from :DBNAME:..syscolumns ' +
1249 'where id = 23 and name = ''pioclmdata'') ' +
1250 'begin ' +
1251 'delete :DBNAME:..syscolumns ' +
1252 'where id = 23 ' +
1253 ' and name in (''pioclmdata'', ''pioclmindex'', ' +
1254 '''piocsmdata'', ''piocsmindex'') ' +
1255 'plan ''(delete(i_scan csyscolumns '
1256 + ':DBNAME:..syscolumns))'' ' +
1257 'insert into :DBNAME:..syscolumns ' +
1258 '(id, number, colid, status, type, length, offset, ' +
1259 'usertype, cdefault, domain, name, status3) ' +
1260 'values(23, 0, 19, 0, 62, 8, 112, ' +
1261 '8, 0, 0, ''spare2'', 0) ' +
1262 'insert into :DBNAME:..syscolumns ' +
1263 '(id, number, colid, status, type, length, offset, ' +
1264 'usertype, cdefault, domain, name, status3) ' +
1265 'values(23, 0, 27, 0, 62, 8, 144, ' +
1266 '8, 0, 0, ''spare4'', 0) ' +
1267
1268 'update :DBNAME:..syscolumns ' +
1269 'set name = ''spare5'' ' +
1270 'where id = 23 and name = ''warmcachepgcnt'' ' +
1271 'end',
1272 'Downgrade : Downgrading Systabstats system table.')
1273
1274 -- Downgrade sysconfigures if going to 15.0.1.5
1275 insert #actions values (5, @masterdb, 15015, @downgrade_15015_config,
1276 'Downgrade : Updating 15.0.1.5 configuration parameters.')
1277
1278 -- Downgrade sysmessages if going to 15.0.1.5
1279 insert #actions values (6, @masterdb, 15015, @downgrade_15015_msg,
1280 'Downgrade : Updating 15.0.1.5 sysmessages.')
1281
1282 -- Revert the length of sysservers.srvnetname from 255 to 32
1283 insert #actions values (7, @masterdb, 15020,
1284 @reset_sysservers_srvnetname_len,
1285 'Downgrade : Resetting max length of sysservers.srvnetname')
1286
1287 -- Downgrade sysprocedures if going to < 15.0.3
1288 insert #actions values (8, @alldbs, 15020,
1289 'update :DBNAME:..sysprocedures ' +
1290 'set qp_setting = null ' +
1291 'where qp_setting is not null ' +
1292 'delete :DBNAME:..syscolumns ' +
1293 'where id = 5 and ' +
1294 'name = ''qp_setting''',
1295 'Downgrade : Downgrading Sysprocedures system table.')
1296
1297 -- Downgrade sysqueryplans if going to < 15.0.3
1298 insert #actions values (9, @alldbs, 15020,
1299 'update :DBNAME:..sysqueryplans ' +
1300 'set dbid = null, qpdate = null, sprocid = null, ' +
1301 'hashkey2 = null, key1 = null, key2 = null, ' +
1302 'key3 = null, key4 = null ' +
1303 'where dbid is not null ' +
1304 'or qpdate is not null ' +
1305 'or sprocid is not null ' +
1306 'or hashkey2 is not null ' +
1307 'or key1 is not null ' +
1308 'or key2 is not null ' +
1309 'or key3 is not null ' +
1310 'or key4 is not null ' +
1311 'delete :DBNAME:..syscolumns ' +
1312 'where id = 27 and name in (''dbid'', ''qpdate'', ' +
1313 '''sprocid'', ''hashkey2'', ' +
1314 '''key1'', ''key2'', ''key3'', ''key4'')',
1315 'Downgrade : Downgrade Sysqueryplans system table.')
1316
1317 -- Remove some config params if going to < 15.0.3
1318 insert #actions values (10, @masterdb, 15020,
1319 'delete from master.dbo.sysconfigures where config > 500',
1320 'Downgrade : Removing pre-15.0.3 configuration parameters.')
1321
1322 -- Remove some config params if going to < 15.5
1323 insert #actions values (11, @masterdb, 15035,
1324 'delete from sysconfigures where config > 508',
1325 'Downgrade : Removing pre-15.5 configuration parameters.')
1326
1327 insert #actions values (12, @masterdb, 15035,
1328 @durability_sql,
1329 'Downgrade : Removing durability column in sysdatabases.')
1330
1331 -- Remove types 'bigdatetime', 'bigtime' if goint to < 15.5
1332 insert #actions values (13, @alldbs, 15035,
1333 'delete :DBNAME:..systypes where name in ' +
1334 '(''bigdatetime'',''bigtime'', ''bigdatetimen'', ''bigtimen'')',
1335 'Downgrade : Removing types ''bigdatetime'' and ''bigtime''')
1336
1337 -- Downgrade sysobjects if going to < 15.5
1338 insert #actions values (14, @masterdb, 15035,
1339 @sysobjstat3_sql,
1340 'Downgrade : Downgrade Sysobjects system table.')
1341
1342 -- Remove index sysdevices.ncsysdevices on sysdevices if going to any SMP < 15.5
1343 -- Exception: 15015 is the only SDC version which is below 15030.
1344 if (@toversid != 15015)
1345 begin --{
1346 insert #actions values (15, @masterdb, 15030,
1347 'set switch on drop_system_index with override ' +
1348 'drop index sysdevices.ncsysdevices ' +
1349 'set switch off drop_system_index ',
1350 'Downgrade : Removing index sysdevices.ncsysdevices on sysdevices.')
1351 end --}
1352
1353 -- Downgrade sysauditoptions if going to <= 15.0.1
1354 if (@sybsecuritydb is not null)
1355 begin --{
1356 insert #actions values (16, @sybsecuritydb, 15010,
1357 @downgrade_sysauditoptions_sql,
1358 'Downgrade : Downgrading global auditing options.')
1359 end --}
1360
1361 -- Remove full logging modes from sysattributes in any version < 15.7
1362 insert #actions values (17, @masterdb, 15500,
1363 'delete sysattributes where class = 38',
1364 'Downgrade : Removing full logging modes from sysattributes.')
1365
1366 -- Downgrade data-only locked table rows if going to <= 15.5.6
1367 insert #actions values (18, @alldbs, 15560,
1368 'declare @ret int '
1369 + 'select @ret = dol_downgrade_check('':DBNAME:'', @toversid) '
1370 + 'print "Database :DBNAME: table downgrade status: %1!", @ret '
1371 + 'if @ret != 0 '
1372 + 'begin '
1373 + ' print "*** Tables in database '':DBNAME:'' cannot be downgraded." '
1374 + ' print "*** See the server error log for details." '
1375 + ' select @exec_error_count = @exec_error_count + 1 '
1376 + 'end '
1377 , 'Downgrade : Downgrading data-only locked table rows.')
1378
1379 -- Remove full logging modes from sysattributes in any version < 15.7
1380 insert #actions values (19, @masterdb, 15500,
1381 'delete sysattributes where class = 38',
1382 'Downgrade : Removing full logging modes from sysattributes.')
1383
1384 -- Remove column sysoptions.number in any version < 15.7
1385 insert #actions values (20, @masterdb, 15699,
1386 'delete syscolumns where id = object_id(''sysoptions'') and name=''number''',
1387 'Downgrade : Removing column sysoptions.number.')
1388
1389
1390
1391 -- Remove srvprincipal column from sysservers table if going to <= SMP 15.5 or SDC 15.5.0.5
1392 insert #actions values (21, @masterdb, 15505,
1393 'delete syscolumns where id = object_id(''sysservers'') ' +
1394 'and name = ''srvprincipal''',
1395 'Downgrade : Removing srvprincipal column from sysservers system table')
1396
1397 -- Remove 'automatic master key access' config params if going to <= 15.5
1398 insert #actions values (22, @masterdb, 15505,
1399 'delete sysconfigures where config = 503',
1400 'Downgrade : Removing ''automatic master key access'' configuration parameter.')
1401
1402 -- Remove sysattribute values if going to <= 15.5
1403 insert #actions values (23, @masterdb, 15505,
1404 'delete sysattributes where class = 25 and attribute in (2, 3)',
1405 'Downgrade : Removing DualControl sysattribute rows')
1406
1407 -- Downgrade sysattributes system catalog if going to <= 15.5
1408 insert #actions values (24, @alldbs, 15505,
1409 'update :DBNAME:..sysattributes ' +
1410 'set object_cinfo2 = null, object_datetime = null ' +
1411 'where object_cinfo2 is not null ' +
1412 'or object_datetime is not null ' +
1413 'delete :DBNAME:..syscolumns ' +
1414 'where id = 21 and name in (''object_cinfo2'', ' +
1415 '''object_datetime'')',
1416 'Downgrade : Downgrading sysattributes system table.')
1417
1418 -- Downgrade syscomments system catalog if going to <= 15.5
1419 insert #actions values (25, @alldbs, 15505,
1420 'update :DBNAME:..syscomments ' +
1421 'set encrkeyid = null ' +
1422 'where encrkeyid is not null ' +
1423 'delete :DBNAME:..syscolumns ' +
1424 'where id = 6 and name = ''version'' ' +
1425 'delete :DBNAME:..syscolumns ' +
1426 'where id = 6 and name =''encrkeyid''',
1427 'Downgrade : Downgrading syscomments system table.')
1428
1429 -- Truncate password and lock role if going to <= SMP 15.5 or SDC 15.5.0.5
1430 insert #actions values (26, @masterdb, 15505,
1431 'delete statistics syssrvroles(password) ' +
1432 'if exists (select 1 from syssrvroles where password is not null) ' +
1433 'begin ' +
1434 'print "Truncating password and locking following role(s)" ' +
1435 'select name from syssrvroles where password is not null ' +
1436 'update syssrvroles set password = null, status = (status | @lockrole) ' +
1437 'where password is not null ' +
1438 'end ' +
1439 'update syssrvroles set locksuid = null, lockreason = null, lockdate = null ' +
1440 'where locksuid is not null ' +
1441 'or lockreason is not null ' +
1442 'or lockdate is not null ' +
1443 'delete syscolumns where id = object_id(''syssrvroles'') ' +
1444 'and name in (''locksuid'', ''lockreason'', ''lockdate'')',
1445 'Downgrade : Truncated role password, locked role and ' +
1446 'removed columns locksuid, lockreason, lockdate from syssrvroles')
1447
1448 -- Resize syssrvroles.password if going to SMP version <= 15.0.1 or SDC version 15.0.1.5
1449 -- This step should be done after step 26 where syssrvroles.password column is accessed.
1450 insert #actions values (27, @masterdb, 15015,
1451 'delete statistics syssrvroles(password) ' +
1452 'update syscolumns set length = 30 ' +
1453 'where id = object_id(''syssrvroles'') and name = ''password''',
1454 'Downgrade : Resizing role password length.')
1455
1456 -- Remove catalog changes for RSA Keypair Regeneration Period and Login Profile
1457 -- if going to <= SMP 15.5 or SDC 15.5.0.5
1458 insert #actions values (28, @masterdb, 15505,
1459 'delete sysattributes where class = 35 ' +
1460 'delete sysattributes where class = 39 ' +
1461 'update syslogins set lpid = null, crsuid = null ' +
1462 'where lpid is not null ' +
1463 'or crsuid is not null ' +
1464 'delete syscolumns where id = object_id(''syslogins'') ' +
1465 'and name in (''lpid'', ''crsuid'') ' +
1466 'delete syslogins where (status & @lp_status) = @lp_status ' +
1467 'update syslogins set status = status & ~(@exempt_lock) ' +
1468 'where (status & @exempt_lock) = @exempt_lock',
1469 'Downgrade : Removing catalog changes for RSA Keypair Regeneration Period and Login Profile')
1470
1471 -- Remove login and login profile management audit options if going to <= SMP 15.5 or SDC 15.5.0.5
1472 if (@sybsecuritydb is not null)
1473 begin
1474 insert #actions values (29, @sybsecuritydb, 15505,
1475 'delete from sybsecurity..sysauditoptions where ' +
1476 'name in (''security_profile'', ''login_admin'')',
1477 'Downgrade : Removing login and login profile management audit options')
1478 end
1479
1480 -- Remove RAT configuration params if going to < 15.7
1481 insert #actions values (30, @masterdb, 15699,
1482 'delete from sysconfigures where config = 542',
1483 'Downgrade : Removing 15.7 RAT configuration parameters.')
1484
1485 -- Remove RAT MRP sysattribute values if going to < 15.7
1486 insert #actions values (31, @allbutmasterdb, 15699,
1487 'delete from :DBNAME:.. sysattributes where ' +
1488 '(class = 11 and object_info1 > 0) ' +
1489 'or (class = 11 and attribute in (36, 37, 38, 39, 40, 41)) ' +
1490 'or (class = 41)',
1491 'Downgrade : Removing RAT MRP sysattribute rows')
1492
1493 -- Remove monLockTimeout table configuration params if going to < 15.7
1494 insert #actions values (32, @masterdb, 15699,
1495 'delete from sysconfigures where config in (544, 545)',
1496 'Downgrade : Removing ''lock timeout pipe max messages'' and ''lock timeout pipe active'' configuration parameters.')
1497
1498 -- Remove 15.7 config options if going to < 15.7
1499 insert #actions values (33, @masterdb, 15699,
1500 'delete from master..sysconfigures where config in (550,551,566,567,568,569)',
1501 'Downgrade : Removing configuration options added in 15.7')
1502
1503 -- Remove 15.7 in-row LOB fields if going to < 15.7
1504 insert #actions values (34, @alldbs, 15699,
1505 'update :DBNAME:..syscolumns set lobcomp_lvl = null where lobcomp_lvl is not null ' +
1506 'update :DBNAME:..syscolumns set inrowlen = null where inrowlen is not null ' +
1507 'delete from :DBNAME:..syscolumns where id = object_id(''syscolumns'') ' +
1508 'and name in (''lobcomp_lvl'', ''inrowlen'')',
1509 'Downgrade : Removing syscolumns.lobcomp_lvl and syscolumns.inrowlen')
1510
1511 -- Remove 15.7 in-row LOB fields if going to < 15.7
1512 insert #actions values (35, @masterdb, 15699,
1513 'update sysdatabases set lobcomp_lvl = null where lobcomp_lvl is not null ' +
1514 'update sysdatabases set inrowlen = null where inrowlen is not null ' +
1515 'update sysdatabases set dcompdefaultlevel = null where dcompdefaultlevel is not null ' +
1516 'delete from syscolumns where id = object_id(''sysdatabases'') ' +
1517 'and name in (''lobcomp_lvl'', ''inrowlen'', ''dcompdefaultlevel'')',
1518 'Downgrade : Removing sysdatabases.lobcomp_lvl and sysdatabases.inrowlen')
1519
1520 -- Remove syscomments.version if going to < 15.7
1521 insert #actions values (36, @alldbs, 15699,
1522 'delete from :DBNAME:..syscolumns where id = 6 and name = ''version''',
1523 'Downgrade : Removing syscomments.version')
1524
1525 /*
1526 ** MAINTAINERS: add downgrade tasks in this area.
1527 **
1528 ** Place individual tasks above this comment, incrementing the
1529 ** action ID.
1530 **
1531 ** Place changes to upgrade IDs below this comment, decrementing
1532 ** the action ID. These changes must be in _descending order_ by
1533 ** target version, since the last one processed will be the ultimate
1534 ** value retained. These must be the highest-numbered items in the
1535 ** list, since we want them to execute last. The minor ID and
1536 ** ASEvers together act as indicators showing that the database
1537 ** is exactly at the target downgrade version.
1538 */
1539 -- Reset minor upgrade ID as appropriate to the target version
1540 insert #actions values (991, @masterdb, 15500,
1541 @dbinfo_update_1550_master_sql,
1542 'Downgrade : Setting master database minor upgrade version.')
1543 insert #actions values (992, @allbutmasterdb, 15500,
1544 @dbinfo_update_1550_userdb_sql,
1545 'Downgrade : Setting user databases minor upgrade version.')
1546 insert #actions values (993, @masterdb, 15020,
1547 @dbinfo_update_1502_master_sql,
1548 'Downgrade : Setting master database minor upgrade version.')
1549 insert #actions values (994, @allbutmasterdb, 15020,
1550 @dbinfo_update_1502_userdb_sql,
1551 'Downgrade : Setting user databases minor upgrade version.')
1552 insert #actions values (995, @alldbs, 15015,
1553 @dbinfo_update_15015_alldbs_sql,
1554 'Downgrade : Setting user databases minor upgrade version.')
1555 insert #actions values (996, @masterdb, 15010,
1556 @dbinfo_update_1500_master_sql,
1557 'Downgrade : Setting master database minor upgrade version.')
1558 insert #actions values (997, @allbutmasterdb, 15010,
1559 @dbinfo_update_1500_userdb_sql,
1560 'Downgrade : Setting user databases minor upgrade version.')
1561
1562 -- Turn on 0x00000020 for dbi_status4 to indicate downgrade happened
1563 insert #actions values (998, @masterdb, @any15versid,
1564 @dbinfo_turnon_downgradeneed_sql,
1565 'Downgrade : Turning on database downgrade indicator.')
1566
1567 -- Reset ASEvers to a value appropriate to the target version
1568 insert #actions values (999, @alldbs, @any15versid,
1569 @dbinfo_reset_asevers_sql,
1570 'Downgrade : Resetting database version indicator.')
1571
1572 /*
1573 ** MAINTAINERS: as you add tasks, add them above or below the
1574 ** "Reset minor upgrade ID" items, as appropriate. Add no items
1575 ** beyond value 999.
1576 */
1577
1578
1579 /* A cursor to traverse sysdatabases */
1580 declare sysdbc cursor for
1581 select dbid from #sysdatabases
1582
1583 /*
1584 ** This cursor will traverse the #actions database and the
1585 ** actions will be performed based on the from and to versions:
1586 ** perform the action if [toversion is "any"] or [toversion
1587 ** less than current AND greater or equal to target].
1588 */
1589 declare actc cursor for
1590 select actionid, dbid, action, errorlog_msg
1591 from #actions
1592 where (toversion = @any15versid)
1593 or ((toversion < @fromversid) and (toversion >= @toversid))
1594 order by actionid
1595
1596 dbcc printolog('Starting downgrading ASE.')
1597 select @exec_error_count = 0
1598
1599 open actc
1600 fetch actc into @actionid, @whatdbid, @sqlbuf, @errorlog_msg
1601 while (@@sqlstatus = 0 and @exec_error_count = 0)
1602 begin
1603 if (@verbose = 1)
1604 begin
1605 print ''
1606 print 'Executing downgrade step %1! [%2!] for :',
1607 @actionid, @sqlbuf
1608 end
1609
1610 /*
1611 ** Write related downgrade step informational message in
1612 ** the errorlog.
1613 */
1614 dbcc printolog(@errorlog_msg)
1615
1616 /*
1617 ** Loop for each writable database and execute
1618 ** the command if it is authorized.
1619 */
1620 open sysdbc
1621 fetch sysdbc into @dbid
1622 while (@@sqlstatus = 0)
1623 begin -- {
1624 if ((@whatdbid in (@alldbs, @dbid)) or
1625 (@whatdbid = @allbutmasterdb and @dbid != @masterdb))
1626 begin -- {
1627 select @dbname = db_name(@dbid)
1628 select @sqlcmd = @sqlbuf
1629 select @dbnameoffset = patindex('%:DBNAME:%', @sqlcmd)
1630 while (@dbnameoffset > 0)
1631 begin
1632 select @sqlcmd =
1633 stuff(@sqlcmd,
1634 @dbnameoffset, 8,
1635 @dbname)
1636 select @dbnameoffset = patindex('%:DBNAME:%',
1637 @sqlcmd)
1638 end
1639 if (@verbose = 1)
1640 begin
1641 print '- Database: %1! (dbid: %2!)',
1642 @dbname, @dbid
1643 if (@show_cmd = 1)
1644 begin
1645 print 'sql command is: %1!', @sqlcmd
1646 print ''
1647 end
1648 end
1649 if (@mark_dbprocs_sql = @sqlcmd)
1650 begin
1651 /*
1652 ** Temporary workaround until DBCC
1653 ** can be run in exec.
1654 */
1655 dbcc markprocs(@dbid)
1656 end
1657 else
1658 if (@durability_sql = @sqlcmd)
1659 begin
1660 /*
1661 ** sp_downgrade_durability cannot
1662 ** be run in execute immediate.
1663 */
1664 exec sp_downgrade_durability 'exec'
1665 end
1666 else
1667 if (@sysobjstat3_sql = @sqlcmd)
1668 begin
1669 /*
1670 ** sp_downgrade_sysobjects cannot
1671 ** be run in execute immediate.
1672 */
1673 exec sp_downgrade_sysobjects 'exec'
1674 end
1675 else
1676 if (@downgrade_15015_config = @sqlcmd)
1677 begin
1678 if (@toversid = 15015)
1679 begin
1680 exec (@downgrade_sysconfigures_15015_sql1)
1681 exec (@downgrade_sysconfigures_15015_sql2)
1682 exec (@downgrade_sysconfigures_15015_sql3)
1683 exec (@downgrade_sysconfigures_15015_cmd)
1684 end
1685 end
1686 else
1687 if (@downgrade_15015_msg = @sqlcmd)
1688 begin
1689 if (@toversid = 15015)
1690 begin
1691 set nocount on
1692 exec sp_inst15015msg
1693 set nocount off
1694 update master..syslanguages set upgrade = 1501
1695 end
1696 end
1697 else
1698 if ((@reset_sysservers_srvnetname_len = @sqlcmd) and (@toversid = 15015))
1699 begin
1700 /*
1701 ** Skip this downgrade item, as len of sysservers.srvnetname is
1702 ** still 255 in 15.0.1.5.
1703 */
1704 if (@verbose = 1)
1705 begin
1706 print ''
1707 print " Skip downgrade step %1! for database %2! on downgrading to version %3!",
1708 @actionid, @dbname, @toversid
1709 end
1710 end
1711 else
1712 begin
1713 exec (@sqlcmd)
1714 if (@@error != 0)
1715 begin
1716 select @exec_error_count = @exec_error_count + 1
1717 print 'downgrade step %1! failed.', @actionid
1718 print 'failing command is: %1!', @sqlcmd
1719 end
1720 end
1721 end -- }
1722 fetch sysdbc into @dbid
1723 end -- }
1724 close sysdbc
1725
1726 /* Pick up next downgrade step. */
1727 fetch actc into @actionid, @whatdbid, @sqlbuf, @errorlog_msg
1728 end
1729 close actc
1730
1731 deallocate cursor actc
1732 deallocate cursor sysdbc
1733
1734 if @exec_error_count != 0
1735 begin
1736 dbcc printolog('One or more downgrade steps failed. Downgrade is not complete.')
1737 return 1
1738 end
1739
1740 dbcc printolog('ASE downgrade completed.')
1741 return 0
1742 end -- }
1743
exec sp_procxmode 'sp_downgrade', 'AnyMode'
go
Grant Execute on sp_downgrade to public
go
| DEFECTS |
 MEST 4 Empty String will be replaced by Single Space |
505 |
| MEST 4 Empty String will be replaced by Single Space |
522 |
| MEST 4 Empty String will be replaced by Single Space |
524 |
| MEST 4 Empty String will be replaced by Single Space |
684 |
| MEST 4 Empty String will be replaced by Single Space |
713 |
| MEST 4 Empty String will be replaced by Single Space |
1605 |
| MEST 4 Empty String will be replaced by Single Space |
1646 |
| MEST 4 Empty String will be replaced by Single Space |
1706 |
| MINU 4 Unique Index with nullable columns master..sysconfigures |
master..sysconfigures |
| MINU 4 Unique Index with nullable columns master..syslanguages |
master..syslanguages |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
503 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent} |
813 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent} |
819 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
854 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
894 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
897 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
286 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
296 |
| QTYP 4 Comparison type mismatch smallint = int |
296 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
384 |
| QTYP 4 Comparison type mismatch smallint = int |
384 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
412 |
| QTYP 4 Comparison type mismatch smallint = int |
412 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
681 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
701 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
705 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
709 |
| QTYP 4 Comparison type mismatch tinyint = int |
709 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
813 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
819 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
926 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
930 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
941 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
945 |
| QUDW 4 Update or delete with no where clause |
1694 |
| TNOI 4 Table with no index master..syscurconfigs |
master..syscurconfigs |
| TNOI 4 Table with no index master..syslisteners |
master..syslisteners |
| TNOU 4 Table with no unique index master..spt_values |
master..spt_values |
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc2 |
440 |
| CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc1 |
614 |
| CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc1 |
646 |
| CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc0 |
739 |
| CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdevc |
782 |
| CUNU 3 Cursor not updated: cursor should contain 'for read only' clause usercachec |
818 |
| CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc0 |
969 |
| MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain |
20 |
| MGTP 3 Grant to public master..spt_values |
|
| MGTP 3 Grant to public master..syscharsets |
|
| MGTP 3 Grant to public master..sysconfigures |
|
| MGTP 3 Grant to public master..syscurconfigs |
|
| MGTP 3 Grant to public master..sysdatabases |
|
| MGTP 3 Grant to public master..sysdevices |
|
| MGTP 3 Grant to public master..syslanguages |
|
| MGTP 3 Grant to public master..syslisteners |
|
| MGTP 3 Grant to public master..sysservers |
|
| MGTP 3 Grant to public sybsystemprocs..sp_downgrade |
|
| MGTP 3 Grant to public sybsystemprocs..syscolumns |
|
| MGTP 3 Grant to public sybsystemprocs..sysobjects |
|
| MNER 3 No Error Check should check @@error after select into |
237 |
| MNER 3 No Error Check should check @@error after select into |
246 |
| MNER 3 No Error Check should check return value of exec |
334 |
| MNER 3 No Error Check should check return value of exec |
338 |
| MNER 3 No Error Check should check @@error after select into |
446 |
| MNER 3 No Error Check should check return value of exec |
503 |
| MNER 3 No Error Check should check @@error after select into |
555 |
| MNER 3 No Error Check should check return value of exec |
561 |
| MNER 3 No Error Check should check @@error after select into |
586 |
| MNER 3 No Error Check should check return value of exec |
592 |
| MNER 3 No Error Check should check @@error after update |
864 |
| MNER 3 No Error Check should check @@error after insert |
1232 |
| MNER 3 No Error Check should check @@error after insert |
1237 |
| MNER 3 No Error Check should check @@error after insert |
1242 |
| MNER 3 No Error Check should check @@error after insert |
1247 |
| MNER 3 No Error Check should check @@error after insert |
1275 |
| MNER 3 No Error Check should check @@error after insert |
1279 |
| MNER 3 No Error Check should check @@error after insert |
1283 |
| MNER 3 No Error Check should check @@error after insert |
1288 |
| MNER 3 No Error Check should check @@error after insert |
1298 |
| MNER 3 No Error Check should check @@error after insert |
1318 |
| MNER 3 No Error Check should check @@error after insert |
1323 |
| MNER 3 No Error Check should check @@error after insert |
1327 |
| MNER 3 No Error Check should check @@error after insert |
1332 |
| MNER 3 No Error Check should check @@error after insert |
1338 |
| MNER 3 No Error Check should check @@error after insert |
1346 |
| MNER 3 No Error Check should check @@error after insert |
1356 |
| MNER 3 No Error Check should check @@error after insert |
1362 |
| MNER 3 No Error Check should check @@error after insert |
1367 |
| MNER 3 No Error Check should check @@error after insert |
1380 |
| MNER 3 No Error Check should check @@error after insert |
1385 |
| MNER 3 No Error Check should check @@error after insert |
1392 |
| MNER 3 No Error Check should check @@error after insert |
1398 |
| MNER 3 No Error Check should check @@error after insert |
1403 |
| MNER 3 No Error Check should check @@error after insert |
1408 |
| MNER 3 No Error Check should check @@error after insert |
1419 |
| MNER 3 No Error Check should check @@error after insert |
1430 |
| MNER 3 No Error Check should check @@error after insert |
1450 |
| MNER 3 No Error Check should check @@error after insert |
1458 |
| MNER 3 No Error Check should check @@error after insert |
1474 |
| MNER 3 No Error Check should check @@error after insert |
1481 |
| MNER 3 No Error Check should check @@error after insert |
1486 |
| MNER 3 No Error Check should check @@error after insert |
1494 |
| MNER 3 No Error Check should check @@error after insert |
1499 |
| MNER 3 No Error Check should check @@error after insert |
1504 |
| MNER 3 No Error Check should check @@error after insert |
1512 |
| MNER 3 No Error Check should check @@error after insert |
1521 |
| MNER 3 No Error Check should check @@error after insert |
1540 |
| MNER 3 No Error Check should check @@error after insert |
1543 |
| MNER 3 No Error Check should check @@error after insert |
1546 |
| MNER 3 No Error Check should check @@error after insert |
1549 |
| MNER 3 No Error Check should check @@error after insert |
1552 |
| MNER 3 No Error Check should check @@error after insert |
1555 |
| MNER 3 No Error Check should check @@error after insert |
1558 |
| MNER 3 No Error Check should check @@error after insert |
1563 |
| MNER 3 No Error Check should check @@error after insert |
1568 |
| MNER 3 No Error Check should check return value of exec |
1664 |
| MNER 3 No Error Check should check return value of exec |
1673 |
| MNER 3 No Error Check should check return value of exec |
1692 |
| MNER 3 No Error Check should check @@error after update |
1694 |
| MUCO 3 Useless Code Useless Begin-End Pair |
27 |
| MUCO 3 Useless Code Useless Brackets |
103 |
| MUCO 3 Useless Code Useless Brackets |
109 |
| MUCO 3 Useless Code Useless Brackets |
121 |
| MUCO 3 Useless Code Useless Brackets |
147 |
| MUCO 3 Useless Code Useless Brackets |
148 |
| MUCO 3 Useless Code Useless Brackets |
149 |
| MUCO 3 Useless Code Useless Brackets |
161 |
| MUCO 3 Useless Code Useless Brackets |
163 |
| MUCO 3 Useless Code Useless Brackets |
165 |
| MUCO 3 Useless Code Useless Brackets |
173 |
| MUCO 3 Useless Code Useless Brackets |
178 |
| MUCO 3 Useless Code Useless Brackets |
182 |
| MUCO 3 Useless Code Useless Brackets |
183 |
| MUCO 3 Useless Code Useless Brackets |
184 |
| MUCO 3 Useless Code Useless Brackets |
195 |
| MUCO 3 Useless Code Useless Brackets |
197 |
| MUCO 3 Useless Code Useless Brackets |
211 |
| MUCO 3 Useless Code Useless Brackets |
252 |
| MUCO 3 Useless Code Useless Brackets |
258 |
| MUCO 3 Useless Code Useless Brackets |
271 |
| MUCO 3 Useless Code Useless Brackets |
280 |
| MUCO 3 Useless Code Useless Brackets |
325 |
| MUCO 3 Useless Code Useless Brackets |
332 |
| MUCO 3 Useless Code Useless Brackets |
336 |
| MUCO 3 Useless Code Useless Brackets |
340 |
| MUCO 3 Useless Code Useless Brackets |
357 |
| MUCO 3 Useless Code Useless Brackets |
367 |
| MUCO 3 Useless Code Useless Brackets |
380 |
| MUCO 3 Useless Code Useless Brackets |
388 |
| MUCO 3 Useless Code Useless Brackets |
398 |
| MUCO 3 Useless Code Useless Brackets |
466 |
| MUCO 3 Useless Code Useless Brackets |
476 |
| MUCO 3 Useless Code Useless Brackets |
481 |
| MUCO 3 Useless Code Useless Brackets |
489 |
| MUCO 3 Useless Code Useless Brackets |
513 |
| MUCO 3 Useless Code Useless Brackets |
526 |
| MUCO 3 Useless Code Useless Brackets |
539 |
| MUCO 3 Useless Code Useless Brackets |
619 |
| MUCO 3 Useless Code Useless Brackets |
651 |
| MUCO 3 Useless Code Useless Brackets |
682 |
| MUCO 3 Useless Code Useless Brackets |
697 |
| MUCO 3 Useless Code Useless Brackets |
724 |
| MUCO 3 Useless Code Useless Brackets |
745 |
| MUCO 3 Useless Code Useless Brackets |
748 |
| MUCO 3 Useless Code Useless Brackets |
752 |
| MUCO 3 Useless Code Useless Brackets |
788 |
| MUCO 3 Useless Code Useless Brackets |
824 |
| MUCO 3 Useless Code Useless Brackets |
850 |
| MUCO 3 Useless Code Useless Brackets |
855 |
| MUCO 3 Useless Code Useless Brackets |
880 |
| MUCO 3 Useless Code Useless Brackets |
890 |
| MUCO 3 Useless Code Useless Brackets |
898 |
| MUCO 3 Useless Code Useless Brackets |
917 |
| MUCO 3 Useless Code Useless Brackets |
933 |
| MUCO 3 Useless Code Useless Brackets |
948 |
| MUCO 3 Useless Code Useless Brackets |
962 |
| MUCO 3 Useless Code Useless Brackets |
974 |
| MUCO 3 Useless Code Useless Brackets |
976 |
| MUCO 3 Useless Code Useless Brackets |
980 |
| MUCO 3 Useless Code Useless Brackets |
990 |
| MUCO 3 Useless Code Useless Brackets |
1000 |
| MUCO 3 Useless Code Useless Brackets |
1009 |
| MUCO 3 Useless Code Useless Brackets |
1028 |
| MUCO 3 Useless Code Useless Brackets |
1059 |
| MUCO 3 Useless Code Useless Brackets |
1083 |
| MUCO 3 Useless Code Useless Brackets |
1101 |
| MUCO 3 Useless Code Useless Brackets |
1117 |
| MUCO 3 Useless Code Useless Brackets |
1344 |
| MUCO 3 Useless Code Useless Brackets |
1354 |
| MUCO 3 Useless Code Useless Brackets |
1472 |
| MUCO 3 Useless Code Useless Brackets |
1601 |
| MUCO 3 Useless Code Useless Brackets |
1603 |
| MUCO 3 Useless Code Useless Brackets |
1622 |
| MUCO 3 Useless Code Useless Brackets |
1624 |
| MUCO 3 Useless Code Useless Brackets |
1630 |
| MUCO 3 Useless Code Useless Brackets |
1639 |
| MUCO 3 Useless Code Useless Brackets |
1643 |
| MUCO 3 Useless Code Useless Brackets |
1649 |
| MUCO 3 Useless Code Useless Brackets |
1658 |
| MUCO 3 Useless Code Useless Brackets |
1667 |
| MUCO 3 Useless Code Useless Brackets |
1676 |
| MUCO 3 Useless Code Useless Brackets |
1678 |
| MUCO 3 Useless Code Useless Brackets |
1687 |
| MUCO 3 Useless Code Useless Brackets |
1689 |
| MUCO 3 Useless Code Useless Brackets |
1698 |
| MUCO 3 Useless Code Useless Brackets |
1704 |
| MUCO 3 Useless Code Useless Brackets |
1714 |
| MUIN 3 Column created using implicit nullability |
1222 |
| MUOT 3 Updates outside transaction |
1694 |
| QAFM 3 Var Assignment from potentially many rows |
234 |
| QAFM 3 Var Assignment from potentially many rows |
679 |
| QAFM 3 Var Assignment from potentially many rows |
699 |
| QAFM 3 Var Assignment from potentially many rows |
703 |
| QAFM 3 Var Assignment from potentially many rows |
852 |
| QAFM 3 Var Assignment from potentially many rows |
892 |
| QAFM 3 Var Assignment from potentially many rows |
895 |
| QCRS 3 Conditional Result Set |
268 |
| QCRS 3 Conditional Result Set |
875 |
| QCRS 3 Conditional Result Set |
912 |
| QCRS 3 Conditional Result Set |
929 |
| QCRS 3 Conditional Result Set |
944 |
| QCTC 3 Conditional Table Creation |
446 |
| QCTC 3 Conditional Table Creation |
555 |
| QCTC 3 Conditional Table Creation |
586 |
| QGWO 3 Group by/Distinct/Union without order by |
929 |
| QGWO 3 Group by/Distinct/Union without order by |
944 |
| QJWT 3 Join or Sarg Without Index on temp table |
250 |
| QPNC 3 No column in condition |
449 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id} |
326 |
| QSWV 3 Sarg with variable @dbid, Candidate Index: sysdatabases.ncsysdatabases unique(dbid) F |
296 |
| QSWV 3 Sarg with variable @dbid, Candidate Index: sysdatabases.ncsysdatabases unique(dbid) F |
412 |
| QTLO 3 Top-Level OR |
551 |
| QTLO 3 Top-Level OR |
558 |
| QTLO 3 Top-Level OR |
582 |
| QTLO 3 Top-Level OR |
589 |
| QTLO 3 Top-Level OR |
866 |
| QTLO 3 Top-Level OR |
1592 |
| VNRD 3 Variable is not read @lp_status |
138 |
| VNRD 3 Variable is not read @exempt_lock |
139 |
| VNRD 3 Variable is not read @lockrole |
140 |
| VUNU 3 Variable is not used @objname |
84 |
| VUNU 3 Variable is not used @objstat3 |
85 |
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) |
1590 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
440 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
614 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
646 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
739 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
782 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
818 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
969 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
1581 |
| MDYS 2 Dynamic SQL Marker |
397 |
| MDYS 2 Dynamic SQL Marker |
474 |
| MDYS 2 Dynamic SQL Marker |
501 |
| MDYS 2 Dynamic SQL Marker |
625 |
| MDYS 2 Dynamic SQL Marker |
664 |
| MDYS 2 Dynamic SQL Marker |
989 |
| MDYS 2 Dynamic SQL Marker |
999 |
| MDYS 2 Dynamic SQL Marker |
1008 |
| MDYS 2 Dynamic SQL Marker |
1027 |
| MDYS 2 Dynamic SQL Marker |
1033 |
| MDYS 2 Dynamic SQL Marker |
1039 |
| MDYS 2 Dynamic SQL Marker |
1058 |
| MDYS 2 Dynamic SQL Marker |
1064 |
| MDYS 2 Dynamic SQL Marker |
1070 |
| MDYS 2 Dynamic SQL Marker |
1082 |
| MDYS 2 Dynamic SQL Marker |
1087 |
| MDYS 2 Dynamic SQL Marker |
1092 |
| MDYS 2 Dynamic SQL Marker |
1680 |
| MDYS 2 Dynamic SQL Marker |
1681 |
| MDYS 2 Dynamic SQL Marker |
1682 |
| MDYS 2 Dynamic SQL Marker |
1683 |
| MDYS 2 Dynamic SQL Marker |
1713 |
| MRST 2 Result Set Marker |
268 |
| MRST 2 Result Set Marker |
875 |
| MRST 2 Result Set Marker |
912 |
| MRST 2 Result Set Marker |
929 |
| MRST 2 Result Set Marker |
944 |
| MSUB 2 Subquery Marker |
286 |
| MSUB 2 Subquery Marker |
325 |
| MSUB 2 Subquery Marker |
549 |
| MSUB 2 Subquery Marker |
580 |
| MSUB 2 Subquery Marker |
870 |
| MSUB 2 Subquery Marker |
907 |
| MSUB 2 Subquery Marker |
923 |
| MSUB 2 Subquery Marker |
938 |
| MSUC 2 Correlated Subquery Marker |
249 |
| MTR1 2 Metrics: Comments Ratio Comments: 21% |
20 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 126 = 139dec - 15exi + 2 |
20 |
| MTR3 2 Metrics: Query Complexity Complexity: 743 |
20 |
