0 Replies - 1554 Views - Last Post: 05 July 2008 - 01:49 AM

#1 dineeshd   User is offline

  • member icon

Reputation: 39
  • View blog
  • Posts: 619
  • Joined: 30-June 08

MSSQL - Accessing Excel file data using Linked Server

Posted 05 July 2008 - 01:49 AM

Description: With this snippet you will be able to access MS Excel file data in MS SQL Server 2000.
/* Step 1 : Adding the Linked Server. Provide your Excel File path (@datasrc) */

EXEC sp_addlinkedserver 
@server = N'ExcelLnkSrv', 
@srvproduct = N'', 
@provider = N'Microsoft.Jet.OLEDB.4.0', 
@datasrc = N'C:Data.xls', 
@provstr = N'Excel 8.0;'

/* Step 2 : Creating login for Linked Server. If you are using a 
user name other than 'sa' then supply the user name. */

EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'ExcelLnkSrv',
@useself = 'false',
@locallogin = 'sa',
@rmtuser = 'admin',
@rmtpassword = NULL

/* Now use the openquery function to fetch data from excel file */

SELECT * FROM OPENQUERY(ExcelLnkSrv, 'Select * from [sheet1$]')

/* In case you want to delete either linked server or logins 
use below sp's */

/* For Dropping Linked Server Login */

EXEC sp_droplinkedsrvlogin 
@rmtsrvname = 'ExcelLnkSrv', 
@locallogin = 'sa'

/* For Dropping Linked Server */
EXEC sp_dropserver
@server = 'ExcelLnkSrv' ,
@droplogins = 'droplogins'



Is This A Good Question/Topic? 0
  • +

Page 1 of 1