DatabaseProcApplicationCreatedLinks
sybsystemprocssp_bindmsg  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	5.0	1.1	07/31/91	sproc/src/bindmsg */
4     
5     /*
6     ** Messages for "sp_bindmsg"            17890
7     **
8     ** 17756, "The execution of the stored procedure '%1!' in database
9     **         '%2!' was aborted because there was an error in writing the
10    **         replication log record."
11    ** 17763, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there
12    **         was an error in updating the schemacnt column in sysobjects."
13    ** 17890, "Constraint name must be in `current' database."
14    ** 17891, "Constraint name must belong to the current user."
15    ** 17892, "Message id must be a user defined message."
16    ** 17893, "No such message exists. Please create the message first using sp_addmessage."
17    ** 17894, "No such referential or check constraint exists. Please check whether
18    ** the constraint name is correct."
19    ** 17895, "Constraint is bound to a message. You must unbind the message first."
20    ** 17896, "Binding message failed unexpectedly. Please try again."
21    ** 17897, "Message bound to constraint."
22    ** 18293, "Auditing for '%1!' event has failed due to internal error. Contact a user with System Security Officer (SSO) role."
23    */
24    
25    create or replace procedure sp_bindmsg
26        @constrname varchar(767), /* name of the constraint */
27        @msgid int /* message id */
28    as
29    
30        declare @msg varchar(1024) /* for printing messages */
31        declare @returncode int /* return from ad_hoc_audit builtin */
32        declare @eventnum int /* event number for bind message auditing */
33        declare @mod_ok int /* successful bind message auditing  */
34        declare @mod_fail int /* failure bind message auditing  */
35    
36        declare @objid int
37    
38        select @eventnum = 7 /* evetn number for bindmsg */
39        select @mod_ok = 1
40        select @mod_fail = 2
41        declare @dbname varchar(30)
42    
43    
44        if @@trancount = 0
45        begin
46            set chained off
47        end
48    
49        set transaction isolation level 1
50    
51        set nocount on
52    
53    
54        /* check to see that the object name  is local to the current database */
55    
56        if (@constrname like "%.%.%")
57        begin
58            /*
59            ** 17890, "Constraint name must be in `current' database."
60            */
61            raiserror 17890
62            return (1)
63        end
64    
65        /* check to see that the object name belongs to the current user */
66    
67        if (@constrname like "%.%")
68        begin
69            /*
70            ** 17891, "Constraint name must belong to the current user."
71            */
72            raiserror 17891
73            return (1)
74        end
75    
76        /* check if the message number is illegal */
77    
78        if (@msgid < 20000)
79        begin
80            /*
81            ** 17892,  "Message id must be a user defined message."
82            */
83            raiserror 17892
84            return (1)
85        end
86    
87        /* check if the message number is nonexistent */
88    
89        if not exists (select * from sysusermessages where error = @msgid)
90        begin
91            /*
92            ** 17893, "No such message exists. Please create the message first using sp_addmessage."
93            */
94            raiserror 17893
95            return (1)
96        end
97    
98    
99        /* check to see that the constraint exists */
100   
101       if not exists (select id
102               from sysconstraints c, sysobjects o
103               where c.constrid = object_id(@constrname)
104                   and o.id = object_id(@constrname)
105                   and o.uid = user_id()
106                   and (o.sysstat & 15 = 7 /* check constraint */
107                       or sysstat & 15 = 9) /* referential constraint */)
108       begin
109           /*
110           ** 17894, "No such referential or check constraint exists. Please check whether the constraint name is correct."
111           */
112   
113   
114           /* Audit the failure to bind a message */
115           select @returncode =
116               ad_hoc_audit(@eventnum, @mod_fail, convert(varchar(30), @msgid),
117                   db_name(), @constrname, user_name(), 0,
118                   object_id(@constrname)
119   
120               )
121           raiserror 17894
122           return (1)
123       end
124   
125   
126       /* Audit the successful permission  to bind a message */
127       select @returncode =
128           ad_hoc_audit(@eventnum, @mod_ok, convert(varchar(30), @msgid), db_name(),
129               @constrname, user_name(), 0, object_id(@constrname)
130   
131           )
132       if (@returncode != 0)
133       begin
134           /* 
135           ** 18293, "Auditing for '%1!' event has failed due to 
136           ** internal error. Contact a user with System Security 
137           ** Officer (SSO) role."
138           */
139           raiserror 18293, @eventnum
140           return (1)
141       end
142       /*
143       **
144       ** NOTE: If the following section of code is ever "uncommented", change
145       ** the @constrid variable in the SQL Statement to object_id(@constrname).
146       **
147       ** check if the constraint is already bound to a message
148       **
149       ** if (exists (select *
150       **		from sysconstraints
151       **		where constrid = @constrid
152       **			and error != 0))
153       ** begin
154       **	**
155       ** 	** 17895, "Constraint is bound to a message. You must unbind the message first."
156       **	**
157       **	raiserror 17895	
158       **	return (1)
159       ** end
160       */
161   
162       /* update sysconstraints table */
163   
164       update sysconstraints
165       set error = @msgid
166       from sysconstraints, sysobjects
167       where sysconstraints.constrid = object_id(@constrname)
168           and sysobjects.id = object_id(@constrname)
169           and sysobjects.uid = user_id()
170           and (sysstat & 15 = 7 /* check constraint */
171               or sysstat & 15 = 9) /* referential constraint */
172   
173       /* check if the update took place */
174   
175       if @@rowcount != 1
176       begin
177           /*
178           ** 17896, "Binding message failed unexpectedly. Please try again."
179           */
180           raiserror 17896
181           return (1)
182       end
183   
184       /*
185       ** Since binding a message is a schema change, update schema count
186       ** for the object in the sysobjects table.
187       */
188   
189       /* 
190       ** This transaction also writes a log record for replicating the
191       ** invocation of this procedure. If logexec() fails, the transaction
192       ** is aborted.
193       **
194       ** IMPORTANT: The name rs_logexec is significant and is used by
195       ** Replication Server.
196       */
197       begin transaction rs_logexec
198   
199       select @objid = sysobjects.id
200       from sysobjects, sysconstraints
201       where sysconstraints.constrid = object_id(@constrname)
202           and sysobjects.id = sysconstraints.tableid
203           and uid = user_id()
204           and (sysstat & 15 = 7 /* check constraint */
205               or sysstat & 15 = 9) /* referential constraint */
206   
207       if (@@rowcount > 0)
208       begin
209   
210           if (schema_inc(@objid, 0) != 1)
211           begin
212               /*
213               ** 17763, "The execution of the stored procedure '%1!'
214               **         in database '%2!' was aborted because there
215               **         was an error in updating the column
216               **         schemacnt in sysobjects."
217               */
218               select @dbname = db_name()
219               raiserror 17763, "sp_bindmsg", @dbname
220               rollback transaction rs_logexec
221               return (1)
222           end
223       end
224   
225       /*
226       ** Write the log record to replicate this invocation 
227       ** of the stored procedure.
228       */
229       if (logexec() != 1)
230       begin
231           /*
232           ** 17756, "The execution of the stored procedure '%1!'
233           ** 	   in database '%2!' was aborted because there
234           ** 	   was an error in writing the replication log
235           **	   record."
236           */
237           select @dbname = db_name()
238           raiserror 17756, "sp_bindmsg", @dbname
239   
240           rollback transaction rs_logexec
241           return (1)
242       end
243   
244       commit transaction
245   
246       /*
247       ** 17897, "Message bound to constraint."
248       */
249       exec sp_getmessage 17897, @msg output
250       print @msg
251   
252       return (0)
253   


exec sp_procxmode 'sp_bindmsg', 'AnyMode'
go

Grant Execute on sp_bindmsg to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysusermessages sybsystemprocs..sysusermessages
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysconstraints c and [sybsystemprocs..sysobjects o], 5 tables with rc... 101
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysconstraints and [sybsystemprocs..sysobjects], 5 tables with rc=1 164
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
203
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 101
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 104
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 168
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 199
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 202
 MGTP 3 Grant to public sybsystemprocs..sp_bindmsg  
 MGTP 3 Grant to public sybsystemprocs..sysconstraints  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysusermessages  
 MNER 3 No Error Check should check @@error after update 164
 MNER 3 No Error Check should check return value of exec 249
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 252
 QISO 3 Set isolation level 49
 QNAJ 3 Not using ANSI Inner Join 102
 QNAJ 3 Not using ANSI Inner Join 166
 QNAJ 3 Not using ANSI Inner Join 200
 QNUA 3 Should use Alias: Column id should use alias o 101
 QNUA 3 Should use Alias: Column sysstat should use alias o 107
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 166
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 170
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 171
 QNUA 3 Should use Alias: Table sybsystemprocs..sysconstraints 200
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 200
 QNUA 3 Should use Alias: Column uid should use alias sysobjects 203
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 204
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 205
 MSUB 2 Subquery Marker 89
 MSUB 2 Subquery Marker 101
 MTR1 2 Metrics: Comments Ratio Comments: 59% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 20dec - 10exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 91 25
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..sysconstraints, o=sybsystemprocs..sysobjects} 0 199

DATA PROPAGATION detailed
ColumnWritten To
@msgidsysconstraints.error  

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table sybsystemprocs..sysusermessages  
read_writes table sybsystemprocs..sysconstraints