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