You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. You have received instructions from management to allow the users at ABC.com on the
SQL Server sample to use the OPENROWSET() function to search remote information sources.
You study incomplete code in the exhibit carefully. (Line numbers are used for reference purposes
only)
Exhibit:
01. sp_configure ‘show advanced options’, 1
02. RECONFIGURE
03.
04. RECONFIGURE
05. GO
06.
07. SELECT a.*
08. FROM OPENROWSET(‘SQLNCLI’, ‘Server=Madrid;Trusted_Connection=yes;’,
09. ‘SELECT GroupName, Name, DepartmentID
10. FROM AdventureWorks.HumanResources.Department
11. ORDER BY GroupName, Name’) AS a;
12. GO
Which T-SQL statement should you insert at line 03 to make the batch meet the requirement in
this scenario?
A.
sp_configure ‘Router control’, 1
B.
sp_configure ‘Router control’, 0
C.
sp_configure ‘Transaction Logs’, 1
D.
sp_configure ‘Transaction Logs’, 0
E.
sp_configure ‘Ad Lib Distributed Queries ‘, 1
F.
sp_configure ‘Ad Lib Distributed Queries ‘, 0
G.
sp_configure ‘Ad Hoc Distributed Queries’, 1
H.
sp_configure ‘Ad Hoc Distributed Queries’, 0
Explanation:
By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and
OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this
option is not set or is set to 0, SQL Server does not allow ad hoc access.
Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to
connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE
should be used only to reference OLE DB data sources that are accessed infrequently. For any
data sources that will be accessed more than several times, define a linked server.