| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_downgrade_sysobjects | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** raiserror Messages for downgrade 5 ** 6 ** 17428 "You must be in the 'master' database in order to change database options." 7 ** 19779 "ERROR: Cannot downgrade to '%1!' server, which does not support server's current default sortorder '%2!'." 8 ** 19891, "ERROR: Unable to downgrade this server from ASE version '%1!' to ASE version '%2!'. Please reconfigure the remote servers that are using SSL to avoid specification of common name and to ensure that the remote server names are less than 32 characters:" 9 ** 19954 "ERROR: Cannot downgrade to '%1!' server, which does not support the current charset '%2!'." 10 ** 19985 "ERROR: Cannot downgrade to '%1!' server, which does not support bigdatetime and bigtime datatypes." 11 */ 12 13 /* 14 ** SP_DOWNGRADE_SYSOBJECTS 15 ** 16 ** Description 17 ** sproc to reset the sysobjects schema back to it's original form. 18 ** 19 ** Parameter: 20 ** @action - "display" to display all objects whose sysstat3 21 ** column has non-zero value. 22 ** "exec" to restore sysobjects back to original version. 23 ** 24 ** Returns 25 ** 0 - success 26 ** 1 - error 27 ** 28 */ 29 create procedure sp_downgrade_sysobjects @action sysname = "display" 30 as 31 begin -- { 32 33 declare @dbname sysname, 34 @objname longsysname, 35 @objstat3 unsigned smallint, 36 @retval int, 37 @dbid int, 38 @sqlcmd varchar(1024), 39 @baddbstat int, 40 @baddbstat2 int, 41 @baddbstat3 int, 42 @warnings int 43 44 if (@action not in ("display", "exec")) 45 begin 46 print "Usage: sp_downgrade_sysobjects [display | exec]" 47 return 1 48 end 49 50 print "Checking sysobjects in preparation for downgrade" 51 52 /* 53 ** COPY from sp_downgrade. 54 ** Downgrade only those databases that are writable 55 ** 56 ** sysdatabases.status not in 0x20 (in load), 0x40 (not rec), 57 ** 0x80 (bypass), 0x100 (suspect), 0x400 (rdonly), 0x8000 (emergency) 58 ** 59 ** sysdatabases.status2 not in 0x10 (offline), 0x400 (online for 60 ** standby access) 61 ** 62 ** sysdatabases.status3 not in 0x1 (user proxy), 0x2 (ha proxy), 63 ** 0x8 (shutdown), 0x10 (failedover), 0x80 (quiesced), 0x400000 64 ** (archive) 65 */ 66 select @baddbstat = 34272, @baddbstat2 = 1040, 67 @baddbstat3 = 4194459 68 69 select dbid 70 into #sysdatabases1 71 from master.dbo.sysdatabases 72 where status & @baddbstat = 0 73 and status2 & @baddbstat2 = 0 74 and status3 & @baddbstat3 = 0 75 order by dbid 76 77 /* 78 ** Temp table is created to hold object name whose sysstat3 79 ** is not zero, as cursor cannot be created based on a select 80 ** statement containing variable "@dbname" and composing cursor 81 ** declaration and "@dbname" into immediate statement doesn't 82 ** work either. 83 */ 84 create table #stat3obj 85 ( 86 name longsysname, 87 sysstat3 unsigned smallint 88 ) 89 90 set @warnings = 0 91 92 /* A cursor to traverse sysdatabases */ 93 declare sysdbc2 cursor for 94 select dbid from #sysdatabases1 95 96 open sysdbc2 97 fetch sysdbc2 into @dbid 98 99 while (@@sqlstatus = 0) 100 begin -- { 101 102 select @dbname = db_name(@dbid) 103 set @retval = 0 104 select @sqlcmd = 'select @retval = count(1) from ' + @dbname + 105 '..sysobjects where sysstat3 != 0 ' + 106 'and type = ''U'' ' 107 exec (@sqlcmd) 108 109 /* 110 ** If rows having non-zero sysstat3 are found, print 111 ** them out as warnings in "display" mode or return as 112 ** errors in "exec" mode. 113 */ 114 if @retval != 0 115 begin 116 /* Report warnings of these tables */ 117 select @sqlcmd = 'insert #stat3obj ' + 118 'select name, sysstat3 from ' + @dbname 119 + '..sysobjects where sysstat3 != 0 ' + 120 'and type = ''U'' ' 121 exec (@sqlcmd) 122 123 declare sysdbc3 cursor for 124 select name, sysstat3 from #stat3obj 125 126 open sysdbc3 127 fetch sysdbc3 into @objname, @objstat3 128 while (@@sqlstatus = 0) 129 begin -- { 130 /* 131 ** Check status3 of sysobjects row, 132 ** 0x0001 - FULL logging mode. 133 ** 0x0002 - MINIMAL logging mode. 134 ** 0x8000 - Incremental transfer eligible. 135 */ 136 if (@objstat3 & 1 = 1) 137 begin 138 print "Error: Table '%1!..%2!' with FULL logging mode should be altered back to default logging mode before downgrade.", @dbname, @objname 139 select @warnings = @warnings + 1 140 end 141 if (@objstat3 & 2 = 2) 142 begin 143 print "Error: Table '%1!..%2!' with MINIMAL logging mode should be altered back to default logging mode before downgrade.", @dbname, @objname 144 select @warnings = @warnings + 1 145 end 146 if (@objstat3 & 32768 = 32768) 147 begin 148 print "Error: Table '%1!..%2!' eligible for incremental transfer should be altered to turn off incremental transfer before downgrade.", @dbname, @objname 149 select @warnings = @warnings + 1 150 end 151 fetch sysdbc3 into @objname, @objstat3 152 end --} 153 close sysdbc3 154 deallocate cursor sysdbc3 155 delete from #stat3obj 156 end 157 158 /* 159 ** User must have altered the tables having non-zero 160 ** sysstat3 column. Remove sysstat3 for sysobjects 161 ** and restore objspare in "exec" mode. 162 */ 163 if ((@action = "exec") and (@warnings = 0)) 164 begin 165 select @sqlcmd = 'delete ' + @dbname + 166 '..syscolumns where id = 1 ' + 167 'and name = ''sysstat3'' ' + 168 'update ' + @dbname + 169 '..syscolumns set type = 56,' + 170 'length = 4, usertype = 7 ' + 171 'where id = 1 and name = ''objspare'' ' 172 exec (@sqlcmd) 173 end 174 175 fetch sysdbc2 into @dbid 176 end --} 177 178 close sysdbc2 179 deallocate cursor sysdbc2 180 181 if (@warnings > 0) 182 begin 183 print "There are %1! errors during checking sysobjects.", @warnings 184 print "Please run alter table to set these reported tables to normal mode before downgrade." 185 return 1 186 end 187 188 return 0 189 end -- } 190 191
exec sp_procxmode 'sp_downgrade_sysobjects', 'AnyMode' go Grant Execute on sp_downgrade_sysobjects to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysdatabases (1)  read_writes table tempdb..#sysdatabases1 (1) read_writes table tempdb..#stat3obj (1) CALLERS called by proc sybsystemprocs..sp_downgrade  |
