DatabaseProcApplicationCreatedLinks
sybsystemprocssp_fix_dtm_ha_roles  31 Aug 14Defects 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)