Internationalization issue with the implementations of IErrorLookup::GetErrorDescription in SQLOLEDB and MSDASQL OLE/DB Providers

One customer reported that he was running SQL Server 2000 Enterprise Edition build 8.00.2187. His System's default language setting was Russian (but it happens that it could be reproduced with any locale different than US English.) He was running one DTS package that included an "Execute SQL" task whose execution caused SQL Server to return an error. When the package was run under one Windows account whose Regional and Language Options - Location was Russian, he got full description of the error returned by SQL Server. But when he ran the DTS package under an account with Locaton English (United States) he only got the error number without any description. This was the case for all his packages and all the possible errors he had hit on several servers they had. This happened whether the DTS packages were being run from Enterprise Manager or from a SQL Server Agent's job.

This was the appearance of the DTS package execution output when it didn't include the description for the error:

The execution of the following DTS Package succeeded:

Package Name: test
Package Description: (null)
Package ID: {93F9896C-5904-47CE-9A74-65E54E54DA65}
Package Version: {B191384F-8F3E-4CBC-A9E2-7735CE8CE774}
Package Execution Lineage: {EF33DBFE-32E3-419F-8000-34C8003176B0}
Executed On: HODJ
Executed By: dbas
Execution Started: 6/19/2006 3:43:46 PM
Execution Completed: 6/19/2006 3:43:46 PM
Total Execution Time: 0.016 seconds

Package Steps execution information:

Step 'DTSStep_DTSExecuteSQLTask_1' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:The statement has been terminated. ( (80040e2f): )
Step Error code: 80040E2F
Step Error Help File:
Step Error Help Context ID:0

Step Execution Started: 6/19/2006 3:43:46 PM
Step Execution Completed: 6/19/2006 3:43:46 PM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0

And this is how it looked when it showed the description:

The execution of the following DTS Package succeeded:

Package Name: test
Package Description: (null)
Package ID: {93F9896C-5904-47CE-9A74-65E54E54DA65}
Package Version: {B191384F-8F3E-4CBC-A9E2-7735CE8CE774}
Package Execution Lineage: {C15162C2-E65C-4ADF-82FC-4BAD50C80FCA}
Executed On: HODJ
Executed By: dbas
Execution Started: 19.06.2006 15:44:19
Execution Completed: 19.06.2006 15:44:19
Total Execution Time: 0,031 seconds

Package Steps execution information:

Step 'DTSStep_DTSExecuteSQLTask_1' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:The statement has been terminated. (Microsoft OLE DB Provider for SQL Server (80040e2f): Violation of PRIMARY KEY constraint 'PK_aaa'. Cannot insert duplicate key in object 'test_table'.)
Step Error code: 80040E2F
Step Error Help File:
Step Error Help Context ID:0

Step Execution Started: 19.06.2006 15:44:19
Step Execution Completed: 19.06.2006 15:44:19
Total Step Execution Time: 0,015 seconds
Progress count in Step: 0

After some debugging and analysis I found that Kagera's (msdasql == OLE DB Provider for ODBC) implementation of IErrorLookup::GetErrorDescription does the following locale check:

if( lcid != GetUserDefaultLCID() )

  return DB_E_NOLOCALE;

Meaning that this provider only supports the locale id passed as a parameter when it matches the one specified as the default locale for the user.

On the other hand, Luxor's (sqloledb == OLE DB Provider for SQL Server) implementation of the same method does this:

if (lcid != LOCALE_ENGLISH_US && lcid != GetUserDefaultLCID() )

  return DB_E_NOLOCALE;

Meaning that it only supports returning the error description when the locale id passed as the fourth parameter is either equal to 0x409 (en-us) or matches the default user locale.

When DTSRun.exe tries to obtain the description for any errors occurred, it invokes IErrorLookup::GetErrorDescription with the value returned by GetSystemDefaultLCID as the fourth parameter. With all this information in mind the possible results vary, depending on which provider you use and what the current System and User default settings are.

The following matrix shows when you get the correct error description (marked as "Provided" in the rightmost column) and when you only get the error number without any description (marked as "Missing"):

In order to get a consistent behavior, DTSRun should pass to GetErrorDescription the locale returned by GetUserDefaultLCID, but unfortunately it doesn't, and implementing a fix in that area at this point in the life cycle of the product might not be worth the risk for regressions.

So, four possible workarounds would be:

1. In order to make sure both OLEDB providers work consistently, returning always the description for all the errors, System and User default locales should be set to matching values.
2. Set the System default locale to English (United States). By doing this, you will also obtain the descriptions for all errors. But this is only true if you're using SQLOLEDB.
3. Manually searching SQL Server's documentation for the error codes returned, in order to find their corresponding descriptions would be a third option.
4. And finally, you could SELECT from master.dbo.sysmessages to find the description of any error given its number.

Once again, thank you for your visit!