Click here to Skip to main content
15,903,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi all.

i want to create view (materialzied view) in SQL Server Database, DB tables are present on ORACLE database and on another server.

Scenario is :: my application is using sql server database only. and through application i want to select/update/insert/delete ORACLE Table data.

i tried using granting permissions / creating database link.
i created a Database link in ORACLE .. and using that link i want to creating view... second option tried is GRANT operation...

suppose i am having access only to only one database name "sushil".

which contains a user "UserABC" so i can say third party that create a database link and give it to me for accessing data.....

but i dont know the exact steps..

can anyone help me out.

Thank in Advanced

Regards
Sushilkumar Bondre
Posted
Comments
sushilbondre 19-Sep-10 6:17am    
waiting for urgent reply
mslliviu 22-Sep-10 3:26am    
I think the first step you have to take is to add the Oracle server to the LinkedServers collection in SQL Server (Server Objects). After that I think you can query Oracle data (don't know what security issues can appear)

1 solution

The only solution that I can see is to create Stored Procedures with aid of C# CLR in SQL Server, and to perform select/update/insert/delete inside these procedures using ODBC/OLE db connection to Oracle.
Or you may even create assembly for SQL Server to wrap the whole ADO.Net functions' batch: execute, executeScalar, executeNonScalar, and keep connection to Oracle as well.
 
Share this answer
 

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