11 Replies Latest reply on Aug 31, 2017 6:22 PM by Luke Beacon

    Initial SQL not completing all loop steps

    Luke Beacon

      Hi there,

       

      I have a relatively complex workbook tied to a database schema intended to allow me to execute a list of queries stored in a table. I store the queries I want to run whenever I refresh the workbook, and then add a Cursor loop to Initial SQL that goes through and runs all of the SQL queries (each of which insert into a 'log' table that I then select from for the extract.)

       

      This is working for the most part, but it seems like the Initial SQL feature is not executing all of the queries that I pass to it.

      It is weird, because when I copy the SQL that is pasted into the 'Initial SQL' dialog box and then run it directly in my SQL Server client, it works perfectly and inserts all of the appropriate records.

       

      Is there some way I can turn on a 'debug mode' of some sort to see any system messages/errors generated by the Initial SQL query? I'd like to know why it isn't completing fully.

       

      Unfortunately the information used in the workbook is confidential and I won't be able to upload an example packaged workbook. If I can provide anything that will help, let me know and I will try to get something

       

      Cheers,

       

      Luke

        • 1. Re: Initial SQL not completing all loop steps
          Dmitry Chirkov

          Can you share your Initial SQL statement, perhaps? Or at least outline that is it doing?

          It it calling a stored procedure? Executing more than one statement? Uses any variables?

          • 2. Re: Initial SQL not completing all loop steps
            Luke Beacon

            No worries, here is the statement:

             

            DECLARE @MeasureClientID int

            DECLARE @MeasureSQL VARCHAR(MAX)

            DECLARE @ClientServer nvarchar(255)

            DECLARE @ClientDatabase nvarchar(255)

            DECLARE @ClientID int

            DECLARE myCursor CURSOR FAST_FORWARD FOR

                SELECT MeasureClientID

                FROM tblMeasureClient

             

             

            OPEN myCursor

            FETCH NEXT FROM myCursor INTO @MeasureClientID

            WHILE (@@FETCH_STATUS = 0)

            BEGIN

               

                SET @ClientID = (SELECT ClientID FROM tblMeasureClient WHERE MeasureClientID = @MeasureClientID)

                SET @ClientServer = (SELECT [Server] FROM tblClient WHERE ClientID = @ClientID)

                SET @ClientDatabase = (SELECT DatabaseName FROM tblClient WHERE ClientID = @ClientID)

                SET @MeasureSQL =

                                (

                                SELECT REPLACE(

                                            REPLACE(

                                                REPLACE(Query

                                                ,'@MeasureClientID',@MeasureClientID)

                                            ,'@ClientServer',@ClientServer)

                                        ,'@ClientDataBase',@ClientDatabase)

                                FROM tblMeasureSQL

                                WHERE MeasureGroupID =

                                                (SELECT MeasureGroupID

                                                FROM tblMeasureClient

                                                WHERE MeasureClientID = @MeasureClientID)

                                )

                EXEC (@MeasureSQL)

             

             

                FETCH NEXT FROM myCursor INTO @MeasureClientID

            END

            CLOSE myCursor

            DEALLOCATE myCursor

             

            Essentially I am creating a dashboard that checks to ensure the data flow coming from our clients is stable. I have a table that contains 'Measures' which are essentially just 'checks'. Each check has an SQL query stored in the table tblMeasureSQL.

            This cursor just runs through the 'Measures' for each client and runs the SQL stored in tblMeasureSQL.

            By the way, each SQL query in tblMeasureSQL is stored as an INSERT statement, which insert into a table called logMeasureCheck.

            This table is then referenced in the extract in Tableau.

             

             

            The weird part is that the cursor query works just find when running in SQL Server...

            1 of 1 people found this helpful
            • 3. Re: Initial SQL not completing all loop steps
              Dmitry Chirkov

              Brenan Mackas, can you take a look, please?

               

              Luke Beacon, have you looked at log files yet? (log.txt)

              Statement looks straightforward enough (i.e. there's nothing to substitute and it'll be blindly passed to connection) so only thing comes to mind is the fact that Tableau creates multiple connections to the database and each one of them will execute Initial SQL statement. Concurrency issues, perhaps?

              • 4. Re: Initial SQL not completing all loop steps
                Luke Beacon

                I have looked in log.txt and tabprotosrv.txt (as I am using a live connection) and I can't see anything obvious.

                 

                17 queries are running correctly and it seems to fail on the 18th. That number does not ring any bells to me.

                • 5. Re: Initial SQL not completing all loop steps
                  Dmitry Chirkov

                  Fail how? Which query? Your Initial SQL statement or some other query from the dashboard?

                  • 6. Re: Initial SQL not completing all loop steps
                    Luke Beacon

                    The Initial SQL query completes and the live connection doesn't give an error. But the Initial SQL runs through a CURSOR and runs INSERT statements. It runs the first 17 insert statements successfully (I can see them in the log table that I insert into), but then the remaining queries don't seem to execute at all.... I don't know if they are failing, or if they are not being started at all.

                    • 7. Re: Initial SQL not completing all loop steps
                      Dmitry Chirkov

                      But this is something that would happen on your database - nothing to do with Tableau as all Tableau does is sending that one big blob of a statement and waits for it to come back...

                       

                      How long does it take to execute that statement? Milliseconds?

                      Any way you could execute two of them at the same time?

                      • 8. Re: Initial SQL not completing all loop steps
                        Luke Beacon

                        That is the perplexing part... When I run it through SQL Server Mgmt Studio (with the same Windows Authentication), it works perfectly. It's only failing in Tableau.

                         

                        It takes about 15 seconds to execute, mainly because the queries it is executing are looking at 100mil row tables.

                         

                        I tried adding WITH (NOLOCK) to all of the FROM clauses with no luck...

                         

                        There is some kind of setting difference between the 'user' executing the query from Tableau and when I execute it from SQL Server Mgmt Studio... I will fiddle around with a few user settings to see if it is that.

                        • 9. Re: Initial SQL not completing all loop steps
                          Luke Beacon

                          There seems to be some issue with the number of INSERT statements the Initial SQL (or Tableau in general) can execute in one sitting.

                           

                          I changed the cursor to execute just one bit sql query as a string... still didn't work.

                          I changed the cursor to a Loop... still nothing.

                           

                          I'm going to chalk this up to an issue with Tableau and possibly investigate further by contacting Tableau directly.

                          If I ever find a solution, I will post it here for you people from the future reading this right now.

                          If I haven't posted a solution here, then ******!

                          • 10. Re: Initial SQL not completing all loop steps
                            Dane Hobbs

                            I am experiencing a similar issue with Initial SQL and MSSQL.  I too have a loop that writes one row to a temp table with each loop.  The loop should execute 159 times as designed.  It runs fine within SSMS but as an Initial SQL statement the loop only executes 14 times.  The Custom SQL statement that selects all from the temp table only returns 14 rows.  My loop executes a stored procedure into a 3rd party application and the procedure is locked from visibility.  I have noticed that if I change one of the options to the stored procedure it can change the number of successful loops up to 27.   I am curious if you ever found a solution.  My gut is telling me that perhaps I need some sort of option in the initial SQL to suppress any warnings or null behavior warnings that may be being generated from within the stored procedure that I don't have any control of.   The behavior I experience is the same from Tableau Desktop and as a published, refreshing Extract on Tableau Server.

                            • 11. Re: Initial SQL not completing all loop steps
                              Luke Beacon

                              Hi Dane,

                               

                              I did not find a solution and ended up just running the initial sql code via our ETL scheduling program.

                               

                              It was a bit of a let down since Initial SQL seemed so promising. There is probably some setting buried deep within Tableau's settings that I am not aware of...

                               

                              Cheers,

                               

                              Luke