Pass-through query to SQL Server truncating field to 255 characters

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

Answer 1

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.

Answer 2

>>>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

Answer 3

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

Answer 4

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

 

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

[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 

Answer 13

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

Answer 14

Nope, Microsoft ODBC Driver 11 for SQL Server on Windows has not fixed it either.

Mike

Answer 15

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

Answer 16

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

[e-mail]

Answer 17

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

[e-mail]

Answer 18

[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

Answer 19

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

[e-mail]

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

Answer 20

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

Answer 21

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

[e-mail]

Answer 22

[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

Answer 23

Again, I would encourage you to be sure that you have all available updates to Office using Windows Update.

-Bruce

Answer 24

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

[e-mail]

Answer 25

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

Answer 26

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

Answer 27

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

Answer 28

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

Answer 29

In SSMS, try running sys.sp_refreshview 'yourViewName'

Then be sure to re-link your view in Access

Answer 30

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