1214879 - Creating a Report using ODBC with Stored Procedures and Temp Tables in SP fails

Symptom

An application uses Crystal Reports for Visual Studio as the reporting development tool.

When attempting to view a report that uses an ODBC connection to connect to a stored procedure that uses temporary tables, the following errors message may appears:

"Server Error in ' Application Name' Application

Exception of type System.Exception was thrown"

Failed to open the connection.: [Database Vendor Code: 53 ]

Invalid cursor state : [Database Vendor Code: 55 ]

Why does this error occur and how do you resolve it?

====================

NOTE:

Temp tables are defiend in the Stored Procedure with a preceding # character.

If you attempt to create a report off the same stored procedure, the following error message appears in Crystal Reports Designer:

"Query Engine Error: 24000:[Microsoft][ODBC SQL Server Driver]Invalid cursor state"

====================

Environment

Crystal Reports 2008
Crystal Reports 2011
Crystal Reports 2013
Crystal Reports 2016
Crystal Reports for Visual Studio

Reproducing the Issue

Using ODBCTest, downloaded from Microsoft.com executing the Stored Procedure generates this error on the second attempt, it does work the first time:

Full Connect(Use Driver)

Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

Successfully connected to DSN 'SQL2014'.
SQLExecDirect:
    In:    hstmt = 0x00366208,
          szSqlStr = "{CALL "xtreme"."dbo"."TestCRTime";1(N'retgsr', 1, {ts ...", cbSqlStr = -3
    Return: SQL_SUCCESS=0
SQLExecDirect:
    In:    hstmt = 0x00366208,
          szSqlStr = "{CALL "xtreme"."dbo"."TestCRTime";1(N'retgsr', 1, {ts ...", cbSqlStr = -3
    Return: SQL_ERROR=-1
    stmt:  szSqlState = "24000", *pfNativeError = 0, *pcbErrorMsg = 62
          MessageText = "[Microsoft][SQL Server Native Client 11.0]Invalid cursor state"

Resolution

This error occurs because ODBC does not support the use of temporary tables in a stored procedure.

To work around this issue, connect to the stored procedure using an OLE DB connection may work. Test....

Do not use temp tables if possible, due to permission and other access issues Temp Tables should not be used.

Keywords

.NET DOTNET STORED PROC TEMPORARY TABLE SYSTEM.EXCEPTION RUNTIME Crystal Reports for Visual Studio Stored Procedure Temporary Tables ODBC error 53, error 55, Invalid cursor state , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

SAP Crystal Reports, developer version for Microsoft Visual Studio ; SAP Crystal Reports, version for Visual Studio .NET 2005 ; SAP Crystal Reports, version for Visual Studio .NET 2008