Database | Proc | Application | Created | Links |
sybsystemprocs | sp_fix_dtm_ha_roles | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Messages for "sp_fix_dtm_ha_roles" 18040 4 ** 5 ** 18040, "Catalog procedure '%1!' can not be run in a transaction. 6 ** 7 ** dtm_tm_role and ha_role metadata is not added to the 8 ** system catalog as part of an upgrade from 11.5.x or 11.9.x to 12.0. 9 ** This stored procedure can be used to fix these catalogs if a 10 ** customer has already upgraded from 11.5.x or 11.9.x to 12.0. 11 ** 12 ** NOTE that an upgrade from 11.0.x to 12.0 does not require this 13 ** stored procedure to be executed nor for an upgrade from 14 ** 11.5.x or 11.9.x to a version that is greater than or equal 15 ** to 12.0.0.1 ESD#1. 16 */ 17 create procedure sp_fix_dtm_ha_roles 18 as 19 declare @sqlstr varchar(255) 20 declare @dbname varchar(30) 21 declare @rid int 22 declare @failure int 23 declare @msg varchar(255) 24 25 if (@@trancount > 0) 26 begin 27 /* 28 ** 18040, "Catalog procedure '%1!' can not be run in a transaction. 29 */ 30 raiserror 18040, "sp_fix_dtm_ha_roles" 31 return (1) 32 end 33 34 /* 35 ** Check if the user has sa role. Builtin proc_role() will 36 ** also do auditing if required as well as print an error 37 ** message if needed. 38 */ 39 if (proc_role("sa_role") = 0) 40 return (1) 41 42 set transaction isolation level 1 43 set chained off 44 45 /* In case these roles are not in syssrvroles */ 46 47 if not exists (select 1 from master.dbo.syssrvroles where srid = 7) 48 begin 49 insert into master.dbo.syssrvroles(srid, name, password) 50 values (7, 'dtm_tm_role', NULL) 51 end 52 53 if not exists (select 1 from master.dbo.syssrvroles where srid = 8) 54 begin 55 insert into master.dbo.syssrvroles(srid, name, password) 56 values (8, 'ha_role', NULL) 57 end 58 59 /* 60 ** Inserts an attribute that holds class id and the password expiration 61 ** for dtm_tm_role and ha_role. 62 */ 63 if not exists (select * from master.dbo.sysattributes 64 where class = 14 and object_type = 'PS' and 65 object_cinfo = 'role' and object = 7) 66 begin 67 insert master.dbo.sysattributes 68 (class, attribute, object_type, object_cinfo, object, int_value) 69 values (14, 0, 'PS', 'role', 7, 0) 70 end 71 72 if not exists (select * from master.dbo.sysattributes 73 where class = 14 and object_type = 'PS' and 74 object_cinfo = 'role' and object = 8) 75 begin 76 insert master.dbo.sysattributes 77 (class, attribute, object_type, object_cinfo, object, int_value) 78 values (14, 0, 'PS', 'role', 8, 0) 79 end 80 81 return (0) 82 83
exec sp_procxmode 'sp_fix_dtm_ha_roles', 'AnyMode' go Grant Execute on sp_fix_dtm_ha_roles to public go
DEFECTS | |
MINU 4 Unique Index with nullable columns master..sysattributes | master..sysattributes |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 64 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 73 |
MGTP 3 Grant to public master..sysattributes | |
MGTP 3 Grant to public master..syssrvroles | |
MGTP 3 Grant to public sybsystemprocs..sp_fix_dtm_ha_roles | |
MNER 3 No Error Check should check @@error after insert | 49 |
MNER 3 No Error Check should check @@error after insert | 55 |
MNER 3 No Error Check should check @@error after insert | 67 |
MNER 3 No Error Check should check @@error after insert | 76 |
MUCO 3 Useless Code Useless Brackets | 25 |
MUCO 3 Useless Code Useless Brackets | 31 |
MUCO 3 Useless Code Useless Brackets | 39 |
MUCO 3 Useless Code Useless Brackets | 40 |
MUCO 3 Useless Code Useless Brackets | 81 |
MUOT 3 Updates outside transaction | 76 |
QISO 3 Set isolation level | 42 |
QIWC 3 Insert with not all columns specified missing 6 columns out of 9 | 49 |
QIWC 3 Insert with not all columns specified missing 6 columns out of 9 | 55 |
QIWC 3 Insert with not all columns specified missing 9 columns out of 15 | 68 |
QIWC 3 Insert with not all columns specified missing 9 columns out of 15 | 77 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, object_cinfo, class} | 64 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, object_cinfo, class} | 73 |
VUNU 3 Variable is not used @sqlstr | 19 |
VUNU 3 Variable is not used @dbname | 20 |
VUNU 3 Variable is not used @rid | 21 |
VUNU 3 Variable is not used @failure | 22 |
VUNU 3 Variable is not used @msg | 23 |
MSUB 2 Subquery Marker | 47 |
MSUB 2 Subquery Marker | 53 |
MSUB 2 Subquery Marker | 63 |
MSUB 2 Subquery Marker | 72 |
MTR1 2 Metrics: Comments Ratio Comments: 42% | 17 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 12dec - 2exi + 2 | 17 |
MTR3 2 Metrics: Query Complexity Complexity: 46 | 17 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..syssrvroles (1) read_writes table master..sysattributes (1) |