none
Pass-through query to SQL Server truncating field to 255 characters RRS feed

  • Question

  • Hi, hope somebody can assist.

    I am using Access 2010 and a User DSN (SQL Server Native Client 11.0) to pass-through to SQL Server 2012.  The query is a simple select query with no WHERE clause, but we might decide to add filters further down the line.  The table that I am trying to retrieve has a nvarchar(MAX) field that currently has examples of data up to 534 characters.  There are about 1000-2000 records.  

    The problem is that the query only returns 255 characters of this field to Access, but works fine in SQL Management Studio (once I've changed the 'Maximum number of characters displayed in each column' in options).

    The Access database design is mostly not using VBA but I have done what I can to explore getting to a similar connection parameter using both the GUI queries and VBA (e.g. DAO.querydef).  Also checked the various options in the DSN.

    Hopefully there is a solution out there that I have not found or thought of yet?

    Cheers

    Mike

    Tuesday, March 1, 2016 4:35 PM

All replies

  • Hi, hope somebody can assist.

    I am using Access 2010 and a User DSN (SQL Server Native Client 11.0) to pass-through to SQL Server 2012.  The query is a simple select query with no WHERE clause, but we might decide to add filters further down the line.  The table that I am trying to retrieve has a nvarchar(MAX) field that currently has examples of data up to 534 characters.  There are about 1000-2000 records.  

    The problem is that the query only returns 255 characters of this field to Access, but works fine in SQL Management Studio (once I've changed the 'Maximum number of characters displayed in each column' in options).

    The Access database design is mostly not using VBA but I have done what I can to explore getting to a similar connection parameter using both the GUI queries and VBA (e.g. DAO.querydef).  Also checked the various options in the DSN.

    Hopefully there is a solution out there that I have not found or thought of yet?

    Cheers

    Mike

    Also, there is no DISTINCT or GROUP BY used.
    Tuesday, March 1, 2016 4:41 PM
  • >>>Hopefully there is a solution out there that I have not found or thought of yet?

    According to your description, I suggest that you could Link to SQL Server data, since data is stored in tables, when you link to a table or view in a SQL Server database, Access creates a new table (often known as a linked table) that reflects the structure and contents of the source object. You can change data either in SQL Server, or in Datasheet view or Form view from Access. The changes that you make to data in one location are reflected in the other. However, if you want to make structural changes, such as removing or changing a column, you must do so from the SQL Server database, or from an Access project that is connected to that database. You cannot add, delete, or change the fields in a linked table while you are working in Access.

    For more information, click here to refer about Import or link Access to SQL Server data

    Wednesday, March 2, 2016 9:53 AM
  • Hello,

    I cannot replicate your problem: I have a table in sql server 2008 (but that should nt be different from 2012)

    as follows

    CREATE TABLE [dbo].[MyTable](
    	[Id] [int] NOT NULL,
    	[LongName] [nvarchar](max) NULL,
     CONSTRAINT [PK_MyTable] 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]
    

    In this table I have 2 records , 1st row has 596 , 2nd rows has 1069 characters in field LongName

    When I connect MS-Access (2013) with ODBC DSN file, I am using the standard SQL server driver

    [ODBC]
    DRIVER=SQL Server

    ...

    to connect to the SQL server . I have made a passthrough query:

    SELECT Id, LongName, len(longname) as lenl from dbo.MyTable

    In the result in datasheet view I can read all characters from the LongName column.

    Could you show your sql server table definition to help to solve your problem?


    Jan D'Hondt - SQL server BI development

    Wednesday, March 2, 2016 11:36 AM
  • This is a problem with the "SQL Server Native Client 11.0" driver. Use the standard "SQL Server" driver, or better, upgrade to the newest "SQL Server Native Client" (which has no number anymore. See here). To install, you need to download the sqlncli.msi file from the SQL Server 2012 SP2 Feature Pack (select the x86 or x64 version appropriate to your OS).

    Matthias Kläy, Kläy Computing AG

     
    • Edited by mklaey Wednesday, March 2, 2016 3:24 PM link changed
    Wednesday, March 2, 2016 3:07 PM
  • Thanks David.  Unfortunately I was not entirely correct when I said it was a table, it is a view.  And this is a read only application. 

    Mike

    Wednesday, March 2, 2016 3:58 PM
  • Thanks for trying Jan.  As I replied to David, it is actually a view of a custom CRM 'Entity' (which is the term people seem to be using).  The design in SQL Server involves 10 tables, which might even turn out to be views?  Rather than try to provide all of that information, is there something specific that you had in mind? 

    Mike

    Wednesday, March 2, 2016 4:05 PM
  • Thanks Matthias, that sounds very promising!  I tried "SQL Server Native Client" but the field was still being truncated.  I will see if I can get the .msi installation approved. 

    Mike

    Wednesday, March 2, 2016 4:07 PM
  • More specifically, can you tell us the datatype of the column which is being truncated?  Regardless of whether the column is ultimately being read from a table or a view you should be able to determine the data type from SSMS by expanding the Columns of the source object in object explorer.  And once you've done that what kind of result do you get when you run a simple 

    select top 10 my_column, len(my_column) from my_view order by len(my_column) asc

    where 'my_column' is the column getting truncated, both from SSMS and as a passthrough?

    -Bruce

    Wednesday, March 2, 2016 4:38 PM
  • Like Mathias said, I am also using the standard SQL server driver.

    Another idea: When you passthrough query is based on a view, try to explicitily cast the column that is causing the problem, e.g.

    SELECT .., CAST(LongName AS Nvarchar(1024)) AS LongName ... FROM <your view>


    Jan D'Hondt - SQL server BI development

    Wednesday, March 2, 2016 4:47 PM
  • More specifically, can you tell us the datatype of the column which is being truncated?  Regardless of whether the column is ultimately being read from a table or a view you should be able to determine the data type from SSMS by expanding the Columns of the source object in object explorer.  And once you've done that what kind of result do you get when you run a simple 

    select top 10 my_column, len(my_column) from my_view order by len(my_column) asc

    where 'my_column' is the column getting truncated, both from SSMS and as a passthrough?

    -Bruce

     Hi Bruce, thanks for your input.

    The  datatype is nvarchar(MAX)

    Interesting results from the queries.  Both SSMS and pass-through give lengths from 673 to 1493.  However,  the SMSS does not truncate whereas the pass-through does.

    NB I edited your query to skip Null records and used DESC rather than ASC.

    Mike


    • Edited by mbeardtoo Wednesday, March 2, 2016 5:32 PM
    Wednesday, March 2, 2016 5:30 PM
  • Like Mathias said, I am also using the standard SQL server driver.

    Another idea: When you passthrough query is based on a view, try to explicitily cast the column that is causing the problem, e.g.

    SELECT .., CAST(LongName AS Nvarchar(1024)) AS LongName ... FROM <your view>


    Jan D'Hondt - SQL server BI development

    Nice idea Jan but it still truncates.

    Mike

    Wednesday, March 2, 2016 5:38 PM
  • [quote without links]

    This is a problem with the "SQL Server Native Client 11.0" driver. Use the standard "SQL Server" driver, or better, upgrade to the newest "SQL Server Native Client" (which has no number anymore. See here). To install, you need to download the sqlncli.msi file from the SQL Server 2012 SP2 Feature Pack (select the x86 or x64 version appropriate to your OS).

    Matthias Kläy, Kläy Computing AG

    [/quote]

    Good suggestion Matthias.  The .msi has now been updated following your links etc but both the standard and the native clients are still truncating.

    Mike 

    Thursday, March 3, 2016 10:43 AM
  • Actually, I'm not convinced that the update worked as I still have SQL Server Native Client 11.0.  I will see if I can get our IT people to get me the Microsoft ODBC Driver 11 for SQL Server on Windows as described here https://msdn.microsoft.com/en-us/library/cc280510(v=sql.110).aspx, but right now the download plage that is linked to is reporting some kind of problem signing in.

    Mike


    • Edited by mbeardtoo Thursday, March 3, 2016 11:15 AM
    Thursday, March 3, 2016 11:14 AM
  • Nope, Microsoft ODBC Driver 11 for SQL Server on Windows has not fixed it either.

    Mike

    Thursday, March 3, 2016 1:56 PM
  • Sorry, yes, I meant DESC :)

    Have you applied all Office patches through Windows Update?  This may be an issue with Access addressed in a service pack somewhere.

    -Bruce

    Thursday, March 3, 2016 7:57 PM
  • Right, but we have what 5+ people suggesting to try the SQL driver (the default one, not ANY THING with native in it).

    The default SQL driver is VERY old, been shipped with windows for 15 years by default (as opposed to having to install the native 10, 11 drivers).

    The "native" drivers are in general a better choice, but they require an install + update to each workstation. So REALLY a good idea to try the SQL Server driver.

    This one:

    So it not likely that using the default driver will work, but at least try the suggestion that been given here SEVERAL times to NOT use a native driver, and try the windows built in one. I also suggest you re-link all tables and also (re-create the pass-though) after you re-linked the database using the SQL driver.

    So try this without a native driver. 

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    Thursday, March 3, 2016 8:04 PM
  • Ok, so in place of a pass-though on the access side, try linking to the view from Access. It will appear a linked table, and in general this approach is preferred to a pass-though query anyway.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    Thursday, March 3, 2016 8:06 PM
  • [qoute without images]

     but at least try the suggestion that been given here SEVERAL times to NOT use a native driver, 

    {/quote]

    Hi Albert,

    At first I was quite taken aback by the harsh tone of this post, but then realised that I had managed to mix up the two clients when I posted at Wednesday, March 02, 2016 4:07 PM  , as I had just tried the standard client.  

    HOWEVER [smile], you seem to have missed my post of Thursday, March 03, 2016 10:43 AM where I say "Good suggestion Matthias.  The .msi has now been updated following your links etc but both the standard and the native clients are still truncating."  

    If you read back through this thread you will see that I have tried to do my own research, given as much information as practical (I gave the field type in my first post), have thanked everybody, have followed suggested tests and reported back promptly (time zones allowing) with any amendments that I had had to make, and generally tried to be a good forumite.  And yes, I have googled and found out how to expedite my MSDN authorisation to quote images and links.  Let's be friends huh?

    Kind regards

    Mike

    Friday, March 4, 2016 11:22 AM
  • Ok, so in place of a pass-though on the access side, try linking to the view from Access. It will appear a linked table, and in general this approach is preferred to a pass-though query anyway.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    Thanks for this Albert, and apologies to David for not trying it sooner.

    I have now tried a linked table with all three clients and viewing the table directly (i.e. no query) and each one results in a truncation at 255 characters.  I deleted the previous linked table each time.

    Mike

    Friday, March 4, 2016 12:25 PM
  • I have now tried a linked table with all three clients and viewing the table directly (i.e. no query) and each one results in a truncation at 255 characters.  I deleted the previous linked table each time.

    Mike

    I thought it through a bit more and decided to try each of the three drivers with a completely new Access database for each one.  Straight linked table, not query.  But it truncated each time.

    Mike


    • Edited by mbeardtoo Friday, March 4, 2016 4:03 PM
    Friday, March 4, 2016 4:03 PM
  • No intent of “harsh” tone. The simple issue was you done MUCH work and obviously have been reading and trying everything you can on this issue.

    So I wanted to make 100% sure we did not miss something simple in the “rush” of testing.

    If there was just one test, and one “lame” try on your part, then I would “just” pointed out to try the standard driver as opposed to the newer. I mean, I think we are just as frustrated as you!

    When trying to drive a stubborn nail then a progressivity larger and larger hammer is required! So just trying to be 100% sure we did not miss tying the standard driver.

    The other thing I would check is after you link the table to the view, then open up the Access linked table in DESIGN mode (you get a message about this being “read only”.

    You want to check the data type. It should appear as a memo type, and not a text type:

    eg this:

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    Friday, March 4, 2016 5:34 PM
  • [quote]

    The other thing I would check is after you link the table to the view, then open up the Access linked table in DESIGN mode (you get a message about this being “read only”.

    You want to check the data type. It should appear as a memo type, and not a text type:

    [snip]

    Regards,

    Albert D. Kallal (Access MVP)

    [/quote]

    Thanks Albert, good question.  The field is memo type and I checked the properties in case there was anything about a size limit.  (and thanks for the extra words too)

    Mike

    Friday, March 4, 2016 6:36 PM
  • Again, I would encourage you to be sure that you have all available updates to Office using Windows Update.

    -Bruce

    Monday, March 7, 2016 10:22 PM
  • So just to be sure you are simply linking to this view, and then running the linked table from the Access side?

    Remember, if you send the results to a report or a local query with a group by, then a comparison occurs, and a truncation to 255 will occur.

    However, no mention of a report, or local query or group by has been noted by you. So I just wanted to point out that the above “common known” truncation issue should not apply unless that view (or pass-though) is being sent to a report (with a group by) or the results are being sent to a local query (again with a group by).

    I should also add that several posters here are not seeing this limitation, nor am I. However, if additional query or reports are being used Access side, then this would explain the truncation you are seeing and why others here are not seeing this issue.

    If you are simply opening the linked view from Access, then the truncation should not be occurring. So some “mystery” or additional issue still remains at large here.

    So do make sure no other query or report or other Access side object is at play here but when testing you are ONLY clicking on (opening) the linked table).

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    Tuesday, March 8, 2016 2:32 AM
  • Again, I would encourage you to be sure that you have all available updates to Office using Windows Update.

    -Bruce

     Hi Bruce,

    Sorry I have not gotten onto this one yet.  Windows looks to be being updated on a regular basis  but it seems that I am at least one Access update behind so I have put in a request to our IT providers.

    Mike

    Tuesday, March 8, 2016 6:13 PM
  • Hi Albert,

    Always worth going through the basics and reducing down to the simplest test :-)  

    And making sure that I knew about the common known truncation issue with GROUP BY.

    This is becoming a long thread and difficult to for everybody to follow what has and has not been done.  Happily, I think I can succinctly respond to your various points by quoting one of my previous posts: "I thought it through a bit more and decided to try each of the three drivers with a completely new Access database for each one.  Straight linked table, not query.  But it truncated each time."

    Sadly I am not allowed to spend more time on this so the Access update is the last test I can try.  The users have accepted a work-around but it pains me to hand over a sub-optimal solution. 

    Thank you for your efforts everyone, I will report back if the update is the solution, or if we find another one at some point.

    Mike

    Tuesday, March 8, 2016 6:38 PM
  • Turned out that not even SP2 had been installed.  However, that and the latest update still did not fix the truncation.

    I closed Access and made a new database for each test.  

    I used just a linked table opened directly in Access without any kind of query whatsoever.

    I ran this test for the native driver, Microsoft ODBC Driver 11 for SQL Server on Windows, and SQL Server Native Client 11.0.

    Thanks again everyone, you were all very helpful but I have to call it a day.

    Mike

    Wednesday, March 9, 2016 11:48 AM
  • I'm sorry you didn't find a resolution.  Other things I would try, but am not optimistic about but which should not take much effort if you had time:

    1) Uncheck "Use regional settings" in whatever DSN you're using to connect to SQL

    2) Copy the sql from your pass through query to a new query and see if your new query has the same results.

    3) Tweak the query in 2) above so that the longest values of the field that is getting truncated sort to the top.

    -Bruce

    Wednesday, March 9, 2016 6:44 PM
  • In SSMS, try running sys.sp_refreshview 'yourViewName'

    Then be sure to re-link your view in Access

    • Edited by Alphonse G Thursday, March 10, 2016 1:13 PM
    Thursday, March 10, 2016 1:13 PM
  • I had a similar issue with a passthrough that I was using to retrieve long JSON data.

    Turns out that the single SQL value was getting returned to the passthrough recordset as four rows.  Looping through the rows seems to have done the trick for me, not sure if it will help those returning multiple records or columns.. but thought I'd leave what worked:

    Function PassthroughScalar(sql As String) As String
        On Error GoTo ERR_PROC
        
    
        'Load Passthrough Query
        Dim qdf As DAO.QueryDef
        Set qdf = CurrentDb.QueryDefs("SqlPassthrough_sqlWIN")
        qdf.ReturnsRecords = True
        qdf.sql = sql
        
        Dim rst As DAO.Recordset
        Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        
        With rst
            While Not .BOF And Not .EOF
                PassthroughScalar = PassthroughScalar & rst.Fields(0)
                .MoveNext
            Wend
        End With
            
        qdf.Close
        rst.Close
        
    EXIT_PROC:
            Exit Function
            
    ERR_PROC:
            MsgBox "ExecutePassthrough Exception: " & Err.description
            Resume EXIT_PROC
            Resume
    
    End Function

    -Jay

    Tuesday, July 23, 2019 5:27 PM