无法使用SQL Server ODBC驱动程序执行同义词存储过程；与OLEDB一起使用 [英] Cannot execute synonym stored procedure with SQL Server ODBC Driver; works with OLEDB
问题描述
此问题是Microsoft ODBC中一系列错误的一部分 驱动程序:
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已表示不会在ODBC中修复这些错误 驱动程序.
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.
在SQL Server Management Studio中可以很好地执行存储过程:
The stored procedure executes fine from within SQL Server Management Studio:
EXECUTE Report_ThirdParty @ContosoGUID = '{CC0ECA32-BEFA-11E5-8E2A-C86000D0B92A}'
如果我使用任何OLEDB提供程序连接到SQL Server:
And if I connect to SQL Server using any OLEDB provider:
- SQL Server Native Client 10.0 OLE DB提供程序:
Provider=SQLNCLI10;Data Source=contoso.stackoverflow.com;User ID=ContosoManager;Password=correct horse battery staple; - 用于SQL Server的Microsoft OLE DB提供程序:
Provider=SQLOLEDB;Data Source=contoso.stackoverflow.com;User ID=contoso.stackoverflow.com;Password=correct horse battery staple;
- 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.
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}'
我得到了错误:
对过程'Report_ThirdParty'的请求失败，因为'Report_ThirdParty'是同义词对象
The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object
这是事实，无论我将原始的ODBC驱动程序用于SQL Server还是本机客户端:
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]对过程'Report_ThirdParty'的请求失败，因为'Report_ThirdParty'是同义词对象
[Microsoft][SQL Server Native Client 11.0][SQL Server]The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object
或对于较早的ODBC驱动程序:
or for the older ODBC driver:
[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]对过程'Report_ThirdParty'的请求失败，因为'Report_ThirdParty'是同义词对象
[Microsoft][ODBC SQL Server Driver][SQL Server]The request for procedure 'Report_ThirdParty' failed because 'Report_ThirdParty' is a synonym object
换句话说:
对过程'％s'的请求失败，因为'％s'是同义词对象
The request for procedure '%s' failed because '%s' is a synonym object
Errors
The Errors collection of the Connection provides more information:
-
错误#1
Error#1
- 编号::0x80040E14
- 来源:用于ODBC驱动程序的Microsoft OLE DB提供程序
- 描述: [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]由于"Report_ThirdParty"是同义词对象，因此对过程"Report_ThirdParty"的请求失败.
- SQLState: 37000
- NativeError :2809
- 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
错误#2
- 编号::0x80040E14
- 来源:用于ODBC驱动程序的Microsoft OLE DB提供程序
- 描述: [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]未声明游标.
- SQLState: 37000
- NativeError: 16945
- 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
放弃切换到ODBC并没有什么害处.而且我不会停止使用同义词.
There's no harm in abandoning the switch to ODBC. And I'm not going to stop using synonyms.
但是什么地方出了问题，我如何告诉ODBC驱动程序使SQL Server正常工作?
But what is wrong, and how do i tell the ODBC Driver for SQL Server to work?
- RPC:开始:声明@ p1整数集@ p1 = 0声明@ p3整数集@ p3 = 16388声明@ p4整数集@ p4 = 8193声明@ p5整数集@ p5 = 0 exec sp_cursoropen @ p1输出，N'EXECUTE Report_ThirdParty @ContosoGUID =``{3492C4E6-D500-4A23-9CAB-CB6582C27ABD}''，'@ p3输出，@ p4输出，@ p5输出选择@ p1，@ p3 ，@ p4，@ p5
- 异常:错误:2809，严重性:18，状态:1
- 用户错误消息:对过程"Report_ThirdParty"的请求失败，因为"Report_ThirdParty"是同义词对象.
- 异常:错误:16945，严重性:16，状态:2
- 用户错误消息:未声明光标.
- RPC:已完成 :: 声明@ p1整数集@ p1 = 0声明@ p3整数集@ p3 = 16388声明@ p4整数集@ p4 = 8193声明@ p5整数集@ p5 = 0 exec sp_cursoropen @ p1输出，N'EXECUTE Report_ThirdParty @ContosoGUID =``{3492C4E6-D500-4A23-9CAB-CB6582C27ABD}''，'@ p3输出，@ p4输出，@ p5输出选择@ p1，@ p3，@ p4，@ p5
- 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
注释
- 本机(即非.NET)代码.您可以假装它是C，C ++，汇编或Delphi.
- Microsoft SQL Server 2008 R2(SP2)-10.50.4000.0(X64)
- Stackoverflow:乱序读取列会返回错误的结果
- Stackoverflow:无法执行为SYNONYM的存储过程
- Stackoverflow: ODBC驱动程序抑制错误
- Stackoverflow: Reading columns out of order returns incorrect results
- Stackoverflow: Cannot execute a stored procedure that is a SYNONYM
- Stackoverflow: ODBC driver suppresses errors
推荐答案
作为我的评论的后续文章，这是我的(有点不那么简单)示例，在这里起作用:
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
并且已经使用gui工具创建了该过程的同义词，名为dbo.fooSyn.
And a synonym to that procedure has been created (using gui tools) named dbo.fooSyn.
我可以在SQL Server 2014 Management Studio中执行这两项操作:
I can execute both from within SQL Server 2014 Management studio:
execute foo @p1 = 2
GO
execute fooSyn @p1 = 3
GO
两个语句均完整无误.
这是我的测试代码，使用该名称执行一次，并使用同义词执行一次:
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?
总结我的设置:
- SQL Server 2014 Express Edition
- SQL Server本机客户端11.0版本2011.110.3000.00(但它仅使用{SQL Server}作为驱动程序也可以工作)
- Windows 7教授
- 与Visual Studio 2013一起编译.
- 使用odbc版本3.8.
- 同义词和存储过程在同一数据库中，甚至在该数据库中的相同架构中也是如此.
这篇关于无法使用SQL Server ODBC驱动程序执行同义词存储过程；与OLEDB一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
