Oracle Linked server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Oracle Linked server

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2009-04-06 : 14:22:25

Hi guys ,
i am pulling data from an oracle data base successfuly.
the thing is i have to update my table daily with ( yesterday's new records) and i do not know how to do this...
Any help will be greatly appreciated..
here is my code:
-- --Clear the linked server logins first
sp_droplinkedsrvlogin @rmtsrvname='LINKED_ORA', @locallogin=null

--Drop Linked Server

EXEC sp_dropserver LINKED_ORA

--add server

EXEC sp_addlinkedserver

@server='LINKED_ORA'
,@provider='MSDASQL'
,@srvproduct='Oracle'
,@datasrc='xxxxxx'

--add login - the DSN is protected by password

EXEC sp_addlinkedsrvlogin

@rmtsrvname='LINKED_ORA'
,@useself='false'
,@rmtuser='user'
,@rmtpassword='pass'

-- Verify tables OK

EXEC sp_tables_ex @table_server ='LINKED_ORA',@table_schema='Myschema'
--INSERT INTO dbo.ARS2009
SELECT * FROM OPENQUERY(LINKED_ORA,
'SELECT
TROUBLEID,
DATEADD(ss,CAST(CREATE_DATE AS int), ''01/01/1970'')AS CREATEDATE
FROM CLIENT_TROUBLE
WHERE CREATE_DATE between ''1230768001'' AND ''1238677385''');
GO
-- I am converting date from epoch to date using
-- DATEADD(ss,CAST(CREATE_DATE AS int), ''01/01/1970'')AS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources