Regional and language settings for Oracle client used with SQL Server linked server

Today, I ran into a problem while migrating a Data Warehouse solution to another server. The solution have linked servers for extracting data from an Oracle source system.

The query would go something like this:

INSERT INTO dbo.MyTable
SELECT *
FROM OPENQUERY(UNDPFSP1ICC, 'SELECT
	TO_CHAR(COL1_DT, ''YYYY'') AS Col1Year
	, COL2
	, COL3
	, SUM(AMOUNT) AS Amount
	, SYSDATE
FROM MYTABLE
GROUP BY
	TO_CHAR(COL1_DT, ''YYYY'')
	, COL2
	, COL3
')

Everything had been migrated, but while running the extract job, which ran queries like the above, we would get errors like these:

Conversion failed when converting the nvarchar value '123456,78' to data type int.

Error converting data type nvarchar to numeric.

The problem was that instead of using a dot (.) as the decimal symbol, a comma (,) was being returned. This would make the data convert to nvarchar, and an implicit conversion to a NUMERIC or INT would fail.

Everything was running fine on the old server. The collation of the server and databases were identical (Latin1_General_CI_AS), but the regional and language settings were different. On the old server they were set to English (United States) and on the new server to Danish. Changing the settings on the new server to English (United States) did not help.

After troubleshooting a bit on #sqlhelp with Amit Banerjee (twitter | blog) and William Durkin (twitter | blog), as well as via Skype with Jørgen Guldmann (twitter | blog), Jørgen ended up pointing me in the right direction.

The problem as that the NLS_LANG parameter was set to Danish (where we use comma (,) as decimal symbol). It is stored in the registry under the following subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG

Where ID is the unique number identifying the Oracle home.

The trick was to change this subkey from:

DANISH_DENMARK.WE8MSWIN1252

To:

AMERICAN_AMERICA.WE8MSWIN1252

And then everything worked fine, and the decimal symbol was now a dot (.) instead of a comma (,).

Leave a Reply