By: Daniel Farina | Last Updated: 2015-11-25 | Comments | Linked Servers
You setup a SQL Server instance with a linked server. The linked server creation was fine, but when you tried to execute a SELECT statement to a remote table you get an error. In this tip I will explain one of the causes of this error message and how you can fix it.
Setting up a linked server to another SQL Server instance is a relatively easy task compared to setting up a linked server against other data providers like Active Directory or MySQL. We just have to enter the target server name and configure the security settings and it's done.
But this time that wasn't the case. The linked server was successfully created, but all queries failed with the following error message:
I had a clue about what was happening, but I decided to search in Google this error. What I have found was the following in the Advantage Database Server knowledge base (http://devzone.advantagedatabase.com/dz/Content.aspx?Key=17&RefNo=031028-1580), but the I didn't like the solution which was to execute the queries with the OPENQUERY() Transact-SQL command. This would require modifying all the existing code, so I proceeded on my own to find a better solution.
From the error message I realized that something with the output of the DBCC SHOW_STATISTICS command was wrong and that was causing the OLEDB provider to fail matching columns ordinals. For those of you who don't know, when you execute a query that contains a reference to a linked server the Query Optimizer retrieves the statistics of the remote server to create the most efficient execution plan.
Considering that both instances were working fine I suspected that the output of DBCC SHOW_STATISTICS must return a different number of columns on both servers and I was right because the target server had trace flag 2388 enabled which was causing the issue.
What is SQL Server Trace Flag 2388?
This trace flag causes the DBCC SHOW_STATISTICS command to show the nature of the leading column of statistics. It is used by experienced database administrators that need to know if the query optimizer treats the leading column as Ascending, Stationary or Unknown. Although it is used for testing purposes, some less experienced DBAs enable it by mistake when applying a patch like this one: FIX: Poor cardinality estimation when the ascending key column is branded as stationary in SQL Server.
Let's take a look at the standard output of DBCC SHOW_STATISTICS.
Now see how it changes when trace flag 2388 is enabled. I divided the screen capture below in order to show all of the output.
As you can see, the output is completely different and since we have identified the cause of the problem we can solve it very easily by disabling this trace flag with the following DBCC Command.
DBCC TRACEOFF(2388, -1)
Testing the Problem
In order to reproduce this error we need two instances of SQL Server, SQL01 and SQL02 and a sample database on SQL01.
USE [master] GO CREATE DATABASE [sampleDB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'sampleDB', FILENAME = N'E:\MSSQL\sampleDB.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'sampleDB_log', FILENAME = N'E:\MSSQL\sampleDB_log.ldf' , SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB) GO
Now we create a very simple table.
USE SampleDB GO CREATE TABLE SampleTable ( ID INT IDENTITY(1, 1) , Name VARCHAR(50) CONSTRAINT PK PRIMARY KEY CLUSTERED ( ID ) ) GO
Let's insert a few rows.
USE SampleDB GO INSERT INTO dbo.SampleTable ( Name ) VALUES ( 'John' ) GO 2000
The next script is to enable trace flag 2388 globally.
USE master GO DBCC TRACEON(2388, -1) GO
Now we create a user for the linked server.
USE master GO CREATE LOGIN link WITH PASSWORD = 'Pa$$w0rd' GO USE sampleDB GO CREATE USER link FOR LOGIN link GO ALTER ROLE db_owner ADD MEMBER link GO
On instance SQL02, we set up a linked server to SQL01.
Notice in the configuration below on the Server Options page that I enabled RPC.
We are now ready to reproduce the error. To do so, execute the following SELECT statement on SQL02. You may have to change the server name according to your environment.
USE master GO SELECT [ID] , [Name] FROM [WIN-CUGJJ179UCQ\SQL01].[sampleDB].[dbo].[SampleTable] GO
Now if we disable trace flag 2388 with the following script you can see that the select statement runs without errors.
USE master GO DBCC TRACEOFF(2388, -1) GO
- You can find more tips about Linked Servers on Linked Servers Tips Category.
- If you are interested on Cardinality Estimation issues you can read my previous tip Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON.
- If you want to learn more about statistics, read this tip How To Interpret SQL Server DBCC SHOW_STATISTICS Output
Last Updated: 2015-11-25
About the author
View all my tips