Invalid Cursor State Error when connecting to a stored procedure

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.

Additional Information

Triage:

Troubleshooting:

Run the following query in a third party tool such as SQL Management Studio:

exec dbo.<sproc name> <paramer name>=<parameter value being used in tableau data connection>

for example to test the sproc AdventureSproc that takes a salesid parameter the command would look like:

exec dbo.AdventureSproc @salesid=2500

You should see two tabs, a results tab and a messages tab. If you do not see a results tab the sproc needs to be fixed or you will get the Invalid Cursor Error in Tableau Desktop.
If there are two tabs collect the following and pass to resolution.