SQL Server nvarchar(max) fields are truncated (1 Viewer)

[valeryk2000]

My Access 2003 (or 2007) has linked tables to SQL Server 2008. Forms, reports and queries are, naturally, local. Problem: all data nvarchar(max) fields are truncated in some very wierd way. How can it be fixed?
Thanks

 

[boblarson]

Is it truncated when you look at the tables?

If not, but it happens in queries and such, it is likely that you are having the issue where you cannot have criteria on a query which includes that field. So, if you need to have criteria, create a query first WITHOUT that field then create another query with that first query and the original table in a new query linked to the PK field and then you can use that field in the query, forms, and reports.

This might be helpful:

http://allenbrowne.com/ser-63.html


Also, since you are using SQL Server 2008, you may need to ensure that you are using the SQL Native Client 10 ODBC driver instead of the plain SQL Server ODBC driver.

 

[mdlueck]

Specifically tested on A2007: One thing to keep in mind... Access has a hard time dealing with large amounts of text.

I bumped into a Form Text Field limit of 65,535 characters. Later I found the limitation documented here:

"Access 2007 specifications"
http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx

The result of trying to put more characters into the Form Text Field is all of Access crashes.

I may have more data in a VBA variable, just do not dare put more in a Form Text Field.

 

[valeryk2000]

BobLarson: Yes, it is truncated.
What I see in Access:
convexity stroke. Brainstem reflexes are present. Prognosis not good in terms of functional recovery.

In SS (i highlighted what was not truncated in Access):
(Related Case #2912) 71 year-old female with a history of multiple strokes, rectal cancer , s/p low anterior resection 2009 and total colectomy with ileostomy 2009. She presented with persistent rectal bleeding from the rectal stump. In 4/11/2012 a completion proctectomy with abdomino-perineal approach, hysterectomy, left oophorectomy, repair of enterotomy, and repair of ventral hernia was done. A second surgeon was called in to assist when an open laparotomy approach became necessary. Pre-op Labs: WBC 6.3, Hgb 13.2/Hct 39.1; Plts 217, During procedure the last 2 hours the patient did not have any urine output and was given a 1 L fluid bolus EBL was 1000cc Upon completion of the procedure the patient developed hypotension and PEA that required fluid resuscitation and some chest compressions. A large amount of blood was coming from the pelvic drain. The abdominal incision was reopened and a large amount of blood was in the abdomen and pelvis. Findings: There was diffuse oozing from all sites including the wound and from the ileum consistent with severe coagulopathic bleeding.. Primarily venous bleeding from the pelvic floor and side wall. Multiple other sites of hemorrhage that were difficult to control locally. Due to the developing coagulopathy ( PT 46.1, INR 5.1, PTT 61 and Plts 50,000)and long period of hypothermia the pelvis was tightly packed and a temporary abdominal closure was done. Total EBL 3600 cc /700cc urine output. Patient responded to multiple blood products: 13 units PRBCs; 1000cc FFP; 500 cc Plts, plus fluids. 4/13/2012 a repeat laparotomy with removal of the packing and control of hemorrhage was performed. The patient remained intubated and sedated in the CTICU. 4/16/ Off sedation 12 hours and remains unresponsive could be persistent Fentanyl effect Neurology consulted; grimaces on vent does not follow commands 4/17 Neurology Impression 72 hours post-op : Anoxic encephalopathy decompensation with underlying multi0infarct state and questionable new left hemispheric convexity stroke. Brainstem reflexes are present. Prognosis not good in terms of functional recovery. Tube feedings started Family initially wanted aggressive care , then decided against trach and ventilator was withdrawn and patient expired 4/16/2012.

 

[boblarson]

Just a quick question. Have you put your mouse inside the field in the table and pressed the up arrow to view the multiple lines (and/or made the row size larger)?

 

[valeryk2000]

Bob, sorry. It's longer:
convexity stroke. Brainstem reflexes are present. Prognosis not good in terms of functional recovery.
Tube feedings started
Family initially wanted aggressive care , then decided against trach and ventilator was withdrawn and patient expired 4/16/2012.

So it was chopped from the end of the record
Len returned 256

 

[valeryk2000]

Mike, these limitations of Memo fields existed in all earlier versions of Access. My maximum size of the text in this field of SQL Server table is currently less than 12,000 characters.
Note that Access prototype of this application had Memo field with the same records - and no problem of showing them in text boxes in forms and reports.

 

[valeryk2000]

Bob:
Interestingly, when I deal with these tables through stored procedures/ADO command/Recordset I have to change the type cast (CaseSummary as Text) - CaseSummary is the name of the navarchar(max) field - then I have the whole record. If I do not cast it - it returns in Access rubbish like a set of small squares and accidental characters in vba window - and nothing in the assigned text box on the form. Certainly no problems inside SS.

 

[boblarson]

Bob, sorry. It's longer:
convexity stroke. Brainstem reflexes are present. Prognosis not good in terms of functional recovery.
Tube feedings started
Family initially wanted aggressive care , then decided against trach and ventilator was withdrawn and patient expired 4/16/2012.

So it was chopped from the end of the record
Len returned 256

So again I say that you most likely may need to use the SQL Native Client 10 ODBC driver instead of the generic SQL Server driver. Here's the breakdown matrix of those:

 

[valeryk2000]

Yes, I'm using SQL Native Client 10

 

[boblarson]

Yes, I'm using SQL Native Client 10

I just realized something. I was looking at the specs for the nvarchar datatype in SQL Server 2008 and it will handle 2^31-1 characters which is way more than Access can even handle via code (Access 2003 can handle roughly 2 million characters via code and 65,535 for display in forms and reports).

So, if the field can be changed to either

varchar(2000000)

or

varchar(65535)

 

[SQL_Hell]

Bob,

That's not correct, the max data length options are:

varchar(8000),
nvarchar(4000)

or

varchar(max)
nvarchar(max)

You cannot specify varchar(2000000), you can only use varchar(max)

 

[mdlueck]

Bob:
Interestingly, when I deal with these tables through stored procedures/ADO command/Recordset I have to change the type cast (CaseSummary as Text) - CaseSummary is the name of the navarchar(max) field - then I have the whole record. If I do not cast it - it returns in Access rubbish like a set of small squares and accidental characters in vba window - and nothing in the assigned text box on the form. Certainly no problems inside SS.

I can confirm this behavior as well. ADO guesses incorrectly for VarChar(max) and some date columns. I mean "guess" via

.Parameters.Refresh

When setting the Parameters values, I must manually correct the type:

    [COLOR=Blue][B].Parameters("@bomcad").Type = adLongVarChar[/B][/COLOR]
    If Me.bomcad = vbNullString Then
      .Parameters("@bomcad").Value = Empty
    Else
      .Parameters("@bomcad").Value = Me.bomcad
    End If

as well as passing the value.

 

[valeryk2000]

I understand that truncation happens in the driver code - (both SQL Native client or ODBC driver for SQL server).
Bob: varchar (8000) is NOT truncated, but we cannot jump the 8000 byte treshold (that means 8000 ascii characters) - this is the size of the SQL server page, and the record cannot be broken between pages.
Mike: I played with using different ADO character types - no good.

 

[SQL_Hell]

I don't think it does, I have never experienced truncation using either one of those drivers

 

[valeryk2000]

SQL-Hell: do you mean that varchar (MAX) was not truncated in your Access applications?

 

[SQL_Hell]

No, I don't use Access anymore. I meant that the problem is not the driver itself which is what I thought you were eluding to

 

[mdlueck]

SQL-Hell: do you mean that varchar (MAX) was not truncated in your Access applications?

It is not in my A2007 application, using the SQL Server driver that comes with Windows XP. (Connecting to a 2008 R2 server)

I could anticipate a varchar(8000) to truncate at 8000 characters. I have successfully stored strings of several hundred thousand K in a varchar(MAX) col.

The only sharp spots I found was I needed to fix up the ADO Parameters datatype and NOT try displaying the entire text in an Access Form Text Field control.

To test if the data is getting truncated with the above specs, I would suggest reading the field back into a VBA variable, and check the variable's length.

 

[valeryk2000]

Mike, you are right. But ... try to link you SQL Server table to Access and see - the varchar(max) field is truncated (as we know now - to ~ 255 characters cut from the end of the record). And my problem is that users need to see the whole record in their Access reports
Thanks

 

[valeryk2000]

SQL-Hell: if not a driver - then Access. We know that both varchar (max) and memo are not stored in the table (record size cannot exceed 8 kb), the table field has only pointer to external space where these fields are stored