DatabaseProcApplicationCreatedLinks
sybsystemprocssp_hadr_update  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_hadr_update
3         @mode int,
4         @state int,
5         @srvname varchar(30),
6         @srvnetname varchar(255),
7         @groupname varchar(30),
8         @genno int,
9         @cur int,
10        @max int
11    as
12        declare @hadrmode int, /* Current HADR mode */
13            @srv_genno int,
14            @HADRMEMBER int,
15            @HADR_CLASS int,
16            @HADR_GENERATION int,
17            @csrvname varchar(30),
18            @csrvnetname varchar(255),
19            @cgroupname varchar(30),
20            @retstat int,
21            @gen_exists int,
22            @gp_enabled int,
23            @status int,
24            @nullarg char(1),
25            @dummy int
26    
27        /* Initialize the defaults */
28        select @hadrmode = @@hadr_mode
29        select @HADR_CLASS = 43, @HADR_GENERATION = 0, @HADRMEMBER = 16
30        select @srv_genno = 0, @retstat = 0, @gen_exists = 0
31    
32        if not exists (select 1 from tempdb.dbo.sysobjects where
33                    name = 'hadr_temp_update' and type = 'U')
34        begin
35            create table tempdb.dbo.hadr_temp_update(
36                srvname varchar(30),
37                srvnetname varchar(255),
38                mode int,
39                state int,
40                groupname varchar(30),
41                genno int)
42        end
43    
44        /* Make sure server is standby */
45        if (@hadrmode != 0)
46        begin
47            dbcc logprint("Rejecting the HADR propagate command as server is not in standby mode.")
48            /* 17159, ASE encountered error during '%1!'. Refer to the ASE error log of server '%2!' for more details. */
49            raiserror 17159, "PROPAGATE", @@servername
50            return (1)
51        end
52    
53        /*
54        ** Do execution checks
55        **
56        **	If granular permissions is not enabled then sa_role is required.
57        **	If granular permissions is enabled then the permission 'manage hadr'
58        **	is required.
59        */
60    
61        select @nullarg = NULL
62        execute @status = sp_aux_checkroleperm "sa_role", "manage hadr",
63            @nullarg, @gp_enabled output
64    
65        if (@gp_enabled = 0)
66        begin
67            if (proc_role("sa_role") = 0)
68                return (1)
69        end
70        else
71        begin
72            select @dummy = proc_auditperm("manage hadr", @status)
73        end
74    
75        if (@status != 0)
76            return (1)
77    
78        if exists (select 1 from master.dbo.sysattributes where
79                    class = @HADR_CLASS and attribute = @HADR_GENERATION)
80        begin
81            select @gen_exists = 1
82            select @srv_genno = int_value from master.dbo.sysattributes where
83                class = @HADR_CLASS and attribute = @HADR_GENERATION
84        end
85    
86        /* Make sure the received generation number is greater than ours */
87        if (@genno <= @srv_genno)
88        begin
89            dbcc logprint("Rejecting the HADR propagate command as the received generation number is less than server current generation number.")
90            /* 17159, ASE encountered error during '%1!'. Refer to the ASE error log of server '%2!' for more details. */
91            raiserror 17159, "PROPAGATE", @@servername
92            return (1)
93        end
94    
95        /* Make sure the group name exists in sysservers */
96        if @groupname not in (select srvname from master.dbo.sysservers
97                where srvclass = 17)
98        begin
99            dbcc logprint("Rejecting the HADR propagate command as the received groupname is incorrect.")
100           /* 17159, ASE encountered error during '%1!'. Refer to the ASE error log of server '%2!' for more details. */
101           raiserror 17159, "PROPAGATE", @@servername
102           return (1)
103       end
104   
105       /* 
106       ** We will save the config info from the primary server in a table in
107       ** tempdb table until we receive all the config info.
108       */
109       if (@cur = 1)
110           delete from tempdb.dbo.hadr_temp_update where groupname = @groupname
111   
112       /* save the info in tempdb table */
113       insert into tempdb.dbo.hadr_temp_update values
114       (@srvname, @srvnetname, @mode, @state, @groupname, @genno)
115   
116       declare hadrcursor cursor for
117       select srvname, srvnetname, groupname
118       from tempdb.dbo.hadr_temp_update
119       where groupname = @groupname
120       /*
121       ** Apply the changes to the sysservers if we received the last row
122       */
123       if @cur = @max
124       begin
125           delete from master.dbo.sysservers where srvclass = @HADRMEMBER
126           open hadrcursor
127           fetch hadrcursor into @csrvname, @csrvnetname, @cgroupname
128           while (@@sqlstatus = 0)
129           begin
130               /* hadr_admin will append DR to the server name so remove it
131               ** the suffix before calling
132               */
133   
134               if RIGHT (@csrvname, 2) = "DR"
135                   select @csrvname = LEFT (@csrvname, LEN(@csrvname) - 2)
136   
137               exec @retstat = sp_hadr_admin addserver, @csrvname, @csrvnetname
138               if (@retstat != 0)
139               begin
140                   close hadrcursor
141                   deallocate hadrcursor
142                   return (1)
143               end
144               fetch hadrcursor into @csrvname, @csrvnetname, @cgroupname
145           end
146           close hadrcursor
147   
148           if @gen_exists = 1
149           begin
150               /* Update the generation number */
151               update master.dbo.sysattributes set int_value = @genno
152               where class = @HADR_CLASS and attribute = @HADR_GENERATION
153           end
154           else
155           begin
156               /* Insert the generation number */
157               insert into master.dbo.sysattributes
158               (class, attribute, object_type, int_value)
159               values (@HADR_CLASS, @HADR_GENERATION, '', @genno)
160           end
161   
162           /* Call hadr_admin with propagate to populate the in-memory member info */
163           dbcc hadr_admin("propagate")
164   
165           /* Cleanup the temp table */
166           delete from tempdb.dbo.hadr_temp_update where groupname = @groupname
167       end
168   
169       deallocate hadrcursor
170   
171       return (0)
172   


exec sp_procxmode 'sp_hadr_update', 'AnyMode'
go

Grant Execute on sp_hadr_update to public
go
DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..hadr_temp_update 110
 PERR 6 Parsing Error Could not find definition for table tempdb..hadr_temp_update 113
 PERR 6 Parsing Error Could not find definition for table tempdb..hadr_temp_update 118
 PERR 6 Parsing Error Could not find definition for table tempdb..hadr_temp_update 166
 MEST 4 Empty String will be replaced by Single Space 159
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch attribute: smallint = int 159
 MTYP 4 Assignment type mismatch class: smallint = int 159
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 32
 MULT 4 Using literal database 'tempdb' tempdb..hadr_temp_update 35
 MULT 4 Using literal database 'tempdb' tempdb..hadr_temp_update 110
 MULT 4 Using literal database 'tempdb' tempdb..hadr_temp_update 113
 MULT 4 Using literal database 'tempdb' tempdb..hadr_temp_update 118
 MULT 4 Using literal database 'tempdb' tempdb..hadr_temp_update 166
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_hadr_admin: parameter # 1 137
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 79
 QTYP 4 Comparison type mismatch smallint = int 79
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 83
 QTYP 4 Comparison type mismatch smallint = int 83
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 97
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 110
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 119
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 152
 QTYP 4 Comparison type mismatch smallint = int 152
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 166
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_hadr_update  
 MGTP 3 Grant to public tempdb..sysobjects  
 MNER 3 No Error Check should check @@error after delete 110
 MNER 3 No Error Check should check @@error after insert 113
 MNER 3 No Error Check should check @@error after delete 125
 MNER 3 No Error Check should check @@error after update 151
 MNER 3 No Error Check should check @@error after insert 157
 MNER 3 No Error Check should check @@error after delete 166
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 171
 MUOT 3 Updates outside transaction 157
 MZMB 3 Zombie: use of non-existent object tempdb..hadr_temp_update 35
 MZMB 3 Zombie: use of non-existent object tempdb..hadr_temp_update 110
 MZMB 3 Zombie: use of non-existent object tempdb..hadr_temp_update 113
 MZMB 3 Zombie: use of non-existent object tempdb..hadr_temp_update 118
 MZMB 3 Zombie: use of non-existent object tempdb..hadr_temp_update 166
 QAFM 3 Var Assignment from potentially many rows 82
 QIWC 3 Insert with not all columns specified missing 11 columns out of 15 158
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
33
 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: {class, attribute}
79
 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: {class, attribute}
83
 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: {class, attribute}
152
 VNRD 3 Variable is not read @dummy 72
 MSUB 2 Subquery Marker 32
 MSUB 2 Subquery Marker 78
 MSUB 2 Subquery Marker 96
 MTR1 2 Metrics: Comments Ratio Comments: 24% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 18dec - 6exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 94 2

DATA PROPAGATION detailed
ColumnWritten To
@gennosysattributes.object   °.object_info1   °.object_info3   °.int_value   °.char_value   sysremotelogins.remoteserverid   sysservers.srvid   sysattributes.object_info1   °.object_info3   °.int_value  
°.char_value   sp_dropdevice_rset_001.device sp_checknames_rset_006.remoteserverid sp_checknames_rset_007.srvid sp_checknames_rset_008.srvid sp_dbrecovery_order_rset_001.Database Name °.Database id °.Recovery Order sp_dbrecovery_order_rset_002.Recovery Order sp_displayroles_rset_001.Role Name
sp_displayroles_rset_002.Role Name °.Grantee sp_displayroles_rset_003.Role Name sp_displayroles_rset_004.Role Name sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.Objid °.Object °.status sp_forceonline_object_rset_002.Objid °.Object
°.status sp_forceonline_page_rset_001.Pageid °.status sp_forceonline_page_rset_002.Pageid °.status sp_listsuspect_object_rset_001.Objid °.Object °.Access sp_listsuspect_page_rset_001.Pageid °.Object
°.Access sp_makesuspect_obj_rset_001.Obj °.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Obj °.Indid
°.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Obj °.Indid °.LogType °.PageType °.ErrType
°.Delay °.TotalNum sp_memlog_rset_001.dumps_per_file sp_passwordpolicy_rset_001.message sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name °.host_port sp_setsuspect_error_rset_001.Error Num
sp_setsuspect_error_rset_002.Error Num sp_setsuspect_granularity_rset_001.Online mode sp_setsuspect_threshold_rset_001.Cur. Suspect threshold sp_setsuspect_threshold_rset_002.Cur. Suspect Threshold sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table master..sysservers (1)  
reads table tempdb..sysobjects (1)  
calls proc sybsystemprocs..sp_hadr_admin  
   writes table tempdb..#rep_progress (1) 
   writes table sybsystemprocs..sp_hadr_admin_rset_006 
   calls proc sybsystemprocs..sp_getmessage  
      reads table master..syslanguages (1)  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      reads table sybsystemprocs..sysusermessages  
   writes table sybsystemprocs..sp_hadr_admin_rset_002 
   calls proc sybsystemprocs..sp_autoformat  
      writes table sybsystemprocs..sp_autoformat_rset_003 
      reads table tempdb..syscolumns (1)  
      read_writes table tempdb..#colinfo_af (1) 
      writes table sybsystemprocs..sp_autoformat_rset_001 
      reads table tempdb..systypes (1)  
      reads table master..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_005 
      writes table sybsystemprocs..sp_autoformat_rset_002 
      calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      writes table sybsystemprocs..sp_autoformat_rset_004 
      reads table master..syscolumns (1)  
   read_writes table tempdb..#FTETEMP (1) 
   reads table master..syscolumns (1)  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
   writes table sybsystemprocs..sp_hadr_admin_rset_010 
   reads table master..syslogins (1)  
   writes table sybsystemprocs..sp_hadr_admin_rset_009 
   writes table sybsystemprocs..sp_hadr_admin_rset_008 
   writes table sybsystemprocs..sp_hadr_admin_rset_004 
   writes table sybsystemprocs..sp_hadr_admin_rset_011 
   reads table master..systransactions (1)  
   writes table sybsystemprocs..sp_hadr_admin_rset_012 
   calls proc sybsystemprocs..sp_dropserver  
      calls proc sybsystemprocs..sp_ha_check_certified  
         reads table tempdb..sysobjects (1)  
      read_writes table sybsystemdb..syscoordinations (1)  
      reads table master..sysdatabases (1)  
      read_writes table master..sysremotelogins (1)  
      read_writes table master..sysservers (1)  
      calls proc sybsystemprocs..sp_aux_checkroleperm  
      calls proc sybsystemprocs..sp_getmessage  
      read_writes table master..sysattributes (1)  
      reads table master..spt_values (1)  
   writes table sybsystemprocs..sp_hadr_admin_rset_005 
   reads table master..sysdatabases (1)  
   writes table master..sysattributes (1)  
   writes table sybsystemprocs..sp_hadr_admin_rset_013 
   reads table master..sysengines (1)  
   writes table sybsystemprocs..sp_hadr_admin_rset_003 
   writes table sybsystemprocs..sp_hadr_admin_rset_001 
   read_writes table tempdb..#FTETEMP2 (1) 
   calls proc sybsystemprocs..sp_hadrvrfyargs  
      reads table master..sysservers (1)  
      reads table master..sysdatabases (1)  
   writes table tempdb..#rep_status (1) 
   read_writes table master..sysservers (1)  
   writes table sybsystemprocs..sp_hadr_admin_rset_007 
   writes table tempdb..#spt_hadrserver (1)