Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

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


By:   |   Last Updated: 2015-11-25   |   Comments   |   Related Tips: More > 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:

The OLE DB provider "SQLNCLI11" for linked server "WIN-CUGJJ179UCQ\SQL01" could not map ordinals for one or more columns of object "DBSCHEMA_TABLE_STATISTICS"

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.

DBCC SHOW_STATISTICS Output.

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.

DBCC SHOW_STATISTICS Output with trace flag 2388.
DBCC SHOW_STATISTICS Output with trace flag 2388.

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.

Linked Server SetUp - General Page.
Linked Server SetUp - Security Page.

Notice in the configuration below on the Server Options page that I enabled RPC.

Linked Server SetUp - Server Options Page.

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 

Error Message.

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

Select Statement Executed Without Errors.
Next Steps


Last Updated: 2015-11-25


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools