.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael


View Complete Post


More Related Resource Links

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

  
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

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server

  

Dear All,

please to try to help me what could be the problem ?. when  i try to excute my stored proc in my sqlserver management studio it excuting fine when i trying to Excute using Asp.net application it giving Error. iam working on Sql Server, mt linked server is Oracle. my proc is like :

select  ID,Name  from OPENQUERY(TEST, ''
SELECT
 ID ,
 Name  from Details)

Error:Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TEST".
OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" returned message "ORA-01017: invalid username/password; logon denied".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.


Please try to guide me i unable run using asp.net App


thanking you,


santhosh


Need Information on pulling data from Oracle Source to SQL Server

  
Hi,   I was told that we will be extracting data from Oracle database and loading into SQL Server Database. I will be developing packages on the Remote Server where SQL Server is installed. I mean I wont be developing anything on my local machine. This is the first time I will be writing a package to extract data from Oracle database and my knowledge on Oracle is zero.   Could someone list out  what all I need to do to extract data from Oracle database ?  Server that I will be working is new and nothing is installed on that except the SQL Server 2008.      

Transforing the Data from Oracle to SQL Server 2008

  
Hi all I have to create a SSIS package which can Transfer the Data from Oracle to SQL Server 2008. Every time when packages run it will suppose to create tables in SQL Server Database. I guess it is possible with export and Import, but the privies tables suppose be deleting before or after dumping.   Suggest me best method to do this.     Thanks in advance     SNIVAS

Oracle 11g R2 64-bit linked server difficulty

  
We are re-integrating a business unit back into IT support this weekend. They have a Oracle production system that's been upgraded to 11G. The previous linked server worked fine. I'm trying to attach from an XP laptop that has a full blown Sql Server 2005 running. I've seen all the post about "in-process" and it doesn't work. I'm getting either 7303 or 7399 regardless of whether I use the Oracle or Microsoft driver. I can successfully connect and query the database from sql plus with no problem. I can create the linked server fine, it's just when I go to query that it errors. "The test connection to the linked server failed." 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 "OraAGS". OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303 Could this be a problem with a 64-bit server connecting from a 32 bit client?? Thanks in advance..

SSIS Package for Delta Data between Oracle and SQL Server

  

Hi all

I have BI Server and Tables and Data Populated using SSIS package.Now Data is ready in BI server.Next week Ihave to same excersize to filll the Data(DROP tables,CREATE table and Pump the Data)

Can any body give an idea how to create SSIS package which can pupm the Delta Data Oracle and SQL Server.

 

Thanks in advance

 

 


SNIVAS

Oracle 11g R2 64-bit linked server difficulty

  

We are re-integrating a business unit back into IT support this weekend. They have a Oracle production system that's been upgraded to 11G. The previous linked server worked fine.

I'm trying to attach from an XP laptop that has a full blown Sql Server 2005 running. I've seen all the post about "in-process" and it doesn't work. I'm getting either 7303 or 7399 regardless of whether I use the Oracle or Microsoft driver. I can successfully connect and query the database from sql plus with no problem. I can create the linked server fine, it's just when I go to query that it errors.

"The test connection to the linked server failed."

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 "OraAGS".
OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303

Could this be a problem with a 64-bit server connecting from a 32 bit client??

Thanks in advance..


Trouble with: Linked Server to Oracle using OraOLEDB.ORacle Provider

  

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

C

In a trigger can up insert data into a table located on a linked server?

  

EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'lnklogin',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query timeout', @optvalue=N'0'

Server Explorer Data Connection to ORACLE in VS2008 on Vista x64?

  
How do I set up a connection to an ORACLE database from Visual Studio 2008's Server Explorer in Vista x64?

If I install the 32-bit ORACLE client, I can configure a Data Connection in Server Explorer, but I can't use it to connect to the database because I can't setup a DSN in the Control Panel (the Oracle provider doesn't show up because it's a 64-bit OS).

If I install the 64-bit ORACLE client, I can configure a DSN in Control Panel, but I can't configure a Data Connection in Server Explorer, because VS2008 is a 32-bit app and throws a BadImageFormatException when it tries to load the 64-bit OCI.

Is there a way to do this without moving to a 32-bit development environment?

Retrieve data from SQL server based on login username (session data)

  

Hi,

I'm trying to make a dropdown list with data available based on the users login name. Heres what I have as my SQL Query in the SQLDataSource:


SELECT Client_Name, Client_ID FROM Client WHERE (Client_Name LIKE '%@Client_Name%')


So say if the login username is "User1" but the database client name is "User1 Steel" I want the dropdownlist to pull "User1 Steel" as the display and Client_ID as the value from the dropdown list. 


I dont know how to use the session data!


Another Oracle linked server problem

  

I have trouble making a linked server from a SQL Server 2008 R2 64bit to an Oracle database.  I have a linked servers on a few other PCs working fine to this same Oracle server.

On this particular one, I configured the following and Command Test connection shows that it is working fine:

TNSNames

But when I tried to create the linked server as shown below, it fails.

The error is: 

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 "CARE".
OLE DB provider "OraOLEDB.Oracle" for linked server "CARE" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

I can't find what other parameters I could adjust.  How should I go about solving this problem.  Thanks.


Suggestion about exporting data into Access database from sql server 2008 without Linked Server

  

Hello,

    We have a website that was using Sql server 2005. There was a option in Website where when a user clicks a button, Some tables data will be exported to an access database. To achieve this, we have setup a linked server to Access database. We have moved from sql server 2005 to 2008 (64bit). We did not want to use that option of having a linked server now. I am looking for different ways to achieve the task. We also donot want to use OPENROWSET and OPENDATASOURCE .

 

Can someone  give some insight about this?

 

Thanks,

Nag


Clustered SQL 2008 R2 x64, Oracle linked server works on one node, fails on the other

  

Juicy bits:

- SQL Server 2008 R2 x64

- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

- Active/passive cluster

- Oracle client 11gR1

- "Allow Inprocess" provider option is enabled

 

I'm stumped.  Our development, QA and UAT environments are all running the same versions of Windows, SQL, and Oracle client.  Linked server between SQL and Oracle works just fine.

 

Production is clustered.  The linked server works just fine on one node of the cluster, but if we failover to the other node, attempts to use the linked server in any way, OPENQUERY, GUI, etc..., cause the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message "ORA-12634: Memory allocation failed".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".

 

What am I missing?  I've reinstalled the Oracle client on the defective node, verified that the SQL Server service account has full rights to the Oracle client dir

Linked server desde SQLServer 2008R2 (64x) hacia Oracle 9i

  

Hola

Tengo el siguiente problema para crear un servidor vinculado, espero alguien me pueda ayudar

Tengo un servidor con SQL Server 2008 R2 64x y requiero crear un servidor vinculado hacia una base de datos de Oracle 9i, cuando intento crear el servidor no encuentro el provider Microsoft OLE DB Provider for Oracle, para realizar la conexión como se hace en SQL 2005.

¿Alguien sabe que pasos debo realizar para poder crear el servidor vinculado?


Linked server config shows no Oracle provider

  

Hi,

In a process to config linked server on MSSQLServer2008 on WS2008R2.

Installed Oracle client 11.2, configured the variables TNS_ADMIN, ORACLE_HOME;

configured Data Sources (ODBC)- System DNS ->Oracle with Driver SQORA32.dll and have all Oracle databases in the drop down menu.

Next:

New linked server - Under provider -> there is no Oracle provider listed!?

What I'm missing here?

 

Thx,


Getting OraOLEDB.Oracle to Show up in List of Providers for Linked Server

  

In 64-bit SQL Server 2008 R2, I am trying to create a linked server to an Oracle 11g database.  I have installed the 32-bit Oracle 11g Instant Client and set up tsnames.ora and sqlnet.ora in proper Oracle home directories. 

In SQL Server management studio, under Server Objects - Linked Servers - Providers, I do not see the OraOLEDB.Oracle in the list.  I've rebooted and re-installed the Instant Client.  I stopped and started the SQL Server service.  I even confirmed that OraOLEDB.Oracle exists as one of the SQL Server providers in the registry.

There are full permissions on the directory where the Oracle Instant Client is installed.

Does anyone have any recommendations about how to get the OraOLEDB.Oracle to show up in the list of providers?  I'm not sure why it would show up in the Registry but not in SQL Server Management Studio.

Thanks,

William H

 

 


Wmholt SharePoint Developer
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend