14 Replies Latest reply on Aug 6, 2017 6:29 PM by Jakub K

    Tableau freezing up - connecting to stored procedure result set

    Jakub K

      We're using stored procedures on SQL server as an interface to a data source that tableau doesn't fully support (openrowset() commands with custom code inside the stored procs)

       

      We've noticed that tableau randomly locks up/freezes when changing a parameter in the workbook that's assigned to the stored proc

      it just sits at 'connecting to stored procedure result set' forever. Not possible to cancel it either. Canceling just brings up the 'cancelling...' prompt which again just sits there forever, need to end the process (and lose all work) to be able to reopen the workbook

       

      It happens randomly on different machines (w10, w7, server 2012r2) and in various versions of tableau 10, including 10.1.4

       

      There are no running or hanging sessions on SQL server.

      From the logs it looks like the last commands tableau sent were to request the sproc metadata (parameter names and data types)

      No queries were sent to the 'end data source'. It looks like it locks up before the 'real' execution of the stored proc

       

      Anyone experienced this before?

        • 1. Re: Tableau freezing up - connecting to stored procedure result set
          Dmitry Chirkov

          Is parameter you are changing used in stored procedure?

          If yes - Tableau has to execute the sproc and pull all the data again which can take time. Non-cancellable Cancel button is a bug that we methodically trying to get rid of but it still pops up here and there :(

          • 2. Re: Tableau freezing up - connecting to stored procedure result set
            Jakub K

            Yes changing the parameter requeries the stored proc, but:

            1. the stored proc executes in a few sec when the cache is cold and <1sec once its warm - the freezing doesn't happen all the time. I have 1 machine that seems to be immune (my dev pc which is w7/tableau 10.1.1 64bit). I've got a trace running on the db server and i can see that the tableau connection never gets to actually executing the stored proc. It freezes after running the information_schema.routines query to pull out the sproc metadata

             

            2. there are no running queries on the DB while tableau is not responding (when the "processing request: connecting to stored procedure result set" prompt is up)

             

            3. i've killed all tableau sessions on the DB server while the 'processing request' prompt was up and tableau didn't die with a connection lost error

            • 3. Re: Tableau freezing up - connecting to stored procedure result set
              Dmitry Chirkov

              Support case might be justified here otherwise I'd love to see set of logs for when this happens.

              • 4. Re: Tableau freezing up - connecting to stored procedure result set
                Jakub K

                Support ticket with logs submitted (02696565) - prefer not to post them on a public forum

                 

                logs from sql server are (after the initial chatter of opening the workbook and everything loading. This log is started just before i change the parameter that causes tableau to stop responding)

                 

                declare @p1 int

                set @p1=14

                exec sp_prepexec @p1 output,NULL,N'SELECT [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].[ORDINAL_POSITION], [P].[IS_RESULT], [P].[PARAMETER_NAME],

                       [P].[DATA_TYPE], [P].[PARAMETER_MODE], [P].[NUMERIC_PRECISION], [P].[NUMERIC_SCALE],

                       [P].[NUMERIC_PRECISION_RADIX], [P].[DATETIME_PRECISION], [P].[CHARACTER_MAXIMUM_LENGTH]

                  FROM [GPA Config Filters].[INFORMATION_SCHEMA].[ROUTINES] AS [R] LEFT OUTER JOIN [GPA Config Filters].[INFORMATION_SCHEMA].[PARAMETERS] AS [P]

                    ON [P].[SPECIFIC_SCHEMA] = [R].[SPECIFIC_SCHEMA] AND [P].[SPECIFIC_NAME] = [R].[SPECIFIC_NAME]

                WHERE [R].[ROUTINE_NAME] = ''sp_GetSupportingPVMByPortfolioData'' AND [R].[ROUTINE_SCHEMA] <> ''SYS''

                ORDER BY [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].ORDINAL_POSITION'

                select @p1

                go

                exec sp_unprepare 14

                go

                • 5. Re: Tableau freezing up - connecting to stored procedure result set
                  Y Lu

                  the stored procedure is to select a bunch fields, and use it as data source for tableau.

                   

                  later when I add one more field into sp, then tableau complaints, and message is 'FieldX is an invalid column'

                  .the fieldX is newly added to sp and refresh within tableau.

                   

                  have you come across some problem?

                  • 6. Re: Tableau freezing up - connecting to stored procedure result set
                    Sergey Feduleyev

                    I am having the same issue. But with a twist. With version 10.0 my report was freezing on the server but working okay from the desktop. Now I have updated my desktop to 10.2, and it has started freezing as well. Everything exactly as described in original post. Tableau has to be killed through Task Manager. Our server will be upgraded soon, and I can't wait to see what is going to happen on it.

                     

                    Meanwhile, I can provide some useful information to you. Nothing happens at the server when it is "connecting". I know for sure as I can see Activity Monitor.

                     

                    Another important fact. The report DOES NOT issue a new call with the new parameter, at all. Here is the full list of open connections from Tableau to SQL Server:

                     

                    1.

                    IF OBJECT_ID('[#Tableau_3_2_Connect]') IS NOT NULL

                      DROP TABLE [#Tableau_3_2_Connect]

                     

                    2.

                    SELECT [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].[ORDINAL_POSITION], [P].[IS_RESULT], [P].[PARAMETER_NAME],

                           [P].[DATA_TYPE], [P].[PARAMETER_MODE], [P].[NUMERIC_PRECISION], [P].[NUMERIC_SCALE],

                           [P].[NUMERIC_PRECISION_RADIX], [P].[DATETIME_PRECISION], [P].[CHARACTER_MAXIMUM_LENGTH]

                      FROM [DW_Report].[INFORMATION_SCHEMA].[ROUTINES] AS [R] LEFT OUTER JOIN [DW_Report].[INFORMATION_SCHEMA].[PARAMETERS] AS [P]

                        ON [P].[SPECIFIC_SCHEMA] = [R].[SPECIFIC_SCHEMA] AND [P].[SPECIFIC_NAME] = [R].[SPECIFIC_NAME]

                    WHERE [R].[ROUTINE_NAME] = 'my_sp_name' AND [R].[ROUTINE_SCHEMA] <> 'SYS'

                    ORDER BY [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].ORDINAL_POSITION

                     

                    3.

                    EXEC [dbo].[my_sp_name] @parameter='old value'

                    The parameter value remains old here!

                     

                    I also have stored procedure logging that confirms that first call completes successfully in 0.07 seconds but second call with a new parameter value never happens.

                     

                    I hope it helps developers to locate the bug.

                    • 7. Re: Tableau freezing up - connecting to stored procedure result set
                      Jeremie Pavelski

                      I am having the same issue.  I have Tableau pulling data from a Stored Procedure with some parameters for dates.   It opens just fine in the desktop version the first time it is run but when I change the dates in the parameters, the sheet locks up and I have to use Task Manager to kill tableau and start over. 

                       

                      If there are any solutions or if there is a work around I would be very interested in hearing it.  If need be I will just write out what I need in SSRS instead of Tableau but I prefer Tableau's flexibility.

                       

                      -Jeremie

                      • 8. Re: Tableau freezing up - connecting to stored procedure result set
                        Trevor Toll

                        I am having the same issue currently. If I enter the parameter while in an individual worksheet it works fine. If I have multiple sheets on a dashboard (all same data source) and attempt to set the parameter there the query freezes at 'Connecting to stored procedure result set.'

                        • 9. Re: Tableau freezing up - connecting to stored procedure result set
                          Y Lu

                          the common problem with tableau using sp happens  when I added a new field to sp, the tableau complained.

                          • 10. Re: Tableau freezing up - connecting to stored procedure result set
                            Jakub K

                            The last working version for us is desktop 10.1

                            Feedback from tableau to our ticket is that versions post 10.1 don't release some resource when querying stored proc metadata, so all subsequent queries hang

                            That explains why the stored proc populates everything when the workbook is first opened but hangs when changing params

                            • 12. Re: Tableau freezing up - connecting to stored procedure result set
                              Jakub K

                              No

                              Last contact from tableau a month or two ago was that it was still being worked on & wasn't fixed in the latest version yet

                              • 13. Re: Tableau freezing up - connecting to stored procedure result set
                                Limor Z

                                We are having the same issue. We use Tableau 10.2.2. Any one knows if this bug is resolved on tableau 10.3.1?

                                • 14. Re: Tableau freezing up - connecting to stored procedure result set
                                  Jakub K

                                  Yes, the version we're now using no longer locks up.

                                   

                                  I've been told that it's part of fix 632697 so check the release notes for that issue id