|
| 
| Submitted on: 8/20/2002 11:37:13 AM
By: Daniel G. Menendez
Level: Intermediate
User Rating:
By 3 Users
Compatibility:SQL Server 2000, SQL Server 7.0, SQL Server 6.5 and earlier, Oracle, Other
Users have accessed this code 2394 times.
|
(About the author) |
| | | It is a SQLServer sp that Links SQLServer and Oracle on line installing Oracle client in your SQLServer DataBase server. Without Oracle“s Dblink and without knowledge Oracle“s code programming.
| |
| |
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
|
--**************************************
--
-- for :Link_SQLServer_Oracle
--**************************************
--
WITH EXEC sp_helpserver you are sure that was created a linkedserver. |
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
1) You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this code from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the code or code's description. |
--**************************************
--
-- Name: Link_SQLServer_Oracle
-- Description:It is a SQLServer sp that
-- Links SQLServer and Oracle on line inst
-- alling Oracle client in your SQLServer D
-- ataBase server. Without Oracle“s Dblink
-- and without knowledge Oracle“s code prog
-- ramming.
-- By: Daniel G. Menendez
--
-- Assumes:Are you sure Oracle Client is
-- installed in your SQLServer?
Are you sure your Oracle“s USER has permission on your SCHEMA or your Oracle“s objects?
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=526&lngWId;=5--for details.--**************************************
--
CREATEprocedure sp_Link_SQLServer_ORACLE
as
SET nocount ON
EXEC sp_addlinkedserver
@server = '10.20.10.XX', --destination Oracle IP or network_name
@srvproduct = 'Oracle', --always
@provider = 'MSDAORA', --always
@datasrc = 'ORCL' --It IS the alias name IN your server“s tnsnames.ora
GO
EXEC sp_addlinkedsrvlogin
@rmtsrvname = '10.20.10.xx', --destination Oracle IP or network_name
@useself = false,
@locallogin = 'sa', -- local SQLServer login
@rmtuser = 'SCOTT', -- Oracle Login
@rmtpassword = 'TIGER' -- Oracle Password
GO
/*If you put the network.name it isn“t necessary "[]"
but IF you put the IP address it IS mandatory
See that it“s necesary the pointsname..owner.table */
SELECT * FROM [10.20.10.XX]..OWNER.ORA_TABLE
GO
| |
| |
| Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
There are no comments on this submission.
|
|
| Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this code has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular code, please click here.
|
|
