9 Replies Latest reply on Jul 7, 2017 11:58 AM by Tom W

    Are dynamic sql queries posssible in tableau?

    L L

      I have a query that is is using dynamic sql and pivot to rename the columns.

       

        DECLARE @cols AS NVARCHAR(MAX)

        SET @cols = STUFF((SELECT distinct ',' + +QUOTENAME(c.Dim1) + ' nvarchar(max)'

                  FROM dbo.Comment c

                  FOR XML PATH(''), TYPE

                  ).value('.', 'NVARCHAR(MAX)')

              ,1,1,'')

        PRINT @cols

          DECLARE @sql NVARCHAR(MAX)

       

        SET @sql= 'EXEC dbo.GetCommentsDemo WITH RESULT SETS

        (([Report] nvarchar(max),

         [UserName] nvarchar(max),

         ' + @cols + ',

         [Comment] nvarchar(max)));'

        EXECUTE(@sql)

      END

       

       

      I have tryed this just as a custom sql query and also created a stored procedure and used

      SELECT * FROM OPENQUERY

      (localhost,'SET FMTONLY OFF; exec [TableauCommentApplication].[dbo].[GetCommentsDemoWrapper]');

      to try to execute the stored procedure and I keep getting this error.

       

      The metadata could not be determined because statement 'EXECUTE(@sql)' in procedure 'GetCommentsDemoWrapper'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

       

      Does anyone know how to make this work?

        • 1. Re: Are dynamic sql queries posssible in tableau?
          Tom W

          If you connect to a stored procedure which returns a result set, I don't think this should be a problem. So rather than using 'custom sql', just create a stored procedure and select it from the list of objects.

          The stored procedure should 'do all your stuff' then return a result set.

          • 2. Re: Are dynamic sql queries posssible in tableau?
            Haroldy Tevaras

            To follow up on this question:

             

            What if the procedure is dynamic because the columns at any given time can be different. As such the results vary. However when I try to connect Tableau with it, I get the following error.  Thoughts?

             

            Thanks!

             

            • 3. Re: Are dynamic sql queries posssible in tableau?
              Tom W

              You'll need to share the stored procedure code, it's probably an issue with you not specifying SET NOCOUNT ON at the beginning or you aren't returning the result set correctly. Or as the error says, the data in one of your return types isn't valid.

               

              Dynamic / changing result sets should be fine. I just tested this procedure and it works OK:

              create procedure dbo.test (@param1 int)

              as

              set nocount on;

              if @param1<2

              SELECT 1 as Field1, 2 as Field2

              else

              SELECT 1 as Field1, 2 as Field2, 'Test' as Field3

               

               

               

               

              With that being said, Tableau isn't going to like the fact Field3 was there, then it isn't there. You're going to see errors in the report. I wouldn't suggest serving up a dataset where the columns change.

              • 4. Re: Are dynamic sql queries posssible in tableau?
                Haroldy Tevaras

                So this is the stored procedure.

                 

                Declare @DBNameTable table (DatabaseName varchar (max))

                 

                insert into @DBNameTable

                select distinct [database_name]

                from [PERSISTENT].[dbo_backupset]

                 

                Declare @DBName varchar (max) =''

                Select @DBName += QUOTENAME(DatabaseName)+','

                from @DBNameTable

                Set @DBName = left(@DBName, len(@DBName)-1)

                 

                 

                Declare @SQL Nvarchar (max)

                 

                 

                Set @SQL='

                 

                 

                Select *

                From

                   

                   (select convert(date,pkdate) as PKDate

                   ,[ACDomainName]

                          ,convert(date,[backup_finish_date]) as DBBackupFinishDate

                          ,[database_name]

                          ,[server_name]

                    from [PERSISTENT].[dbo_backupset] a

                    inner join [INFO].[Calendar] b on convert(date,a.[backup_finish_date]

                    ) = b.PKDate) a

                Pivot (

                 

                 

                        Count(dbbackupfinishdate)

                        for [database_name]

                        in (' +@DBName+

                ')

                ) b'

                execute sp_executesql @sql

                 

                and I agree with you, but the databases in our environment are not static. We can add them and delete them at any given time. This is the original problem I am trying to solve.

                 

                Date Comparison with Aggregated and Non-Agregated Fields+ Calendat

                • 5. Re: Are dynamic sql queries posssible in tableau?
                  Tom W

                  You aren't using SET NOCOUNT ON; at the start of the stored procedure. That should do it, but I can't guarantee it will wotk as the sp_executesql might be tripping Tableau out. I also can't tell from any of that what the datatypes are etc.

                   

                  Changing columns in your result set is going to make it extremely difficult to create reports in Tableau. One minute you have a 'Customer' column and you build off of that, next minute it's gone - the report is broken until you remove that column from the report.

                   

                  If I were you, I would create one master database with a table (or set of tables) which you populate using a stored procedure to connect to your other databases.

                  That way you can connect Tableau to one set of tables where the structure doesn't change.

                  1 of 1 people found this helpful
                  • 6. Re: Are dynamic sql queries posssible in tableau?
                    Haroldy Tevaras

                    Well you were right about the no count on! the problem is that I now have too many columns for tableau . oh well! thanks for your help!

                    • 7. Re: Are dynamic sql queries posssible in tableau?
                      Tom W

                      Maybe you shouldn't be pivoting everything then. You should reconsider your

                      table structure so it goes deep, not wide.

                       

                      On Jul 7, 2017 2:14 PM, "Haroldy Tevaras" <tableaucommunity@tableau.com>

                      • 8. Re: Are dynamic sql queries posssible in tableau?
                        Haroldy Tevaras

                        Yeah, that was my original plan, but  after much trial and error I dont see how I can keep it deep. I'll keep exploring and trying though. Thanks so much for your help!

                        • 9. Re: Are dynamic sql queries posssible in tableau?
                          Tom W

                          Continuing my thoughts in your other thread.