/**
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16754036/viewspace-713676/