MSSQL Linked Server To Oracle RDB
Jul 23, 2005
Hi,
I am using MSSQL 2k, and I have a linked server set up to an Oracle RDB
version 7. It goes thru an OLE DB provider for ODBC drivers on a system
DSN, which is using an Oracle RDB ODBC driver version 3.0.2.
The problem occurs when I send a query that returns zero rows - query
analyzer just does not complete nor return. This problem is not seen
when there are rows being returned.
I ran a trace and this is the error message I get-:
Non-interface error: OLE DB provider MSDASQL returned an incorrect
value for DBPROP_CONCATNULLBEHAVIOR which should be either
DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL
Anybody who experienced this before has a solution?
Thanks,
Lawrence
View 2 Replies
ADVERTISEMENT
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
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
Apr 6, 2007
am i going to cause problems if i install MSSQL2005 on a server with Oracle already installed and running ?
thank you
View 6 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
Nov 27, 2006
Hi Friends,
I want to know if there are a manner of replication(two-way) between MSSQL SERVER and (postgresql or mysql or ORACLE).
Thanks
View 3 Replies
View Related
Nov 7, 2005
I can create a linked server to another MsSQL from MsSQL,but encounter error when create a linked server to MySQL:
Error 7399:OLE DB provider 'MSDASQL' reported an error.
Data source name not found and no default driver specified.
......
Anyone can help me?
Thanks!
View 6 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
Jan 13, 2005
Hi!
I’m having the fallowing error when I try to open/access a linked server
Error:
Error 7302: Could not create an instance of OLE DB provider 'MSOLAP.2'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP.2 returned 0x80004005].
I have already tried to create the server using SEM and also with SQL syntax in ISQLW.
Code:
EXEC sp_addlinkedserver
@server='LINKED_OLAP',
@srvproduct='',
@provider='MSOLAP.2',
@datasrc='MyServerName.MyDomain’,
@catalog='MyDataBase'
I’m allowed to create the linked server but I can’t use it.
Information:
OS: Windows 2003EE
SQL: MSSQL 2000 + SP3a
MDAC: MDAC2.8 rtm
Thanks,
Paulo
View 4 Replies
View Related
Aug 26, 2006
Hi All.
I test OLEDB provider for Interbase/Firebird servers with MSSQL 2005 [standard v9.00.1399.06] and find next problem:
If "Allow InProcess"=No - MSSQL read column data with binary blob through DBBINDING
-----
iOrdinal :12
obValue :0
obLength :0
obStatus :0
dwPart :1 [DBPART_VALUE]
dwMemOwner :0
eParamIO :0
cbMaxLen :2147483647
dwFlags :0
wType :0x80[DBTYPE_BYTES ]
bPrecision :255
bScale :255
---------
This column has flags: 0xEA [MAYDEFER | WRITE_UNKNOWN | ISNULLABLE | MAYBENULL | ISLONG]
Why MSSQL send to my provider cbMaxLen==-1 ???
Columns with fixed-length not has this problem - MSSQL create accessor with correct DBBINNDING's
----
If I configure OLEDB provider with "Allow InProcess"=Yes - All is OK. MSSQL get this column through storage-object.
Sorry for my english
Regards,
Dmitry Kovalenko
www.ibprovider.com
View 1 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
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 3 Replies
View Related
Feb 26, 2008
Hi,I have created a linked server to oracle, which works fine.But when I try and create a view joining the SQL table with the linked Oracle table, it only returns the primary key field in the Oracle table and nothing else.Anyone know why?ThanksN.B. I'm using SQL Server 2005 btw.
View 2 Replies
View Related
Jun 8, 2015
I have a SQL Server 2012 instance set up with a linked server to an Oracle Database. I need to build a stored procedure (single NVARCHAR(6) parameter in: @accountID) which executes a select statement against the Oracle DB.
Everything's working fine when testing with 'EXEC (''<oraclecodeblock>'') at LINKEDSERVERNAME' if I hard code the value of @accountID in <oraclecodeblock> but when I encapsulate it all within sp_executesql and attempt to parameterize @accountID it's throwing ORA-01722 'invalid number' errors on the Oracle DB. The parameterized value is NVARCHAR as are the account numbers.
Working:
DECLARE @accountID VARCHAR(6);
SET @accountID = '10842';
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'EXEC (''SELECT * from A.Accounts WHERE ID = ''''10842'''';'') AT LINKEDSERVER';
[code]...
View 0 Replies
View Related
Jul 8, 2013
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'
View 3 Replies
View Related
Sep 7, 2007
Hello,
I have a problem which I have spent the whole day trying to resolve but with little success...
Here is the situation:
I installed the Oracle Client 9.x.x on to my production MS Windows 2003 Server running MS SQL Server 2005 Enterprise (I need to make a Linked Server connnection to an Oracle database). The problem is if I try to create the Linked Server, I get an "Oracle Client software not installed. You must have the Oracle Client software installed to create connection" message from SQL Server. This is frustrating because the software is installed and I am able to make queries through SQL*Plus to the Oracle Database...
Here is my checklist ---
1) the PATH variable on the Windows Server is set correctly to the proper ORACLE HOME directory (with proper /bin directories set as well)
2) I have an updated MDAC component installed
3) registry configuration are correct for Oracle 9i
4) created test Linked Server connections to various datasources (not Oracle)
I am assuming the tsnames.org file is set up correctly or I would be able to make queries from SQL*PLUS, is this assumption correct?
I am at a lost as to what to try next...
Any information would be greatly appreciated,
Thank you,
mgarrettDev
View 1 Replies
View Related
Aug 11, 2005
I'm currently trying to establish a linked server to an Oracledatabase.Setup:Connecting to 8x version of OracleUsing 9i client tools (Net Manager)SQL-Server 2000Windows 2000I installed the Oracle 9i client tools, and set up a Service for theOracle connection. Testing from the 9i client produces a successfullconnection.I then opened SQL-Server and created a new Linked Server with thefollowing setup:Name - PYR_LinkProvider - Microsoft OLE DB Provider for OracleProduct Name - PYRData Source - PYR (9i Service Name)Provider string - MSDAORAI get the unspecific 7399 error that OLE DB provider MSDAORA reportedan error, trace 'Initialize returned 0x80004005'I've searched through the archives, checked all of the relevantMicrosoft articles I could find, and still have no idea what is wrong.SQL-Server reports that the provider is registered in the system, but Ican't figure out what I'm missing.Help or a guide for troubleshooting would be much appreciated.Tim
View 2 Replies
View Related
Mar 7, 2008
Hello,
I created a linked server to Oracle on SQL-Server 2005. I have stored the remote login username and password in the security settings (option "...Be made using this security context" in the Security tab of the Linked Server Properties.
The "Test connection" function works fine, but a select doesn't for every table the user owns. What we found out is: if the table consists only of columns defined as varchar2, the select works. As soon as the table contains only one column defined as number (without scale and precision), it doesn't work. The error displayed is:
The OLE DB provider "MSDAORA" for linked server "NEXUS_FVADM" supplied inconsistent metadata for a column. The column "WEBVORZUGSTYP" (compile-time ordinal 2) of object ""FVADM"."AKZ"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.
The Column WEBVORZUGSTYP is defined as number.
Any ideas?
Regards,
Ralf
View 1 Replies
View Related
Feb 7, 2008
I have a SQL Server 2005 with a linked server to an Oracle RDB 6.0, using the Microsoft OLE DB for ODBC provider. I would like to select some rows from the Oracle RDB and then Update those specific rows afterwards. Whenever I try to do this in a transaction (BEGIN TRAN) I get an error "Driver not capable". Is there any way to do this in a single transaction?
View 1 Replies
View Related
May 15, 2006
Hello.
I have the following problem:
I set up a linked server in SQL Server 2000 to Oracle with Oracle OLEDB provider. I see all the tables in the linked server so the connection is successful but when I tried to select from some remote table, most often I get the following error:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.Oracle' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
MOREOVER, selecting from some of the tables, returns other errors:
* Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
or:
Server: Msg 7317, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.Oracle' returned an invalid schema definition.
All these errors are very strange to me!
I set up a linked server to the same Oracle database but with Microsoft OLE DB provider for Oracle. And everything just works perfect! (I would use this provider but it does not support distributed transactions so I can't do all my job.)
I just can't understand why the Oracle OLEDB provider is not working properly.
The same situation is happening with another PC in different LAN, with different SQL server, different Oracle database and so on.
Any help will be appreciated! Thanks.
View 7 Replies
View Related
Dec 26, 2003
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected)
Data access (selected)
RPC (not selected)
RPC Out (not selected)
collation name
connection timeout
query timeout
View 1 Replies
View Related
Jun 9, 2006
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.
View 4 Replies
View Related
Aug 7, 2000
I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:
DECLARE @ins_id varchar(7)
DECLARE @sqlstring nvarchar(2000)
Select @ins_id = '123456'
Select @ins_id = convert(int, @ins_id)
select @ins_id
select @sqlstring = 'DELETE FROM OPENQUERY(LinkedServerName,"SELECT * FROM OracleTableName WHERE I_ID = '
+ '' + @ins_id + '' + '")'
select @sqlstring
EXEC sp_executesql @sqlstring
*Note...I had an additional data conversion from varchar to integer in my statement.
I hope this information helps any others who are having trouble performing this type of task.
View 2 Replies
View Related
