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
')

I had migrated everything, 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 a comma (,) as the decimal symbol). It is stored in the registry under the following sub-key: 
HKEY_LOCAL_MACHINESOFTWAREORACLEHOMEIDNLS_LANG 
Where ID is the unique number identifying the Oracle home. 
The trick was to change this sub-key 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 (,).

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

One thought on “Regional and language settings for Oracle client used with SQL Server linked server

  1. Giancarlo says:

    i have changed and no more problems, the query result dot columbs and dot columbs also second query.
    Thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *