SQL Server + Oracle Data linked in one query

Chris

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.

Jun 8 '06 #1

Subscribe Reply

4176

Saiko Consulting

I know it's a similiar concept but how about via linked servers?

Mark Graveline
mg********@sqlc hallenge.com
http://www.sqlchallenge.com
*** Where Only The Best Technical Professionals Compete

Chris wrote:

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.

Jun 8 '06 #2

Chris

Thanks Mark, I tried the linked servers. I have read permission on
oracle tables (from VB .NET I have no problem executing queries on
tables and retrieving data) but when I execute

select * from RAS...PRODUCT

I get the error "The OLE DB provider "MSDAORA" for linked server "RAS"
does not contain the table "COUNTRY". The table either does not exist
or the current user does not have permissions on that table.

For exec sp_tables_ex 'RAS','product'

NULL MyUser PRODUCT SYNONYM NULL

Do I need different permissions for linked server ?

Saiko Consulting wrote:

I know it's a similiar concept but how about via linked servers?

Mark Graveline
mg********@sqlc hallenge.com
http://www.sqlchallenge.com
*** Where Only The Best Technical Professionals Compete

Chris wrote:
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.

Jun 13 '06 #3

Chris

The error appeared because I defined schema name in lowercase.

Jun 13 '06 #4

Similar topics

24290

Linked Server (Oracle 9i)

by: David Gray | last post by:

Hello all, Having problems connecting to an Oracle 9i database from within SQL/Server 2000 using the Security/Linked Servers feature. Server1 (SQL/Server) ----------- Windows Server 2003, Standard edition MS SQL/Server 2000 Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools

Oracle Database

12588

Linked Server to Oracle

by: Sudhesh Nayak | last post by:

Hi, I have an Oracle (8.1) & a SQL Server 2000 database with Production data. There are situations when I need data from both the databases. My first choice was to link Oracle to SQL and run DTS overnight. But this would have a 1 day latency not to mention the time it would take. 1. Has any one tried real time access via Linked server to Oracle? How good is the performance?

Microsoft SQL Server

9581

Linked Server Error Between SQL Server and Oracle

by: Jevon | last post by:

I have used Linked Servers to hook up to an Oracle 9i database in SQL Server 2000. I can see all the Tables and Views when I look in the linked server section. I can also run the following SQL statement : Select * from Orcldb..SYSTEM.CrossTabSaveSites and get a return result. The problem I realize is that some of the tables are going to be quite larger and I can't wait for SQL Server to download all the data and parse it itself, so...

Microsoft SQL Server

3878

Linked server to oracle

by: Pardhasaradhy | last post by:

Hello, I have a linked server to oracle 7.1 from SQL 2000. when I try to execute simple select statement which returns one row of data using openquery is not fetching the data. After 30 minutes SQL Query analyser is hanging. This is happening on the production server and it is working from last 6 months. I have tried by deleting and recreating the linked server, but no use. Please help Thanks,

Microsoft SQL Server

14378

Can't connect to Oracle Dbase with SQL Server 2005 Express

by: danbredy | last post by:

Hi, I'm attempting to connect to an Oracle database using SQL Server 2005 Express (OS is Windows XP Professional) and having absolutely no luck. Here is the information SQL Plus gives me about the database when I log in - Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options

Microsoft SQL Server

1672

Linked Server Time Difference

by: MartyP | last post by:

I am using SQL 2000 and/or SQL 2005 to link to an Oracle Db using OPENQUERY. The SQL 2000 box is SP4 on Win2000. I can run a query thru Q.A. for SQL 2000 or I can run an Oracle query thru SQL-Plus to see the data. But, if I run a date-query using the linked server then SQL Server returns a TIME that is off by 4 hours. SQL Query: SELECT * FROM OPENQUERY( ORASERVER, 'SELECT MYDATE FROM MYTABLE')

Microsoft SQL Server

7248

Access as Frontend to Oracle questions - Memo-to-CLOB? View vs. Query?

by: jonceramic | last post by:

Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends, but will be migrating my back ends to Oracle ODBC. 1. Does anyone have recommendations for books or web resources for general rules/guidelines/help on doing this? I haven't found a good

Microsoft Access / VBA

1284

Linked Server and Oracle10 Performance Issues

by: aberton | last post by:

Hi all, I am having major performance issues between my linked server and an Oracle10 db. I have created a number of views in my SQL Server database which map to corresponding Oracle tables via a linked server. In my Oracle environment via Sql*Plus I run a simple query (count) which does a join between a 400K table and a 2.5 mil table - this takes about 10 seconds. In my SQL Server environment using the views created via Linked Server, the...

Microsoft SQL Server

1559

Microsot Linked Server & Oracle10 Performance Issues

by: aberton | last post by:

Hi all, I am having major performance issues between my linked server (SQL Server) and an Oracle10 db. I have created a number of views in my SQL Server database which map to corresponding Oracle tables via a linked server. In my Oracle environment via Sql*Plus I run a simple query (count) which does a join between a 400K table and a 2.5 mil table - this takes about 10 seconds. In my SQL Server environment using the views created via...

Oracle Database

9721

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9601

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10376

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

7653

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6881

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5687

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4332

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3861

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

3013

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General