DatabaseProcApplicationCreatedLinks
sybsystemprocssp_droplanguage  31 Aug 14Defects 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 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    
69    
70        select @HA_CERTIFIED = 0
71    
72    
73    
74    
75        /* check to see if we are using HA specific SP for a HA enabled server */
76        exec @retstat = sp_ha_check_certified 'sp_droplanguage', @HA_CERTIFIED
77        if (@retstat != 0)
78            return (1)
79    
80        /*
81        **  If we're in a transaction, disallow this since it might make recovery
82        **  impossible.
83        */
84        if @@trancount > 0
85        begin
86            /* 17260, "Can't run %1! from within a transaction." */
87            raiserror 17260, "sp_locklogin"
88            return (1)
89        end
90        else
91        begin
92            set chained off
93        end
94    
95        set transaction isolation level 1
96    
97        /* check if user has sa role, proc_role will also do auditing
98        ** if required. proc_role will also print error message if required.
99        */
100   
101       if (proc_role("sa_role") = 0)
102           return (1)
103   
104       /*  Check to see if the language exists. */
105       declare @returncode int
106       select @returncode = 0
107       execute @returncode = sp_validlang @language
108       if @returncode != 0
109       begin
110           /*
111           ** 17201, "'%1!' is not an official language name from Syslanguages." 
112           */
113           raiserror 17201, @language
114           return 1
115       end
116   
117       /* Get language id from syslanguages. */
118       select @langid = (select langid
119               from master.dbo.syslanguages
120               where name = @language)
121   
122       /*  Check to see if "dropmessages" is requested. */
123       select @dropmsgs = 0
124       if @dropmessages is null
125       begin
126           if exists (select * from master.dbo.sysmessages
127                   where langid = @langid)
128           begin
129               /* 
130               **  Cannot drop a language if the language has associated
131               **  entries in the master.dbo.sysmessages.
132               */
133   
134               /*
135               ** 17505, "Can't drop '%1!' because there are associated entries in master.dbo.sysmessages.  Run sp_droplanguage with the 'dropmessages' flag."
136               */
137               raiserror 17505, @language
138               return 1
139           end
140       end
141       else
142       begin
143           if @dropmessages != "dropmessages"
144           begin
145               /*
146               ** 17506, "The only legal value for @dropmessages is 'dropmessages'."
147               */
148               raiserror 17506
149               return 1
150           end
151   
152           select @dropmsgs = 1
153       end
154   
155   
156   
157       /* 
158       **  Drop the language and delete messages from Sysmessages if there is any.
159       */
160       if @dropmsgs = 1
161       begin
162           delete master.dbo.sysmessages
163           where langid = @langid
164   
165   
166   
167       end
168   
169       delete master.dbo.syslanguages
170       where langid = @langid
171   
172   
173   
174       /*
175       ** 17507, "Language deleted."
176       */
177       exec sp_getmessage 17507, @msg output
178       print @msg
179       return (0)
180   
181   clean_all:
182   
183   
184       return (1)
185   


exec sp_procxmode 'sp_droplanguage', 'AnyMode'
go

Grant Execute on sp_droplanguage to public
go
DEFECTS
 MURC 6 Unreachable Code 181
 MURC 6 Unreachable Code 184
 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}
127
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {langid}
163
 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 162
 MNER 3 No Error Check should check @@error after delete 169
 MNER 3 No Error Check should check return value of exec 177
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 184
 MUOT 3 Updates outside transaction 169
 QISO 3 Set isolation level 95
 MSUB 2 Subquery Marker 118
 MSUB 2 Subquery Marker 126
 MTR1 2 Metrics: Comments Ratio Comments: 65% 58
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 8dec - 5exi + 2 58
 MTR3 2 Metrics: Query Complexity Complexity: 57 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_ha_check_certified  
   reads table tempdb..sysobjects (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang