SQL Server OPENROWSET

Configuring SQL Server Surface Area before using OPENROWSET

view source

print?

01	USE [master]

02

GO

03	sp_configure 'show advanced options',1

04

GO

05	reconfigure with override

06

GO

07	sp_configure 'Ad Hoc Distributed Queries',1

08

GO

09	reconfigure with override

10

GO

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

view source

print?

1	-- server: server1

2	-- database: AdventureWorks

3

4	SELECT a.*

5	FROM OPENROWSET('SQLNCLI', 'Server=server1;Trusted_Connection=yes;',

6	'SELECT GroupName, Name, DepartmentID

7	FROM AdventureWorks.HumanResources.Department

8	ORDER BY GroupName, Name') AS a;

Connecting from SQL Server to MS Access Database with OPENROWSET

view source

print?

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

2

3	SELECT CustomerID, CompanyName

4	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

5	'C:\Northwind.mdb';

6	'admin';'',Customers)

7

GO

Connecting from SQL Server to an Excel File using OPENROWSET

view source

print?

1	-- Excel File: c:\data.xls

2	-- Worksheet : Sheet1

3

4

SELECT *

5	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

6	'Excel 8.0;Database=c:\data.xls', [Sheet1$])

Connecting from SQL Server to text file using OPENROWSET

view source

print?

1	-- file name: c:\data.txt

2

3

SELECT *

4	FROM OPENROWSET

5	( BULK 'C:\data.txt',SINGLE_CLOB)

6

AS a

Generating an excel document with the output of a query

view source

print?

1	insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

2	'Excel 8.0;Database=D:\testing.xls;',

3	'SELECT * FROM [SheetName$]') select * from SQLServerTable