Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi ,

is it posibble?
How can i use left join oracle table and genius(sql) table

What I have tried:

"PROVIDER=MSDAORA.Oracle;DATA SOURCE=xxxx;USER ID=yyyy;PASSWORD=zzzz"

oracle

"Provider=SQLOLEDB.1;Password=aaaa;Persist Security Info=True;User ID=bbbb;Data Source=cccc;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial "


sql
Posted
Updated 8-Oct-20 2:11am

1 solution

Set up the Oracle server/database as a Linked Server to the SQL database

An explanation of SQL Linked Servers : Understanding SQL Server Linked Servers[^]

Specific Example of SQL to Oracle : Configuring Connection from SQL Server to Oracle using Linked Server - SQLines Open Source Tools[^]

Edit:
Once the servers are linked you can just refer to the fully-qualified table names in your join
SQL
--Your select statement here ...
FROM [SQLServerName].[SQLDatabaseName].[SQLschemaname].[SQLTable] S
JOIN [ORAServerName].[ORADatabaseName].[ORAschemaname].[ORALTable] O ON S.columnname = O.columnname
 
Share this answer
 
v2
Comments
Member 14588284 8-Oct-20 10:22am    
i didnt do it... :(

my query runs with these codes
PROVIDER=MSDAORA.Oracle;DATA SOURCE=xxxx;USER ID=yyyy;PASSWORD=zzzz"
cmd.Execute ("SELECT COL1 FROM SYS20.CARTM001")
Set RS = cmd.Execute

For X = 1 To RS.Fields.Count
Cells(1, X) = RS.Fields(X - 1).Name
Next

Range("A2").CopyFromRecordset RS
Conn.Close

oracle

Hide Copy Code
ConnectionString="Provider=SQLOLEDB.1;Password=aaaa;Persist Security Info=True;User ID=bbbb;Data Source=cccc;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial "
cnn.Open ConnectionString

sql


RS.Open "SELECT COL1 FROM CUSTOMER", cnn
For X = 1 To RS.Fields.Count
Cells(1, X) = RS.Fields(X - 1).Name
Next
Range("A2").CopyFromRecordset RS
CHill60 8-Oct-20 11:40am    
Why didn't you do it?
It looks as if you are using Excel and VBA - how about explaining exactly what it is you are trying to do with this data and tag your question properly

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900