DatabaseProcApplicationCreatedLinks
sybsystemprocssp_unbindmsg  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/unbindmsg */
4     
5     /*
6     ** Messages for "sp_unbindmsg"          17940
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    ** 17894, "No such referential or check constraint exists. Please check whether
16    ** the constraint name is correct."
17    ** 17940, "Constraint is not bound to any message."
18    ** 17941, "Unbinding message failed unexpectedly. Please try again."
19    ** 17942, "Message unbound from constraint."
20    ** 18293, "Auditing for '%1!' event has failed due to internal error. Contact a user with System Security Officer (SSO) role."
21    */
22    
23    create or replace procedure sp_unbindmsg
24    
25        @constrname varchar(767) /* name of the constraint */
26    as
27    
28        declare @msg varchar(1024) /* message text */
29        declare @returncode int /* return from ad_hoc_audit builtin */
30        declare @eventnum int /* event number for bind default auditing */
31        declare @mod_ok int /* successful bind default auditing  */
32        declare @mod_fail int /* failure bind default auditing  */
33    
34    
35        select @eventnum = 69 /* unbindmsg event number */
36        select @mod_ok = 1
37        select @mod_fail = 2
38        declare @dbname varchar(255)
39        declare @objid int
40    
41    
42    
43        if @@trancount = 0
44        begin
45            set chained off
46        end
47    
48        set transaction isolation level 1
49    
50        set transaction isolation level 1
51    
52        set nocount on
53    
54        /* check to see that the object names are local to the current database */
55    
56        if (@constrname like "%.%.%")
57        begin
58            /* 17890, "Constraint name must be in `current' database." */
59            raiserror 17890
60            return (1)
61        end
62    
63        /* check to see that the object name belongs to the current user */
64    
65        if (@constrname like "%.%")
66        begin
67            /* 17891, "Constraint name must belong to the current user." */
68            raiserror 17891
69            return (1)
70        end
71    
72    
73        /* check to see that the constraint exists */
74    
75        if not exists (select id
76                from sysconstraints c, sysobjects o
77                where c.constrid = object_id(@constrname)
78                    and o.id = object_id(@constrname)
79                    and o.uid = user_id()
80                    and (sysstat & 15 = 7 /* check constraint */
81                        or sysstat & 15 = 9) /* referential constraint */)
82        begin
83            /*
84            ** "No such referential or check constraint exists. Please check whether the constraint name is correct."
85            */
86    
87            /* Audit the failure to unbind a message */
88            select @returncode =
89                ad_hoc_audit(@eventnum, @mod_fail, NULL, db_name(),
90                    @constrname, user_name(), 0, object_id(@constrname)
91    
92                )
93            raiserror 17894
94            return (1)
95        end
96    
97    
98        /* Audit the successful permission to unbind a message */
99        select @returncode =
100           ad_hoc_audit(@eventnum, @mod_ok, NULL, db_name(), @constrname,
101               user_name(), 0, object_id(@constrname)
102   
103           )
104   
105       if (@returncode != 0)
106       begin
107           /* 
108           ** 18293, "Auditing for '%1!' event has failed due to 
109           ** internal error. Contact a user with System Security 
110           ** Officer (SSO) role."
111           */
112           raiserror 18293, @eventnum
113           return (1)
114       end
115   
116       /* check if the constraint is bound to a message */
117   
118       if exists (select c.error
119               from sysconstraints c, sysobjects
120               where c.constrid = object_id(@constrname)
121                   and sysobjects.id = object_id(@constrname)
122                   and sysobjects.uid = user_id()
123                   and (sysstat & 15 = 7 /* check constraint */
124                       or sysstat & 15 = 9) /* referential constraint */
125                   and c.error = 0)
126       begin
127           /*
128           ** 17940, "Constraint is not bound to any message."
129           */
130           raiserror 17940
131           return (1)
132       end
133   
134   
135       /* update sysconstraints table */
136   
137       update sysconstraints
138       set error = 0
139       from sysconstraints, sysobjects
140       where sysconstraints.constrid = object_id(@constrname)
141           and sysobjects.id = object_id(@constrname)
142           and sysobjects.uid = user_id()
143           and (sysstat & 15 = 7 /* check constraint */
144               or sysstat & 15 = 9) /* referential constraint */
145   
146       /* check if the update took place */
147   
148       if @@rowcount != 1
149       begin
150           /*
151           ** 17941, "Unbinding message failed unexpectedly. Please try again."
152           */
153           raiserror 17941
154           return (1)
155       end
156   
157       /*
158       ** Since unbinding a message is a schema change, update schema count
159       ** for the object in the sysobjects table.
160       */
161   
162       /* 
163       ** This transaction also writes a log record for replicating the
164       ** invocation of this procedure. If logexec() fails, the transaction
165       ** is aborted.
166       **
167       ** IMPORTANT: The name rs_logexec is significant and is used by
168       ** Replication Server.
169       */
170       begin transaction rs_logexec
171   
172       select @objid = sysobjects.id
173       from sysobjects, sysconstraints
174       where sysconstraints.constrid = object_id(@constrname)
175           and sysobjects.id = sysconstraints.tableid
176           and uid = user_id()
177           and (sysstat & 15 = 7 /* check constraint */
178               or sysstat & 15 = 9) /* referential constraint */
179   
180       if (@@rowcount > 0)
181       begin
182           if (schema_inc(@objid, 0) != 1)
183           begin
184               /*
185               ** 17763, "The execution of the stored procedure '%1!'
186               **         in database '%2!' was aborted because there
187               **         was an error in updating the column
188               **         schemacnt in sysobjects."
189               */
190               select @dbname = db_name()
191               raiserror 17763, "sp_undbindmsg", @dbname
192               rollback transaction rs_logexec
193               return (1)
194           end
195       end
196   
197       /*
198       ** Write the log record to replicate this invocation 
199       ** of the stored procedure.
200       */
201       if (logexec() != 1)
202       begin
203           /*
204           ** 17756, "The execution of the stored procedure '%1!'
205           ** 	   in database '%2!' was aborted because there
206           ** 	   was an error in writing the replication log
207           **	   record."
208           */
209           select @dbname = db_name()
210           raiserror 17756, "sp_unbindmsg", @dbname
211   
212           rollback transaction rs_logexec
213           return (1)
214       end
215   
216       commit transaction
217   
218       /*
219       ** 17942, "Message unbound from constraint."
220       */
221       exec sp_getmessage 17942, @msg output
222       print @msg
223   
224       return (0)
225   


exec sp_procxmode 'sp_unbindmsg', 'AnyMode'
go

Grant Execute on sp_unbindmsg to public
go
DEFECTS
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysconstraints c and [sybsystemprocs..sysobjects o], 5 tables with rc... 75
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysconstraints c and [sybsystemprocs..sysobjects], 6 tables with rc=1 118
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysconstraints and [sybsystemprocs..sysobjects], 5 tables with rc=1 137
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
176
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 75
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 78
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 121
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 141
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 172
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 175
 MGTP 3 Grant to public sybsystemprocs..sp_unbindmsg  
 MGTP 3 Grant to public sybsystemprocs..sysconstraints  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after update 137
 MNER 3 No Error Check should check return value of exec 221
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 224
 QISO 3 Set isolation level 48
 QISO 3 Set isolation level 50
 QNAJ 3 Not using ANSI Inner Join 76
 QNAJ 3 Not using ANSI Inner Join 119
 QNAJ 3 Not using ANSI Inner Join 139
 QNAJ 3 Not using ANSI Inner Join 173
 QNUA 3 Should use Alias: Column id should use alias o 75
 QNUA 3 Should use Alias: Column sysstat should use alias o 80
 QNUA 3 Should use Alias: Column sysstat should use alias o 81
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 119
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 123
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 124
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 139
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 143
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 144
 QNUA 3 Should use Alias: Table sybsystemprocs..sysconstraints 173
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 173
 QNUA 3 Should use Alias: Column uid should use alias sysobjects 176
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 177
 QNUA 3 Should use Alias: Column sysstat should use alias sysobjects 178
 MSUB 2 Subquery Marker 75
 MSUB 2 Subquery Marker 118
 MTR1 2 Metrics: Comments Ratio Comments: 52% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 24dec - 9exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 94 23
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..sysconstraints, o=sybsystemprocs..sysobjects} 0 172

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
read_writes table sybsystemprocs..sysconstraints  
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)