1 2 Previous Next 19 Replies Latest reply on Jun 15, 2017 4:08 PM by greg.jones.8

    Using stored procedures and temp tables

    Richard Kellar


      Hi there

       

      I am fairly new to Tableau and just upgraded to v8.1, with the hope of being able to take advantage using stored procedures as a data source.  I am using MSSQL 2008 R2 (64-bit) and have a bunch of stored procedures that I use for authoring reports in Reporting Services.  After a few hours of trying to open various SP's as data sources, it seems that Tableau doesn't like that I reference #tmp tables in my sql.  I've tested this by creating two simple stored procedures that uses the same data sources, with one using #tmp tables and the other does not.  The one that didn't use #tmp tables connected while the other generated a "the stored procedure does not return a result set".

       

      My question -- Is this a known issue?  I've had trouble finding any info on this in the forums or knowledge base.  If this is a limitation, does anyone have any workarounds? -- or do I need to create extracts instead?

       

      Thanks in advance.

       

      Rich

        • 1. Re: Using stored procedures and temp tables
          Robert Morton

          Hi Richard,

           

          This is not yet a known issue, since we did not encounter it during development and testing of the feature. However it does sound like it has a similar root cause to other issues we have seen with SQL Server connectivity in Tableau 8.1 -- including stored procedure support -- due to our switch to the SQL Server ODBC driver (since Microsoft has ceased development of their OLE DB provider). The gist of the problem is that the ODBC driver forces us to use server-side cursors in most scenarios, and the Microsoft ODBC driver has known issues with supporting metadata queries and result set retrieval for certain cursor types when temporary tables are involved.

           

          We are already in the process of looking for ways to return to using 'default result sets', which is Microsoft's term for a streaming client-side cursor that has better performance and more robust functionality than server-side cursors. This may also enable us to work with stored procedures that internally use temporary tables, but we cannot commit to that yet.

           

          Please do contact Support (support@tableausoftware.com) with the specifics of your problem so they may work with you to collect log files, and if possible, examples of DDL for your stored procedures that can reproduce the problem. Please mention my name and my forum comments so the case may be routed quickly to the team working on changes to SQL Server connectivity.

           

          -Robert

          1 of 1 people found this helpful
          • 2. Re: Using stored procedures and temp tables
            Richard Kellar

            Thanks for your prompt reply Robert.  I will re-write my sp and remove #tmp tables.  Thanks again. Richard

            • 3. Re: Using stored procedures and temp tables
              Bora Beran

              Hi Richard,

              Before you remove the temp tables can you try  adding SET NOCOUNT ON to your stored procedures?

               

              http://technet.microsoft.com/en-us/library/ms189837.aspx

               

              Without this stored procedure will return a result to Tableau every time a statement is executed but what it returns will be the count of rows affected by this change (inserted into temp table, deleted, updated etc.) instead of a result table that consists of rows and columns which is what Tableau expects.

               

              I suspect it may be the reason why you're seeing that error.

               

              This is also likely to improve the performance of your stored procedure.

               

              Let me know if this solves the problem.

               

              Thank you,

               

              ~Bora

              2 of 2 people found this helpful
              • 4. Re: Using stored procedures and temp tables
                Richard Kellar

                Thanks Bora....I placed this in my store procedure, but I still get the same result set error.  I will be sure to add this to my code to help with performance.

                 

                Thanks again,

                 

                Richard

                • 5. Re: Using stored procedures and temp tables
                  Jeff Pressman

                  FWIW Adding SET NOCOUNT ON solved the #temp table problem for me. I did have to Exit Tableau and reconnect, presumably so that Tableau could get new meta data about the proc after my change.

                  • 6. Re: Using stored procedures and temp tables
                    Nat Lutz


                    For those of you that have gotten the #temp tables to work - what do you see for performance running them from Tableau vs SQL Server?

                     

                    I just started testing these - but the first I tried takes around 15 minutes if I run the stored proc - place the output in a table - and then just Select * from that table in the Tableau query. When I use the stored procedure directly (instead of inserting into table output to Tableau) and I canceled the query at 1 hour - my projection is it would have taken between 2 and 3 hours to complete based on the extract progres at that point.

                     

                    I am going to try a stored procedure that just does a single select statement next. My guess is the performance on running stps from Tableau is just not good right now.

                    • 7. Re: Using stored procedures and temp tables
                      Bora Beran

                      If you're observing this on SQL Server, this is most likely because of MARS which proven to be a performance bottleneck  for larger result sets in some setups. In our 8.1.4 maintenance release which will be coming out shortly we have a fix for this issue. It would be great if you could try running your stored procedure with 8.1.4 and let us know if the performance improved.

                      • 8. Re: Using stored procedures and temp tables
                        Justin Larson

                        Glad I found this post, at least. I am running into the same problem.

                         

                        Running a stored procedure in SQL Server 2012 from Tableau 8.1.6. Stored procedure must use temp tables for performance, but Tableau returns the same "no result set" error described.

                         

                        It also follows up with a "can't connect to server" error message. Did you run into that as well?

                         

                        Has anyone found any workarounds or insights into root causes? I'll change up the query without using temp tables, in this case. It's a pretty hefty result set with many millions of rows, which is easier to make perform well without restricting use of temp tables.

                        • 9. Re: Using stored procedures and temp tables
                          Bora Beran

                          Are you using SET NOCOUNT ON ?

                           

                          SET NOCOUNT (Transact-SQL)

                           

                          Not having this in procedures that contain multiple steps often is a common cause of "no result set" error.

                          • 10. Re: Using stored procedures and temp tables
                            Justin Larson

                            yes, set nocount is on.

                             

                            In fact the procedure doesn't contain a lot of bells and whistles, as procedures go. In my case, it's a CTE that writes to a temp table, then results are returned from the temp table joined to another table. No variables, no parameters, no cursors, no other blackbox magic.

                             

                            The result set is big, so going without the temp table slows down materially.

                            • 11. Re: Using stored procedures and temp tables
                              brad.earle.1

                              I have no problem connecting to temp tables that are the result set from a stored procedure using MS SQL server.  The trick, however, is to use ## as the prefix to the temp table, making it a global temp table, not the instance specific # prefix (which limits it to a local temp table).  A temp table named ##temp can be created by SQL, then accessed directly by Tableau (however, you'll need to use Custom SQL with something like 'Select * from ##temp', as the table/view list that the Tableau interface provides will not show these tables, so it can't be 'selected' from the standard Tableau interface.

                               

                              Having the procedure incur various intermediate #temp tables in a stored procedure also hasn't been a problem that I've encountered.  Recognize that the scope of a #temp table is specific to that session (which is owned by the stored procedure and not the session opened to connect to the stored procedure by Tableau), those tables are not accessible.  Any intermediate table that you might want to connect to after a stored procedure is run needs to have visibility across all connections, hence the ## global prefix.

                               

                              MSDN reference on creating Temporary Tables: http://msdn.microsoft.com/en-us/library/ms174979.aspx#code-snippet-5

                              • 12. Re: Using stored procedures and temp tables
                                Sylvia Johnson

                                I to was having the same issues. But I believe I have come up with an easier solution then above.

                                What I did was the keep the stored procedure I needed to migrate as is, with all of the multiple temp tables I was using and logic. Then I created another stored procedure that just executed the stored procedure  I wanted to use. In tableau I use the second stored procedure. In the second stored procedure I had did SET NOCOUNT ON. Therefore if another developer needs to do any updating is a lot easier. All I had to do is create a simple stored procedure that executed the one I wanted, without having to do any additional coding and faster migration.

                                • 13. Re: Using stored procedures and temp tables
                                  Bora Beran

                                  I think that's a great solution if you can't modify the original stored procedure. I think in this case though the problem was the "no resultset" error. The most likely reason for this is either the NOCOUNT setting since this makes sure SQL Server doesn't return intermediate status results to Tableau that are not the results but may look like it. The other likely reason is that the stored procedure isn't really returning results after creating temp tables since there is no SELECT ... from temp table at the end of the stored procedure. Another reason could be what Robert listed earlier in this thread.

                                  • 14. Re: Using stored procedures and temp tables
                                    Trong Bo

                                    I like this solution!  This helped me after all others failed.  I was getting the "no resultset" error when trying to connect directly to my original stored proc from Tableau.  I tried adding the "NOCOUNT ON" to the original stored proc, but couldn't get it to take for some reason.  Then after reading your post, tried it and it worked.  Thanks for sharing!

                                    1 2 Previous Next