Troubleshooting problems with linked servers in SQL Server 2017 If you have problems with linked servers in SQL Server 2017, review this troubleshooting checklist to find potential solutions. 1. Check that you have appropriate permissions to create linked servers. To create a linked server, you must have ALTER ANY LINKED SERVER permission. So, if you have no this permission, you...
Tag: openquery
Tips for using SQL Server 2017 distributed queries
Tips for using SQL Server 2017 distributed queries Try to avoid using the TOP clause in the distributed queries. Because distributed queries that involve the TOP clause are never delegated to an OLE DB provider and are always evaluated locally, you should avoid using the TOP clause in the distributed queries to increase the local SQL Server performance. The first...
Tips for using linked servers in SQL Server 2017
Tips for using linked servers in SQL Server 2017 Ensure that the connection between the linked servers is fast. Ideally, the linked servers should be in the same subnet. Try to avoid using distribution transaction or minimize it using. Because distribution transactions incur more overhead than general transactions, avoid using distribution transactions, whenever possible. One of the first steps to...
Troubleshooting problems with linked servers in SQL Server 2016
Troubleshooting problems with linked servers in SQL Server 2016 If you have problems with linked servers in SQL Server 2016, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2016 service pack. Because some SQL Server 2016 linked servers bugs were fixed in SQL Server service packs, you should install the latest SQL Server service...
Some tips for using linked servers in SQL Server 2016
Some tips for using linked servers in SQL Server 2016 Try to avoid the ad hoc queries that use the OPENROWSET or the OPENDATASOURCE functions to access remote data. Create a linked server and perform a linked server’s query instead of using an ad hoc query with OPENROWSET or OPENDATASOURCE functions. Using the linked server’s queries to access remote data...
Some tips for using SQL Server 2016 distributed queries
Some tips for using SQL Server 2016 distributed queries Try to avoid using distribution queries or minimize it using. Because distribution transactions incur more overhead than general transactions, avoid using distribution queries, whenever possible. The first steps to optimize distributed queries against a SQL Server 2016 linked server is rewriting queries so, that the most work will be performed on...
Tips for using XML in SQL Server 2014
Tips for using XML in SQL Server 2014 Use SQLXML instead of Native XML Web Services. Beginning in SQL Server 2014, the Native XML Web Services is removed. So, you can no longer use CREATE ENDPOINT or ALTER ENDPOINT to add or modify SOAP/HTTP endpoints. Consider using the RAW mode of FOR XML queries, instead of AUTO or EXPLICIT modes....