This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

Ad by

予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

Duno your purpose. But just give you the result from my platform. (2 SQL server in the network)

whereismyid(到处找ID)

3. you should modify Diff to DateDiff
Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))
Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From [LinkedServer].[testdb].[dbo].[VTABLE]
Where DateDiff(Day,EffectiveDate,getDate())<1

sELECT * FROM @temp

-- VTABLE records:
Code EffectiveDate Rate DDescription
cd1 2009-01-01 00:00:00.000 100 sql linked to sql
cd2 2009-01-02 00:00:00.000 200 sql linked to sql
cd3 2009-01-03 00:00:00.000 300 sql linked to sql
cd4 2009-01-21 00:00:00.000 400 sql linked to sql

-- Result:
cd4 2009-01-21 00:00:00.000 400.000000000

(#4997014@0)
Last Updated: 2009-1-21
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 学科技术讨论 / 紧急求助，数据库问题，谢谢！ -henhen(亨亨，明天会更好 *_*); 2009-1-21 {1192} (#4996875@0)
本文发表在 rolia.net 枫下论坛因对ORACLE不熟而且没法测试，请用ORACLE和SQL SERVER的朋友们帮个忙，测一下下面的CASE：

目的：在ORACLE LINKED SERVER上查询一个表或VIEW，把当天数据捣到sql server的表中

1. ORACAL上有个数据库XXX包含这样的VTABLE：
Code Varchar2(3)
EffectiveDate Datetime
Rate Numeber(12,9)
DDescription Varchar2(100)
输几条记录做测试用 ,请用不同日期,其中至少一个用当前日期

2. 在SQL SERVER 2005 上建立ORACLE LINKED SERVER，名称就叫ORALINKEDSrv好了

3. 在 SQL SERVER 2005上做下面的QUERY:

Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))

Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From ORALINKEDSrv.XXX.SYSOWNER.VTABLE -- SYSOWNER请改用实际OWNER名
Where Diff(Day,EffectiveDate,getDate())<1

sELECT * FROM @temp

Go

4.假如3不行，改用OPENQUERY -- 对其中ORACLE的QUERY可能有错，请修改

Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))

Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From OpenQuery(ORALINKEDSrv,'Select Code,trunc(EffectiveDate)=date(),Rate From XXX.SYSOWNER.VTABLE')

SELECT * FROM @temp

Go

请把3和4的结果贴在这里或者请PM，多谢！更多精彩文章及讨论，请光临枫下论坛 rolia.net

up -henhen(亨亨，明天会更好 *_*); 2009-1-21 (#4996917@0)
改正 -henhen(亨亨，明天会更好 *_*); 2009-1-21 {378} (#4996943@0)
4.假如3不行，改用OPENQUERY -- 对其中ORACLE的QUERY可能有错，请修改

Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))

Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From OpenQuery(ORALINKEDSrv,'Select Code,EffectiveDate,Rate From XXX.SYSOWNER.VTABLE where trunc(EffectiveDate)=[DATE]')

SELECT * FROM @temp

Go
How about SQL Server Replication? I used the service to replicate data from Oracle to SQL Server a couple of years ago and it worked very well. -renjl0810(相信网络,相信Google); 2009-1-21 (#4996983@0)
Duno your purpose. But just give you the result from my platform. (2 SQL server in the network) -whereismyid(到处找ID); 2009-1-21 {688} (#4997014@0)
3. you should modify Diff to DateDiff
Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))
Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From [LinkedServer].[testdb].[dbo].[VTABLE]
Where DateDiff(Day,EffectiveDate,getDate())<1

sELECT * FROM @temp

-- VTABLE records:
Code EffectiveDate Rate DDescription
cd1 2009-01-01 00:00:00.000 100 sql linked to sql
cd2 2009-01-02 00:00:00.000 200 sql linked to sql
cd3 2009-01-03 00:00:00.000 300 sql linked to sql
cd4 2009-01-21 00:00:00.000 400 sql linked to sql

-- Result:
cd4 2009-01-21 00:00:00.000 400.000000000
1. Date filter condition. In Oracle, it should be EffectiveDate >=TRUNC(Sysdate -1) or likewise. 2. Not sure what’s your goal, query (or join query) data in Oracle database from sql server, or transfer data from Oracle? -deep_blue(BLUE); 2009-1-21 (#4997085@0)

@Las Vegas

Duno your purpose. But just give you the result from my platform. (2 SQL server in the network)

Replies, comments and Discussions:

More Topics