Linked Servers +Microsft SQL Server, Excel, Access (Ms-Access)





Linked Servers + Distributed Queries

Linking to External Data Sources

Following stored procedure will allow you to create a link to another SQL Server. Link server name would be “SECOND”

EXEC sp_addlinkedserver

@server=‘myservername’,–name you will be using to refer link server @srvproduct = ‘Excel’, –Product name i.e excel @provider = ‘Microsoft.Jet.OLEDB.4.0’, –Jet engine

@datasrc = ‘c:\myexcelfile.xls’, –Excel file path

@provstr = ‘Excel 8.0’

To view the linked servers on your mchine or Server You can use following Query. SELECT [Name], Product, Provider, Data_source FROM sys.servers WHERE Is_Linked = 1; To Drop a linked server EXEC sp_DropServer @server = ‘Nyack’; Distributed Security and LoginsEXEC sp_addlinkedsrvlogin @rmtsrvname = ‘MyServer\SECOND’, @useself = ‘false’, @locallogin = ‘HPPRESENT\total’, @rmtuser = ‘sa’, @rmtpassword = ‘password’; Linking with ExcelEXEC sp_droplinkedsrvlogin ‘MyServer’, NULLEXEC sp_DropServer @server = ‘MyServer’; EXEC sp_addlinkedserver @server = ‘Argo’, @srvproduct = ‘Excel’, @provider = ‘Microsoft.Jet.OLEDB.4.0’, @datasrc = ‘C:\Path\Myfile.xls’, @provstr = ‘Excel 8.0’EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘MyServer’, @useself = ‘false’; Linking with Microsoft AccessEXEC sp_DropServer @server = ‘MyServer;EXEC sp_addlinkedserver ‘MyServer’, ‘Access 2003’, ‘Microsoft.Jet.OLEDB.4.0’, ‘C:\SQLData\MyAccessDatabase.mdb’;




Tags: , , , , , ,

Comments are closed.