You are here:Home » tsql » Linked Server and OPENQUERY with Execl Source - SQL Server

Linked Server and OPENQUERY with Execl Source - SQL Server


As I have written posts for the linked servers with SQL Server option in previous post, the same way we have another option is available and these are with other data sources as a linked server. With SQL Server as a linked server, we can communicate with two SQL servers. But we can also communicate other data sources like excel, csv and others as well. Let us link our SQL server with Excel and get the data from excel. For that we need to go through the same way as I did for SQL Server linked server, but here we need to choose option of other data sources.


How can we do it using TSQL?
 
USE [master]
GO

EXEC [master].[dbo].[sp_addlinkedserver] 
@server='ExcelImport',
@srvproduct='Excel', 
@provider='Microsoft.Jet.OLEDB.4.0', 
@datasrc='D:\Import\Import_1.xls',
@provstr= 'Excel 8.0'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'EXCELIMPORT', @locallogin = NULL , @useself = N'False'
GO
It has other data source also like as following,

Now, after creating excel as linked server, we have time to communicate with it and fetch data through linked server & OPENQUERY
 
SELECT 
* 
FROM ExcelImport...[Sheet1$]

-- OR --

SELECT 
* 
FROM OPENQUERY(ExcelImport, 'SELECT * FROM [Sheet1$]')
Your comments are appreciated.