I've successfully imported data using DTS from an Oracle
Database to SQL Server. Now, I'd like to write a SP that
will be called periodically to append the "new" rows of
data that were added since the last update.
I have no idea how to code a remote connection in a SQL
Server Stored Proc. Can anyone send a small example
and/or point me to a good resource on the subject?
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Vuillermet Jacques
There are several ways to do that.
The easiest, I think, is to create a linked server with : sp_addlinkedserver @server = 'TheLinkName', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'SQLNetAliasDeclareInTNSNAMEORAFile' GO sp_addlinkedsrvlogin @rmtsrvname='TheLinkName', @useself='false', @locallogin='MyDomainMyLogin', @rmtuser='MyOracleUser', @rmtpassword='ItsPassword'
Then in your code you can write : SELECT * FROM TheLinkName..TheSchema.TheTable
or, SELECT * FROM TheLinkName..TheSchema.TheOracleTable A INNER JOIN MyLocalTable B ON A.Fld = B.Fld ...
For different ways see also : OPENQUERY, OPENROWSET and OPENDATASOURCE from SQL Server Books On Line.
Jacques.
"SC" a écrit dans le message de news: 473501c3e439$2d7b7400$
Hi,
I've successfully imported data using DTS from an Oracle Database to SQL Server. Now, I'd like to write a SP that will be called periodically to append the "new" rows of data that were added since the last update.
I have no idea how to code a remote connection in a SQL Server Stored Proc. Can anyone send a small example and/or point me to a good resource on the subject?
Thanks a lot,
SC
There are several ways to do that.
The easiest, I think, is to create a linked server with :
sp_addlinkedserver @server = 'TheLinkName', @srvproduct = 'Oracle',
@provider = 'MSDAORA', @datasrc = 'SQLNetAliasDeclareInTNSNAMEORAFile'
GO
sp_addlinkedsrvlogin @rmtsrvname='TheLinkName', @useself='false',
@locallogin='MyDomainMyLogin', @rmtuser='MyOracleUser',
@rmtpassword='ItsPassword'
Then in your code you can write :
SELECT *
FROM TheLinkName..TheSchema.TheTable
or,
SELECT *
FROM TheLinkName..TheSchema.TheOracleTable A
INNER JOIN MyLocalTable B
ON A.Fld = B.Fld ...
For different ways see also : OPENQUERY, OPENROWSET and OPENDATASOURCE from
SQL Server Books On Line.
Jacques.
"SC" <anonymous@discussions.microsoft.com> a écrit dans le message de news:
473501c3e439$2d7b7400$a001280a@phx.gbl...
Hi,
I've successfully imported data using DTS from an Oracle
Database to SQL Server. Now, I'd like to write a SP that
will be called periodically to append the "new" rows of
data that were added since the last update.
I have no idea how to code a remote connection in a SQL
Server Stored Proc. Can anyone send a small example
and/or point me to a good resource on the subject?
The easiest, I think, is to create a linked server with : sp_addlinkedserver @server = 'TheLinkName', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'SQLNetAliasDeclareInTNSNAMEORAFile' GO sp_addlinkedsrvlogin @rmtsrvname='TheLinkName', @useself='false', @locallogin='MyDomainMyLogin', @rmtuser='MyOracleUser', @rmtpassword='ItsPassword'
Then in your code you can write : SELECT * FROM TheLinkName..TheSchema.TheTable
or, SELECT * FROM TheLinkName..TheSchema.TheOracleTable A INNER JOIN MyLocalTable B ON A.Fld = B.Fld ...
For different ways see also : OPENQUERY, OPENROWSET and OPENDATASOURCE from SQL Server Books On Line.
Jacques.
"SC" a écrit dans le message de news: 473501c3e439$2d7b7400$
Hi,
I've successfully imported data using DTS from an Oracle Database to SQL Server. Now, I'd like to write a SP that will be called periodically to append the "new" rows of data that were added since the last update.
I have no idea how to code a remote connection in a SQL Server Stored Proc. Can anyone send a small example and/or point me to a good resource on the subject?
