Error Message Text in Multi Languages

Transact-SQL

Bhudev


Basically I am making ETL Project in which there is need to display Error Message in Multi Languages e.g.: US English, Japanese. User may have option to see Error Log Table in Japanese or US English, so I was working on it and found solution is below Sample Code, in which I¡¯m checking same validation twice first for US English language and second for Japanese language. I feel it will take little bit extra time. Is there any better option to achieve this Please Help¡­¡­..

SIMPLY COPY PASTE AND RUN

SET NOCOUNT ON

--> DECLARING @PhoneNumber TABLE VARIABLE HAVING PhoneNumber List

DECLARE @PhoneNumber TABLE(PhoneNumbebr NVARCHAR(7))

--> INSERTING PhoneNumber List INTO it.

--> Phone Number should be Integer Type.

INSERT INTO @PhoneNumber VALUES('2766105')

INSERT INTO @PhoneNumber VALUES('2766110')

INSERT INTO @PhoneNumber VALUES('2766112')

INSERT INTO @PhoneNumber VALUES('2710XYZ') --> Invalid Phone Number

INSERT INTO @PhoneNumber VALUES('2766116')

INSERT INTO @PhoneNumber VALUES('ABC6105') --> Invalid Phone Number

INSERT INTO @PhoneNumber VALUES('2766199')

INSERT INTO @PhoneNumber VALUES('2766120')

--> DECLARING @ErrorTable TABLE VARIABLE FOR STORING Error Messages in English and Japanese Lenguages.

DECLARE @ErrorTable TABLE(EnglishErrorText NVARCHAR(1000),JapaneseErrorText NVARCHAR(1000))

--> DECLARING @DistinationTable TABLE VARIABLE WILL STORE VALIDATED Phone List.

DECLARE @DistinationTable TABLE(PhoneNumbebr INT)

--> DECLARING THE VARIABLES TO STORE THE VALUES RETURED BY FETCH

DECLARE

@PhoneNo NVARCHAR(50),

@EnglishText NVARCHAR(1000),

@JapaneseText NVARCHAR(1000)

DECLARE MultiLanguageErrorCursor CURSOR FOR

SELECT PhoneNumbebr FROM @PhoneNumber

OPEN MultiLanguageErrorCursor

/* PERFORM THE FIRST FETCH AND STORE THE VALUES IN VARIABLES.

NOTE: THE VARIABLES ARE IN THE SAME ORDER AS THE COLUMNS

IN THE SELECT STATEMENT. */

FETCH NEXT FROM MultiLanguageErrorCursor INTO @PhoneNo

--> CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH.

WHILE @@FETCH_STATUS = 0

BEGIN

--> INITILISATION OT VARIABLES

SET @EnglishText = ''

SET @JapaneseText = ''

BEGIN TRY

--> CHANGING LANGUAGE SETTING TO us_english

SET LANGUAGE us_english

INSERT INTO @DistinationTable SELECT CONVERT(INT,@PhoneNo)

FETCH NEXT FROM MultiLanguageErrorCursor INTO @PhoneNo

END TRY

BEGIN CATCH

SET @EnglishText = ERROR_MESSAGE() --> IT WILL RETURN English ERROR MESSAGE TEXT.

--> CHANGING LANGUAGE SETTING TO Japanese TO GET ABOVE ERROR IN JAPANESE.

SET LANGUAGE Japanese

BEGIN TRY

INSERT INTO @DistinationTable SELECT CONVERT(INT,@PhoneNo)

END TRY

BEGIN CATCH

SET @JapaneseText = ERROR_MESSAGE() --> IT WILL RETURN Japanese ERROR MESSAGE TEXT.

END CATCH

/* RESETTING THE LANGUAGE TO us_english TO PREVENT FROM

ERROR NO 16958: Could not complete cursor operation because the set options have changed since the cursor was declared. */

SET LANGUAGE us_english

--> INSERTING ERROR MESSAGE TEXT INTO @ErrorTable TABLE VARIABLE.

INSERT INTO @ErrorTable VALUES(@EnglishText,@JapaneseText)

FETCH NEXT FROM MultiLanguageErrorCursor INTO @PhoneNo

CONTINUE

END CATCH;

END

CLOSE MultiLanguageErrorCursor

DEALLOCATE MultiLanguageErrorCursor

SELECT * FROM @ErrorTable

GO

Top

Re: Error Message Text in Multi Languages

Chris Howarth


I may be slightly simplifying things here, but can you not just avoid raising errors of this nature in the first place

e.g. use:

SELECT PhoneNumber

FROM @PhoneNumber

WHERE PhoneNumber NOT LIKE '%[A-Z]%'

...when you declare the cursor (this might even remove the need for you to use a cursor).

Chris






Top

Re: Error Message Text in Multi Languages

joeydj

I agree with chris.

cursor is very slow for this.

--good data

SELECT PhoneNumber

FROM @PhoneNumber

WHERE PhoneNumber NOT LIKE '%[A-Z]%'

-bad data

SELECT PhoneNumber,'english error', 'other language error'

FROM @PhoneNumber

WHERE PhoneNumber LIKE '%[A-Z]%'






Top

Re: Error Message Text in Multi Languages

Chris Howarth

Just to add that the wildcard pattern I provided was for demo purposes only and might not filter out all of the bad data in your PhoneNumber column.

Have a look at this link for assistance on creating wildcard patterns for use with LIKE and NOT LIKE:

http://msdn2.microsoft.com/en-us/library/ms179859.aspx

Chris





Top

Re: Error Message Text in Multi Languages

Bhudev

Chris Howarth

You are rite. But there is some misunderstanding. May be my language was like this...

My English is not very good, you may understand, Please do not mind.

What is there we are making ETL. In which we getting Flat Files. And From Flat File Data we are making Staging database. In which every thing we are taking in Nvarchar type. So that all Valid or Invalid data can be bulk insert into tables. Then we are testing validations as per our requirements. e.g.: which columns must have Integer Value which column has Character Length, we defined etc. And we need to trap all the Invalid Rows with indicating in which column, which row, what nature of error and how many rows was valid, how many rows was invalid, how many rows was updated, how many rows was inserted etc. And beside this we need to get Error Message Text in Two languages English and Japanese. The requirement is this. We have done all the things.

And for traping the Error Messages in Two Languages there is two ways....

  1. We can make user define Error Messages and that will be in Multi Languages. For Each Language Error Message Text Seperate Column.
  2. And other retrieve System Error Messages. I believe that if we will work with System Error Messages then Code will be simple as compair to Opetion 1 and we donot have to go Language Converter for this.

Then there was problem how to retrieve Error Messages in English as well as in Japanese, so on that I worked and get success. But I believe that is not good way, there must be some hidden option in CLR or some other way by which we can get both Error Messages in one hit. So that I should not check same error twice with Setting different languages.

Bhudev


Top