Problem Making Oracle Linked Server Work
Jul 23, 2005
Hi all,
I set up our Oracle Financials as a linked server to one of my SQL
Server boxes. On running a test query, I got the following error
message:
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'TEST_NUM'
(compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reported
to have a
DBTYPE of 130 at compile time and 5 at run time].
The Oracle datatype of the column with the supposedly inconsistent
metadata was NUMBER, which according to the Oracle OLE DB documentation
actually maps to 139. 130 is a null-terminated unicode character
string, 5 is a float, and 139 is a variable-length, exact numeric value
with a signed scale value. Oracle NUMBER is an all-purpose numeric
type, apparently they use that instead of int, float, etc. The Oracle
guy used it for this column in the test table because in OF it is used
in pretty much every table. (For starters it is the datatype of their
identity columns.)
There is something in the OLE DB spec about all datatypes having to be
able to be expressed as DBTYPE_WSTR (130), but what I don't get is that
I can connect to the same Oracle instance using VB6 code and the
MSDAORA provider and there is no problem at all interpreting the NUMBER
columns. So why does it work from VB and not as a linked server? And
much more importantly, HOW DO I MAKE THE LINKED SERVER WORK?
TIA
View 11 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
Sep 13, 2007
Hi,
I just found a procedure to make pivot tables,But iam getting an error,can someone help in Overcoming the error.
The syntax is as below
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql= @sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
just run the procedure and help me to fix the error.The error iam getting is
Msg 156, Level 15, State 1, Procedure crosstab, Line 23
Incorrect syntax near the keyword 'pivot'.
Any help is greatly apprecited.
Thanks,
SVGP
View 6 Replies
View Related
Apr 23, 2007
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
View 5 Replies
View Related
Jan 19, 2006
I'm having a very difficult time getting any type of transactions to work without encountering blocking.
Here's what I have. I created a package using the Import data option on a database to start with. That gave me the following:
On the control flow tab there is a Preparation SQL task which truncates a table followed by a Data Flow Task that then copies from an oracle database into that same table.
The two are connected by the green success arrow.
For obvious reasons, if the import from oracle fails, I would like the truncate of the table to be rolled back so that the table in question is returned to its previous state.
I have tried the following:
Setting the transactions setting on the data flow panel to "supported" and changing it on the Control Flow tab to required.
Setting both to required.
Setting the data flow setting to "supported", then adding a sequence container onto the control flow panel, moved both the preparation and data flow tasks into it and then set the sequence container to "required".
In all cases I have installed the package onto the server and then scheduled a job in SQL Server agent to run the package.
No matter what way I do it, the preparation sql task apparently opens a transaction and then the following data flow task starts a different one and is blocked by the first one. If you check a log file I set it to generate it clearly gets stuck after the sql preparation job when trying to start the data flow task. Checking sql server itself (Activity monitor), the job is stuck because it is blocked by process "-2".
So I'm lost as to how to make one single transaction be used for the entire package and get the behavior i need....
Thx.
R-
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
Feb 20, 2005
I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.
In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().
However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.
Here's some particulars:
---
EXEC sp_addlinkedserver
@server='VFP',
@provider='VFPOLEDB',
@datasrc='\hdmcpdctis1 isrnddata',
@srvproduct='Visual FoxPro'
--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go
--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \hdmcpdctis1 isrnddata ',
'select * from [tislists.DBF]')
With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================
Any Help is greatly appreciated! Thanks,
peter :confused:
View 5 Replies
View Related
Oct 4, 2007
Hello.
Suddenly OPENQUERY had started to raise an exception 'Msg 7355, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "sql2000" supplied inconsistent metadata for a column. The name was changed at execution time.'
The OPENQUERY stament looks like:
Code Block
SELECT * FROM OPENQUERY(sql2000, 'execute dbo.sc_List @AmountId = 24, @WorkMode=1, @SortMode=0')
I don't know what has been changed - seems that everyting is OK.
The problem is that sql2000 is MS SQL 2000 server and this statment is executed on the MS SQL 2005 server.
dbo.sc_List is a procedure that selects data and I need insert that data to the temporary table on the sql2005 server to make some calculations.
I've used OPENQUERY to prevent DTC coordinator from starting distributed transaction.
Whole statement originally looks like
Code Block
INSERT INTO #Templates SELECT * FROM OPENQUERY(sql2000, 'execute dbo.sc_List @AmountId = 24, @WorkMode=1, @SortMode=0')
where #Template is temporary table with columns that exactly match with stored procedure output.
Help me please - what is wrong with sql servers? I know that this query has been working fine for a months and now - such strange errors. I should say also that strored procedure returns always the same columns when called - there is one large select inside it.
Thank you.
s w
View 4 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
Jul 23, 2005
Hi,I am using MSSQL 2k, and I have a linked server set up to an Oracle RDBversion 7. It goes thru an OLE DB provider for ODBC drivers on a systemDSN, which is using an Oracle RDB ODBC driver version 3.0.2.The problem occurs when I send a query that returns zero rows - queryanalyzer just does not complete nor return. This problem is not seenwhen 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 incorrectvalue for DBPROP_CONCATNULLBEHAVIOR which should be eitherDBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULLAnybody who experienced this before has a solution?Thanks,Lawrence
View 2 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
Jun 28, 2007
Hi,
I just upgraded to SQL server 2005 and some of my Openrowset to Oracle doesnt work. I found out that the issue occur with field with structure Numeric.
The issue is not solve even when I try to cast the field to varchar. sometimes it succeeded and sometimes I get error 7320 & 7321.
I'm using Oracle connector from SQL to Oracle.
I've 64 bit SQL Server.
Attach the code, the Numeric field is SCORE.
select *
FROM
OPENROWSET('ORAOLEDB.ORACLE',aaaa;bbbb;cccc,
'SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, MAJOR_CODE,
CAST(SCORE AS Varchar(250))
FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2')
Anyone can advice on resolution?
Thanks,
Assaf
View 6 Replies
View Related
Jun 21, 2006
Hi,
The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.
OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
View 7 Replies
View Related
May 24, 2006
I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.
I changed the Active Directory Group login to an individual active directory login and the connection worked fine.
I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?
View 1 Replies
View Related
