You are here:Home » tsql » Add Linked Server by script of SQL Server and Excel

Add Linked Server by script of SQL Server and Excel

USE master;
GO
EXEC sp_addlinkedserver 'SVR04', N'SQL Server'
GO

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'SVR04', @optname=N'data access', @optvalue=N'true'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SVR04', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dba@eetpl'
GO



------------



EXEC sp_addlinkedserver 'ExcelShare',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
NULL,
'Excel 5.0'


SELECT * FROM OPENQUERY(ExcelShare, 'SELECT * FROM [Sheet1$]')