DatabaseProcApplicationCreatedLinks
sybsystemprocssp_droplanguage  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/defaultlanguage */
4     
5     /*
6     ** Messages for "sp_droplanguage"       17505
7     **
8     ** 17201, "'%1!' is not an official language name from Syslanguages." 
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17505, "Can't drop '%1!' because there are associated entries in master.dbo.sysmessages.  Run sp_droplanguage with the 'dropmessages' flag."
11    ** 17506, "The only legal value for @dropmessages is 'dropmessages'."
12    ** 17507, "Language deleted."
13    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '% 1!' on the companion server '%2!'"
14    ** 18775, "Unable to find a language entry with language name '%1!' and language id '%2!'"
15    */
16    
17    /* 
18    ** IMPORTANT: Please read the following instructions before
19    **   making changes to this stored procedure.
20    **
21    **	To make this stored procedure compatible with High Availability (HA),
22    **	changes to certain system tables must be propagated 
23    **	to the companion server under some conditions.
24    **	The tables include (but are not limited to):
25    **		syslogins, sysservers, sysattributes, systimeranges,
26    **		sysresourcelimits, sysalternates, sysdatabases,
27    **		syslanguages, sysremotelogins, sysloginroles,
28    **		sysalternates (master DB only), systypes (master DB only),
29    **		sysusers (master DB only), sysprotects (master DB only)
30    **	please refer to the HA documentation for detail.
31    **
32    **	Here is what you need to do: 
33    **	For each insert/update/delete statement, add three sections to
34    **	-- start HA transaction prior to the statement
35    **	-- add the statement
36    **	-- add HA synchronization code to propagate the change to the companion
37    **
38    **	For example, if you are adding 
39    **		insert master.dbo.syslogins ......
40    **	the code should look like:
41    **	1. Before that SQL statement:
42    **		
43    **	2. Now, the SQL statement:
44    **		insert master.dbo.syslogins ......
45    **	3. Add a HA synchronization section right after the SQL statement:
46    **		
47    **
48    **	You may need to do similar change for each built-in function you
49    **	want to add.
50    **
51    **	Finally, add a separate part at a place where it can not
52    **	be reached by the normal execution path:
53    **	clean_all:
54    **		
55    **		return (1)
56    */
57    
58    create or replace procedure sp_droplanguage
59        @language varchar(30),
60        @dropmessages varchar(30) = NULL
61    as
62    
63        declare @msg varchar(1024)
64        declare @langid smallint
65        declare @dropmsgs int
66        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
67        declare @retstat int
68        declare @nullarg char(1)
69        declare @dummy int
70        declare @gp_enabled int
71    
72    
73    
74        select @HA_CERTIFIED = 0
75    
76    
77    
78    
79    
80        /* check to see if we are using HA specific SP for a HA enabled server */
81        exec @retstat = sp_ha_check_certified 'sp_droplanguage', @HA_CERTIFIED
82        if (@retstat != 0)
83            return (1)
84    
85        /*
86        **  If we're in a transaction, disallow this since it might make recovery
87        **  impossible.
88        */
89        if @@trancount > 0
90        begin
91            /* 17260, "Can't run %1! from within a transaction." */
92            raiserror 17260, "sp_locklogin"
93            return (1)
94        end
95        else
96        begin
97            set chained off
98        end
99    
100       set transaction isolation level 1
101   
102       /* 
103       ** check if user has sa role, proc_role will also do auditing
104       ** if required. proc_role will also print error message if required.
105       */
106       select @nullarg = NULL
107       execute @retstat = sp_aux_checkroleperm "sa_role",
108           "manage server", @nullarg, @gp_enabled output
109   
110       /*
111       ** Send apropriate audit records.
112       */
113       if (@gp_enabled = 0)
114       begin
115           select @dummy = proc_role("sa_role")
116       end
117       else
118       begin
119           select @dummy = proc_auditperm("manage server", @retstat)
120       end
121   
122       if (@retstat != 0)
123           return 1
124   
125       /*  Check to see if the language exists. */
126       declare @returncode int
127       select @returncode = 0
128       execute @returncode = sp_validlang @language
129       if @returncode != 0
130       begin
131           /*
132           ** 17201, "'%1!' is not an official language name from Syslanguages." 
133           */
134           raiserror 17201, @language
135           return 1
136       end
137   
138       /* Get language id from syslanguages. */
139       select @langid = (select langid
140               from master.dbo.syslanguages
141               where name = @language)
142   
143       /*  Check to see if "dropmessages" is requested. */
144       select @dropmsgs = 0
145       if @dropmessages is null
146       begin
147           if exists (select * from master.dbo.sysmessages
148                   where langid = @langid)
149           begin
150               /* 
151               **  Cannot drop a language if the language has associated
152               **  entries in the master.dbo.sysmessages.
153               */
154   
155               /*
156               ** 17505, "Can't drop '%1!' because there are associated entries in master.dbo.sysmessages.  Run sp_droplanguage with the 'dropmessages' flag."
157               */
158               raiserror 17505, @language
159               return 1
160           end
161       end
162       else
163       begin
164           if @dropmessages != "dropmessages"
165           begin
166               /*
167               ** 17506, "The only legal value for @dropmessages is 'dropmessages'."
168               */
169               raiserror 17506
170               return 1
171           end
172   
173           select @dropmsgs = 1
174       end
175   
176   
177   
178       /* 
179       **  Drop the language and delete messages from Sysmessages if there is any.
180       */
181       if @dropmsgs = 1
182       begin
183           delete master.dbo.sysmessages
184           where langid = @langid
185   
186   
187   
188       end
189   
190       delete master.dbo.syslanguages
191       where langid = @langid
192   
193   
194   
195       /*
196       ** 17507, "Language deleted."
197       */
198       exec sp_getmessage 17507, @msg output
199       print @msg
200       return (0)
201   
202   clean_all:
203   
204   
205       return (1)
206   


exec sp_procxmode 'sp_droplanguage', 'AnyMode'
go

Grant Execute on sp_droplanguage to public
go
DEFECTS
 MURC 6 Unreachable Code 202
 MURC 6 Unreachable Code 205
 MINU 4 Unique Index with nullable columns master..syslanguages master..syslanguages
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {langid}
148
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {langid}
184
 MGTP 3 Grant to public master..syslanguages  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_droplanguage  
 MNER 3 No Error Check should check @@error after delete 183
 MNER 3 No Error Check should check @@error after delete 190
 MNER 3 No Error Check should check return value of exec 198
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 205
 MUOT 3 Updates outside transaction 190
 QISO 3 Set isolation level 100
 VNRD 3 Variable is not read @dummy 119
 MSUB 2 Subquery Marker 139
 MSUB 2 Subquery Marker 147
 MTR1 2 Metrics: Comments Ratio Comments: 63% 58
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 10dec - 5exi + 2 58
 MTR3 2 Metrics: Query Complexity Complexity: 66 58

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysmessages (1)  
read_writes table master..syslanguages (1)  
calls proc sybsystemprocs..sp_validlang  
   reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)