1 2 Previous Next 16 Replies Latest reply on Nov 14, 2016 5:10 AM by Matz Rasmus

    String value being truncated at 1024 characters

      We are using a Microsoft SQL database & Tableau server for reporting.  Many of our fields are string values, and several of them contain responses greater than 1024 characters.  These responses are being truncated somewhere in the process of going from the DB to a Tableau report (as I can pull the values from a table in SQL Server Management Studio and get the full value even when greater than 1024 characters).

       

      I've tried using a data extract with no luck.


      Please help!

       

      Thank you,

      John M

        • 1. Re: String value being truncated at 1024 characters
          Tracy Rodgers

          Hi John,

           

          One thing to try would be to change the data type in Microsoft SQL to VARCHAR instead of using LONGTEXT.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: String value being truncated at 1024 characters
            Rachel Ambler

            As of at least 7.0.5 Tableau does NOT like any VarChar or NVarChar data types defined with a MAX length.

             

            We had to convert to a VarChar(8000) just to get it to display over 1,024 characters.

             

            Nuts, I know, but there it is...

             

            http://random-thunks.com/2012/12/13/tableau-and-the-1k-string-limiting-with-sql-server/

            • 4. Re: String value being truncated at 1024 characters
              Richard Leeke

              Just ran into this issue and tried Rachel's various workarounds.

               

              I tried the ODBC / native client approach first, but although that returned the full text field it broke various other things (like filters on datetimes).

               

              So I fell back to trying to get the first 8,000 characters - but I can't actually change the data type of the underlying column to VARCHAR(8000), so decided to try doing the conversion on the fly with a RAWSQL expression.

               

              My first attempt was like this:

               

              RAWSQL_STR("CAST(%1 AS VARCHAR(8000))", [TextData])

               

              But Tableau was too quick for me - it gets in and casts the long column to VARCHAR(1024) before my cast, so that comes out like this:

               

              CAST((CAST([TextData] AS VARCHAR(1024))) AS VARCHAR(8000))

               

              Next attempt was just to include the column name as a literal in the SQL expression, without a substitution parameter:

               

              RAWSQL_STR("CAST([TextData] AS VARCHAR(8000))")

               

              That generates the right expression in the select list - but doesn't add it to the GROUP BY clause, so the SQL is invalid.

               

              Finally I tried this which worked - but is a bit ugly:

               

              RAWSQL_STR("CAST([TextData] AS VARCHAR(8000))", [TextData])

               

              The literal [TextData] inside the string means that Tableau doesn't even know that is there, so doesn't try to truncate it. The (unreferenced) [TextData] parameter causes Tableau to realise that it needs to include the expression in the GROUP BY clause. Luckily it isn't put off by the lack of references to the parameter (ie the fact that there is no '%1' in the string).

               

              I wonder if anyone (Robert Morton ?) knows of any other way to get at the full string - or can point out any reason why my RAW_SQL bodge is a bad idea.

              • 5. Re: String value being truncated at 1024 characters
                Robert Morton

                Hi Richard,

                 

                Are you using Tableau 8.1? That is the first version with the first-class SQL Server connector operating natively through ODBC instead of OLE DB. The problems you described with broken features via Generic ODBC (aka "Other data sources (ODBC)") should not be present in Tableau 8.1 with the first-class SQL Server connector. Even if you're unable to commit to upgrading to Tableau 8.1 yet, please consider running a brief test with Tableau 8.1 to determine if this new release addresses your problems. If not, please report these problems to Support (support@tableausoftware.com) so they may gather more information about this issue.

                 

                As for your calculated field, your final try is indeed the approach I would have taken too. And you're spot-on with the subtle but very important need to include the text field as an additional, unused parameter to the RAWSQL calculation.

                 

                I hope this helps,

                Robert

                • 6. Re: String value being truncated at 1024 characters
                  Richard Leeke

                  Thanks Robert

                   

                  Yes, I'm using 8.1 - and in fact I came across another thread where you had mentioned that 8.1 should address the issue after I had posted that last night.

                   

                  I'm seeing truncation to 1024 characters with the Tableau SQL Server connector, but not with "Other sources (ODBC)" and choosing native client 11.0 - but the ODBC approach has the other breakages. I was wondering if it might be that I haven't got the necessary versions of the SQL Server drivers installed. I have some release of the driver for both SQL Server 2008 R2 and SQL Server 2012 installed, but those seem to pre-date the 8.1 beta. Just to be on the safe side I'm planning to install the very latest of each. If that doesn't help I'll certainly contact support if 8.1 was supposed to fix this.

                   

                  Thanks again.

                  Richard

                  • 7. Re: String value being truncated at 1024 characters
                    Robert Morton

                    Hi Richard,

                     

                    It turns out that Tableau must still perform truncation when using the following data types: 'text', 'ntext' and 'image'. The reason is that these columns are presented to Tableau as requiring 2 GB of memory for every single row of data. This is clearly excessive for most use cases, but the driver is unable to provide the actual amount of memory needed to handle the largest record present in the table. You can see evidence of this in the Tableau log.txt file after establishing a connection, with the list of columns and their types containing entries like the following:

                     

                    Column: [text] SQL_LONGVARCHAR(2147483647)

                     

                    Starting with SQL Server 2005 you should be using the new convention for large string columns: varchar(max), nvarchar(max) and varbinary(max). Such columns are capable of holding very long text strings, but unlike a 'text' type they will report a size requirement of zero bytes. This indicates to Tableau that it should allocate a reasonable non-zero size for fetching the result set, and Tableau will react to any data truncation warnings by dynamically re-sizing its buffers and re-fetching data as needed. Currently Tableau coerces zero-size columns to a default buffer size of 32KB per column per row, which may even avoid dynamic handling of data truncation altogether. You can see this happening in tabprotosrv*.txt log files, with entries such as the following:

                     

                    ODBCProtocolImpl::ResolvePreferredImportBinding: Field named 'none:nvarchar(max):nk' forced to wire data type -8 (SQL_C_WCHAR) and octet length 32768; SQL data type is -9 (SQL_WVARCHAR). Original: wire data type -8 (SQL_C_WCHAR) and octet length 0.

                     

                    Microsoft has documented their recommended replacement of 'text' types with varchar(max) in more detail here: http://technet.microsoft.com/en-us/library/ms130896.aspx

                     

                    I hope this helps,

                    Robert

                     

                    (mentioning those who may want to bookmark this: Jen Underwood Russell Christopher Rachel Ambler, kettan )

                    • 8. Re: String value being truncated at 1024 characters
                      Richard Leeke

                      Thanks Robert - I was half way through writing an email to support, but you saved me the trouble.

                       

                      The irony in this case is that the database schema in question is Microsoft's Visual Studio load testing results database - so they obviously haven't followed their own guidelines - the column in question is NTEXT.

                       

                      I can't change the schema, so I've just tried changing my RAWSQL to cast the NTEXT as VARCHAR(MAX).

                       

                      That certainly returns more than the 8,000 limit, but is still getting truncated - though it's a bit hard to say exactly what the limit is because different ways of accessing it seem to give a different subset. Displaying it in a tooltip it certainly has more than a screenful. Right-clicking on a mark and selecting Copy Data I get 17,475 characters of the field. Using View Data I get 16384 characters.

                       

                      Looking in the logs I see this message:

                       

                      The field 'TextDataMax' contains N values that were truncated when returned from the database

                       

                      Unfortunately all the rows in the table are either under 10 KB or over 500 KB, so I can't tell easily what the limit is. Given the different lengths I see with Copy Data and View Data I suspect that there may be truncation happening at one size in the data source and then further truncation on access through the client.

                       

                      I'll experiment with copying the data into an NVARCHAR(MAX) column and see if that behaves the same and also creating some data of different lengths and try to narrow down what the limit on data retrieval is.

                       

                      The field in question varies between maybe 40 characters and over a million. Clearly I don't want to go bringing back MBs for lots of rows, so what I ideally want to be able to do is show the first N characters for every row, but be able to select a row and use an action filter to bring back the entire field on a different sheet. Even then the big ones will be too long to display - but I'd really like the user to be able to copy the text out to paste into a text editor.

                       

                      As an aside, I also noticed a lot of these messages in the logs:

                       

                      2014-02-08 08:52:31.584 (-,-,-,-) 2abc: Error creating temporary table to optimize ad-hoc group

                      2014-02-08 08:52:31.584 (-,-,-,-) 2abc: [Microsoft][SQL Server Native Client 11.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

                      2014-02-08 08:52:31.584 (-,-,-,-) 2abc: The last tuple inserted was (14438407, "Denorm", #2014-01-19 17:06:30.540000#, #2014-01-19 16:26:27.530000#, 1)

                      2014-02-08 08:52:31.584 (-,-,-,-) 2abc: Error inserting 139 tuples into table [#Tableau_3_Filter].

                       

                      That was the ad-hoc group created by the action filter. Is that a known issue, Robert, or should I raise that with support?

                      • 9. Re: String value being truncated at 1024 characters
                        Robert Morton

                        Hi Richard,

                         

                        Thanks for digging in. Would you please report the data insertion issue to Support?

                         

                        This issue stems from a known problem with the SQL Server Native Client 11.0 driver, and it's one that Microsoft appears to have no interest in resolving. The problem is that their driver is unable to properly report metadata for insert parameters bound to fields in a temporary table. When we discovered this problem initially, we were able to fix the issue for the following types: BIGINT, fixed-precision numerics, and strings. The fractional seconds precision issue for date/time is one we had not previously encountered (are you using datetime2 or other extended types?), and it's something we should try to address. The logs and DDL you can provide Support will help with this immensely.

                         

                        In the meantime, there are two possible workarounds:

                        • Use a TDC customization to force the use of the older SQL Server driver:
                          <customization name='odbc-connect-string-extras' value='DRIVER={SQL Server Native Client 10.0}' />
                        • Downgrade your driver to SQL Server Native Client 10.0. I do not know of any drawbacks to doing so, but I'm sure there are caveats to keep in mind.

                         

                        I hope this helps,

                        Robert

                        1 of 1 people found this helpful
                        • 10. Re: Re: String value being truncated at 1024 characters
                          Richard Leeke

                          I've logged the case for the fractional seconds precision issue. The suggested workaround of falling back to the earlier client did indeed resolve it, too.

                           

                          On the truncation of the long field, I found that I was actually getting 16384 bytes whichever way I accessed it (copy data, view data, etc). As 2 x 16384 is 32768 and the logs show the truncation to 32 KB that you mentioned, that all fits (2 byte unicode characters).

                           

                          SELECT (CAST([TextData] AS NVARCHAR(MAX))) AS [none:Calculation_5300207201746920:nk],

                          ...

                          ODBCProtocolImpl::ResolvePreferredImportBinding: Field named 'none:Calculation_5300207201746920:nk' forced to wire data type -8 (SQL_C_WCHAR) and octet length 32768; SQL data type is 12 (SQL_VARCHAR). Original: wire data type 1 (SQL_C_CHAR) and octet length 0.

                           

                          Robert: you mentioned that: "Tableau will react to any data truncation warnings by dynamically re-sizing its buffers and re-fetching data as needed". It doesn't look as if that is happening, though. Could that be because the column is the result of a cast and not a base table column? I might try an experiment with a VARCHAR(MAX) column and see if that shows the dynamic re-sizing.

                          • 11. Re: Re: String value being truncated at 1024 characters
                            Richard Leeke

                            I tried a  VARCHAR(MAX) column and it was again truncated at 32 KB - so no sign of resizing. The log showed this:

                             

                            ODBCProtocolImpl::ResolvePreferredImportBinding: Field named 'none:TextData:nk' forced to wire data type -8 (SQL_C_WCHAR) and octet length 32768; SQL data type is -9 (SQL_WVARCHAR). Original: wire data type -8 (SQL_C_WCHAR) and octet length 0.

                            ODBCRowsetSource::ODBCRowsetSource: Attempting to set the row fetch size to 512 rows, with 32768 bytes per row.

                            ODBCRowsetSource::BindColumn: Binding column 1 named 'none:TextData:nk' with wire type -8 (SQL_C_WCHAR) and octet length 32768.

                            ODBCRowsetSource::VerifyRowFetchCount: Fetched row at offset 0 has error status 5 (SQL_ROW_ERROR).

                            • 12. Re: Re: String value being truncated at 1024 characters
                              Robert Morton

                              Hi Richard,

                              It appears that the driver is not reporting the truncation problem properly. The result should not be an error condition, but rather SQL_SUCCESS_WITH_INFO that indicates the data transfer succeeded with caveats. I'm not sure yet if this is a known issue with the SQL Server ODBC driver.

                              -Robert

                              1 of 1 people found this helpful
                              • 13. Re: Re: String value being truncated at 1024 characters
                                Richard Leeke

                                Thanks Robert.

                                 

                                It's not a huge issue for us, but would be nice to get past it sometime. Getting the first 16,000 characters is a lot better than 1,024 and will usually be good enough.

                                • 14. Re: String value being truncated at 1024 characters
                                  Jen Underwood

                                  Interesting and thanks for the mention Robert.  If there is an issue with SQL Server OBDC, Microsoft should be pinged too and have a "Connect" request to get this on their radar.

                                  1 2 Previous Next