1 adding a linked server in SQL Server

1 linking to an instance of another SQL Server

EXEC sp_addlinkedserver  ' server address ', @srvproduct ='SQL Server'  Go

The return of XML data types is not supported in distributed queries

2 SQL Server connects to an external data source

Conditional OLE DB provider is available

When the spreadsheet is open in Excel, the SQL Server cannot hold row access to the Excel table's distributed query

exec sp_addlinkedserver @server = ' Template ',
@srvproduct = ' Jet 4.0 ',
@provider = ' microsoft.jet.oledb.4.0 ',
@datasrc = ' D:\Temp\Template.xlsx ',
@provstr = ' Excel 5.0 '
2 SQL Server uses the OPENDATASOURCE function to query another instance of SQL Server

Select  from OpenDataSource ('SQLOLEDB','Data source=szdcdevdb02; User Id=dmclient2; password=icanfly#2013'). [Securitydata]. [dbo]. [Performancebasics]

3 Opendatasouce function can also query Excel

Select  from OpenDataSource ('microsoft.jet.oledb.4.0','Data Source ' =d:\temp\template.xlsx; Extended properties=excel 5.0') ... A1

--4 Processing of distributed things

Set XACT_ABORT on-The connection option causes the current thing to roll back in the event of an error

BEGIN Distributed TRANSACTION--check if the DTC service is available

This contains WML action statements

Commit TRANSACTION--commit things

5 Database Sharding

Decompose the database into many smaller chunks, called shards, and then walk these chunks into many distributed servers

1 adding a linked server in SQL Server