SQL Server Linked Server Error could not map ordinals for one or more columns

By: Daniel Farina   |   Comments   |   Related: > Linked Servers

Problem

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.

Solution

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

Next Steps

• 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

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips