Linking Oracle View To SQL Server Express

Sep 6, 2007

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,
Stara

View 3 Replies


ADVERTISEMENT

Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2

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

Problems Linking To Oracle Server

Jun 8, 2006

Has anyone successfully linked to an Linux Oracle server running 9i? We are unable to get the link working.....have installe dvarious drivers and still get connection refused. Any thoughts?

View 1 Replies View Related

Linking Two SQL Server Express Servers

Oct 26, 2006

Hello all!

How do I go about linking two SQL Server Express servers? I can't seem to find information on this.

Also, I need to import data from MSDE. Is there anyway to do this?



Regards,



Andrew Cappella-Pakos

View 1 Replies View Related

Linking To Oracle

Nov 7, 2000

Can anyone tell me how to link to a oracle user? DO I use a predefined DSN? Do I just specify the server and the user and password?

Your assistance is appreciated,
David

View 2 Replies View Related

Linking To Oracle

Sep 21, 2007

I need to query both Oracle and SQL Server tables for reporting and processing, ....no updates, insert or deletes.

Is creating a linked server the only way? My company does not want me to see the entire database.

Can you just link tables? Like Access or Excel does?

Any articles or links would be appreciated.

Thanks,

Mike


View 1 Replies View Related

Linking SQL Server 2000 Servers In Express 2005

Jun 5, 2006

Hello,

I was wondering whether it is possible to query linked servers in SQL Server Management Studio Express 2005 if those servers linked to are SQL Server 2000 machines?

I have added some linked servers to express and queried them, however the login timeout is expiring. I thought it might be because of an incompatability.

Cheers,
Damien.

View 1 Replies View Related

Need Some Assistance Linking An Oracle Database

Dec 15, 2004

I'm trying to set up an Oracle database as a linked server in SQL server 7 but having some problems.

The client software is installed and seems to be working fine (Used access to link some tables). I set the product name to Oracle 8.1 and the Datasource to the system dns name. I left provider string, location and catalog blank. Under the security tab I had it set to map to the oracle account and also tried the impersonate option, but always got the same error.

Error: 7399 OLE DB provider 'MSDASQL' reported an error. Driver's SQLSetConnectAttr failed]
ORA-12640: Authentication Adapter initialization failed]

This is my first time trying to set up and Oracle database as a linked server, any help is appreciated.

View 4 Replies View Related

Linking Form In MS Access To Oracle

May 9, 2008

hello

we suppose to link the form in MS Access with Oracle

can anyone tell me how to write the code for these buttons

i tried to write as I learned in VB.NET,
But it seems doesn't work :(

http://www3.speedyshare.com/data/191404757/10803567/78224332/db2.jpg

View 2 Replies View Related

How To VIEW Oracle Tables In SQL Server ?

Aug 5, 2002

Is there a way to view the Oracle (8i) databaes, tables and data in SQL 2000 without physically importing those the tables ?

Thanks,
Scott

View 1 Replies View Related

View With Linked Oracle Server

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

Linking Two Tables In A Detail View

Apr 10, 2007

I have a province table in a my database.  I would like to link this province table to a resource table's Province_ID.  This Province_ID is an int.
Vic Valentic
CEO/President
Open Door
2 Elite Dr. #33
Hamilton, Ontario
L8W 2N3
905-389-7492
http://www.wlu.ca/next/opendoor

View 6 Replies View Related

Viewing A Table In Oracle As A View In Sql Server

Aug 9, 2000

Any idea on how I do this?
Any advice welcome.........

View 1 Replies View Related

SQL Server Express And Oracle

Nov 21, 2006

Can sql server express and Oracle be installed on the same machine.What possible problems can I expect ?

View 2 Replies View Related

Connecting To An Oracle Databse Server Using SQL Express

Jan 10, 2006

Hello everyone,

 

I am working on a project for a professor. The project entails
designing and creating a database.  We
were given individual space on the schools database server and we were given
SSH secure shell to connect and manage our respective databases.  I took it upon myself to try out SQL Server
Express.  Using the management studio I
am trying to connect to my school database and can€™t seem to figure it
out.  Can someone please give a noob some
simple and quick instructions on how to achieve this?

 

Thank you,

Your Friendly Neighborhood Programmer,

 

This is the SSH product in case any one was interested, ftp://ftp.ssh.com/pub/ssh/SSHSecureShellClient-3.2.9.exe

 

View 1 Replies View Related

SQLExpress Linked Server To Oracle Express 10g

Dec 20, 2006

Trying to set up a development environment on an XP SP2 machine with an installation of SQLExpress and Oracle 10g Express. Having been trying to create a Linked Server in SQL to access the Oracle EX database. Have had no luck using either Oracle driver or Microsoft driver. With Oracle driver get the following error on an openquery statement:

select * from openquery(xe,'select top 1 * from HR.REGIONS')

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "xe" 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 "xe".

I am mapping a Windows authenticated login to an Oracle login/password in the Linked Server setup. I can connect to the Oracle database fine using Oracle tools. Likewise, I can connect to SQL Server database just fine. I have set up the linked server using the Oracle Service Name in the TSNAMES.ora file as the Data Source in SQL Server. ('XE') What could I be doing wrong? What else can I check?

View 5 Replies View Related

Can't Connect To Oracle Dbase With SQL Server 2005 Express

Dec 16, 2005

Hi,I'm attempting to connect to an Oracle database using SQL Server 2005Express (OS is Windows XP Professional) and having absolutely no luck.Here is the information SQL Plusgives me about the database when I log in -Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - ProductionAs you can see I have no problem connecting with the Oracle specifictools (SQL PLUS and PL/SQL Developer).I've tried creating a linked server with both the Microsoft providedOLE DB provider and the Oracle provider. With the Microsoft Provider Iget the following error when attempting a query in the query editwindow.OLE DB provider "MSDAORA" for linked server "ORACLE" returned message"Oracle client and networking components were not found. Thesecomponents are supplied by Oracle Corporation and are part of theOracle Version 7.3.3 or later client software installation.Provider is unable to function until these components are installed.".Msg 7303, Level 16, State 1, Line 2Cannot initialize the data source object of OLE DB provider "MSDAORA"for linked server "ORACLE".When I try connecting withe Oracle provider I get the following -OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLETEST2"returned message "ORA-01017: invalid username/password; logon denied".Msg 7303, Level 16, State 1, Line 2Cannot initialize the data source object of OLE DB provider"OraOLEDB.Oracle" for linked server "ORACLETEST2".I've set the password up as a local to remote login mapping using thesecurity page of the Linked Server property dialog. The username /password are definitely correct -- I used them to log in using SQL PLUSand PL/SQL Developer. I'm using an Oracle 9 client.PLEASE HELP !!!!!Thanks,Signed Frustrated

View 8 Replies View Related

Can You Connect To An Oracle DB Using MS Sql Server Management Studio Express?

Sep 27, 2007

Hi Everyone,

Just like the title says:

Can you connect to an Oracle DB using MS Sql Server Management Studio Express?

If so, can anyone please give me an example?

Thanks heaps!

View 7 Replies View Related

Linking To EXCEL Database Sheet Using SQL 2005 Express

Aug 9, 2006

I'm followed instructions to create a linked server to an EXCEL document that has data in 1 sheet.

Used the instructions from http://support.microsoft.com/kb/306397/EN-US/

Was planning to import data using a linked server as describe in http://support.microsoft.com/kb/Q321686

"How to import data from EXCEL to SQL Server (using linked servers). All went well until...

The EXCEL document shows up under linked servers correctly, but there were no tables.

I'm using the following:

Server: Microsoft SQL Server 2000

SQL Server Managerment Studio (2005)

Does anyone have Idea what I may do to correct this problem?

I 'm using this because I didn't see any other way to import Excel into SQL Database using SMO 2005.

SMO 2005 doesn't have DTS import export commands.



View 5 Replies View Related

Can Co-installations Of MS SQL 2005 Express And Oracle 10g XE Database Server Interfere With MS SQL?

Feb 24, 2007

Hi -

I may not sound desparate, but I really am. I have spent many days now trying to install and re-install Microsoft SQL 2005 Express. I still cannot connect to the database! I have read this forum and tried various suggestions, like setting the remote connections and TCP/IP + Named Pipes. Still doesnt work!

The environment is Dell Dimension 24000 (I86) with WinXPPro.

Out of desparation, I downloaded and installed Oracle 10g XE. It works just fine !

My question is: Does the Oracle database server interfere with connecting to the Microsoft SQL 2005 Express server ?

Also, I notice that there are an awful lot of questions posted on this Forum concerning just getting your software up and running. Are there that many stupid folks out there like myself, or has this application been released prematurely ?

View 3 Replies View Related

I Cannot View Table Content In MS SQL Server Express

Mar 19, 2007

I was able to view table content easily before, but after Ireinstalled everything, I cannot find the option to view table contentin MS SQL Server Express. I can define table with no problem. Checkout the screen snapshot below, from which you'll see that the popupmenu frrom right-clicking on the table name does not have the "Opentable" option. What is going on?http://farm1.static.flickr.com/167/...839620d0b_o.png

View 6 Replies View Related

Hitting An Oracle View

Mar 1, 2005

I have been asked to include a view from our Oracle system in one of my SQL queries. As a programmer I can write querries all day long, but I have no administration skills.

Can someone just give me the basics on creating this ability? On my development system I have installed the Oracle client and configured an ODBC driver that points to the Oracle views. This has allowed me to create a linked table in Access, but I have never attempted to port this to SQL Server 2k.

I assume I just load the drivers on the production system as before, but do I then use an ODBC driver again, or is there a better method with SQL Server? If its the ODBC method, then how do I add a linked table in SQL?

Sorry for the basic questions, and I really appreciate any help.

Thansks,

Rob

View 3 Replies View Related

View With A Query To An Oracle Database

Jul 20, 2005

Hi,In our environment a database view is based on columns in a table from anoracle database.This is a linked server I believe.I am told that since we use this construction no where clause is possible.So we get to many rows in our database which we have to filter later on.How can we do this differently ?? Since it takes a lot of time to go throughthis process.Bye,Arno de Jong, The Netherlands.

View 2 Replies View Related

SSIS Connection To Oracle View

Jun 20, 2006

Hi

I have a number of scripts that run against an oracle server to retreive data to manipulate before populating a sqlserver database. I am connecting using an Odbc connection and using the DataReader Source to read from the Oracle table. It all seems to work ok.

When I have tried to extract data from an Oracle view in the same way it doesn't seem to work. The error message mentioned the PrimeOutput() method and error code 0xC02090F5. The view is very big so is it possible that the connection simply timed out?

Any ideas?

Thanks

B

View 6 Replies View Related

Transact SQL :: Can Define A View To Oracle Data

Apr 30, 2015

I have a subset of data whose source of truth resides in an Oracle Database. Can I define a SQL Server View to retrieve this subset of data and make it available on the SQL Server side for say Reporting and SSRS? I think I also saw where they tend to prefer User-Defined functions as opposed to a view.

If I define the view using an OPENQUERY, I have noticed that it seems to run FOREVER on the SQL Server side and returns pretty timely if I run in Oracle SQL Developer. Is there anything I can tweak to improve this timeliness or lack there of? And the whole reason I'd like to create a view of this Oracle data is so that I can take advantage of all the views and functions that we have created on the SQL Server side.

View 4 Replies View Related

Oracle View Returns ORA-01403: No Data Found

Jul 13, 2007

I am trying to get data from an Oracle view using an OLE DB data source and a "SQL Command". When I "preview" the data it looks fine, but when I execute the package I get the following error:

Error: 0xC0202009 at Data Flow Task, CEDAR View [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "CEDAR View" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

View 3 Replies View Related

Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?

May 8, 2015

we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.

what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?

View 2 Replies View Related

Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider

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

Why Not Like Oracle Database 10g Express??

Dec 14, 2006

The last few months I learn something about SQL by learning Oracle database 10g XE?

Yesterday I downloaded a SQL server express from MS, but we I installed it, together with management studio, I got stucked, I don't know how to start, even the most simple thing, how to create tables, views, and users, and all these simple things.

Am I need more softwares for creating this things?

Any good idea to give a layman like me, something to read??





Really Thanks!!

View 1 Replies View Related

Linking To SQL Server 5.0

Sep 2, 2007

I'm writing my first web site using VS 2005 and with my login page I'm trying to link with my SQL database using the following setting in my web.config file
<appSettings> <add key="SQLDB" value="server=STEVE-LT; database=MasterDB; uid=sa pwd="/></appSettings>
Then in my Login.aspx file I use the following code to access the SQL database
Dim Sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("SQLDB"))Dim SqlCmd As SqlCommand = New SqlCommand("Select Password, Name from Login")SqlCmd.CommandType = CommandType.StoredProcedure
Sc.Open()SqlCmd.Connection = Sc
 This raises the following error message
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
Could someone please tell me where I am going wrong. Thanks

View 2 Replies View Related

Linking 6.5 Server

Mar 8, 1999

I can't seem to link a 6.5 database from 7.0 and there's nothing in help which seems to help. Is it a "SQL Server" or do you have to use the OLE DB Provider? If the latter, what's supposed to be in the Product, Data Source, Provider String and Catalog fields? What about security? I've tried everything I can think of and can't get it to work. I also get errors creating a new DTS package that says "Could ot create Component Categories Manager", and then "Error during creation of DTS Package". I installed the desktop version of 7 on an NT Workstation. Has anyone successfully linked a 6.5 server in this environment?

View 4 Replies View Related

About Linking Server

Oct 27, 2007

About linking server













Hi,
I am using sqlserver2005, when i make linked server connection between sqlserver2005 and oracle10g . It shows an error like that " Particular table doesnot exist or user doesn't have permission to connect ti this table"and below this one message is shown which is " Message 7314, line16".But i am giving it a proper "username/password ". In this linked server i am using Oracle10g new provider "ODAC101040.exe". so, please elaborate my such problem and also gives me the solution of this problem.
Thanx
regards
gautam

View 3 Replies View Related

Linking From SQL Server To MySQL

Dec 13, 2004

Hi all, hoping someone can help.
I have created an ODBC connection to a remote MySQL database.
I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
I am so stuck. Any help would be very much appreciated.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved