Select the product you need help with

BUG: Changed Locale Settings in Extended Stored Procedure May Cause Incorrect Results

Article ID: 246199 - View products that this article applies to.

This article was previously published under Q246199

BUG #: 18859 (SQLBUG_65)
BUG #: 56451 (SQLBUG_70)
BUG #: 213085 (SHILOH)

SYMPTOMS

Any code that changes the locale settings and that runs in an extended stored procedure, may cause SQL Server to interpret numbers incorrectly. The symptoms may vary widely depending on the exact circumstances but do include the following:

Incorrectly receiving the following 8114 error message:
Error converting data type varchar to float.
Incorrectly receiving the following 8134 (also in system procedures such as sp_MSsubscriber_status) error message:
Divide by zero error encountered.
Incorrect results for calculations using literals.
Incorrect conversion of strings to numbers, which should trigger an 8114 error message. For example, "10,32" is converted to 10.32.

This problem may occur if both of the following conditions are true:

SQL Server calls an extended stored procedure that changes the locale settings. For example, using xp_startmail with the Outlook Address Book.

-and-
SQL Server uses the conversion routines of the Msvcrt.dll file.

NOTE: For SQL Server 6.5, this means SQL Server uses the Msvcrt40.dll file version 4.20.6172 (this file is installed by Microsoft Windows NT 4.0 into the System32 directory), because this version of Msvcrt40.dll is a "wrapper DLL" and forwards all conversions to the Msvcrt.dll file.

Back to the top | Give Feedback

CAUSE

Locale settings are process-wide. These settings are used by SQL Server to convert strings (such as literals in Transact-SQL statements) to numbers. Every Win32 process is initialized with the ANSI-C settings; these are the correct settings for interpreting numbers in Transact-SQL statements. For example, the decimal separator is point ("."), and so on.

Any changes to the locale settings of an extended stored procedure affect the way SQL Server converts strings to numbers. For example, an extended stored procedure calling "setlocale(LC_ALL, "")" sets the locale setting to the regional settings of the process owner, and thus exhibits this problem.

Back to the top | Give Feedback

WORKAROUND

If this problem happens with an extended stored procedure and you own the source code, you can avoid changing the locale settings within your code. If that is not possible, you can reset the locale settings to ANSI-C before you return from your extended stored procedure. For example, by calling "setlocale( LC_ALL, "C" );".

In the scenario described in the "More Information" section, suppose the following:

SQL Server runs under a Windows NT account with non-SQL regional settings (for example, "German (Standard)").
SQLMail is enabled.
The MAPI profile used by SQLMail contains the Outlook Address Book service.
On SQL Server 6.5 only: SQL Server uses Msvcrt40.dll version 4.20.6172.

In this scenario, the possible workarounds are:

Remove the Outlook Address Book service from the MAPI profile used by SQLMail.

-or-
Run SQL Server under a Windows NT account with regional settings compatible to SQL Server (for example, "English (United States)").

-or-
For SQL Server 6.5 only: Make sure that SQL Server uses a Msvcrt40.dll file newer than version 4.20.6172, having a file size > 300 kilobytes.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Back to the top | Give Feedback

MORE INFORMATION

Steps to Reproduce Behavior

Install Microsoft Outlook 98 on the SQL Server.
Change the regional settings of your domain account to different decimal and thousand separators. For example, use "German (Standard)".
Use the account created in step 2 to run SQL Server (or run it from a command line).
Create a new MAPI profile by doing the following:
1. Double-click the Mail icon in Control Panel.
2. Click Show Profiles.
3. Click Add.
4. Select Microsoft Exchange Server, and then click Next.
5. In the Profile Name dialog box, type SQLMail and then click Next.
6. Type the names of the Microsoft Exchange Server computer and the mailbox to use in the appropriate boxes.
7. Click Next and accept all the default values until you are finished adding the new profile.NOTE: The new profile automatically includes the Outlook Address Book service.
Log out from any MAPI client.
Start SQL Server.

For SQL Server version 6.5 only: Ensure that SQL Server is using the Msvcrt40.dll file version 4.20.6172 from the System32 directory. You can do this by using either of the following:

Running SQL Server with the command ntsd -g sqlservr -c.

-or-
Using the ListDLLs tool from:
http://technet.microsoft.com/en-us/sysinternals/default.aspx
(http://technet.microsoft.com/en-us/sysinternals/default.aspx)
NOTE: You can force SQL Server to use the Msvcrt40.dll file from the System32 directory by renaming this DLL in the Binn folder.

Run the following script:

set nocount on
print 'First w/o SQLMail:'
go
select 'Result=10.32'=convert(float, '10.32')
go
print 'Error 8114:'
select 'Result=10.0 (on 6.5)'=convert(float, '10,32')
go
print ' '
declare @val float select @val = 123.45e+2 select 'Result=12345.0'=@val
go
exec master..xp_startmail 'SQLMail'
go
print 'Now w/ SQLMail:'
go
select 'Result=10.32'=convert(float, '10.32')
go
print 'Error 8114:'
select 'Result=10.0 (on 6.5)'=convert(float, '10,32')
go
print ' '
declare @val float select @val = 123.45e+2 select 'Result=12345.0'=@val
go

Back to the top | Give Feedback