Oracle ORA-12154 TNS Could not resolve service name Error

Question

I am a SQL Server user    I am on a project that is using oracle  which I rarely use   I need to create an ODBC connection so I can access the some data via MS Access I have a application on my machine called oraHome90  It seems to allow a configuration of something called a listener in a    net configuration utility     I think that a    Local Net Service Name Configuration    needs to also be done   The IT support gave me this information to set up the ODBC connection   I have tried every combination that I can think of  I can get past a test that successfully passes a test to    login     to the oracle server database   When I try to create the ODBC connection I get the following error  ORA-12154  TNS  Could not resolve service name   Assuming that I want to start from scratch and the following information is supposed to allow for me to connect to the database      Any suggestions or comment   Note  ultimately the project will have a website  ASP page query the data  but I have to first prove that I can see the data using the ODBC connection via MS Access  Service name  SERVICENAME HOST   HOST XYZi com User Id  MYUSERID Password  MYPASSWORD                                Oracle Connection Dim ocst Dim oconn  ocst    Provider OraOLEDB Oracle    amp              Data Source DATASOURCE    amp              User ID CHIJXL    amp              Password password     set oconn   CreateObject  ADODB Connection

User · Answer

Only restart the SID services  For example you SID name   orcl then all the services which related to orcl should be restart then you problem will be solved

User · Answer

Hours of problems SOLVED    I had installed the Beta Entity Framework for Oracle and in in visual studio 2010 MVC 3 project I was referencing under the tab  NET  the Oracle DataAccess     This kept giving me the   Oracle ORA-12154  TNS  Could not      error    I finally just browsed to the previous Oracle install under c  Oracle product      using the old  10 2 0 100 version of the dll   Finally it works now   Hope it helps someone else

User · Answer

I have resolved this issue by removing sqlnet ora from the C  oracle ora92 network ADMIN path    Make sure TNSNAMES ORA file exists in the right directory Make sure PATH environment variable is having entry for oracle Make sure no syntax issues in the TNSNAMES ORA Try removing sqlnet ora file

User · Answer

I have resolved this issue by removing sqlnet ora from the C  oracle ora92 network ADMIN path    Make sure TNSNAMES ORA file exists in the right directory Make sure PATH environment variable is having entry for oracle Make sure no syntax issues in the TNSNAMES ORA Try removing sqlnet ora file

User · Answer

I had a same problem and the same error was showing up  my TNSNAMES ORA file was also good to go but apparently there was a problem due to firewall blocking the access  SO a good tip would be to make sure that firewall is not blocking the access to the datasource

User · Answer

I fixed this problem using this steps    First of all  this error occured   if you didn t install same directory or drive    But the answer is here     Login windows as a Adminstrator  Go to Control Panel  System Properties and click Enviroment Find the OS variable and change name as a  TNS ADMIN   And change the value as a  tnsnames s directory address   Restart the system   Congrulations

User · Answer

If you have a 32bit DSN and a 64bit DSN with same names  Windows will automatically choose the 64bit one and If your application is 32bit it shows this error  Just watch out for that

User · Answer

In reference to  7 in this MSDN POST   adding a registry entry worked for me  I had Vs2010  et oracle 11 0 installed      Check for the registry key    TNS ADMIN    at HKEY LOCAL MACHINE SOFTWARE ORACLE  If it exists then make sure it has   the right value as    Dir  app product 11 1 0 client 1 network admin       If you don   t see the key then create the key and set appropriate value   as below  Regedit- HKEY LOCAL MACHINE- Software- Oracle- RightClick   NEW- StringValue and name  it TNS ADMIN and give the value       X  app product 11 1 0 client 1 network admin

User · Answer

from http   ora-12154 ora-code com  ORA-12154   TNS could not resolve the connect identifier specified Cause     A connection to a database or other service was requested using a connect identifier  and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured  For example  if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository  or the repository could not be located or reached  Action      If you are using local naming  TNSNAMES ORA file   Make sure that  TNSNAMES  is listed as one of the values of the NAMES DIRECTORY PATH parameter in the Oracle Net profile  SQLNET ORA  Verify that a TNSNAMES ORA file exists and is in the proper directory and is accessible  Check that the net service name used as the connect identifier exists in the TNSNAMES ORA file  Make sure there are no syntax errors anywhere in the TNSNAMES ORA file  Look for unmatched parentheses or stray characters  Errors in a TNSNAMES ORA file may make it unusable  If you are using directory naming  Verify that  LDAP  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Verify that the LDAP directory server is up and that it is accessible  Verify that the net service name or database name used as the connect identifier is configured in the directory  Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier If you are using easy connect naming  Verify that  EZCONNECT  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Make sure the host  port and service name specified are correct  Try enclosing the connect identifier in quote marks  See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming

User · Answer

from http   ora-12154 ora-code com  ORA-12154   TNS could not resolve the connect identifier specified Cause     A connection to a database or other service was requested using a connect identifier  and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured  For example  if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository  or the repository could not be located or reached  Action      If you are using local naming  TNSNAMES ORA file   Make sure that  TNSNAMES  is listed as one of the values of the NAMES DIRECTORY PATH parameter in the Oracle Net profile  SQLNET ORA  Verify that a TNSNAMES ORA file exists and is in the proper directory and is accessible  Check that the net service name used as the connect identifier exists in the TNSNAMES ORA file  Make sure there are no syntax errors anywhere in the TNSNAMES ORA file  Look for unmatched parentheses or stray characters  Errors in a TNSNAMES ORA file may make it unusable  If you are using directory naming  Verify that  LDAP  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Verify that the LDAP directory server is up and that it is accessible  Verify that the net service name or database name used as the connect identifier is configured in the directory  Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier If you are using easy connect naming  Verify that  EZCONNECT  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Make sure the host  port and service name specified are correct  Try enclosing the connect identifier in quote marks  See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming

User · Answer

It has nothing to do with a space embedded in the folder structure   I had the same problem  But when I created an environmental variable  defined both at the system- and user-level  called TNS HOME and made it to point to the folder where TNSNAMES ORA existed  the problem was resolved  Voila   venki

User · Answer

Going on the assumption you re using TNSNAMES naming  here s a couple of things to do    Create modify the tnsnames ora file in the network admin subdirectory associated with OraHome90 to include an entry for your oracle database      gt  SERVICENAME alias    gt      DESCRIPTION    gt       ADDRESS    PROTOCOL   TCP  HOST   HOST XYZi com  PORT   1521    gt       CONNECT DATA    SERVICE NAME   SERVICENAME      This is assuming you re using the standard Oracle port of 1521  Note that servicename alias can be any name you want to use on the local system  You may also find that you need to specify  SID   SERVICENAME  instead of  SERVICENAME SERVICENAME     Execute tnsping servicename alias to verify connectivity  Get this working before going any further  This will tell you if you re past the 12154 error  Assuming a good connection  create an ODBC DSN using the control panel  specifying the ODBC driver for Oracle of your choice  generally there s a Microsoft ODBC driver at least  and it should work adequately as a proof of concept   I ll assume the name you gave of DATASOURCE  Use the servicename alias as the Server name in the ODBC configuration  At this point you should be able to connect to your database via Access  I am not a VB programmer  but I know you should be able to go to File- Get External Data- Link Tables and connect to your ODBC source  I would assume your code would work as well

User · Answer

We also had the similar issue  What we found out that we had provided multiple aliases for our connection string in tnsnames ora  something like   svc01  svc02  DESCRIPTION  ADDRESS  PROTOCOL TCP  HOST xxxx  port 50   CONNECT DATA  SERVER DEDICATED  service name yyyysvc world     so when creating a connection using ODBC  when we selected the value for TNS service name  the auto populate was showing  svc01    please note the extra comma there   As soon as we removed the comma  it started working for us

User · Answer

Warren and  DCookie have covered the solution  one thing to emphasise is the use of tnsping  You can use this to prove your TNSNames is correct before attempting to connect   Once you have set up tnsnames correctly you could use ODBC or try TOra which will use your native oracle connection  TOra or something similar  TOAD  SQL Plus etc  will prove invaluable in debugging and improving your SQL   Last but not least when you eventually connect with ASP net remember that you can use the Oracle data connection libraries  See Oracle com for a host of resources

User · Answer

We resolved our issues by re-installing the Oracle Database Client  Somehow the installation wasn t successful on 1 of the workstations  even though there was no logging   however when comparing size files folders of the Oracle directory to a working client workstation  there was a significant amount of files that were missing  Once we performed a clean install  everything worked perfectly

User · Answer

Warren and  DCookie have covered the solution  one thing to emphasise is the use of tnsping  You can use this to prove your TNSNames is correct before attempting to connect   Once you have set up tnsnames correctly you could use ODBC or try TOra which will use your native oracle connection  TOra or something similar  TOAD  SQL Plus etc  will prove invaluable in debugging and improving your SQL   Last but not least when you eventually connect with ASP net remember that you can use the Oracle data connection libraries  See Oracle com for a host of resources

User · Answer

Only restart the SID services  For example you SID name   orcl then all the services which related to orcl should be restart then you problem will be solved

User · Answer

This error message can be very confusing and the solution can be surprisingly primitive  In my case  Oracle stored procedure sends recordset to MS Excel via  quot Provider OraOLEDB Oracle Data Source     etc quot     The problem was a number of decimal numbers in the Oracle data column sent to Excel 2010  When I used Oracle SQL query ROUND grosssales eur 2  AS grosssales eur  it worked fine

User · Answer

We resolved our issues by re-installing the Oracle Database Client  Somehow the installation wasn t successful on 1 of the workstations  even though there was no logging   however when comparing size files folders of the Oracle directory to a working client workstation  there was a significant amount of files that were missing  Once we performed a clean install  everything worked perfectly

User · Answer

I struggled to resolve this problem for hours until I found an Environment variable called TNS ADMIN set in My Computer    Properties    Advanced    Environment Variables    look in System variables for an entry called TNS ADMIN   TNS ADMIN is added to change the default path for Tnsnames ora entry  This is useful when its used in a network environment where a generic tnsnames ora entry can be setup for all the network computers  To look at the default path of tnsnames ora add the default path in TNS ADMIN

User · Answer

If there is a space in the beginning of the tns name define in file tnsnames ora  then some of the the connectors like odbc may give this error  Remove space character in the beginning

User · Answer

I had a same problem and the same error was showing up  my TNSNAMES ORA file was also good to go but apparently there was a problem due to firewall blocking the access  SO a good tip would be to make sure that firewall is not blocking the access to the datasource

User · Answer

I experienced this problem too  I discovered the problem is because Oracle DB does not like the space in C program files  x86  Toad       so I created a new directory named C App Toad then reinstalled in it to connect Toad to Oracle  It worked

User · Answer

I fixed this problem using this steps    First of all  this error occured   if you didn t install same directory or drive    But the answer is here     Login windows as a Adminstrator  Go to Control Panel  System Properties and click Enviroment Find the OS variable and change name as a  TNS ADMIN   And change the value as a  tnsnames s directory address   Restart the system   Congrulations

User · Answer

In my case  the error are because I have 2 Oracle clients  It s the solution   Oracle ORA-12154 error on local IIS  but not with Visual Studio Development Server

User · Answer

I just spend an hour on this  I m new to Oracle so i was thoroughly confused     the situation   just installed visual studio 2012 Oracle developer tools  When i did this I lost the items in my drop down which contained my TNS entries in TOAD  I was getting this error from Visual studio AND TOAD   WTH  so i added the environmental Variable TNS ADMIN under  ALL USERS  with the path to my  ora file  which i now worked fine because it worked until I broke it   Toad picked up that change  Still Visual Studio wouldn t give me any love    still getting same error  THEN  i added the environmental Variable TO MY USER VARIABLES   VIOLA     ENSURE THE ENVIRONMENTAL VARIABLES ARE SET FOR THE SYSTEM AND THE USER

User · Answer

This was mentioned in a comment to another answer  but I wanted to move it to an actual answer since this was also the problem in my case and I would have upvoted it if it had been an answer   I m on Linux and the tnsnames ora file was not set to readable by everyone  After making it readable connecting via tns locally worked     chmod  r tnsnames ora

User · Answer

Going on the assumption you re using TNSNAMES naming  here s a couple of things to do    Create modify the tnsnames ora file in the network admin subdirectory associated with OraHome90 to include an entry for your oracle database      gt  SERVICENAME alias    gt      DESCRIPTION    gt       ADDRESS    PROTOCOL   TCP  HOST   HOST XYZi com  PORT   1521    gt       CONNECT DATA    SERVICE NAME   SERVICENAME      This is assuming you re using the standard Oracle port of 1521  Note that servicename alias can be any name you want to use on the local system  You may also find that you need to specify  SID   SERVICENAME  instead of  SERVICENAME SERVICENAME     Execute tnsping servicename alias to verify connectivity  Get this working before going any further  This will tell you if you re past the 12154 error  Assuming a good connection  create an ODBC DSN using the control panel  specifying the ODBC driver for Oracle of your choice  generally there s a Microsoft ODBC driver at least  and it should work adequately as a proof of concept   I ll assume the name you gave of DATASOURCE  Use the servicename alias as the Server name in the ODBC configuration  At this point you should be able to connect to your database via Access  I am not a VB programmer  but I know you should be able to go to File- Get External Data- Link Tables and connect to your ODBC source  I would assume your code would work as well

User · Answer

We also had the similar issue  What we found out that we had provided multiple aliases for our connection string in tnsnames ora  something like   svc01  svc02  DESCRIPTION  ADDRESS  PROTOCOL TCP  HOST xxxx  port 50   CONNECT DATA  SERVER DEDICATED  service name yyyysvc world     so when creating a connection using ODBC  when we selected the value for TNS service name  the auto populate was showing  svc01    please note the extra comma there   As soon as we removed the comma  it started working for us

User · Answer

Warren and  DCookie have covered the solution  one thing to emphasise is the use of tnsping  You can use this to prove your TNSNames is correct before attempting to connect   Once you have set up tnsnames correctly you could use ODBC or try TOra which will use your native oracle connection  TOra or something similar  TOAD  SQL Plus etc  will prove invaluable in debugging and improving your SQL   Last but not least when you eventually connect with ASP net remember that you can use the Oracle data connection libraries  See Oracle com for a host of resources

User · Answer

If you have a 32bit DSN and a 64bit DSN with same names  Windows will automatically choose the 64bit one and If your application is 32bit it shows this error  Just watch out for that

User · Answer

I had this problem because of a typo in the filename  tsnames ora instead of tnsnames ora

User · Answer

I just spend an hour on this  I m new to Oracle so i was thoroughly confused     the situation   just installed visual studio 2012 Oracle developer tools  When i did this I lost the items in my drop down which contained my TNS entries in TOAD  I was getting this error from Visual studio AND TOAD   WTH  so i added the environmental Variable TNS ADMIN under  ALL USERS  with the path to my  ora file  which i now worked fine because it worked until I broke it   Toad picked up that change  Still Visual Studio wouldn t give me any love    still getting same error  THEN  i added the environmental Variable TO MY USER VARIABLES   VIOLA     ENSURE THE ENVIRONMENTAL VARIABLES ARE SET FOR THE SYSTEM AND THE USER

User · Answer

This was mentioned in a comment to another answer  but I wanted to move it to an actual answer since this was also the problem in my case and I would have upvoted it if it had been an answer   I m on Linux and the tnsnames ora file was not set to readable by everyone  After making it readable connecting via tns locally worked     chmod  r tnsnames ora

User · Answer

In reference to  7 in this MSDN POST   adding a registry entry worked for me  I had Vs2010  et oracle 11 0 installed      Check for the registry key    TNS ADMIN    at HKEY LOCAL MACHINE SOFTWARE ORACLE  If it exists then make sure it has   the right value as    Dir  app product 11 1 0 client 1 network admin       If you don   t see the key then create the key and set appropriate value   as below  Regedit- HKEY LOCAL MACHINE- Software- Oracle- RightClick   NEW- StringValue and name  it TNS ADMIN and give the value       X  app product 11 1 0 client 1 network admin

User · Answer

from http   ora-12154 ora-code com  ORA-12154   TNS could not resolve the connect identifier specified Cause     A connection to a database or other service was requested using a connect identifier  and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured  For example  if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository  or the repository could not be located or reached  Action      If you are using local naming  TNSNAMES ORA file   Make sure that  TNSNAMES  is listed as one of the values of the NAMES DIRECTORY PATH parameter in the Oracle Net profile  SQLNET ORA  Verify that a TNSNAMES ORA file exists and is in the proper directory and is accessible  Check that the net service name used as the connect identifier exists in the TNSNAMES ORA file  Make sure there are no syntax errors anywhere in the TNSNAMES ORA file  Look for unmatched parentheses or stray characters  Errors in a TNSNAMES ORA file may make it unusable  If you are using directory naming  Verify that  LDAP  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Verify that the LDAP directory server is up and that it is accessible  Verify that the net service name or database name used as the connect identifier is configured in the directory  Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier If you are using easy connect naming  Verify that  EZCONNECT  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Make sure the host  port and service name specified are correct  Try enclosing the connect identifier in quote marks  See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming

User · Answer

If there is a space in the beginning of the tns name define in file tnsnames ora  then some of the the connectors like odbc may give this error  Remove space character in the beginning

User · Answer

Arrhhh   I RAN INTO THIS AGAIN     Just install ToadForOracle in C   or any directory without parenthesis in the path     In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers   I am using SQL Reporting Services with an Oracle Database  The problem is the brackets in the path to Visual Studio  BIDS   Oracle doesn t like apps that start in a path with brackets   RDBMS 10g XE problem with parenthesis in path  So I made a BAT file to open Visual Studio with Progra 2 as the short path name for  Program Files  x86     Here is the contents of the BAT file   rem Progra 2 is short path name for  Program Files  x86   and works around an Oracle client bug that doesn t like the    s in the path start  B  C  Progra 2 Microsoft Visual Studio 9 0 Common7 IDE   C  Progra 2 Microsoft Visual Studio 9 0 Common7 IDE devenv exe    I name this BAT file StartBIDS BAT and put it in the directory        C  Program Files Microsoft SQL Server Start BIDS bat    Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon  This allows me to open TOAD  Visual Studio  BIDS and etc apps that I use to work with Oracle   Update   Alternatively make a Junction    mklink  J  C  Program Files  x86     C  Program Files x86     Then remove the brackets in the shortcut

User · Answer

Warren and  DCookie have covered the solution  one thing to emphasise is the use of tnsping  You can use this to prove your TNSNames is correct before attempting to connect   Once you have set up tnsnames correctly you could use ODBC or try TOra which will use your native oracle connection  TOra or something similar  TOAD  SQL Plus etc  will prove invaluable in debugging and improving your SQL   Last but not least when you eventually connect with ASP net remember that you can use the Oracle data connection libraries  See Oracle com for a host of resources

User · Answer

I experienced this problem too  I discovered the problem is because Oracle DB does not like the space in C program files  x86  Toad       so I created a new directory named C App Toad then reinstalled in it to connect Toad to Oracle  It worked

User · Answer

Hours of problems SOLVED    I had installed the Beta Entity Framework for Oracle and in in visual studio 2010 MVC 3 project I was referencing under the tab  NET  the Oracle DataAccess     This kept giving me the   Oracle ORA-12154  TNS  Could not      error    I finally just browsed to the previous Oracle install under c  Oracle product      using the old  10 2 0 100 version of the dll   Finally it works now   Hope it helps someone else

User · Answer

Going on the assumption you re using TNSNAMES naming  here s a couple of things to do    Create modify the tnsnames ora file in the network admin subdirectory associated with OraHome90 to include an entry for your oracle database      gt  SERVICENAME alias    gt      DESCRIPTION    gt       ADDRESS    PROTOCOL   TCP  HOST   HOST XYZi com  PORT   1521    gt       CONNECT DATA    SERVICE NAME   SERVICENAME      This is assuming you re using the standard Oracle port of 1521  Note that servicename alias can be any name you want to use on the local system  You may also find that you need to specify  SID   SERVICENAME  instead of  SERVICENAME SERVICENAME     Execute tnsping servicename alias to verify connectivity  Get this working before going any further  This will tell you if you re past the 12154 error  Assuming a good connection  create an ODBC DSN using the control panel  specifying the ODBC driver for Oracle of your choice  generally there s a Microsoft ODBC driver at least  and it should work adequately as a proof of concept   I ll assume the name you gave of DATASOURCE  Use the servicename alias as the Server name in the ODBC configuration  At this point you should be able to connect to your database via Access  I am not a VB programmer  but I know you should be able to go to File- Get External Data- Link Tables and connect to your ODBC source  I would assume your code would work as well

User · Answer

from http   ora-12154 ora-code com  ORA-12154   TNS could not resolve the connect identifier specified Cause     A connection to a database or other service was requested using a connect identifier  and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured  For example  if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository  or the repository could not be located or reached  Action      If you are using local naming  TNSNAMES ORA file   Make sure that  TNSNAMES  is listed as one of the values of the NAMES DIRECTORY PATH parameter in the Oracle Net profile  SQLNET ORA  Verify that a TNSNAMES ORA file exists and is in the proper directory and is accessible  Check that the net service name used as the connect identifier exists in the TNSNAMES ORA file  Make sure there are no syntax errors anywhere in the TNSNAMES ORA file  Look for unmatched parentheses or stray characters  Errors in a TNSNAMES ORA file may make it unusable  If you are using directory naming  Verify that  LDAP  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Verify that the LDAP directory server is up and that it is accessible  Verify that the net service name or database name used as the connect identifier is configured in the directory  Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier If you are using easy connect naming  Verify that  EZCONNECT  is listed as one of the values of the NAMES DIRETORY PATH parameter in the Oracle Net profile  SQLNET ORA   Make sure the host  port and service name specified are correct  Try enclosing the connect identifier in quote marks  See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming

User · Answer

It has nothing to do with a space embedded in the folder structure   I had the same problem  But when I created an environmental variable  defined both at the system- and user-level  called TNS HOME and made it to point to the folder where TNSNAMES ORA existed  the problem was resolved  Voila   venki

User · Answer

In my case  the error are because I have 2 Oracle clients  It s the solution   Oracle ORA-12154 error on local IIS  but not with Visual Studio Development Server

User · Answer

I had this problem because of a typo in the filename  tsnames ora instead of tnsnames ora

User · Answer

I struggled to resolve this problem for hours until I found an Environment variable called TNS ADMIN set in My Computer    Properties    Advanced    Environment Variables    look in System variables for an entry called TNS ADMIN   TNS ADMIN is added to change the default path for Tnsnames ora entry  This is useful when its used in a network environment where a generic tnsnames ora entry can be setup for all the network computers  To look at the default path of tnsnames ora add the default path in TNS ADMIN

User · Answer

This error message can be very confusing and the solution can be surprisingly primitive  In my case  Oracle stored procedure sends recordset to MS Excel via  quot Provider OraOLEDB Oracle Data Source     etc quot     The problem was a number of decimal numbers in the Oracle data column sent to Excel 2010  When I used Oracle SQL query ROUND grosssales eur 2  AS grosssales eur  it worked fine

User · Answer

Going on the assumption you re using TNSNAMES naming  here s a couple of things to do    Create modify the tnsnames ora file in the network admin subdirectory associated with OraHome90 to include an entry for your oracle database      gt  SERVICENAME alias    gt      DESCRIPTION    gt       ADDRESS    PROTOCOL   TCP  HOST   HOST XYZi com  PORT   1521    gt       CONNECT DATA    SERVICE NAME   SERVICENAME      This is assuming you re using the standard Oracle port of 1521  Note that servicename alias can be any name you want to use on the local system  You may also find that you need to specify  SID   SERVICENAME  instead of  SERVICENAME SERVICENAME     Execute tnsping servicename alias to verify connectivity  Get this working before going any further  This will tell you if you re past the 12154 error  Assuming a good connection  create an ODBC DSN using the control panel  specifying the ODBC driver for Oracle of your choice  generally there s a Microsoft ODBC driver at least  and it should work adequately as a proof of concept   I ll assume the name you gave of DATASOURCE  Use the servicename alias as the Server name in the ODBC configuration  At this point you should be able to connect to your database via Access  I am not a VB programmer  but I know you should be able to go to File- Get External Data- Link Tables and connect to your ODBC source  I would assume your code would work as well

User · Answer

Arrhhh   I RAN INTO THIS AGAIN     Just install ToadForOracle in C   or any directory without parenthesis in the path     In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers   I am using SQL Reporting Services with an Oracle Database  The problem is the brackets in the path to Visual Studio  BIDS   Oracle doesn t like apps that start in a path with brackets   RDBMS 10g XE problem with parenthesis in path  So I made a BAT file to open Visual Studio with Progra 2 as the short path name for  Program Files  x86     Here is the contents of the BAT file   rem Progra 2 is short path name for  Program Files  x86   and works around an Oracle client bug that doesn t like the    s in the path start  B  C  Progra 2 Microsoft Visual Studio 9 0 Common7 IDE   C  Progra 2 Microsoft Visual Studio 9 0 Common7 IDE devenv exe    I name this BAT file StartBIDS BAT and put it in the directory        C  Program Files Microsoft SQL Server Start BIDS bat    Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon  This allows me to open TOAD  Visual Studio  BIDS and etc apps that I use to work with Oracle   Update   Alternatively make a Junction    mklink  J  C  Program Files  x86     C  Program Files x86     Then remove the brackets in the shortcut

[oracle] Oracle ORA-12154: TNS: Could not resolve service name Error?

Examples related to oracle

Examples related to odbc

Examples related to tns