All these configuration options can also be done very easily with SQL Server Management Studio. The following sections occasionally describe that capability but focus on the SQL commands method because you will usually use this method in real-life production systems.
Log on to MS-SQL-SERVER 2012
To create a linked server to access an Oracle database instance
Make sure the Oracle client software on the server that is running Microsoft SQL Server is at the level required by the provider. The Oracle Provider for OLE DB (8.1.7.3.0) requires Oracle Client 8.1.7.0.
Create an alias name on the server that is running SQL Server that points to an Oracle database instance. For more information, see the Oracle documentation.
Execute sp_addlinkedserver to create the linked server, specifying OraOLEDB.Oracle as provider_name, and the alias for the Oracle database as data_source.
The following example assumes that the alias has been defined as DQORA8:
Log on to MS-SQL-SERVER 2012
Click on New Query
Type...
exec sp_addlinkedserver @server='cdbt11dgbiap01k',
@srvproduct='Oracle',
@provider='OraOLEDB.Oracle',
@datasrc='GBISYST'
Click Execute
Use sp_addlinkedsrvlogin to create login mappings from SQL Server logins to Oracle logins.
The following example maps the SQL Server login Petera (local login Administrator SQL-SERVER) to the linked server defined in step 3 by using the Oracle login and password OrclUsr and OrclPwd:
Click on New Query
Type...
exec sp_addlinkedsrvlogin @rmtsrvname = 'cdbt11dgbiap01k',
@useself = 'false',
@locallogin = 'petera',
@rmtuser = 'gbitest',
@rmtpassword = 'gbi'
Click Execute
Log off SQL-SERVER
Log on
Click on Database
Click on Server Object
Click on Linked Server
...should see your recently registered LinkedServer
sp_addlinkedserver
Before you can access an external data source through SQL Server, it must be registered inside the database as a linked server. Essentially, you must capture (register) the connection information and specific data source information within SQL Server. After it is registered, the data source can simply be referenced within the SQL statement by a single logical name. You use the sp_addlinkedserver stored procedure for this registering purpose. Only users with the sysadmin or setupadmin fixed server roles can run this procedure.
SQL Server 2008 ships with a number of OLE DB providers, including providers for Oracle, DB2, Informix, Access, and other SQL Server 6.5/7.0/2000/2005 databases, as well as databases that can be reached through ODBC and JDBC. SQL Server also comes with OLE DB providers for Microsoft Excel spreadsheets and Indexing Service and a whole bunch more. Microsoft puts each of these providers through extensive testing to certify them. We have found that sometimes a provider isn’t available on the 64-bit version of SQL Server 2008 yet. So, as a precaution, check the list of 64-bit providers before you upgrade your SQL Server to the 2008 64-bit version. If you are not using a Microsoft-certified provider, however, you might still be able to use that provider if it is compliant with the OLE DB provider specifications.
Tip
When a linked server is created using sp_addlinkedserver, a default self-mapping is added for all local logins. This means that for non–SQL Server providers, SQL Server authenticated logins may be able to gain access to the provider under the SQL Server service account. If you want, you can issue the sp_droplinkedsrvlogin command to remove these mappings.
Figure 1 depicts the overall technical architecture of what is being enabled via linked servers and providers. There is a SQL Server 2008 side set of provider DLLs, along with a client-side provider that communicates directly with the data sources. Clients usually connect to SQL Server 2008 via the native SQLNCLI provider (OLE DB) or via the ODBC provider.
Figure 1. Linked servers provider architecture.
image: http://mscerts.wmlcloud.com/image/201010/Adding%20Dropping%20and%20Configuring%20Linked%20Servers_1.jpg
This provider architecture allows data sources to be accessed from within SQL Server 2005 with the highest degree of efficiency and integrity possible.
Some of the arguments for sp_addlinkedserver are needed only for certain OLE DB providers. Because of the number of different options and settings available, you should always double-check the documentation for the OLE DB provider to determine exactly which arguments must be provided and what strings are to be specified. Following is the sp_addlinkedserver procedure:
sp_addlinkedserver [@server =] 'server'
[, [@srvproduct =] 'product_name']
[, [@provider =] 'provider_name']
[, [@datasrc =] 'data_source']
[, [@location =] 'location']
[, [@provstr =] 'provider_string']
[, [@catalog =] 'catalog']
- provider_name— The unique programmatic identifier (PROGID). This value must match the PROGID in the Registry for the particular OLE DB provider (@provider parameter). The following are the OLE DB providers delivered with SQL Server and the corresponding values for the provider_name argument:
OLE DB Provider Value (PROGID) SQL Server SQLNCLI (OLE DB provider) SQL Server SQLOLEDB Access DB/Jet Microsoft.Jet.OLEDB.4.0 (32-bit only) Excel spreadsheets Microsoft.Jet.OLEDB.4.0 (32-bit only) ODBC MSDASQL DB2 DB2OLEDB Oracle MSDAORA (32-bit only) Oracle, Version 8 or later OraOLEDB.Oracle (32-bit only) File system MSIDXS (through Indexing Service) - data_source— A data source that points to the particular version of the OLE DB source (@datasrc parameter). For example, for setting up an Access linked server, this argument holds the path to the file. For setting up a SQL Server linked server, this argument holds the machine name of the linked SQL Server. Following are the OLE DB providers delivered with SQL Server and the corresponding values for this argument:
OLE DB Provider Value SQL Server Network name of the SQL Server Access DB/Jet Full pathname to the file Excel spreadsheet Full pathname to spreadsheets ODBC System DSN or ODBC connection string Oracle SQL*Net alias Oracle, Version 8 or later Alias for the database DB2 Catalog name of the database File System Indexing Service catalog name (Indexing Service)
The SQLNCLI native SQL provider uses the same OLE DB provider code, so it is considered the same provider as SQLOLEDB. There are also many other “certified” providers, such as an Analysis Services data mining model provider, an Analysis Services OLAP provider, XML providers, SSIS (DTS) providers, PostgresSQL providers, and even data replication providers. Figure 2 shows the entire list of providers available within the Linked Servers node of SQL Server Management Studio.
Figure 2. Linked server providers supplied by Microsoft with SQL Server 2008.
image: http://mscerts.wmlcloud.com/image/201010/Adding%20Dropping%20and%20Configuring%20Linked%20Servers_2.jpg
The following example adds an Oracle linked server called 'ORACLE_DATABASE' that connects to the database specified by the SQL*Net string 'my_sqlnet_connect_string':
EXEC sp_addlinkedserver @server='ORACLE_DATABASE',
@srvproduct='Oracle', @provider='MSDAORA',
@datasrc='my_sqlnet_connect_string'
The next example creates a linked server reference for an Access database called CustomersPlus.mdb. As you can see in Figure 3, this Microsoft Access database is an Access 2000 file format database.
Figure 3. The CustomersPlus.mdb Microsoft Access database.
image: http://mscerts.wmlcloud.com/image/201010/Adding%20Dropping%20and%20Configuring%20Linked%20Servers_3.jpg
The following example adds an Access database linked server entry called 'ACCESS_DATABASE_CUSTOMERS' that establishes a connection to the database 'CustomersPlus.mdb' stored in the C:\temp directory:
EXECUTE sp_addlinkedserver @server='ACCESS_DATABASE_CUSTOMERS',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
The CustomersPlus.mdb is available on the CD included with this book For this example, copy this Access database file from the CD onto the same server machine on which you have SQL Server installed. The Microsoft Access client provider also needs to be present on this machine (which it will be you have installed Microsoft Office on the system). You can now reference this linked server directly from a SQL statement. sp_addlinkedserver and all other SQL statements for this linked server set of examples are also on the CD for this book in the script file LinkedServerSQL.sql. Here is a small sample of SQL code that selects all values from this Access database linked server entry:
SELECT CustomerID, ContactName, City, Country
FROM ACCESS_DATABASE_CUSTOMERS...SWCustomers
ORDER BY ContactName
Go
CustID ContactName City Country
------ ------------------ ---------- ---------------
PICCO Adam Greifer Los Angele USA
CHOPS Martin Sommer Berkeley USA
MEREP Thierry Gerardin Vannes FRANCE
BLAUS Juliana Bertucci Eugene USA
HANAR Yves Moison Paris FRANCE
(5 row(s) affected)
Here’s an example that adds a “SQL Server” linked server that resides on the 'DBARCH-LT2\SQL08DE01' machine:
EXECUTE sp_addlinkedserver @server='DBARCH-LT2\SQL08DE01',
@srvproduct='SQL Server'
----------------------------------------------------
-- Selecting data from the Linked Server directly --
----------------------------------------------------
SELECT TOP 10
[CustomerID]
,[CustomerName]
,sum([YTDSalesTotal]) as 'YTD Sales'
FROM [DBARCH-LT2\SQL08DE01].[Unleashed].[dbo].[HotCustomerPlus]
GROUP BY [CustomerID]
,[CustomerName]
ORDER BY 3 desc
go
CustomerID CustomerName YTD Sales
----------- ---------------------------------------- -------------
85 Roadway Bicycle Supply 188509.608000
599 Westside Plaza 178835.169285
433 Thorough Parts and Repair Services 168182.664000
205 Rally Master Company Inc 146097.433161
546 Field Trip Store 135208.616867
193 Perfect Toys 134068.411416
697 Brakes and Gears 125784.511476
448 Action Bicycle Specialists 122380.103796
506 Great Bikes 122283.656936
230 Global Bike Retailers 115936.910796
(10 row(s) affected)
/* Set up of an Excel linked server */
EXEC sp_addlinkedserver
'ExcelSW', /* linked server name you want to use*/
'Jet Excel', /* product name – can be anything */
'Microsoft.Jet.OLEDB.4.0', /* OLE provider name */
'd:\SWCustomers.xls', /* datasource name */
NULL, /* location not needed in this case */
'Excel 8.0', /* Provider string if needed */
NULL /* catalog name if needed */
go
This example adds an ODBC data source as a linked server called 'ODBC_with_DATA_SOURCE'. The ODBC connection string must be registered on the local server to use this linked server:
EXEC sp_addlinkedserver
@server='ODBC_with_DATA_SOURCE',
@srvproduct='ODBC',
@provider='MSDASQL',
@datasrc='My_ODBC_connection_string'
This example adds an ODBC data source as a linked server called 'ODBC_with_PROVIDER_STRING'. Unlike with the previous example, an ODBC data source does not need to exist. The information normally stored as an ODBC data source is stored in the provstr argument:
EXEC sp_addlinkedserver
@server='ODBC_with_PROVIDER_STRING',
@srvproduct='ODBC',
@provider='MSDASQL',
@provstr='DRIVER={SQL Server}; SERVER=MyServer; UID=sa;PWD=;'
SELECT substring(CustomerID,1,5) as 'CustID',
substring(ContactName,1,18) as ContactName,
substring(City,1,10) as 'City',
substring(Country,1,15) as 'Country'
FROM [Linked ExcelSW]...[SWCustomers$]
UNION
SELECT substring(CustomerID,1,5) as 'CustID',
substring(ContactName,1,18) as ContactName,
substring(City,1,10) as 'City',
substring(Country,1,15) as 'Country'
FROM ACCESS_DATABASE_CUSTOMERS...SWCustomers
go
CustID ContactName City Country
------ ------------------ ---------- ---------------
BLAUS John Monroe Concord USA
BLAUS Juliana Bertucci Eugene USA
CHOPS Martin Sommer Berkeley USA
HANAR Yves Moison Paris FRANCE
MEREP Thierry Gerardin Vannes FRANCE
PICCO Adam Greifer Los Angele USA
(6 row(s) affected)
In this example, you unite customers from the Access database with customers from the Excel spreadsheet, all as a single SQL statement within SQL Server.
sp_linkedservers
EXEC sp_linkedservers
Go
SRV_NAME SRV_PROVIDERNAME SRV_PRODUCT SRV_DATASOURCE
------------------------- ----------------------- ---------- ---------------------
ACCESS_DATABASE_CUSTOMERS Microsoft.Jet.OLEDB.4.0 Access C:\temp\CustomersPlus.mdb
DBARCHLT\SQL08DE04 SQLNCLI SQL Server DBARCHLT\SQL08DE04
Linked ExcelSW Microsoft.Jet.OLEDB.4.0 Jet Excel
C:\TEMP\SWCustomers.xls
(3 row(s) affected)
Since the introduction of SQL Server 2005, you can also get the same information via system views set up for this purpose. In this case, you can query the system catalog view sys.servers directly to get your information on linked servers:
select server_id,
substring(name,1,26) as 'name',
substring(product,1,10) as 'Product',
substring(provider,1,24) as 'Provider',
substring(data_source,1,25) as 'Source'
from sys.servers
order by server_id
go
id name Product Provider Source
---- -------------------------- ---------- ------------------------ --------------
0 DBARCHLT\SQL08DE04 SQL Server SQLNCLI DBARCHLT\SQL08DE04
3 ACCESS_DATABASE_CUSTOMERS Access –Microsoft.Jet.OLEDB C:\temp\
CustomersPlus.mdb
4 Linked ExcelSW Jet Excel –Microsoft.Jet.OLEDB C:\TEMP\
SWCustomers.xls
(3 row(s) affected)
sp_dropserver
To unregister linked servers, you can use sp_dropserver. Only members of the sysadmin and setupadmin fixed server roles can execute this stored procedure:
Code View: Scroll / Show All
sp_dropserver [@server =] 'server' [, [@droplogins =] {'droplogins' | NULL}]
Code View: Scroll / Show All
EXECUTE sp_dropserver @server='ORACLE_DATABASE', @droplogins='droplogins'
EXECUTE sp_dropserver @server='ACCESS_DATABASE_CUSTOMERS'
EXECUTE sp_dropserver @server='DBARCH-LT2\SQL08DE01',@droplogins='droplogins'
sp_serveroption
You can configure linked servers by using sp_serveroption. This affects how distributed queries behave at the linked server provider level. In other words, the options you set with the sp_serveroption procedure are for a particular linked server entry only. Only users with the sysadmin or setupadmin fixed server roles can run this procedure, which has the following syntax:
sp_serveroption [[@server =] 'server']
[,[@optname =] 'option_name']
[,[@optvalue =] 'option_value']
The following example disables distributed queries to the ORACLE_DATABASE linked server:
EXECUTE sp_serveroption @server='ORACLE_DATABASE',
@optname='data access', @optvalue='FALSE'
EXECUTE sp_serveroption @server='SQL_SERVER_DB',
@optname='rpc out', @optvalue='TRUE'
To set the query timeout to 60 seconds for the SQL Server data source, you execute the following command:
EXECUTE sp_serveroption 'DBARCH-LT2\SQL08DE01', 'query timeout', 60
EXECUTE sp_helpserver @server='DBARCH-LT2\SQL08DE01'
GO
name network_name status id connect..
------------------- ------------------- ------- -- ---------
Read more at http://mscerts.wmlcloud.com/sql_server/Adding,%20Dropping,%20and%20Configuring%20Linked%20Servers%20.aspx#kw0jHEw2lDQu4MIt.99
No comments:
Post a Comment