Cannot execute synonym stored procedure with SQL Server ODBC Driver; works with OLEDB

Question

This question is part in a series of bugs in the Microsoft ODBC driver:

ODBC driver fails to raise errors; but instead suppresses them

Reading columns out of order returns incorrect results

Cannot execute a stored procedure that is a SYNONYM

Microsoft has said they will not be fixing these bugs in their ODBC driver.

I have (many) stored procedures that are actually synonyms. The stored procedure exists canonically in one database, but are visible in others.

The stored procedure executes fine from within SQL Server Management Studio:

EXECUTE Report_ThirdParty @ContosoGUID = '{CC0ECA32-BEFA-11E5-8E2A-C86000D0B92A}'

And if I connect to SQL Server using any OLEDB provider:

SQL Server Native Client 10.0 OLE DB Provider: Provider=SQLNCLI10;Data Source=contoso.stackoverflow.com;User ID=ContosoManager;Password=correct horse battery staple;
Microsoft OLE DB Provider for SQL Server: Provider=SQLOLEDB;Data Source=contoso.stackoverflow.com;User ID=contoso.stackoverflow.com;Password=correct horse battery staple;

Then the stored procedure executes fine. I get results. And everyone's happy.

But not with the ODBC driver

With the announcement of the deprecation of OleDb drivers, I wanted to test using the ODBC drivers for SQL Server. When I change the connection to use one of the SQL Server ODBC drivers (e.g. "{SQL Server}") and execute the same SQL statement

EXECUTE Report_ThirdParty @ContosoGUID = '{CC0ECA32-BEFA-11E5-8E2A-C86000D0B92A}'

I get the error:

The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object

This is true whether I use the original ODBC driver for SQL Server, or the native client:

SQL Server: Provider=MSDASQL;Driver={SQL Server};Server={contoso.stackoverflow.com};UID={contosoManager};PWD={correct horse battery staple};
SQL Server Native Client 11.0: Provider=MSDASQL;Driver={SQL Server Native Client 11.0};Server={contoso.stackoverflow.com};UID={ContosoManager};PWD={correct horse battery staple};

In both variations i get the same error:

[Microsoft][SQL Server Native Client 11.0][SQL Server]The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object

or for the older ODBC driver:

[Microsoft][ODBC SQL Server Driver][SQL Server]The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object

In other words:

The request for procedure '%s' failed because '%s' is a synonym object

The Errors collection of the Connection provides more information:

Error#1
- Number: 0x80040E14
- Source: Microsoft OLE DB Provider for ODBC Drivers
- Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object.
- SQLState: 37000
- NativeError: 2809
Error#2
- Number: 0x80040E14
- Source: Microsoft OLE DB Provider for ODBC Drivers
- Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The cursor was not declared.
- SQLState: 37000
- NativeError: 16945

There's no harm in abandoning the switch to ODBC. And I'm not going to stop using synonyms.

But what is wrong, and how do i tell the ODBC Driver for SQL Server to work?

The SQL Profiler results

RPC:Starting: declare @p1 int set @p1=0 declare @p3 int set @p3=16388 declare @p4 int set @p4=8193 declare @p5 int set @p5=0 exec sp_cursoropen @p1 output,N'EXECUTE Report_ThirdParty @ContosoGUID = ''{3492C4E6-D500-4A23-9CAB-CB6582C27ABD}''',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5
Exception: Error: 2809, Severity: 18, State: 1
User Error Message: The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object.
Exception: Error: 16945, Severity: 16, State: 2
User Error Message: The cursor was not declared.
RPC:Completed: : declare @p1 int set @p1=0 declare @p3 int set @p3=16388 declare @p4 int set @p4=8193 declare @p5 int set @p5=0 exec sp_cursoropen @p1 output,N'EXECUTE Report_ThirdParty @ContosoGUID = ''{3492C4E6-D500-4A23-9CAB-CB6582C27ABD}''',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5

Notes

Native (i.e. not .NET) code. You can pretend it's C, C++, assembly, or Delphi.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

Other unintended features of ODBC (that work in OLEDB)

Stackoverflow: Reading columns out of order returns incorrect results
Stackoverflow: Cannot execute a stored procedure that is a SYNONYM
Stackoverflow: ODBC driver suppresses errors

Bonus Reading

KB928372: FIX: Error message when you use a synonym for a stored procedure in SQL Server 2005: "A severe error occurred on the current command"

Have you run a trace and captured the command text ODBC is sending? — Alex K.
The ODBC driver is always going to call sp_cursoropen, for me I see the same errors as you using that in SSMS with an SP synonym so its presumably issue with the implementation of that system SP, something else to research — Alex K.
You probably already saw these, but, FWIW, this post from what appears to be an MSFT employee suggests that there's limited support but not much elaboration. This entry suggests that it's a known limitation under investigation. — 500 - Internal Server Error
I cannot reproduce this in Sql Server 2014 using C++ and Sql Server native client 11. But in my simple test case the the stored procedure and the synonym are both in the same database. — erg

erg erg · Accepted Answer · 2016-02-10T11:01:57

As a follow up to my comment, here is my (somewhat no-so-mimimal) example that works here:

A very simple stored procedure:

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[foo] 
@p1 int
AS
RETURN 13 + @p1

And a synonym to that procedure has been created (using gui tools) named dbo.fooSyn.

I can execute both from within SQL Server 2014 Management studio:

execute foo @p1 = 2
GO
execute fooSyn @p1 = 3
GO

both statements complete with no error.

And here is my test code to execute once using the name and once using the synonym:

#include <windows.h>
#include <tchar.h>
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <sqlucode.h>   

void printErrDbc(SQLHDBC hDbc)
{
    SQLSMALLINT recNr = 1;
    SQLRETURN ret = SQL_SUCCESS;
    while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
        SQLWCHAR sqlState[5 + 1];
        errMsg[0] = 0;
        SQLINTEGER nativeError;
        SQLSMALLINT cb = 0;
        ret = SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
        }
        ++recNr;
    }
}

void printErrStmt(SQLHSTMT hStmt)
{
    SQLSMALLINT recNr = 1;
    SQLRETURN ret = SQL_SUCCESS;
    while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
        SQLWCHAR sqlState[5 + 1];
        errMsg[0] = 0;
        SQLINTEGER nativeError;
        SQLSMALLINT cb = 0;
        ret = SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
        }
        ++recNr;
    }
}

int _tmain(int argc, _TCHAR* argv[])
{
    SQLRETURN   nResult = 0;
    SQLHANDLE   handleEnv = 0;

    nResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, (SQLHANDLE*)&handleEnv);

    nResult = SQLSetEnvAttr(handleEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, SQL_IS_INTEGER);

    SQLHANDLE   handleDBC = 0;
    nResult = SQLAllocHandle(SQL_HANDLE_DBC, handleEnv, (SQLHANDLE*)&handleDBC);

    SQLWCHAR     strConnect[256] = L"Driver={SQL Server Native Client 11.0};Server=.\\INSTANCE;Database=Test;Trusted_Connection=yes;";
    SQLWCHAR     strConnectOut[1024] = { 0 };
    SQLSMALLINT nNumOut = 0;
    nResult = SQLDriverConnect(handleDBC, NULL, (SQLWCHAR*)strConnect, SQL_NTS, (SQLWCHAR*)strConnectOut, sizeof(strConnectOut),
        &nNumOut, SQL_DRIVER_NOPROMPT);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrDbc(handleDBC);
    }

    nResult = SQLSetConnectAttr(handleDBC, SQL_ATTR_AUTOCOMMIT, (SQLUINTEGER)SQL_AUTOCOMMIT_OFF, NULL);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrDbc(handleDBC);
    }

    SQLHSTMT    handleStatement = 0;
    nResult = SQLAllocHandle(SQL_HANDLE_STMT, handleDBC, (SQLHANDLE*)&handleStatement);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrDbc(handleDBC);
    }

    // Bind return code
    SQLINTEGER res = 0;
    SQLLEN cb = 0;
    SWORD sParm1 = 0;
    SQLLEN cbParm1 = SQL_NTS;

    nResult = SQLBindParameter(handleStatement, 1, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &sParm1, 0, &cbParm1);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrStmt(handleStatement);
    }

    // And call using synonym name
    nResult = SQLExecDirect(handleStatement, L"{? = call fooSyn(3)}", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrStmt(handleStatement);
    }

    nResult = SQLFetch(handleStatement);
    std::wcout << L"Result is: " << sParm1 << std::endl;

    // Note: It also works using EXECUTE - but I dont remember how to read return value like that.
    nResult = SQLExecDirect(handleStatement, L"execute foo @p1 = 2", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrStmt(handleStatement);
    }
    else
    {
        std::wcout << L"Working using name" << std::endl;
    }
    nResult = SQLExecDirect(handleStatement, L"execute fooSyn @p1 = 2", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrStmt(handleStatement);
    }
    else
    {
        std::wcout << L"Working using synonym" << std::endl;
    }

    return 0;
}

This prints out the expected output:

Result is: 16
Working using name
Working using synonym

So, what is the difference to your setup?

To summarize my setup:

SQL Server 2014 Express Edition
SQL Server native Client 11.0 version 2011.110.3000.00 (But it also worked using just {SQL Server} as driver)
Windows 7 prof
Compiled with visual studio 2013.
using odbc version 3.8.
synonym and stored procedure in same database and same even in the same schema in this database.