DatabaseProcApplicationCreatedLinks
sybsystemprocssp_procxmode  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** 17756, "The execution of the stored procedure '%1!' in database
6     **         '%2!' was aborted because there was an error in writing the
7     **         replication log record."
8     */
9     
10    create or replace procedure sp_procxmode
11        @procname varchar(767) = null,
12        @execmode varchar(30) = null
13    as
14    
15        declare @uid int
16        declare @oid int
17        declare @msg varchar(250) /* message text */
18        declare @dbname varchar(255)
19        declare @owner varchar(30)
20        declare @objname varchar(257)
21        declare @objid int
22        declare @alltranmask int
23        declare @retcode int
24        declare @cmd varchar(255)
25    
26        if @@trancount = 0
27        begin
28            set chained off
29        end
30    
31        set transaction isolation level 1
32    
33        /* 
34        ** If either parameter is null we will be joining with a temporary table
35        ** to convert transaction mode numbers (0, 16, 32, 256) to strings 
36        ** ("Unchained", "Chained", "Any Mode", "Dynamic Ownership Chain").
37        ** The "Dynamic Ownership Chain" is not an exclusive mode. It exists 
38        ** in conjunction with one of the other three.
39        */
40        if ((@procname is null) or (@execmode is null))
41        begin
42            create table #execmode(intval integer, charval varchar(30))
43            insert into #execmode values (0, 'Unchained')
44            insert into #execmode values (16, 'Chained')
45            insert into #execmode values (32, 'Any Mode')
46            insert into #execmode values (256, 'Dynamic Ownership Chain')
47    
48            select @alltranmask = 16 + 32
49        end
50    
51        /* 
52        ** If the first parameter is null, we're to report the transaction-modes
53        ** of every stored procedure in the current database.
54        */
55        if (@procname is null)
56        begin
57            select 'procedure name' = o.name, 'user name' = user_name(o.uid),
58                'transaction mode' = t.charval
59            from sysobjects o, #execmode t
60            where ((o.type = 'P') or (o.type = 'XP')) and
61                (((o.sysstat2 & t.intval) != 0) or
62                    ((t.intval = 0) and (o.sysstat2 & @alltranmask) = 0))
63            order by o.name
64    
65            return (0)
66        end
67    
68        /* 
69        ** If only the second parameter is null, we will report the
70        ** transaction-mode of the specified procedure.
71        */
72        if ((@procname is not null) and (@execmode is null))
73        begin
74            /* 
75            ** Check if proc name is prefixed with the dbname. The proc sp_namecrack
76            ** cannot be used, because sp_procxmode is created before and it
77            ** would cause a dependency failure at creation time.
78            */
79            if @procname like "%.%.%.%"
80            begin
81                select @procname = substring(@procname, charindex(".", @procname) + 1, 125)
82            end
83    
84            if @procname like "%.%.%"
85            begin
86                select @dbname = substring(@procname, 1, charindex(".", @procname) - 1)
87                select @procname = substring(@procname, charindex(".", @procname) + 1, 125)
88            end
89    
90            if @procname like "%.%"
91            begin
92                select @owner = substring(@procname, 1, charindex(".", @procname) - 1)
93                select @procname = substring(@procname, charindex(".", @procname) + 1, 125)
94            end
95    
96            select @objname = @procname
97    
98            /*
99            ** If a database qualifies, it has to be this database.
100           ** Execute the procedure in the target database.
101           */
102           if ((@dbname is not null) and (@dbname != db_name()))
103           begin
104               select @cmd = @dbname + '.dbo.sp_procxmode'
105               select @procname = @owner + '.' + @objname
106               exec @retcode = @cmd @procname
107               return (@retcode)
108           end
109   
110           select @objid = object_id(@procname)
111           if (@objid is not null)
112           begin
113               if not exists (select 1 from sysobjects
114                       where id = @objid
115                           and type in ('P', 'XP'))
116               begin
117                   select @objid = null
118               end
119           end
120   
121           if (@objid is null)
122           begin
123               /*
124               ** Force an error message, since we haven't
125               ** installed sp_getmessage yet.
126               */
127               dbcc update_tmode(@procname, 'anymode')
128               return (1)
129           end
130   
131   
132           select 'procedure name' = o.name, 'user name' = user_name(o.uid),
133               'transaction mode' = t.charval
134           from sysobjects o, #execmode t
135           where (@objid = o.id) and
136               ((o.type = 'P') or (o.type = 'XP')) and
137               (((o.sysstat2 & t.intval) != 0) or
138                   ((t.intval = 0) and (o.sysstat2 & @alltranmask) = 0))
139   
140           return (0)
141       end
142   
143       /* 
144       ** If neither parameter is null, we're to set the transaction-mode
145       ** of the specified procedure to the specified value.
146       */
147       if ((@procname is not null) and (@execmode is not null))
148       begin
149           /* Start the transaction to log the execution of this procedure.
150           **
151           ** IMPORTANT: The name "rs_logexec is significant and is used by 
152           **            Replication Server
153           */
154           begin transaction rs_logexec
155   
156           /*
157           ** Update transaction-mode in both sysobjects and DES.
158           */
159           dbcc update_tmode(@procname, @execmode)
160   
161           /* If dbcc update_tmode returned an error, return
162           ** an error now.
163           */
164           if (@@error != 0)
165           begin
166               rollback transaction
167               return (1)
168           end
169   
170           /*
171           ** Write the log record to replicate this invocation 
172           ** of the stored procedure.
173           */
174           if (logexec() != 1)
175           begin
176               /*
177               ** 17756, "The execution of the stored procedure '%1!'
178               ** 	   in database '%2!' was aborted because there
179               ** 	   was an error in writing the replication log
180               **	   record."
181               */
182               select @dbname = db_name()
183               raiserror 17756, 'sp_procxmode', @dbname
184               rollback transaction
185               return (1)
186           end
187   
188           commit transaction
189   
190       end
191   


exec sp_procxmode 'sp_procxmode', 'AnyMode'
go

Grant Execute on sp_procxmode to public
go
RESULT SETS
sp_procxmode_rset_002
sp_procxmode_rset_001

DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 154
 MCTR 4 Conditional Begin Tran or Commit Tran 188
 MTYP 4 Assignment type mismatch @objname: varchar(257) = varchar(767) 96
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 114
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 135
 MGTP 3 Grant to public sybsystemprocs..sp_procxmode  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after insert 43
 MNER 3 No Error Check should check @@error after insert 44
 MNER 3 No Error Check should check @@error after insert 45
 MNER 3 No Error Check should check @@error after insert 46
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 185
 MUIN 3 Column created using implicit nullability 42
 QCRS 3 Conditional Result Set 57
 QCRS 3 Conditional Result Set 132
 QCTC 3 Conditional Table Creation 42
 QISO 3 Set isolation level 31
 QJWT 3 Join or Sarg Without Index on temp table 61
 QJWT 3 Join or Sarg Without Index on temp table 137
 QNAJ 3 Not using ANSI Inner Join 59
 QNAJ 3 Not using ANSI Inner Join 134
 VUNU 3 Variable is not used @uid 15
 VUNU 3 Variable is not used @oid 16
 VUNU 3 Variable is not used @msg 17
 MDYE 2 Dynamic Exec Marker exec @retcode 106
 MRST 2 Result Set Marker 57
 MRST 2 Result Set Marker 132
 MSUB 2 Subquery Marker 113
 MTR1 2 Metrics: Comments Ratio Comments: 37% 10
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 20dec - 7exi + 2 10
 MTR3 2 Metrics: Query Complexity Complexity: 101 10

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#execmode (1) 
reads table sybsystemprocs..sysobjects  
writes table sybsystemprocs..sp_procxmode_rset_001 
writes table sybsystemprocs..sp_procxmode_rset_002