Unable to insert data into ORACLE database because of NLS_LANG exception.

I have made a small app in .NET 2.0 which is copying data from a SQL Server to ORACLE.
It is running as a Windows service on the ORACLE server.
For accessing ORACLE I am using ODP.NET 11g, and for accessing SQLServer I'm using built in functionality i the .NET framework.

When running the service on the development machine, it works just great.
When running it on the ORACLE server, it fails with an exception:

"Oracle.DataAccess.Client.OracleException ORA-12705: Cannot access NLS data files or invalid environment specified    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)
   at Oracle.DataAccess.Client.OracleConnection.Open()"

The same thing happens when trying to run the service on a different machine which already has ORACLE client installed.
The work-around on this PC was to actually remove the NLS_LANG entry from the reigstry.
I don't see that as the solution on the ORACLE server.

Anyone who know if this is possible?
BTW I also tried using Microsoft's ORACLE provider with the same result.
This is actually becoming a showstopper for us!
Ole_BrunAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Are you able to to do an sqlplus on the server and connect to oracle?
Ole_BrunAuthor Commented:
Yes, sqlplus works ok.
It also appears that acessing the database using an old .NET 1.1 application works.
However my .NET 2.0 application is constantly getting the error message.
SujithData ArchitectCommented:
Where is it getting the NLS_LANG setting from?

Can you check the environment variables?
Is it bundled in your application itself?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ole_BrunAuthor Commented:
It looks as if it is getting it from the reigstry.
As I mentioned, one way of solving the problem was to actually remove the registry entry.
This is not the solution on the ORACLE server.
schwertnerCommented:
It seems the value of NLS_LANG is wrong.
NLS_LANG on the server has no big meaning for everyday operation.
The main governing value is the character set of the database and it is
stored in the DB parameters.
NLS_LANG plays role for utilities like Export, Import.
Also check how many homes you have and check if you use the correct one.
SujithData ArchitectCommented:
Do you need the NLS_LANG setting in the server for any other applications?

If not; try to create an environment variable with name NLS_LANG and set it to an empty string and then launch the application.(The idea is to unset NLS_LANG and run your application).
Ole_BrunAuthor Commented:
The ORACLE database is part of the Honeywell Uniformance installation, and I believe the NLS_LANG setting is required by that application. However I can see that the NLS_LANG string has been configured several places in the registry. I have a colleague experimenting with removing the ones that we know are not required by the Uniformance application.
Will update you on the progress.
SujithData ArchitectCommented:
>> When running it on the ORACLE server

Do you know the userid which runs this service? You may create an env variable for this user as I suggested above and try.
Ole_BrunAuthor Commented:
We have now tried a few things, and finally found a solution.

In the registry there are two entries for NLS_LANG.
HKLM\SOFTWARE\ORACLE was set to NA, whereas
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 was set to (AMERICAN_AMERICA.WE8MSWIN1252)

By changing \HKLM\SOFTWARE\ORACLE to AMERICAN_AMERICA.WE8MSWIN1252 the application started working! :-)

Thanks for your suggestions though!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.