Invalid Cursor State Error when connecting to a stored procedure

KNOWLEDGE BASE

Invalid Cursor State Error when connecting to a stored procedure

Published: 29 Dec 2015
Last Modified Date: 21 Aug 2019

Issue

When connecting to a stored procedure the following error message appears: "[Microsoft][SQL Server Native Client] Invalid cursor state."

Environment

Tableau Desktop
MS SQL Server

Resolution

Ensure that the stored procedure is returning a result set. The best sproc structure is one that defines the return table, inserts data into that table and returns that table at the end of the procedure. This ensures a consistant returned schema and one that always returns a result set to Tableau Desktop.

Cause

Stored Procedures can return anything. This can prove problematic for Tableau where a data source should have a static schema. Tableau Desktop will end up with the Invalid Cursor state error when stored procedures do not return any result set. This can be tested by running exec dbo.<sproc name> <parameters> in a third party tool such as SQL Management Studio. If the command runs with no results tab expect to receive an Invalid Cursor State error in Tableau Desktop.