How to access remote server database tables

Working with Ad Hoc Distributed Queries in SQL Server

By default, SQL Server restricts the use of ad hoc distributed queries with functions like OPENROWSET and OPENDATASOURCE. This behavior enhances security by requiring administrators to explicitly enable these options before allowing access to external data sources.

To enable ad hoc queries, run the following commands:

-- Enable advanced options
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

-- Enable Ad Hoc Distributed Queries
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Once enabled, you can query remote data sources without creating a linked server. For example:

SELECT TOP 10 *
FROM OPENDATASOURCE('MSOLEDBSQL',
  'Server=myserver;Database=mydatabase;TrustServerCertificate=Yes;Trusted_Connection=Yes;').mydatabase.dbo.TableA;

🔗 Microsoft Docs: Ad Hoc Distributed Queries

---

Using OPENROWSET

The OPENROWSET function lets you access remote data using OLE DB in a one-time, ad hoc manner. It includes connection details and the query to execute against the remote source. While useful for occasional access, it's recommended to use linked servers for repeated queries.

Example:

SELECT a.*
FROM OPENROWSET('SQLNCLI', 
    'Server=DEO-myserver;Trusted_Connection=yes;', 
    'SELECT TOP 10 * FROM DbName.dbo.TableA') AS a;

🔗 Microsoft Docs: OPENROWSET

---

Using OPENQUERY

OPENQUERY allows you to execute a pass-through query on a linked server. The query runs directly on the remote server and can be used in SELECT, INSERT, UPDATE, or DELETE statements.

Examples:

-- Insert data
INSERT OPENQUERY(OracleSvr, 'SELECT name FROM joe.titles')  
VALUES ('NewTitle');

-- Update data
UPDATE OPENQUERY(OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')  
SET name = 'ADifferentName';

-- Delete data
DELETE OPENQUERY(OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

-- Query data
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

🔗 Microsoft Docs: OPENQUERY

---

Using OPENDATASOURCE

OPENDATASOURCE offers a way to define connection details inline in your query without setting up a linked server. It supports four-part naming to reference remote objects.

Examples:

-- Access a SQL Server instance
SELECT *  
FROM OPENDATASOURCE('SQLNCLI',  
    'Data Source=London\\Payroll;Integrated Security=SSPI').AdventureWorks2012.HumanResources.Employee;

-- Read from an Excel file
SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  
 'Data Source=C:\\DataFolder\\Documents\\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$];

-- Query data from another SQL Server
SELECT GroupName, Name, DepartmentID  
FROM OPENDATASOURCE('MSOLEDBSQL', 
  'Server=Seattle1;Database=AdventureWorks2016;TrustServerCertificate=Yes;Trusted_Connection=Yes;').HumanResources.Department  
ORDER BY GroupName, Name;

🔗 Microsoft Docs: OPENDATASOURCE
🔗 Importing Bulk Data with OPENROWSET and BULK INSERT

---

This article outlines how to use SQL Server’s ad hoc query features to interact with external data sources efficiently. These tools are especially useful for data migration, one-time imports, or interacting with legacy systems without altering your current environment.