syntaxhelp.com Your online cheat sheet!

Configuring SQL Server Surface Area before using OPENROWSET

USE [master]
GO
sp_configure 'show advanced options',1
GO
reconfigure with override
GO
sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure with override
GO

Connecting from SQL Server to SQL Server with OPENROWSET (Windows Authentication)

-- server: server1
-- database: AdventureWorks

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=server1;Trusted_Connection=yes;',
       'SELECT GroupName, Name, DepartmentID
        FROM AdventureWorks.HumanResources.Department
        ORDER BY GroupName, Name') AS a;

Connecting from SQL Server to MS Access Database with OPENROWSET

-- Access MDB File: c:\Northwind.mdb

SELECT CustomerID, CompanyName
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
        'C:\Northwind.mdb';
        'admin';'',Customers)
GO

Connecting from SQL Server to an Excel File using OPENROWSET

-- Excel File: c:\data.xls
-- Worksheet : Sheet1

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=c:\data.xls', [Sheet1$])

Connecting from SQL Server to text file using OPENROWSET

-- file name: c:\data.txt

SELECT  * 
FROM OPENROWSET
        ( BULK 'C:\data.txt',SINGLE_CLOB) 
AS a

Generating an excel document with the output of a query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\testing.xls;',
    'SELECT * FROM [SheetName$]') select * from SQLServerTable