/**

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博客

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