2 Replies Latest reply on Oct 29, 2013 2:10 AM by Patrick Calnan

    Openquery-Error validating custom SQL connection

    Patrick Calnan

      Hi,

       

      I want to invoke stored procedures in SQL and bring them into Tableau. I have set up my linked-looped back server as per the following website:

      HOWTO: define a loopback linked server in MS SQL Server 2005

       

      I have tested the connection and it has been successful.

       

      I wanted to test it in Tableau with a simple stored proc that has no parameters

       

      select * from openquery(LINK, 'EXEC [dbo].[Max_Date]' )

       

      I have tested the code in SQL and it is fine. When I run it in Custom SQL I get the following error messages:

      1) SQK Server database error 0x80040E14: No Column was specified for column 1 of 'TableauSQL'

      2) Unable to connect to the server "ESGWRAP84". Check the server is running and that you have access privileges to the requested database.

       

      Is there additional security levels that I should apply for my Linked Server?

       

      PC

        • 1. Re: Openquery-Error validating custom SQL connection
          Russell Christopher

          Hey Patrick -

           

          I used to use this trick all the time in order to fire stored procedures. Always worked fine for me.

           

          A couple thoughts:

           

          • Have you confirmed that the troublee isn't the sproc itself? Can you get a resultset from another sproc with simple SQL like  SELECT * FROM Bar?
          • Can you fire a simple SELECT in place of EXEC [dbo].[sproc]? Maybe '.SELECT * from [some table in adventureworks]
          • In Tableau 8.1 (due out soon), you'll be able to execute stored procedures directly and won't have to jump through these hoops to begin with.
          • 2. Re: Openquery-Error validating custom SQL connection
            Patrick Calnan

            Thanks Russell,

             

            I have used a simple SELECT * FROM [TABLE} to ensure that the connection is working

             

            I have executed the sproc in SQL and it works so I believe it is the connection to the linked server. I am thinking it may have to do with permissions, but then I can pull back data using a SELECT statement.

            When I try the sproc via custom SQL it actually says I actually cant connect to the server?

             

            It would be great if this was resolved in Tableau 8.1

             

            PC