1 2 Previous Next 18 Replies Latest reply on Nov 15, 2016 3:58 PM by Emmanuel Iturbide

    SQL Parameters using Varchar(Max) columns

    Joseph Giombi

      Hello,

       

      I am having a hard time trying to use a stored proc that has a Varchar(Max) Column within Tableau. This Column stores query text which can easily exceed 8000 charaters (hense the point of a varchar(max)).  Being that the Column is a Varchar(Max) I would not think this would be an issue?

       

      The Exact error I get is below.

       

      [Microsoft][SQL Server Native Client 11.0][SQL Server]The size (16383) given to the column 'Statement' exceeds the maximum allowed for any data type (8000). Unable to connect to the server "dix-q-dbadbs-03.sentry.com". Check that the server is running and that you have access privileges to the requested database.

      The exact stored proc I am calling within Tableau is as follows:

       

      I don't see any other way to obtain this information outside of a stored proc.

      Any help would be greatly appreciated, thanks!

       

      Create Procedure [dbo].[usp_Tableau_BPPolicyODS_Duration_Throughput] 
      AS 
      Set Nocount ON 
      Select top 10 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count],cast ([Statement] as Varchar(MAX)) as [Statement] ,queryid Into #tempTable 
        from BaselineQueries_v where server_nme='SHO-P-BPODBS-05.sentryextranet.com'  
        and Database_Name='BP_Policy_ODS'
        and [time] > GETDATE()-30 
        order by [Duration(s)] desc
      
      
      Select top 10 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count],cast ([Statement] as Varchar(MAX)) as [Statement] ,queryid Into #tempTable2 
        from BaselineQueries_v where server_nme='SHO-P-BPODBS-05.sentryextranet.com'  
        and Database_Name='BP_Policy_ODS'
        and [time] > GETDATE()-30 
        and Principal_Name not like '%Informatica%' 
        order by [Duration(s)] desc
         
      Select * from #tempTable
      Union All Select * From #tempTable2
      order by [Duration(s)] desc
      
      
      
      
      GO
      
        • 1. Re: SQL Parameters using Varchar(Max) columns
          Tom W

          Does this query / stored procedure execute OK through SQL Server Management Studio? It doesn't seem to me that it would.

          What version of SQL server are you using?

          • 2. Re: SQL Parameters using Varchar(Max) columns
            Joseph Giombi

            Yup it executes perfecly fine via SQL Server.

             

            I'm using SQL 2014 ( 12.0.2269.0)

            • 3. Re: SQL Parameters using Varchar(Max) columns
              Tom W

              Just to clarify, does EXEC [dbo].[usp_Tableau_BPPolicyODS_Duration_Throughput] run through SSMS OK or just the code within the proc?

              • 4. Re: SQL Parameters using Varchar(Max) columns
                Joseph Giombi

                Yup, no prob.

                 

                EXEC [dbo].[usp_Tableau_BPPolicyODS_Duration_Throughput] will run gracefully.

                • 5. Re: SQL Parameters using Varchar(Max) columns
                  Tom W

                  My guess is there's a limitation in the way Tableau is preparing the execution.

                   

                  I'd take a look at a TRACE on the SQL Server to see what SQL is being generated by Tableau and narrow down the problem statement. Alternatively use this to get at the generated SQL - Viewing Underlying SQL Queries | Tableau Software

                  • 6. Re: SQL Parameters using Varchar(Max) columns
                    Joseph Giombi

                    One thing to note. I did change my query to only pull back the first 7500 characters (as a test) to see if I could get past the varchar(Max) issue. I am no longer getting that same specific error. However, I am getting another error. The new Stored Proc is as follows.  (It executes flawlessly within sql)

                     

                     

                    Create Procedure [dbo].[usp_DurationTest]

                    AS

                    Set Nocount ON

                    Select top 5 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count], Left ([Statement], 7500) as [Statement] ,queryid Into #tempTable

                      from BaselineQueries_v where server_nme='SHO-P-CBPDBS-01.sentry.com'

                      -- and Database_Name='BP_Policy_ODS'

                      -- and [time] > GETDATE()-30

                      order by [Duration(s)] desc

                     

                     

                    Select top 5 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count],Left ([Statement], 7500) as [Statement] ,queryid Into #tempTable2

                      from BaselineQueries_v where server_nme='SHO-P-CBPDBS-01.sentry.com'

                      -- and Database_Name='BP_Policy_ODS'

                      -- and [time] > GETDATE()-30

                      and Principal_Name not like '%SAPP_CBPS_P%'

                      order by [Duration(s)] desc

                     

                    Select * from #tempTable

                    Union All Select * From #tempTable2

                    order by [Duration(s)] desc

                     

                     

                     

                     

                    GO

                     

                     

                    --------------------------------------------------------------------------------------------------

                     

                    New Error:

                    [Microsoft][SQL Server Native Client 11.0]Invalid precision value Unable to connect to the server "dix-q-dbadbs-03.sentry.com". Check that the server is running and that you have access privileges to the requested database.

                    --------------------------------------------------------------------------------------------------

                     

                    Within the Tableau Performance Recording - No Queries get logged.

                    Within The Log File :

                    {"ts":"2016-08-09T15:51:21.371","pid":5532,"tid":"6eac","sev":"info","req":"-","sess":"-","site":"{027039A4-3D87-4B7F-BA1E-DB361C02B11E}","user":"-","k":"end-query","v":{"cols":12,"elapsed":0.094,"protocol-id":10,"query":"SELECT [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].[ORDINAL_POSITION], [P].[IS_RESULT], [P].[PARAMETER_NAME],\n [P].[DATA_TYPE], [P].[PARAMETER_MODE], [P].[NUMERIC_PRECISION], [P].[NUMERIC_SCALE],\n [P].[NUMERIC_PRECISION_RADIX], [P].[DATETIME_PRECISION], [P].[CHARACTER_MAXIMUM_LENGTH]\n  FROM [BaselineData].[INFORMATION_SCHEMA].[ROUTINES] AS [R] LEFT OUTER JOIN [BaselineData].[INFORMATION_SCHEMA].[PARAMETERS] AS [P]\n    ON [P].[SPECIFIC_SCHEMA] = [R].[SPECIFIC_SCHEMA] AND [P].[SPECIFIC_NAME] = [R].[SPECIFIC_NAME]\n WHERE [R].[ROUTINE_TYPE] = 'PROCEDURE' AND [R].[ROUTINE_SCHEMA] <> 'SYS'\n ORDER BY [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].ORDINAL_POSITION","query-hash":3358823891,"rows":4}}

                    {"ts":"2016-08-09T15:51:37.464","pid":5532,"tid":"7a54","sev":"info","req":"-","sess":"-","site":"{027039A4-3D87-4B7F-BA1E-DB361C02B11E}","user":"-","k":"begin-query","v":{"protocol-id":10,"query":"SELECT [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].[ORDINAL_POSITION], [P].[IS_RESULT], [P].[PARAMETER_NAME],\n [P].[DATA_TYPE], [P].[PARAMETER_MODE], [P].[NUMERIC_PRECISION], [P].[NUMERIC_SCALE],\n [P].[NUMERIC_PRECISION_RADIX], [P].[DATETIME_PRECISION], [P].[CHARACTER_MAXIMUM_LENGTH]\n  FROM [BaselineData].[INFORMATION_SCHEMA].[ROUTINES] AS [R] LEFT OUTER JOIN [BaselineData].[INFORMATION_SCHEMA].[PARAMETERS] AS [P]\n    ON [P].[SPECIFIC_SCHEMA] = [R].[SPECIFIC_SCHEMA] AND [P].[SPECIFIC_NAME] = [R].[SPECIFIC_NAME]\n WHERE [R].[ROUTINE_NAME] = 'usp_DurationTest' AND [R].[ROUTINE_SCHEMA] <> 'SYS'\n ORDER BY [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].ORDINAL_POSITION","query-hash":1068424983}}

                     

                    Within a SQL trace, I did get a little more.

                     

                    Same text for SQL Batch Starting and SQL Batch Completed

                    :

                    CREATE TABLE [#Tableau_8_14_sp_temp] (

                      [Time] DATETIME,

                      [Principal_Name] VARCHAR(100),

                      [Duration(s)] FLOAT(53),

                      [logical_reads] BIGINT,

                      [row_count] BIGINT,

                      [Statement] VARCHAR(7500),

                      [queryid] INTEGER NOT NULL

                     

                    A lot of User Error Messages such as these 4 numerous times:

                     

                    Invalid object name '#Tableau_8_14_sp_temp'.

                    Incorrect syntax near the keyword 'INSERT'.

                    Executing SQL directly; no cursor.

                    The batch could not be analyzed because of compile errors.

                    • 7. Re: SQL Parameters using Varchar(Max) columns
                      Tom W

                      It's really tough for me to troubleshoot this very far without the schema for the tables you're selecting from.

                       

                      Here's personally what I would go through to try narrow it down;

                      • Eliminate the second half of the union, see if that fixes it
                      • Remove one column at a time until it works
                      • Stop using SELECT INTO, instead CREATE TABLE upfront and do INSERT INTO (This would also be helpful for me so I could see the schema!)
                      • Clean up your column names (i.e. the Durations(s) column)
                      • 8. Re: SQL Parameters using Varchar(Max) columns
                        Tom W

                        Also, get rid of the ORDER BY in your stored proc - it's irrelevant to Tableau and it's only going to slow things down.

                        • 9. Re: SQL Parameters using Varchar(Max) columns
                          Joseph Giombi

                          Thanks Tom, appreciate your help with this.

                           

                          Unfortuneately, I do need the ORDER BY by clause as I essentially have 3 procs I will be using, all grabbing a Top 10 of worst performing ODS queries by duration, logical reads, and row count. The order by allows me to get the top 10 of each category.

                           

                          I did forget to mention that I already did try to put these procs in the most simplest form possible and still get the exact same errors. It does not lead me to believe it is a problem with a query.

                           

                          I am essentially just doing a select (column names) from View within the stored proc which results in the exact same error.

                          Here are the two stripped down procs I have tried.

                           

                          create Procedure [dbo].[usp_DurationTest2]

                          AS

                          Set Nocount ON

                          Select top 5 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count], [Statement] ,queryid from BaselineQueries_v

                           

                          GO

                           

                          create Procedure [dbo].[usp_DurationTest3]

                          AS

                          Set Nocount ON

                          Select top 5 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count], Left ([Statement], 7500) as [Statement] ,queryid from BaselineQueries_v

                          GO

                           

                           

                          Here is the BaselineQueries_V (which works perfectly fine in tableau by itself)

                          CREATE view [dbo].[BaselineQueries_v]

                          as

                          select p.server_nme, b.* from baselineexpensivequeries b

                          inner join pb_data..pb_server p

                          on b.ServerId = p.Server_ID

                           

                           

                           

                           

                          GO

                           

                           

                          Here is the schema for the BaselineExpensiveQueries table

                          CREATE TABLE [dbo].[BaselineExpensiveQueries](

                            [EventName] [varchar](50) NULL,

                            [Time] [datetime] NULL,

                            [CPU(ms)] [bigint] NULL,

                            [Duration(s)] [float] NULL,

                            [query_hash] [binary](8) NULL,

                            [physical_reads] [bigint] NULL,

                            [logical_reads] [bigint] NULL,

                            [writes] [bigint] NULL,

                            [row_count] [bigint] NULL,

                            [BatchText] [varchar](max) NULL,

                            [Statement] [varchar](max) NULL,

                            [Database_Name] [varchar](100) NULL,

                            [Principal_Name] [varchar](100) NULL,

                            [ServerId] [int] NULL,

                            [queryid] [int] NOT NULL,

                            [Id] [int] IDENTITY(1,1) NOT NULL,

                            [result] [varchar](100) NULL,

                            [client_app_name] [varchar](100) NULL,

                          CONSTRAINT [PK_BaselineExpensiveQueries] PRIMARY KEY CLUSTERED

                          (

                            [Id] ASC

                          )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

                          ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

                           

                           

                          GO

                          • 10. Re: SQL Parameters using Varchar(Max) columns
                            Tom W

                            The ordering makes sense, sorry I missed that.

                             

                            When I say simplify, I mean; Remove a column, save the stored proc, try and connect. Same error? Remove another column and repeat until the stored proc executes. Using this approach, you should be able to narrow it down to a problematic column.

                             

                            Nothing looks too out of the ordinary here in terms of the specification of the of the table itself, but clearly there is something which Tableau is getting hung up on.

                             

                            I have a couple of suggestions for next steps;

                            • You could upload the top 5 rows being returns by the stored proc so I can insert them into a table on my end and test it
                            • Create a ticket with Tableau support. Send them everything they will need to replicate - schemas for tables, sample data etc.
                            • 11. Re: SQL Parameters using Varchar(Max) columns
                              Joseph Giombi

                              Well it is indeed the [statement] column which is the Varchar(MAX). When I remove that column from the stored proc everything works perfectly within Tableau.

                               

                              I did find this on Tableau.com

                               

                              Does what I underlined and referenced in bold mean that there is absolutely no way to include a varcharMax Column inside a stored proc? If so that's just silly.... I just assumed that as long as a column exceeding 8060 was defined as a varcharMAX; there would be no issue.

                               

                              Thanks again for all your help. After reading this and you would still like the top 5 rows of data to test. I can gladly provide that.

                               

                              Stored Procedure Constraints for SQL Server Databases

                              The following constraints apply for stored procedures on SQL Server databases.

                              • If the result set for a stored procedure contains columns of type IMAGE or TEXT, the stored procedure will fail with an "Incorrect syntax" error message.
                              • If the total width of the result set (number of bytes in each row) exceeds 8060, the stored procedure fails. This can occur with very wide tables (hundreds of columns) or with tables having large text columns, intended to hold thousands of characters of text.
                              • Tableau does not display stored procedures from schema "sys".
                              • If the user does not provide a value for one or more parameters that the procedure requires, Tableau displays a SQL Server database error in the form "The procedure requires a value for parameter @x but one was not provided."
                              • Stored procedures that contain multiple queries should follow the guidelines listed in Notes on Stored Procedures (above).
                              • Tableau Desktop does not support the Microsoft SQL Server TIME data type. When fields of this type are included in a stored procedure on a Microsoft SQL Server database Tableau Desktop will not import them.
                              • 12. Re: SQL Parameters using Varchar(Max) columns
                                Tom W

                                I can replicate on my end without your sample data with a simple test;

                                 

                                CREATE PROCEDURE dbo.twtest
                                AS
                                SET NOCOUNT ON
                                SELECT
                                'Row1' as RowIdentifier,
                                REPLICATE('a',8200) as RandomString
                                

                                 

                                I use nvarchar(max) all the time in stored procs and Tableau but I've never hit this limitation because my total row has never exceeded 8060 bytes. So yes, it's possible to use nvarchar(max).

                                My guess is there's a limitation in the way Tableau prepares a stored procedure using the sp_prepexec and associated logic you can see Tableau spinning through when you trace the initial connection to the SP from the Tableau Desktop client.

                                 

                                Your options;

                                • Limit the amount of text being returned from the super long field. This leads me to my next question, what exactly are you visualizing in Tableau which is 8000 characters of free text? Is there any opportunity to parse some of this information out into a better structure of columns perhaps?
                                • Get funky in SQL. Perhaps you could split these long columns across multiple ROWS which don't exceed 8000 bytes. So if you have 20,000 bytes in one column, that would be split into three child rows, 8k,8k,4k.
                                • 13. Re: SQL Parameters using Varchar(Max) columns
                                  Joseph Giombi

                                  Well heres the thing. I did try limiting the column as mentioned above by only bringing back the first 7500 characters using Left ([Statement], 7500) as [Statement]  which resulted in that different error:

                                   

                                  [Microsoft][SQL Server Native Client 11.0]Invalid precision value Unable to connect to the server "dix-q-dbadbs-03.sentry.com". Check that the server is running and that you have access privileges to the requested database.

                                   

                                   

                                  Actutally... After you reitterated the term bytes , not characters... I chanted it from Left ([Statement], 7500) as [Statement]  to Left ([Statement], 1000) as [Statement]. A bit overkill in terms of bytes, but I just wanted to go to the extreme to prove the concept. IT WORKED

                                  My problem was that the first 7500 characters still exceeded 8060 bytes.

                                   

                                  Thank you very much!

                                   

                                   

                                   

                                  • 14. Re: SQL Parameters using Varchar(Max) columns
                                    Tom W

                                    The other thing is, don't forget the total row cannot exceed 8060 bytes. The other fields being returned are going to contribute to this length so be mindful of that as it makes it hard to pin down a suitable length.

                                     

                                    In my testing earlier I found it was better to do CREATE TABLE #myoutput (........) upfront with a hard limit on the statement column i.e. Statement NVARCHAR(6000) , then insert into that column using LEFT(something,6000) then finish off with SELECT * FROM #myoutput.

                                     

                                    Tableau seemed to handle it better versus just a straight up select statement where it has to infer the datatypes.

                                     

                                    I'm glad you got this one solved!

                                     

                                    Finally, now I have some better context of your SP I'm questioning whether you need one at all? Why not change your SP code in the original post to this? I'm sure it was partly to work around the order by limitation in inline views / derived tables, but you can totally use orderby in conjunction with a top n clause.

                                     

                                    CREATE VIEW [dbo].[viewinsteadofstoredproc]

                                    AS

                                    Select top 10 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count],cast ([Statement] as Varchar(MAX)) as [Statement] ,queryid

                                      from BaselineQueries_v where server_nme='SHO-P-BPODBS-05.sentryextranet.com'   

                                      and Database_Name='BP_Policy_ODS' 

                                      and [time] > GETDATE()-30  

                                      order by [Duration(s)] desc 

                                    UNION ALL 

                                    Select top 10 [Time],Principal_Name,[Duration(s)],logical_reads,[row_count],cast ([Statement] as Varchar(MAX)) as [Statement] ,queryid

                                      from BaselineQueries_v where server_nme='SHO-P-BPODBS-05.sentryextranet.com'   

                                      and Database_Name='BP_Policy_ODS' 

                                      and [time] > GETDATE()-30  

                                      and Principal_Name not like '%Informatica%'  

                                      order by [Duration(s)] desc 

                                    1 of 1 people found this helpful
                                    1 2 Previous Next