ITPub博客

SQLServer建立DBLINK到Oracle,并使用opendatasource或者openquery查询

原创 Linux操作系统 作者:hwtong 时间:2011-12-21 10:36:30 0 删除 编辑
/**
Parameters description:
1.@server will be the name of the DBLink
2.@srvproduct is the product name of the OLE DB data source.
3.@provider is the provider_name.Data source is Oracle,so we need to specify it 'MSDAORA'
4.@datasrc is the name of the Oracle database connection string in tnsnames.ora file
**/
EXEC master.dbo.sp_addlinkedserver @server = N'WISON_DEV', @srvproduct=N'ORACLE', @provider=N'MSDAORA', @datasrc=N'NEO_ORACLE_DEV'
 /**
Then we need to configure the security 
  **/

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'WISON_DEV',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'WISON_DEV',@useself=N'False',@locallogin=N'sa',@rmtuser=N'APPSR',@rmtpassword='APPSR'

---Now we can use the DBLink to query the data from Oracle
SELECT * FROM OPENQUERY(WISON_DEV, 
'SELECT PO_NUMBER ,PO_LINE_NUM ,INVENTORY_ITEM_ID ,SHIP_QTY ,PART_NUMBER ,ITEM_REVISION ,PROCESS_FLAG,ERROR_MESSAGE,CREATION_DATE
FROM  XXN_PO_RECEIPT_DETAILS WHERE process_flag<>''Y'' and creation_date>sysdate-1/12') 


--If we don't create the dblink between Oracle and SQL Server,we can run the opendatasource to fetch the data.

SELECT * FROM OPENDATASOURCE('MSDAORA','DATA SOURCE=NEO_DEV;USER ID=APPSR;PASSWORD=APPSR')..APPSR.TEMP_ITEMS

--But if we created the dblink between Oracle and SQL Server,we can run the openquery to fetch the data,and can add some search conditions

SELECT * FROM OPENQUERY(NEO_ORACLE, 
'SELECT PO_NUMBER ,PO_LINE_NUM ,INVENTORY_ITEM_ID ,SHIP_QTY ,PART_NUMBER ,ITEM_REVISION ,PROCESS_FLAG,ERROR_MESSAGE,CREATION_DATE
FROM  XXN_PO_RECEIPT_DETAILS WHERE process_flag<>''Y'' and creation_date>sysdate-1/12') 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16754036/viewspace-713676/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-06

  • 博文量
    116
  • 访问量
    280318