KNOWLEDGE BASE

No Data Returned Connecting to Stored Procedures Containing VARCHAR(MAX) or EXEC INSERT


Published: 15 Feb 2018
Last Modified Date: 18 May 2018

Issue

When connecting to stored procedures containing EXEC INSERT or containing or referencing fields set to VARCHAR(MAX), no data is returned.

Environment

  • Tableau Desktop 10.3 - 10.5
  • Microsoft SQL Server

Resolution

Use one of the following workarounds:

Option 1

Upgrade to Tableau Desktop 2018.1.1, 10.5.4, 10.4.7, 10.3.11 or a later version. For more information, see Upgrading Tableau Desktop in Tableau Help.

Option 2

As appropriate, write the stored procedure to not use EXEC INSERT or reference other stored procedures, and/or change the metadata from VARCHAR(MAX) to something like VARCHAR(255). 

Option 3

Download the attached "disable exec insert.tdc" and save the file in the My Tableau Repository > Datasources folder.
It may be necessary to update "10.5" in the above file to the version of Tableau Desktop being used

Important: Tableau does not test or support TDC files. These files should be used as a tool to explore or occasionally address issues with your data connection. Creating and maintaining TDC files requires careful manual editing, and there is no support for sharing these files.

Option 4

The Microsoft OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement.

Example query: 
SELECT * FROM
OPENROWSET('SQLNCLI','SERVER=(local);Trusted_Connection=yes;UID=test;PWD=password;','EXEC [zpipkin].[dbo].[TableauTestProcedureNested]')

Cause

This behavior is related to a known issue (ID: 732364) which has been fixed in a recent release of Tableau Desktop. 

Additional Information

Additional details about this issue can also be found in the following third party article
Did this article resolve the issue?