Accessing Oracle during SQL Server Stored Procedures

1. sp_addmediaserver create a linked server (http://msdn.microsoft.com/zh-cn/library/ms190479.aspx)

Exec sp_addmediaserver
@ Server = 'servername' -- or server IP,
@ Srvproduct = 'oracle ',
@ Provider = 'msdaora ',
@ Datasrc = 'oracledbname ',
@ Location = NULL,
@ Provstr = NULL,
@ Catalog = NULL
 

 

[ @ Server =] ' Server '

The name of the linked server to be created.ServerThe data type of isSysname, No default value.

[ @ Srvproduct =] ' Product_name '

The product name of the ole db data source to be added as the linked server.Product_nameThe data type of isNvarchar (128)The default value is null. IfSQL Server, You do not have to specifyProvider_name,Data_source,Location,Provider_stringAndCatalog.

[ @ Provider =] ' Provider_name '

The unique programming identifier (progid) of the ole db access interface corresponding to this data source ). For the specified ole db access interface installed on the current computer,Provider_nameMust be unique.Provider_nameThe data type of isNvarchar (128)The default value is null.Provider_nameSqlncli is used. Sqlncli is the SQL local OLE DB access interface. The ole db access interface should be registered in the Registry with the specified progid.

[ @ Datasrc =] ' Data_source '

Name of the data source interpreted by the ole db access interface.Data_sourceThe data type of isNvarchar (4000).Data_sourcePassed as the dbprop_init_datasource attribute to initialize the ole db access interface.

[ @ Location =] ' Location '

The location of the database explained by the ole db access interface.LocationThe data type of isNvarchar (4000)The default value is null.LocationPassed as the dbprop_init_location attribute to initialize the ole db access interface.

[ @ Provstr =] ' Provider_string '

The connection string specified by the ole db access interface. It can identify a unique data source.Provider_stringThe data type of isNvarchar (4000)The default value is null.ProvstrOr passIdatainitializeOr set it to the dbprop_init_providerstring attribute to initialize the ole db access interface.

When creating a linked server for the ole db access interface of the SQL local client, you can use the server keyword as server =Servername/InstanceNameTo specify the specific SQL ServerServernameThe instance is the name of the computer that runs SQL Server,InstanceNameIs the name of the specific SQL server instance that the user will connect.

2. sp_add1_srvlogin

Exec sp_addmediasrvlogin @ rmtsrvname = ''servername'' -- or '192. 168.1.100 ',
@ Useself = false,
@ Locallogin = NULL,
@ Rmtuser = 'oracle _ username,
@ Rmtpassword = oracle_userpassword'

 

3. Select * From openquery (@ servername, ''Select ...)