All geek questions in one place

Unable to execute synonym stored procedure using SQL Server ODBC driver; works with OLEDB

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

The stored procedure runs perfectly 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 does a fine. I get the results. And everyone is happy.

But not with the ODBC driver

OleDb, ODBC SQL Server. ODBC SQL Server (, "{SQL Server}" ) SQL

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

:

"Report_ThirdParty" , "Report_ThirdParty"

, ODBC SQL Server :

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

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

:

[Microsoft] [ SQL Server 11.0] [SQL Server] "Report_ThirdParty" , "Report_ThirdParty"

ODBC:

[Microsoft] [ SQL Server ODBC] [SQL Server] "Report_ThirdParty" , "Report_ThirdParty"

:

"% s" , "% s" -

Errors Connection :

  • # 1

    • : 0x80040E14
    • : Microsoft OLE DB ODBC
    • : [Microsoft] [ SQL Server ODBC] [SQL Server] "Report_ThirdParty" , "Report_ThirdParty" .
    • SQLState: 37000
    • NativeError: 2809

  • # 2

    • : 0x80040E14
    • : Microsoft OLE DB ODBC
    • : [Microsoft] [ SQL Server ODBC] [SQL Server] .
    • SQLState: 37000
    • NativeError: 16945

ODBC. .

, , ODBC SQL Server ?

SQL Profiler

  • RPC: : @p1 int set @p1 = 0 @p3 int set @p3 = 16388 @p4 int set @p4 = 8193 @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
  • : : 2809, : 18, : 1
  • : "Report_ThirdParty" , "Report_ThirdParty" .
  • : : 16945, : 16, : 2
  • . .
  • RPC: :: @p1 int set @p1 = 0 @p3 int set @p3 = 16388 @p4 int set @p4 = 8193 @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

  • (.. .NET) . C, ++, Delphi.
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

ODBC ( OLEDB)

+2
1

, ( ) , :

:

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

( gui) dbo.fooSyn.

SQL Server 2014:

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

.

:

#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;
}

:

Result is: 16
Working using name
Working using synonym

, ?

:

  • - SQL Server 2014
  • SQL Server 11.0 2011.110.3000.00 ( SQL- )
  • Windows 7 prof
  • visual studio 2013.
  • odbc 3.8.
  • .
0

Source: https://habr.com/ru/post/1650374/

More articles:


All Articles