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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Trying to do an inner join openquery against an Oracle database

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server


How do I do an inner join on a SQL table & Oracle table using Openquery?  Here is the code that I have:

select * from openquery
(OracleServer, 'select column1 from table1') a
inner join openquery(SqlServer,'select column2 from sqldb.dbo.table2') b on
a.column1 = b.column2

The datatypes for columns 1 and 2 are the same -- varchar(40).  

Unfortunately I can't do the 4 part naming convention on the Oracle server.  So hopefully this can be done via OPENQUERY.

Dynamic SQL???

View Complete Post

More Related Resource Links

Need Oracle Data Provider .CS File for Oracle 10g Database connection !



I need a 'Wrapper.cs' file which takes care of the Database connection ( Oracle 10g) where

i can just call the method with my SQL Query


Gridview1.DataSource = SampleWrapper.ExecuteDatatable("THE SQL QUERY");


Plz Post the link if there is any open source !    

access Oracle database


Pre- .Net Framework 4.0 supported using Oracle.DataAccess.Client, using Oracle.DataAccess.Types, System.Data.OracleClient, OracleDataReader, OracleConnection. What are the equivalents in .Net Framework 4.0? Does it require any download?

Thank you.

Oracle Database access

Hello all,I have created an application with VS 2008. It accesses my oracle database (Oracle Database 10g Enterprise Edition Release which is on my external harddrive. All of this needs to be accessed remotely. I was told to use Apache, so I install Oracle HTTP Server after a few consultations. I was told I just needed mod_plsql and that I can even use ApEx. I have no clue what to do here. Any suggestions, links to tutorials/wikis are very helpful. My main issue is how to use a page in my application to access the database remotely. Help!Thanks for your time

SELECT COUNT(*) FROM [Table] from an Oracle database

Hi friends, I have problem when retrieving a result from SELECT COUNT(*) FROM [Table] from an Oracle database. When I try to put the result (single row) in a variable I get the following error message. [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowsSource": "Unsupported data type on result set binding RowsSource.". Pls help me Mahe

Excel Services to Oracle database

  We've set up a Sharepoint 2010 system on Windows Server 2008 R2. I'd like to set up Excel Services, so we can create dashboards to our back-end databases, which are Oracle. I'm struggling to get the data connection to work. To do this, should I be setting up my DSN with the 32-bit ODBC Oracle drivers, or the 64-bit ODBC Oracle drivers? Looking at the Excel Services setup, I think that the the list of trusted data sources provided are the 32-bit ODBC drivers. I did install the 32-bit ODBC oracle drivers, and set up the DSN I needed. When I log in to the Sharepoint server via RDC and load the Excel spreadsheet, the data connection works fine. But, when I use Excel Services to create a dashboard, and refresh the data connection, I get "Unable to refresh data for a data connection in the workbook." Looking at the Sharepoint log files, I see (password removed): The Connection Information is invalid: ConnectionString: DSN=nifoem;UID=sp_oem;PWD=[XXX];SERVER=nifoem;, CredentialsMethod: Integrated, SSOApplicationId: , QueryType: Odbc bd9d6ef3-6097-4ed5-9b8a-ffafc3997d26 Any idea what I might be doing wrong here? Thanks, John  

Errors in the back-end database access module. The managed provider 'Oracle.DataAccess.Client' cou

Hi. I have connected my SSAS in SQL server 2008 to an Oracle database.  I have installed the Oracle ODAC to enable me to set up the Data Source, the Data Source View and the Cube.  Now everything was going fine.  I could connect to the Oracle Database and I could see my tables and pull them into the DSV. But.  When I come to process the Cube it throws these errors.  Why all of a sudden do the connection objects stop working? Errors in the back-end database access module. The managed provider 'Oracle.DataAccess.Client' could not be instantiated. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'DBIBGEORAclients', Name of 'DBIBGEORAclients'. Can anyone help here please? Thanks, David.

Windows Workflow Persistence Service using Oracle Database



We are using windows state machine workflow. We need to use Oracle for persisteing and tracking workflow instances. Can anyone provide custom services for Oracle?

We have even tried WFTools but not able to connect to Oracle database. Please provide the same code if anyone has tried.





Does SQL Server has the similar function just like Trace Event 10104 for hash join in Oracle?


My question is  how can SQL server display the  information about the buckets when doing the hash join just like the function of Trace Event 10104 in Oracle.By the way ,In Oracle ,the Event 10104 event dumps hash join statistics .
The buckets message what i needed is similar as the following,that is to say ,it must contains the information about buckets .
01 ### Hash table ### 
02 # NOTE: The calculated number of rows in non-empty buckets may be smaller 
03 # than the true number. 
04 Number of buckets with 0 rows: 16373 
05 Number of buckets with 1 rows: 0 
06 Number of buckets with 2 rows: 0 
07 Number of buckets with 3 rows: 1

SSAS 2008R2 - Enable to process a database using Oracle OraOLEDB data provider



I've created a data source in BIDS using provider OraOLEDB.Oracle.1. The Oracle database is 10gR2. The client installed on my computer is 10gR2. The connexion is OK when I use Test Connexion in BIDS.

In the data source view I created a very simple query: select * from user_objects. All is fine, the fields are correctly defined.

I've created a dimension based on this query with object_id as the key.

I've deployed the solution to the server. All is working fine for the moment.

But when I process the dimension I get a dozen of error messages like (sorry error messages are in french) :

"Erreur OLE DB : Erreur OLE DB ou ODBC : Syntax error near 'OraOLEDB.Oracle.1' on line 8; 42000."

Erreurs dans le moteur de stockage OLAP : Une erreur s'est produite lors du traitement de dimension portant l'ID 'Test' et le nom 'Test'.

Erreurs dans le moteur de stockage OLAP : Une erreur s'est produite lors du traitement de l'attribut 'SUBOBJECT NAME' de la dimension 'Test' de la base de données 'CTRL_DOMAINES'.

Serveur : L'opération a été annulée.

Erreur OLE DB : Erreur OLE DB ou ODBC : Syntax error near 'OraOLEDB.O

Openquery - truncation problem (converting data from Oracle 10g to SQL 2005)


I am having problems with data truncating when inserting into a SQL 2005 db from Oracle 10g- using openquery and a linked server.   For instance, if I run something like:

insert dbo.CWDocumentStaging
select * from openquery(LK_Snomass,'select RSFORM.REPRESENTATION as REPRESENTATION

The results of the blob data are only 202 hexademical characters in length.  And then when I convert it to varchar, the results are cut-off.  For instance, on a particular test record that I converted, here is what I have in the SQL database (using):

select convert(varchar(max), DOCUMENT_TEXT), DOCUMENT_TEXT from dbo.CWDocumentStaging

I get the following:

for Document_Text, I get the following:


then for convert(varchar(max), DOCUMENT_TEXT):

this old man, he played one, he played knick-knack on my thumb with a knick-knack paddy whack give a

If I insert the data running the query against the linked server directly, the data does not truncate

Problems Connecting Excel Services to Oracle Database


I am just beginning to experiment with using Excel Services with BI Dashboards in SharePoint 2010.  I created a Excel Workbook that has a data connection using connection type of OLE DB Query with a connection string of "Provider=MSDAORA.1;User ID=<username>;Data Source=<oracleEnvironment>".  I have the Excel Services Authentication Settings set up for a specific SSS ID <mySSSID>.


This Excel Workbook has been Saved to SharePoint with an entire sheet published.  The Excel Web App is displaying the sheet and the Slicers are functioning just like they do in the Excel client.  When I refresh the data on the client I get prompted for a password and it works fine.  When I refresh on the Web App I have received a variety of errors depending on what I try but nothing has helped me track down what I have missed.

what is the process of sharepoint FBA WITH ORACLE DATABASE

what is the process of sharepoint FBA WITH ORACLE DATABASE

Form Based Authentication(FBA) with Oracle database

can anybody give me configuration details for share point Form Based Authentication with Oracle database

Appreciate your comments on Database migration from Oracle to SQL Server ...


Appreciate your comments on Database migration from Oracle to SQL Server ...

From my perspective, there are two major known issues that would cause major

resource consumption for porting My-App from Oracle to Microsoft SQL Server. First,

the Perl code within My-App reads the Oracle object meta-data for defining it's

internal objects and data types. SD would be the best group to identify the effort

to replace this dynamic functionality. The second issue is the coding required

to replace Oracle's built-in functionality to work with data hierarchies / tree

structures / parent-child relationships.

The My-App Data Model is heavily based on parent-child relationships through out

numerous tables; every item has a parent, every person has a manager, every team

may or may not be nested, etc. Oracle has native SQL clause, connect_by, to query

these structures. Oracle also provides pseudo columns for the connect_by meta data,

such as levels, path, isleaf, cycle options, etc. The connect_by functionality with

Microsoft SQL Server (see Reference below) is done natively with a "with" clause or

via custom procedures. SQL Server does not provide any pseudo columns functionality.

Also, SQL Server does not support any analytic and aggregate func

Export millions of records from database (Oracle) to excel


Hey guys, I'm running a query that brings millions of records and makes the server run out of memory. I'm thinking I should get the data in chunks. So I was wondering if someone could shed some light on this or point me to another place where I can read further.

Currently, I just implemented "paging". I can now pass a "page index" parameter and "page size". So I could call 1,000 times the stored procedure with a page size of 1,000 (for 1 million records). This might be an option, but I wanted to hear someone else's opinion on how to "stream" records from oracle to the web server and then to a file.


Problem in connecting to oracle database,which is using Provider=MSDAORA.1


Hi I am Naveen, I am upgrading a internal website from .Net 1.1 to .Net2.0 in Vb.

The database is deployed in the server, but i could not connect to that database.

i have to connect database to few drop down lists,

my manager has given connection string to me... i have placed it in web.config and given that link properly in the code..

but the database is not connecting.. the list is showing empty..

my conn string is

<add key="D2" value="Provider=MSDAORA.1;Data Source=baan; User ID=xxxx; Password=xxxx;Persist Security Info=True"/>

can anyone help me,,, thanks in advance...

SharePoint connect to Oracle Database


We are running a SharePoint 2007 farm and we have staff that want to use a dataview to connect to an Oracle Database as well as a SQL database.  The SLQ connection worked fine. When we try an Oracle Connection using a custom connection string we recieve an error retriving the list of databases.  

Some of the information I have read mentions installing the Oracle Client on the SP Web Front  End servers and others do not.  

Is there a way to connect to an Oracle database without installing the Oracle Cleint on the SP Web Front End servers?

I've read about using BDC, but that seems to involve having the Oracle.dll in the GAC which I believe would require the Oracle client to be installed.

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