1 Reply Latest reply on Sep 25, 2013 2:41 PM by Jonah Kim

    help with syntax for OPENQUERY - use SQL Server Stored Procedure for data source

    Justin Larson

      Can someone help me with the correct syntax to use a stored procedure as a data source? I found this article:  Working with Stored Procedures and Functions | Tableau Software which pretty well spells out how to connect, but I can't seem to get the parameter correct. I put this together and successfully executed the stored procedure with no parameter, but after I added a date parameter to the sproc in SQL Server, I just can't get it to work. (yes, it executes just fine in SSMS)

       

      My ultimate goal is to provide the user with the ability to make a [Tableau] parameter selection that passes the value through to the stored procedure.

       

      The simple syntax in the custom SQL box, which worked before I added a parameter to the sproc looks like this:

       

           SELECT * FROM OPENQUERY(SERVERNAME, 'EXEC [BIDev].[Analysis].[sproc_KeyMeasures]')

       

      Great. Then I added a date parameter to the stored procedure. Back in Tableau, I've tried all sorts of syntax variations to get the parameter to accept a value, but Tableau keeps throwing an error. Slightly different messages for different syntax when I try to provide a hardcoded value, but for what seems like it should work:

       

           SELECT * FROM OPENQUERY(Ulysses, 'EXEC [BIDev].[Analysis].[sproc_KeyMeasures] @ReportDate=<Parameters.ReportDateSelector>')

       

      I get

      "

      SQL Server database error 0x80040E14: Incorrectsyntax near '2013'.

      "

      I did try to change the parameter data type to datetime (instead of date) to match SQL Server datatype, but same message.

       

      Any help? Anyone?

        • 1. Re: help with syntax for OPENQUERY - use SQL Server Stored Procedure for data source
          Jonah Kim

          The bad news:

          There’s currently a limitation with the OPENQUERY method and dates. The only workaround is to pass them as strings.

           

          A potential solution is to use a string parameter (e.g. drop down with list of dates or have the user type in dates).

           

          The string parameter can be cast inside the stored procedure do perform date operations in the query. The parameter name needs to be wrapped in quotes when using OPENQUERY.

           

          The good news:

          In 8.1 (releasing in the next few months) with certain databases, (Sybase ASE, Teradata, and SQL Server), you will be able to call a stored procedure and pass parameter values dynamically to the stored procedure to modify the result set.

           

          This will work with dates as parameters because we don’t use OPENQUERY with this new feature.