The following are known issues that you might encounter when using a SQL Server linked server to work with your data.
When using a linked server, the scripting options that are accessible from the Object Explorer are not supported. For example, if you try to execute a query using one of the Script Table As options, the Management Studio will return an error message stating that the specified table "contains no columns that can be selected or the current user does not have permissions on that object".
As a workaround for this issue, define your queries using the editor that opens when you click the New Query button in the toolbar instead of using the scripting options.
If a type conversion error occurs after you try to execute a query, then the result set might contain a column that is longer than 8000 bytes. This issue occurs because the maximum data length that SQL Server supports is 8000 bytes.
To resolve this issue, modify your query to exclude the column from your result set.
When using a linked server, filtered queries might take longer than expected to execute. This issue occurs because the filters are being processed by the linked server. By default, the linked server does not pass filters to the driver.
To resolve this issue, modify your query to enclose the filters in the OPENQUERY function. When you execute a query with the filters contained in the OPENQUERY function, the linked server passes the filters to the driver for processing, improving performance.
For example, if you execute the following query, the linked server will process the filters:
SELECT TOP 100 Profit FROM [YOURLINKEDSERVER].[default].[FoodMart].[Sales] WHERE [Profit] > 10000
To retrieve the same data but make sure that filtering is processed by the driver instead, execute the following query:
SELECT * FROM OPENQUERY(YOURLINKEDSERVER, 'SELECT TOP 100 Profit FROM Sales WHERE Profit > 10000')
For more information about the OPENQUERY function, see the "OPENQUERY (Transact-SQL)" article in the Transact-SQL Reference: https://msdn.microsoft.com/en-CA/library/ms188427.aspx.