I need to link some data from SQL Server 2005 with Oracle 10 data.
One way is to link Oracle server to SQL Server and use ROWSOURCE for
retrieving data.
What other ways for joing data from both databases exist ?
Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objects
shall I use for opening database and running an sql ?
Thanks a lot.
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.
I run a query using an Oracle Linked Server.
This seems to run forever.
SELECT * FROM LinkedServer..Schema.View WHERE TN= '2034561295'
...and any other field in the where clause work fine.
SELECT * FROM LinkedServer..Schema.View WHERE SPID= '8088'
Basic description:
We have developed a solution that sends data from SQL Server to an Oracle server as the result of a stored procedure called by a job that runs every minute. While this development worked fine in our test environment, after moving it to production it ran successfully the first minute, but the second minute the stored procedure hung, and the process could not be killed. In order to stop the process I had to stop both the SQL Agent and the MSDTC services.
Our SQL Server box:
SQL Server 2000 Standard Edition SP4
Windows 2003 Server R2 SP1
Our Oracle box:
Test: Oracle 9.2.0.6
Production: Oracle 9.2.0.4
To setup the SQL box, I did the following:
1) Install Oracle Client Tools version 10.2.0.1
2) Restart Server
3) Modify the registry as follows:
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI] "OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll" "OracleOciLib"="oci.dll"
4) Modified the PATH variable so that all references to SQL Server appear in front of Oracle path references
5) Added the linked server via sp_addlinkedserver '<tns name>','Oracle','MSDAORA','<tns name>'
6) Added linked server logins via sp_addlinkedsrvlogin '<tns name>','False','<SQL user>','<Oracle User name>','<password on oracle>'
7) Changed the registry for MSDTC to match this:
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTC]
"AllowOnlySecureRpcCalls"=dword:00000000
"FallbackToUnsecureRPCIfNecessary"=dword:00000001
"TurnOffRpcSecurity"=dword:00000001
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCSecurity]
"NetworkDtcAccess"=dword:00000001
"NetworkDtcAccessAdmin"=dword:00000001
"NetworkDtcAccessClients"=dword:00000001
"NetworkDtcAccessTransactions"=dword:00000001
"NetworkDtcAccessTip"=dword:00000001
"XaTransactions"=dword:00000001
"DomainControllerState"=dword:00000000
"AccountName"="NT Authority\NetworkService"
"NetworkDtcAccessOutbound"=dword:00000001
"NetworkDtcAccessInbound"=dword:00000001
8) Stopped and restarted services in the following order:
1) MSDTC Stop
2) SQL Server Stop
3) MSDTC Start
4) SQL Server Start
The stored procedure:
In a single transaction, the stored procedure compares a production table against a logging table. If a record exists in the production table that is not in the logging table, a record is inserted into logging table, and a record sent to Oracle via an INSERT INTO OPENQUERY('INSTANCE','SELECT Column1, column2, column3,... FROM SCHEMA.TABLE')
SELECT column1,column2,column FROM SQLTable
This stored procedure has worked just fine for us in test, to either the test or production Oracle boxes, but it now fails, and hangs, in production to either the test or production Oracle boxes.
Additionally, I can run the following query via Query Analyzer from our test box to both the test Oracle and production Oracle and it runs successfully (this is NOT used in our stored procedure code, but is presented here as an indication that I think there is something wrong with the settings on our production SQL box):
SELECT * FROM OPENQUERY('INSTANCE','SELECT * FROM SCHEMA.TABLE')
When I run this same query via Query Analyzer on our Production SQL box, to either the test Oracle or production Oracle, it hangs, and I have to kill the process, and restart the MSDTC service.
Other queries that hang are:
SELECT * FROM SERVER..SCHEMA.TABLE
Additionally, I noticed that when I used this method to kill the process I would see errors like the following in the Application Event Log on the SQL box:
The XA Transaction Manager attempted to perform recovery with the XA resource manager. The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll.
Since I figured this was an aborted transaction still residing in the MSDTC log file, I would stop the MSDTC service, delete the MSDTC log file, reset the MSDTC log, and then restart the MSDTC service in order to prevent this error from occurring.
Not ALL queries from the production SQL box to production and test Oracle boxes fail. I can get results returned for this query:
SELECT COLUMN1, COLUMN2 FROM SERVER..SCHEMA.TABLE
I've been scouring the internet for about a week now, and I've run out of ideas on what to check on the production SQL box. Any suggestions would be greatly appreciated.
Tim
When I perform a query on a linked Oracle server in the Query analyser Ihave noprboblem' to perform this query.However, when I create this query in a stored procedure I get a compilationerrorwhen saving this procedure. (Not when compiling; it has no errors)Server: Msg 7405, Level 16, State 1, Line 1Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics.Enable these options and then reissue your query.When I create a dynamic SQL statement then I can save this stored procedurewhen I run the stored procedure this same error happens.What do I have to do.Arno de Jong, The Netherlands
View 1 Replies View Related
I finally have my server linked, figured out my date issed with the decode statement and now I get a missing Expression error from Oracle.
Here is the statement:
SELECT *
FROM OPENQUERY(PROD_ORACLE,'
SELECT LEFT(CUST_ORDER_STUS_NME, 25) as CUST_ORDER_STUS_NME,
LEFT(SRVC_ORDER_STUS_NME, 25) as SRVC_ORDER_STUS_NME,
LEFT(Ser_ORD, 20) AS Ser_ORD,
LEFT(CUST_ORDER_NME, 25) as CUST_ORDER_NME,
LEFT(SRVC_ORDER_NME, 25) as SRVC_ORDER_NME,
LEFT(CLS_ALLOWED_NBR, 2) as CLS_ALLOWED_NBR,
LEFT(NOC_TO_NOC_NME, 3) as NOC_TO_NOC_NME,
LEFT(CHARS_ID, 20) as CHARS_ID,
LEFT(PRI_DNS_QTY, 5) as PRI_DNS_QTY,
LEFT(SCNDY_DNS_QTY, 5) as SCNDY_DNS_QTY,
LEFT(ORDER_TYPE_CD, 10) as ORDER_TYPE_CD,
LEFT(ACTY_NME, 25) as ACTY_NME,
replace(LEFT(CUST_A_NME, 50), ''|'', ''-'') as CUST_A_NME,
LEFT(RLAT_CKT_ID, 8) as RLAT_CKT_ID,
DECODE (BILL_CLR_DT, GREATEST(BILL_CLR_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), BILL_CLR_DT, NULL),
DECODE (CMPLT_DT, GREATEST(CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), CMPLT_DT, NULL),
REPLACE(replace(LEFT(CMNT_TXT, 1000), char(10), '' ''), ''|'', ''-'') as CMNT_TXT,
LEFT(CUST_H1_ID, 9) as CUST_H1_ID,
LEFT(CUST_CMS_ID, 8) as CUST_CMS_ID,
LEFT(Circuit_ID, 30) as Circuit_ID,
DECODE (SO_CMPLT_DT, GREATEST(SO_CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), SO_CMPLT_DT, NULL),
LEFT(DNS_ADMIN_NME, 30) AS DNS_ADMIN_NME,
LEFT(DNS_ADMIN_PHN_NBR, 15) AS DNS_ADMIN_PHN_NBR,
LEFT(DNS_ADMIN_EMAIL_ADDR, 128) AS DNS_ADMIN_EMAIL_ADDR,
LEFT(CONTACT, 30) AS CONTACT,
LEFT(GOVT_CD, 10) AS GOVT_CD,
LEFT(SOTS_ID, 30) AS SOTS_ID,
LEFT(RAS_USER_BLK_NBR, 20) AS RAS_USER_BLK_NBR,
LEFT(VSYS_QTY, 10) AS VSYS_QTY,
LEFT(ZONE_QTY, 10) AS ZONE_QTY,
LEFT(PLCY_QTY, 10) AS PLCY_QTY,
LEFT(PIC_CODE, 6) AS PIC_CODE,
LEFT(SO_Order_Entry_Nbr, 15) as SO_Order_Entry_Nbr,
LEFT(SO_Rlat_Order_Entry_Nbr, 15) as SO_Rlat_Order_Entry_Nbr,
'' '' as filler
From PROD_ORACLE..RDBADM.CUST_SRVC_ORDER_V')
Here is Query Analyzers response:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'OraOLEDB.Oracle'.
[OLE/DB provider returned message: ORA-00936: missing expression]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare:repare returned 0x80040e14].
ANY help is greatly appreciated!!
Hi,
I created a linked server as follows:
EXEC sp_addlinkedserver 'OracleLinkedServer', 'Oracle', 'MSDAORA', 'fcstage'
EXEC sp_addlinkedsrvlogin 'OracleLinkedServer', false, 'SA', 'fc_stage', 'password'
Now I try firing a simple select statement
SELECT FINANCIAL_TRX_INFO_ID FROM
[OracleLinkedServer]..[FC_STAGE].[WFS_FINANCIAL_TRX_INFO]
WHERE SFS_BUSINESS_SEGMENT IS NOT NULL
But I get the following error:
OLE DB provider "MSDAORA" for linked server "OracleLinkedServer" returned message "ORA-01426: numeric overflow
".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OracleLinkedServer".
This seems to be a generic error statement. Can anyone tell me where am I going wrong.
Thanks.
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
View 4 Replies View RelatedWe have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
Hi,
I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.
Please let me know.
Thanks
I wanna to create a linked server which connect to oracle. Here are my steps:
1.Install Oracel drivers Oracle Data Access Components.
2.Configure the Oracle Ole DB provider to run inside the SQL Server process and configure it to accept parameters. exec
master.dbo.sp_MSset_oledb_prop
'ORAOLEDB.Oracle',
N'AllowInProcess', 1
exec master.dbo.sp_MSset_oledb_prop
'ORAOLEDB.Oracle',
N'DynamicParameters', 1
3.Create linked server with scripts:
exec sp_addlinkedserver N'MyOracle1', 'Oracle', 'ORAOLEDB.Oracle', N'//10.154.14.235', N'FetchSize=2000', ''
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'root', @rmtpassword='Sqlexp!23'
4. After create successful and testing the connection ,I got the error below.(My windows firewall is turn off)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle".OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA". (Microsoft SQL Server, Error: 7303)
I am new to sqlserver.
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
Any ideas thnx.
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
Hi ,
I am having 2 data store .
1. Oracle 10g
2 SQL server 2000
My requirement is that , i need to insert some data from sql server database table to oracle database using sql server query analyser or interface.
If there is any way ,plz let me know it
Thanks
Abraham
I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password
I can then see the tables in EM, but when I try to query them in QA I get this
Select * from db2db...customer
Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'
I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server
My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data
any ideas ? I think I need to enter something in the catalog box, but what ?
thanks in advance.
I am trying to run a select statements against linked server.
I have NT server running SQL7 Enterprice and Rumba2000. I used Rumba driver to configure a DSN (ODBC based) to a AS400/DB2. Then I created a linked server. Good news are : I can see a list of tables.
Bad news : when I am trying to run a select statement either through stored procedures or directly in SQL analyzer I am receiving error message Object 'linkedservername.databasename.tablename' doesn't exist.
What am I missing here?
Hi,
Please bear with me if my vocabulary or phraseology is wrong (and if it is, I wouldn’t mind being corrected).
Background:
We have an application that uses a Cache database (which is a complete black box for me). The data is exposed to us through ODBC. We have set up a linked server that I can see the names of the tables and views in Enterprise Manager and can query on them with Query Analyzer.
Problem:
There are a lot of extra long string fields. I can see the data in both Crystal Reports and Access (in Crystal they are shown defined as sting [32395] and in Access they are shown as a Memo field).
I cannot get Query Analyzer to display the data. I have tried the following select statements:
select definition_text from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as ntext) from cwsavpmtest_live..system.problem_def_data
select convert(ntext,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as text) from cwsavpmtest_live..system.problem_def_data
select convert(text,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as varchar(8000)) from cwsavpmtest_live..system.problem_def_data
select convert(varchar(8000), definition_text) from cwsavpmtest_live..system.problem_def_data
… and get the same error for every one:
quote:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[problem_def_data].definition_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
So my question is: Am I out of luck and have to be content with looking at the data in Access or Crystal or is there some way I can look at these fields through Query Analyzer?
Thanks,
Laurie
I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it
View 4 Replies View RelatedHi:
I configured SQL Server 7.0 by Linked Server to a Oracle db. What I found was I could select data from SQL Enterprise Manage but fail to insert any data through Query Analyst from SQL Server to Oracle. The platform for the SQL Server is NT 4.0 SP6.
Could you point out what I missed while inserting data from Query Analyst to Oracle?
Thank you very much.
Rick
Hi,I have an Oracle (8.1) & a SQL Server 2000 database withProduction data. There are situations when I need data from both thedatabases. My first choice was to link Oracle to SQL and run DTSovernight. But this would have a 1 day latency not to mention the timeit would take.1. Has any one tried real time access via Linked server to Oracle?How good is the performance?2. The Oracle db is fairly big, so I'm kinda not in favor ofcopying the whole thing over into SQL overnight. Is there an easierway to just get only the changed records from Oracle?3. Is there a better solution to this?4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamicallychange the SQL that you pass it? e.g. can the query accept aparameter?Thanks in advanceSudhesh
View 14 Replies View RelatedHello,I have a linked server to oracle 7.1 from SQL 2000. when I try toexecute simple select statement which returns one row of data usingopenquery is not fetching the data. After 30 minutes SQL Queryanalyser is hanging. This is happening on the production server and itis working from last 6 months. I have tried by deleting and recreatingthe linked server, but no use. Please helpThanks,Regards,Pardhasaradhy
View 2 Replies View RelatedHello all,Having problems connecting to an Oracle 9i database from withinSQL/Server 2000 using the Security/Linked Servers feature.Server1 (SQL/Server)-----------Windows Server 2003, Standard editionMS SQL/Server 2000Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management toolsMicrosoft ODBC for OracleOracle OLEDBMDAC 2.8 RTMServer2 (Oracle)-----------Windows 2000 - Advanced ServerOracle 9i database (v9.2.0.1.0)Two nodes clustered using Microsoft cluster manager. (Nodes areDATABASE01 & DATABASE02 - Cluster is WMCLUSTER)When I try to connect to the linked server in Enterprise Manager I getthe following error messages.Error 7399 OLE DB provider 'MSDAORA' reported an error. Authenticationfailed.OLE DB error trace [OLE/DB Provider 'MSDAORA'IDBInitialize::Initialize returned 0x80040e4d: Authenticationfailed.].From within Query analyzer I get a slightly different messagereporting that the username/password are incorrect.dbcc traceon(7399)select * from TURLIVE..SONICA.INV_LOCServer: Msg 7399, Level 16, State 1, Line 3OLE DB provider 'MSDAORA' reported an error. Authentication failed.[OLE/DB provider returned message: ORA-01017: invalidusername/password; logon denied]OLE DB error trace [OLE/DB Provider 'MSDAORA'IDBInitialize::Initialize returned 0x80040e4d: Authenticationfailed.].I know the username/password combination is correct and I can usethese from with Oracle enterprise Manager with sucess.TURLIVE is the name I've given the linked server, SONICA is the nameof the schema on the Oracle database and INV_LOC is a valid table.TURLIVE is also the name of the database instance on Server2.Steps taken so farInstall Oracle client tools (Enterprise Manager, Net manager etc) onServer1.Setup an entry in TNSNAMES.ORA to the cluster that has the Oracledatabase. e.g.TURLIVE =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = TURLIVE)))This works fine, I can connect via Oracle Enterprise manager and I canTNSPING WMCLUSTER, DATABASE01 & DATABASE02.Configured an ODBC source to TURLIVE.On Server1 I've configured the linked server using the following SQL.sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'(password blanked)I then rebooted Server1The properties of the new linked server are:Product name = OracleData Source = TURLIVEProvider String = blankI've modifed the registry on Server1 as instructed by a Microsoft KBarticle.HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI OracleXaLib = "oracleclient8.dll"OracleSqlLib = "orasql8.dll"OracleOciLib = "oci.dll"Still no luck. Can anyone please point out he bleeding obvious? :-)Thanks in advanceAs an aside, has anyone ever configured a linked server to an OracleRdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?Are there any HOWTO guides for this type of connectivity?CheersDave.
View 3 Replies View RelatedAnyone using a SQL Server Linked Server to access Oracle RDB on a VMS/Alpha? What connection are you using? What is reuqired? I am using OLE DB Provider for ODBC to access an ODBC connection I setup using the Oracle ODBC driver for Oracle RDB 2.1 and it seems slow and limited. You can only use the OPENQUERY method to access data, etc.
View 1 Replies View Related
We're having some trouble connecting SQL Srvr2005 to Oracle (10g) in our 64-bit environment. We have Oracle's 32-bit Ole Db provider installed and have been able to use that in SSIS to connect and return some data running in 32-bit debug mode (as long as the query isn't long, complicated or using parameters =)
However, we have some hefty queries that we need to gather some data and want to put them in a stored proc on Oracle and call it through SSIS. We tried adding our Oracle DB as a linked server and are getting some errors. I've tried the following code replacing the provider with 'OraOLEDB.Oracle.1', 'OraOLEDB.Oracle' as well as 'MSDAORA':
EXEC sp_addlinkedserver
'ORA_TSTW', 'Oracle',
'OraOLEDB.Oracle.1', 'TSTW'
EXEC sp_addlinkedsrvlogin 'ORA_TSTW', false,
'user', 'lituser',
'pw'
select * from openquery(ORA_TSTW, 'select * from lituser.customer')
We keep getting the error for each of the providers we try. I've tried it in Management Studio as well as in an Exec SQL task (with debug set to 32-bit) to no avail.
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle.1" for linked server "ORA_TSTW".
I've checked and the MSDAORA.dll is registered in both C:Program Files (x86)Common FilesSystemOle DB as well as C:Program FilesCommon FilesSystemOle DB.
If we can't where we can call an Oracle stored proc, we were thinking of just using a script task to connect to Oracle and retrieve the data we need, but I have a feeling performance this way would be degraded.
Anyone have thoughts/suggestions? TIA!
Sabrina
I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
View 3 Replies View RelatedHello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
I need to push 40k rows daily to an Oracle database. I am on a Win2k Pro box with the 8.1 client and ADO 2.6 loaded. I've configured the linked server with the Oracle as well as the Microsoft provider and it takes over a minute to push just 24 rows, less than 100 bytes each, at it. Any suggestions?
View 1 Replies View RelatedHi All - does anyone have a step by step guide as to what needs to be set up on the Server from the Oracle side, for Oracle linked servers to work ??
I keep getting the Eror 7399 ; OLEDB provider 'MSDAORA' reported an error.
Thanks,Jazz.
Does anyone know how to link an Oracle (8.0.6.0) database to a MS SQL 7.0 db? I have set up the ODBC and it works with no problem. I can get tables from the oracle db and bring them into MS access with ODBC so I know that it is set up correctly. I just cant get visability to the db in a sql 7 environment. Any help or sugestion would be appreciated.
Thanks
-NES
Can someone please tell me what oracle software needs to be installed on a sql server machine that will link to an Oracle database on an NT4 machine.
Also can you also confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I have read about people having prolems executing stored procedures with arguments using the OPENQUERY Function.
Thanks
Anyone have any suggestion on increasing the performance going over a linked server? IS this connection persistent? Or does it establish a connection with each reference? Any other Providers for the connection other than Microsofts OLE DB Provider for Oracle? I imagine that building index's on the referenced oracle tables would help?
Any help, input or suggestions would help.
David
Hi All - does anyone have a step by step guide as to what needs to be set up on the Server from the Oracle side, for Oracle linked servers to work ??
I keep getting the Eror 7399 ; OLEDB provider 'MSDAORA' reported an error.
Thanks,Jazz.