Problems With Linked Server To ORACLE (on SQL 2005 64bit)
Jan 26, 2007
Hi all
I have a big problem with creating a linked server to ORACLE database!
- I have installed ORACLE Client 10g on my server (Windows Server 2003 64 bit)
- With ORACLE Tools I am able to connect to the ORACLE Server
- When I try to connect in SQL Server I get the following error:
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
"MISPROD.WORLD".
I have tried I guess all combinations of getting data out of ORACLE ... :-(
For example:
- SELECT * FROM OPENQUERY([MISPROD.WORLD], 'SELECT * from ORACLE_TABLE')
- SELECT * FROM [MISPROD.WORLD]...ORACLE_TABLE
I allways get the same error and I dont know what to do ...
Would by very very happy for any comment.
Best regards
Frank Uray
View 1 Replies
ADVERTISEMENT
Apr 29, 2008
We have recently migrated from SQL 2000 (32bit) to SQL 2005 on Win2003 (both are 64bit). And we have Legacy DTS packages on SQL 2005, some packages are pulling records from Oracle 10g (32bit server). When I execute the DTS package I am getting following error.
Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle
Did I need to install/update any MS OLE DB driver for Orace.
Some blogs & forums suggesting to install Oracle 10g Client. If so Which version of Oracle client(32bit/64bit) I have to install?.
Thanks.
Dhans
View 1 Replies
View Related
Mar 14, 2007
Hello all.
I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.
We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:
=====================
OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".
Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".
=====================
A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.
Thanks,
Adam
View 3 Replies
View Related
Jan 23, 2007
I am trying to pull data from an Oracle 9i database to my SQL Server 2005 sp1 database on Windows 2003 64bit Itanium server. Each time I try to use the import wizard, select the Microsoft OLE DB driver for Oracle, I get an error message telling me that the Oracle client tools are not installed. This happened after I had already installed the 9i tools, tested the connection using TNSPING and confirmed the tnsnames file was correct. So, I uninstalled the 9i tools and then installed the 10g client tools. Tested everything after the install and then tried the import again selecting the MS OLE DB driver for Oracle. I again got the error message that the Oracle tools were not installed.
has anyone ran into this problem and if so, were you able to fix it?
View 2 Replies
View Related
Dec 17, 2007
Hello,
How can I link server to a 32bit ORACLE from a 64bit MSSQL?
LiJun
View 1 Replies
View Related
Jul 30, 2007
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
View 15 Replies
View Related
Oct 26, 2006
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.
View 1 Replies
View Related
Jun 6, 2007
I just installed the 64bit Vista and than wanted to install the 64bit SQL 2005 Std. Server on it.
Note: I downloaded my license from https://licensing.microsoft.com/eLicense/L1033/ProductDetail.Asp?SKU=228-05338.
Installing from SQLSTDSEL.ISO file results in the warning that for the REPORT- Server the .NET version has to be 32bit.
Stopping the installation at this point and try to install from SW CD SQL Svr Standard Edtn 2005 64Bit X64 English #1 x64 MLF.x11-57664 + SW CD SQL Svr Standard Edtn 2005 64Bit X64 English #2 x64 MLF.x11-57665 does not give the above error but on the other hand installs no server at all since somehow the installation routine is not working at all.
Installing now - after at least analysis server and reports server are 64bit and installed - from the first mentioned ISO results in the installation of the DB- Server.... but as a 32bit installation.
Now I wonder what did not work out - so maybe someone here has an idea if and how this problem is solveable.
View 1 Replies
View Related
Jan 11, 2007
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?
View 3 Replies
View Related
Mar 4, 2007
Dose any one have a simple workaround to implement a connection fromSQL 2005 SSIS 64 bit to Oracle 10 G
View 3 Replies
View Related
Oct 2, 2007
We have a 64bit installation of SQL Server (SSIS, SSAS) running and working fine. There is a requirement to install 64bit SSRS on this existing setup.
The problem is: While running the SQL Server Setup wizard, the Reporting Services checkbox remains greyed out. In other words, there isnt an option to select SSRS for installation.
So the question is: Is it even possible to install 64bit SSRS on an existing 64bit SQL server installation - given that SSRS wasnt installed during the first pass.
PS: The SQL Server is SP2 on a Windows Server 2003 configured as an App Server.
View 9 Replies
View Related
Apr 24, 2007
This may seem like a completely foolish question, but is there any issue in using the 32bit workstation components (on XP) to connect to the 64bit server-side components? New to SQL2K5 and never had any issues using the 2K workstation/server components together.
Thank you!
View 3 Replies
View Related
Nov 14, 2006
Hi,
I'm having problems installing SP1 on one of our servers.
If I try to install the x64 version of SP1 setup says it can not update my server and I should use x86 version. Trying to install the x86 version results in the message I should use x64 version....
Any ideas?
Regards, Jan
View 7 Replies
View Related
Jul 11, 2007
Hi,
I have implemented a application on SQL Server 2005, this application runs huge queries and some stored procedures. Now that client is shifting on SQL Server 64bit edition, what should i need to worry about. What all changes am i expected to handle.
I would also like to know if there are any other issues to be considered.
Thank You.
STG Labs
View 6 Replies
View Related
May 20, 2008
Hi,
Recently, we have migrated our database from 32bit to 64bit. So far the application is working fine.
But I want to confirm that by any tool or automated process.
Is there any way or tool to check that the migrated data is correct and also to check if migrated database objects will work fine in 64 bit environment.
Any timely help would be greatly appreciated !!
Thanks,
Bala
View 4 Replies
View Related
Oct 2, 2007
We have an application where part of the database is used for searching products and is very processor intensive due to the number of permutations and calculation that are required. This section of the database is scaled out by using transactional replication so that the €œsearch€? load is removed from central database€™s daily operations.
Each €œsearch€? server can handle around 15,000 search an hour with SQL taking about 3 seconds to return the result in XML (the system has be optimised to squeeze out every last bit of performance). This is in a 32bit environment. One other point, which is probably relevant, is that the Search data only totals 500MB.
With the possibility of upgrading these severs in the near future would we see any performance benefits in going 64bit. I know we won€™t take advantage of increased memory access due to the size of the search data but is there any discernable performance advantage between 32bit v€™s 64 bit processors and SQL Server 2005 64Bit. Or should I stick with SQL Workgroups and 32bit processor and save on the Standard SQL licence?
Many thanks
View 5 Replies
View Related
Dec 17, 2007
Hello
I'm trying to use application designed for 32bit operating system on 64bit (Longhorn beta 2). This application requires SQL Server CE 2005, I installed it (x32) but the application still says that it isn't.
What can be the problem? I suggest the application cannot retrieve information about SQL Server CE installation. But maybe the reason is I need to install something like SQL Server CE for 64bit operating system? Is it present "in native"?
View 3 Replies
View Related
Jul 15, 2002
Hi:
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
View 1 Replies
View Related
Jul 20, 2005
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 Related
Jul 20, 2005
Hello,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 Related
Jul 20, 2005
Hello 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 Related
Oct 10, 2007
Anyone 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
Oct 23, 2007
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
View 6 Replies
View Related
Aug 22, 2005
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 Related
Oct 4, 2007
We recently migrated our 32bit instance of SQL Server 2005 SP2 to a 64bit instance of SQL Server 2005 SP2. The migration was performed by copying all .mdf and .ldf files (including master, tempdb, etc...).
Everything seems to have worked nearly flawlessly except for a few glitches seeming to relate to the master database. I am hoping there is a way to mitigate these issues without reinstalling and performing the migration again.
First, when attempting to view the Activity Monitork, I get the error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor)
View or function 'master.sys.dm_exec_sessions' has more column names specified than columns defined. (Microsoft SQL Server, Error: 4502)
Second, the procedure: master.dbo.sp_procedure_params_rowset fails because of the line: option (OPTIMIZE CORRELATED UNION ALL).
Like I said, I know this is probably an issue arising from copying the master database, but if it is at all possible to avoid another migration, I want to.
Mike Thomas
Software Developer
The Master's College
View 14 Replies
View Related
Feb 11, 2007
I am about to work on a SQL server 2005 migration and I have no clue what SQL server 2005 is as I have been working on 2K all along.
Fist of all I am not sure if the given box is having 64bit or 32 bit Operationg System?
Second of all I am trying to find if the SQL server 2005 installed on this box is 32bit or 64bit?
Thanks in advance,
sk
View 3 Replies
View Related
Apr 17, 2007
I have a problem with DTS - ActiveXScripting that it cannot initialize the script engine, I have the backward compatibility stuff installed, even tried a repair but still does not work.
Many thanks for any help you can provide on this problem.
Paul
Error Information below:
Date 17/04/2007 13:07:32
Log Job History (Test ActiveX Script)
Step ID 1
Server GBCONV1A002V03
Job Name Test ActiveX Script
Step Name Step 1: Exec TestActiveX script
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: WW004ConSrvSQLAgentA002. ...TSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220487 (800403F9) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220487 (800403F9); Provider Error: 0 (0) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSActiveScriptTask_1' failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: ... Process Exit Code 1. The step failed.
View 3 Replies
View Related
Apr 5, 2006
Hi
I have the following configuration:
- Server with 8 AMD Opteron Processors
- 24 GB Memory
- Windows Server 2003 64 bit
- SQL 2005 64 bit
- EMC Storage
The main instance is defined to use 7 Processors and
has about 10 databases.
The problem is:
When I run a "simple" Query (DISTINCT or GROUP BY) on a Table
with something like 1 million rows, SQL 2005 is very very slow !!!
The same query is on a SQL 2000 about 1000times faster ...
Does anybody has some ideas how to configure the system
to make it more useful ... ??
Thanks for any comment.
Best regards
Frank Uray
View 8 Replies
View Related
May 30, 2001
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 Related
Aug 10, 2001
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.
View 2 Replies
View Related
Feb 2, 2001
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
View 3 Replies
View Related
Jan 20, 2000
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
View 1 Replies
View Related
Jun 6, 2002
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
View 2 Replies
View Related
