February 19, 2016 at 11:49 pm
Why Try Catch doesnt catch collation exceptions? How can I handle it?
BEGIN TRY
IF '1' COLLATE Arabic_CI_AI = '1' COLLATE Arabic_100_CI_AI
PRINT 1
SELECT 'ok'
END TRY
BEGIN CATCH
SELECT 'nok'
END CATCH
February 20, 2016 at 3:44 am
farax_x (2/19/2016)
Why Try Catch doesnt catch collation exceptions? How can I handle it?
BEGIN TRY
IF '1' COLLATE Arabic_CI_AI = '1' COLLATE Arabic_100_CI_AI
PRINT 1
SELECT 'ok'
END TRY
BEGIN CATCH
SELECT 'nok'
END CATCH
This statement doesn't parse and therefore it's never executed. My question is what is the purpose of this?
😎
February 20, 2016 at 5:24 am
Eirikur Eiriksson (2/20/2016)
farax_x (2/19/2016)
Why Try Catch doesnt catch collation exceptions? How can I handle it?
BEGIN TRY
IF '1' COLLATE Arabic_CI_AI = '1' COLLATE Arabic_100_CI_AI
PRINT 1
SELECT 'ok'
END TRY
BEGIN CATCH
SELECT 'nok'
END CATCH
This statement doesn't parse and therefore it's never executed. My question is what is the purpose of this?
😎
I have an sp which contains join between two database tables that have different collations. SP create logs about errors in catch section! How can I log this error ?
February 20, 2016 at 7:55 am
1) If you want to find/log differences in collations you can use various system objects to see the collation settings for different items. Just write queries to compare what is important to you and make a log table insert.
2) You MUST use explicit BEGIN/END pairs after EVERY conditional!! You are setting yourself and others up for NASTY logic failures in the future if you don't do that!
Say you have:
IF conditional
DO SOMETHING
Now someone comes along later and says "I need to do a second thing if I meet that condition. And they do this:
IF conditional
DO SOMETHING
DO SOMTHING ELSE
BROKEN LOGIC!!! DO SOMETHING ELSE ALWAYS gets executed.
You can't ever go wrong if your code always looks like this:
IF conditional
BEGIN
DO SOMETHING
END
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 20, 2016 at 7:56 am
As I understand it, this code is never even executed so your TRY/CATCH is not even considered. During the early stages of execution the QO is evaluating the possibility of comparing your data and runs into a fatal error trying to rationalize the two collations.
If you have multiple collations to deal with you'll need to start coding defensively and explicitly specify compatible collations in your queries because as you can see you have no recourse from an exception handling perspective.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 23, 2016 at 8:01 am
See: -
BEGIN TRY
DECLARE @sql NVARCHAR(MAX) = 'IF ' + CHAR(39) + '1' + CHAR(39)
+ ' COLLATE Arabic_CI_AI = ' + CHAR(39) + '1' + CHAR(39)
+ ' COLLATE Arabic_100_CI_AI
PRINT 1';
EXECUTE [sys].[sp_executesql] @sql;
SELECT 'ok';
END TRY
BEGIN CATCH
SELECT 'nok';
END CATCH;
The result is "nok". This is because now the QA actually runs the code, so catches the error.
February 23, 2016 at 9:24 am
If you have collation conflicts, then add a collation statement to the queries to resolve things. That's better than wrapping queries in this try..catch.
Here's an example: http://www.sqlservercentral.com/articles/Administration/collate_part1/875/
February 23, 2016 at 9:35 pm
Cadavre (2/23/2016)
See: -
BEGIN TRYDECLARE @sql NVARCHAR(MAX) = 'IF ' + CHAR(39) + '1' + CHAR(39)
+ ' COLLATE Arabic_CI_AI = ' + CHAR(39) + '1' + CHAR(39)
+ ' COLLATE Arabic_100_CI_AI
PRINT 1';
EXECUTE [sys].[sp_executesql] @sql;
SELECT 'ok';
END TRY
BEGIN CATCH
SELECT 'nok';
END CATCH;
The result is "nok". This is because now the QA actually runs the code, so catches the error.
That's a nifty workaround but in my opinion does not scale to the point of being a viable strategy for managing a non-trivial sized codebase that might be plagued with the type of problem detailed in the original post.
The real issue is that code in this multi-collation environment is not written to account for multiple collations and regardless of the approach will take some effort to remediate. I feel that that the effort would be better spent staying with a declarative approach and fixing the collations on all comparisons as not to take the situation from bad to worse.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply