| Author |
Topic |
|
ruserious
Starting Member
6 Posts |
 Posted - 2004-11-20 : 18:48:47
|
| G'day all...My goal is to connect to a server using Oracle Database version 8.1.7.4, from a server which does not have either Oracle Database or the full Oracle Client, preferrably using SQL Server DTS packages.With that said, I have installed instant client basic and instant client ODBC suppliment on our first development server using the instructions at the following link without any difficulties (although I do admit that I only have a tnsnames.ora file and not a sqlnet.ora file):http://www.oracle.com/technology/tech/oci/instantclient/instantclient.htmlI've got a DTS package up and running - can see the data.The trouble is, when I attempt to deploy it on any of our other servers it fails. On any of these servers, when I attempt to connect using Microsoft ODBC Driver for Oracle in a DTS package in SQL Server I get the following error message:HResult of 0x8004005 (-2147467259) returned Unexpected error occurred. An error result was returned without an error message.(Note: This is seen on all other servers I've tried this on except for the first one).I have also tried setting up a System ODBC connection using the Instant Client driver but it returned this error message:Unable to connectSQLState=S1000[Oracle][ODBC][Ora]ORA-12705: invalid or unknown NLS parameter value specifiedIn desperation I have also tried installing the Oracle RDB Driver and the Oracle 8.1.7.8.1.0 ODBC driver. Both appear to install successfully yet nothing seems to work as far as connecting to the Oracle Server.As far as I can tell, these are the noticable differences between the first server and the other servers:- First server is MS SQL Server 2000 version: 8.00.760, rest are 8.00.818- First server is running MS SQL Server Analysis services, rest are not- First server is MS Windows 2000 Advanced Server, rest are MS Windows 2000 ServerSo my first question: Does anyone know if the Instant Client tool works with SQL Server version: 8.00.818? I've seen a lot of forum postings that state that you cannot connect to Oracle database servers without having the full Oracle client installed, however that doesn't explain how I can connect successfully on one server but not on any other. Also, I've followed up with the DBA for this server - the login credentials that I'm using is not a single-user login or anything that only works from one server/location/connection/etc.My second question: Can anyone else suggest something that I haven't tried yet, short of installing the Full Oracle Client on this server? Not that I'm opposed to doing that, it's just that it's a last resort idea as of now...Please help - need to roll this into production soon... |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-20 : 19:41:30
|
| Did the first server have any Oracle products installed prior to the Instant Client Installation ?rockmoose |
 |
|
|
ruserious
Starting Member
6 Posts |
Posted - 2004-11-22 : 15:52:44
|
No, unfortunately not.quote:
Originally posted by rockmoose
Did the first server have any Oracle products installed prior to the Instant Client Installation ?rockmoose
|
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-11-22 : 16:08:21
|
| do you have a metalink account? Note 215255.1 is probably your fix. I don't know what Oracle's policy of posting metalink content is outside of metalink, so I will not post it to this forum. However, if you provide an email address I can send you the info. Check my profile and send me an email to that address.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-11-22 : 16:17:43
|
| I sent the article to the email account you registered here. Let me know if the workaround provided works. Good luck.-ec |
|
|
|
ruserious
Starting Member
6 Posts |
Posted - 2004-11-22 : 17:57:53
|
Thanks for the email eyechart, I have tried changing the permission levels to allow Everyone Full access on all Oracle folders that I have on my other machines but it is still returning the same error messages.Also, I believe Solution 2 in the email doesn't apply to me (yet) as I haven't installed Oracle client yet.Will still keep entertaining other suggestions though...quote:
Originally posted by eyechart
I sent the article to the email account you registered here. Let me know if the workaround provided works. Good luck.-ec
|
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-11-22 : 20:10:50
|
quote:
Originally posted by ruserious
Thanks for the email eyechart, I have tried changing the permission levels to allow Everyone Full access on all Oracle folders that I have on my other machines but it is still returning the same error messages.Also, I believe Solution 2 in the email doesn't apply to me (yet) as I haven't installed Oracle client yet.Will still keep entertaining other suggestions though...quote:
Originally posted by eyechart
I sent the article to the email account you registered here. Let me know if the workaround provided works. Good luck.-ec
Yes, the 2nd solution is to apply a patchset to the full Oracle client install. This isn't available, since you are using the Instant Client.Do you have a metalink account? Your best bet here is to open a TAR with Oracle support. Another option would be to post this question over at dbforums.com in their Oracle section. You might get some more help there.I'll do a couple more searches in metalink on that specific error and forward anything I find to you. Good luck.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-11-22 : 20:24:39
|
OK, I think I found the fix for you. It looks like you are just missing the NLS_LANG environment variable. Here is the information on setting up the Instanct Client. I sent you another metalink article with that info.Oracle has their documentation freely available online. here is the link for the Instant Client setup info: http://www.oracle.com/technology/tech/oci/instantclient/releasenotes/ODBC_IC_ReleaseNotes.html . You will need an OTN account to access that link, btw OTN accounts are free.Here is a blurb from the install doc for anyone else who is having a similar problem: quote:
<SNIP>After the installation the environment needs to be configured in the following way. - Add the directory to the PATH system environment variable. - Otherwise ODBC manager will be not able to load/find the driver.- Define the TNS_ADMIN to point the .ora files directory for OCI. - This needs to be set for OCI to resolve the TNS name. - If TNS_ADMIN is not set, OCI will examine an operating system dependent set of directories to find tnsnames.ora. This search path includes looking in the directory %ORACLE_HOME%/network/admin/tnsnames.ora. This is the only reason to set the ORACLE_HOME environment variable for Oracle Instant Client. If ORACLE_HOME is set when running Instant Client applications, it must be set to a directory where Instant Client exists (in this case C:\Oracle\instantclient). - Set any Oracle globalization variables required for your locale. See the Oracle Database 10g Globalization Support Guide for more information. - For example on Windows to set NLS_LANG: - set NLS_LANG=JAPANESE_JAPAN.JA16EUC <SNIP>
-ec |
|
|
|
ruserious
Starting Member
6 Posts |
Posted - 2004-11-23 : 10:48:33
|
Yup, that worked - thanks eyechart.   It turned out that it was a case where the NLS_LANG variable had not been set. Here's what I did to resolve the problem for the benefit of anyone else who has a similar problem:Create an environment variable (on Windows 2000, R-click on My Computer->Advanced->Environmental Variables->System Variables->New)called NLS_LANG and set it to one of the above languages in my case I set it to: AMERICAN_AMERICA.WE8ISO8859P1), or any valid language. I also put my tsnnames.ora file into the Instant Client folder as well.quote:
Originally posted by eyechart
OK, I think I found the fix for you. It looks like you are just missing the NLS_LANG environment variable. Here is the information on setting up the Instanct Client. I sent you another metalink article with that info.Oracle has their documentation freely available online. here is the link for the Instant Client setup info: http://www.oracle.com/technology/tech/oci/instantclient/releasenotes/ODBC_IC_ReleaseNotes.html . You will need an OTN account to access that link, btw OTN accounts are free.Here is a blurb from the install doc for anyone else who is having a similar problem: quote:
<SNIP>After the installation the environment needs to be configured in the following way. - Add the directory to the PATH system environment variable. - Otherwise ODBC manager will be not able to load/find the driver.- Define the TNS_ADMIN to point the .ora files directory for OCI. - This needs to be set for OCI to resolve the TNS name. - If TNS_ADMIN is not set, OCI will examine an operating system dependent set of directories to find tnsnames.ora. This search path includes looking in the directory %ORACLE_HOME%/network/admin/tnsnames.ora. This is the only reason to set the ORACLE_HOME environment variable for Oracle Instant Client. If ORACLE_HOME is set when running Instant Client applications, it must be set to a directory where Instant Client exists (in this case C:\Oracle\instantclient). - Set any Oracle globalization variables required for your locale. See the Oracle Database 10g Globalization Support Guide for more information. - For example on Windows to set NLS_LANG: - set NLS_LANG=JAPANESE_JAPAN.JA16EUC <SNIP>
-ec
|
|
|
|
|
Instant Client with SQL Server - HELP!!