Data truncated at start when written to table

[303factory]

Hi peeps

I'm having a problem where data is being truncated at the start when written into my table. It happens when updating a field with a string that is several thousand characters long. Apparently truncating at the start not the end is not normal behaviour for any database so im a bit confused as to how this is happening.

Details:
Back end: SQL Server 2008
Front end: MSAccess client
Tables linked using ODBC with SQL Server native client 10.0 driver

The field with the observed behavour is the type Nvarchar(MAX) which should theoretically hold 2GB data. If I change the field type to Nvarchar(4000) then the behaviour does not occur, and my client automatically truncates it to the field size at the end instead of the beginning

Code:

Connection declaration (global variable)

Set gSQLdbase = New ADODB.Connection
With gSQLdbase
.ConnectionString = "Provider=SQLNCLI10;Server=" & [serverip] & ";Database=[databaseName];Uid=[logon]; Pwd=[password];"
 .Open
End With

The error occurs in this body of code

Dim strMessageBody as string
Dim rstRS As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblMessages WHERE 1=0"
Set rstRS = New ADODB.Recordset
rstRS.Open strSQL, gSQLdbase, adOpenKeyset, adLockOptimistic, adCmdText

' [loop through XML nodes]

With rstRS
     .AddNew
     
      strMessageBody = [string read from an xml file]

     .Fields("Message") = strMessageBody

End With

'[end loop]

rstRS.Update
rstRS.Close
set rstRS = nothing

If I debug and look at the '.Fields("Message") = strMessageBody' line, then the strMessageBody contains the entire string, but if I look at the table after the .update then the data in the table has been truncated.
Our IT department have tested the server using INSERT code and have shown that the Nvarchar(MAX) field is functioning and capable of holding long strings.

Ive scoured the net but failed to find anything useful.. any ideas?

thanks in advance, 303.

 

[Galaxiom]

Could it be this?

To enable ADO to use new features of recent versions of SQL Server, some enhancements have been made to the SQL Server Native Client OLE DB provider which extends the core features of OLE DB. These enhancements allow ADO applications to use newer SQL Server features and to consume two data types introduced in SQL Server 2005: xml and udt. These enhancements also exploit enhancements to the varchar, nvarchar, and varbinary data types. SQL Server Native Client adds the SSPROP_INIT_DATATYPECOMPATIBILITY initialization property to the DBPROPSET_SQLSERVERDBINIT property set for use by ADO applications so that the new data types are exposed in a way compatible with ADO. In addition, the SQL Server Native Client OLE DB provider also defines a new connection string keyword named DataTypeCompatibility that is set in the connection string.

To enable the usage of SQL Server Native Client, ADO applications will need to implement the following keywords in their connection strings:

DataTypeCompatibility=80

http://msdn.microsoft.com/en-us/library/ms130978.aspx

 

[303factory]

Could it be this?

To enable ADO to use new features of recent versions of SQL Server, some enhancements have been made to the SQL Server Native Client OLE DB provider which extends the core features of OLE DB. These enhancements allow ADO applications to use newer SQL Server features and to consume two data types introduced in SQL Server 2005: xml and udt. These enhancements also exploit enhancements to the varchar, nvarchar, and varbinary data types. SQL Server Native Client adds the SSPROP_INIT_DATATYPECOMPATIBILITY initialization property to the DBPROPSET_SQLSERVERDBINIT property set for use by ADO applications so that the new data types are exposed in a way compatible with ADO. In addition, the SQL Server Native Client OLE DB provider also defines a new connection string keyword named DataTypeCompatibility that is set in the connection string.

To enable the usage of SQL Server Native Client, ADO applications will need to implement the following keywords in their connection strings:

DataTypeCompatibility=80

http://msdn.microsoft.com/en-us/library/ms130978.aspx

Thanks!

Interesting.. if I do this then the full string gets populated in the SQL table (if I look with the SQL Server mangement studio)

However if I look at the linked table in MSAccess, the front is still truncated. Any idea why this may be? Is there any connection string equivalent for using linked with ODBC / sql server native clinet 10.0?

 

[Banana]

So you're saying that data never was truncated when you look in SSMS but only when you look in Access?

Maybe this article will give you some ideas where to look?

 

[303factory]

So you're saying that data never was truncated when you look in SSMS but only when you look in Access?

Maybe this article will give you some ideas where to look?

Well the plot thickens.. the data is truncated at the end without using the data compatibility connection string (but my software did this as far as I know, it truncates to the field size unless field size returns '-1' as it should with nvarchar(MAX))

But either way the data is truncated at the beginning when you view it in access. I'll take a look at that article and get back to you.

 

[303factory]

I can prevent truncation in the linked tables by using the NTEXT field type instead of NVARCHAR(MAX)

However if I try to open an access query that links to the table I get the following error

'The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.'

*sobs*

 

[303factory]

'There are problems with the support for nvarchar(max) in Access 2003.
Replace this type with NTEXT and you should be OK.'

http://www.microsoft.com/communitie...c5-8290-9bb98bc8a85d&cat=&lang=&cr=&sloc=&p=1

 

[Banana]

Hmm. I'm now wondering...

When it's NVARCHAR(Max), does Access recongize this as a Text or a Memo in the design view? If as a text, then that's probably why - I know that SQL Server does something magical with NVARCHAR(Max) that it is to be preferred over old way of using NVARCHAR(4000), and it's possible that the magic is confusing either ODBC or Access into thinking it's not a TEXT (don't confuse this with Access's Text - in most RDBMS, TEXT means anything more than 255 characters aka Access's Memo in general) but rather just VARCHAR (which is Access's Text).

As for the inability to use IS NULL or LIKE... I would imagine that's expected. As that page I showed, there's lot of restrictions upon anything bigger than 255 characters and for a good reason. What is your query?

 

[303factory]

Hmm. I'm now wondering...

When it's NVARCHAR(Max), does Access recongize this as a Text or a Memo in the design view? If as a text, then that's probably why - I know that SQL Server does something magical with NVARCHAR(Max) that it is to be preferred over old way of using NVARCHAR(4000), and it's possible that the magic is confusing either ODBC or Access into thinking it's not a TEXT (don't confuse this with Access's Text - in most RDBMS, TEXT means anything more than 255 characters aka Access's Memo in general) but rather just VARCHAR (which is Access's Text).

As for the inability to use IS NULL or LIKE... I would imagine that's expected. As that page I showed, there's lot of restrictions upon anything bigger than 255 characters and for a good reason. What is your query?

It appears as text(255) - this is how I found out about the problems with nvarchar(max) in access 2003. So looking for now like I have to workaround it until a fix is available.

MY query in sql view is as such

SELECT tblmessages.ReportOrder, tblmessages.Hex, tblmessages.Status, tblmessages.FolderName, tblmessages.ID, tblmessages.UniqueRef, tblmessages.Type, tblmessages.StatusManual, tblmessages.DestinationOrSourceNumber, tblmessages.DestinationOrSourceNumberManual, tblmessages.DestinationOrSourceName, tblmessages.DestinationOrSourceNameManual, tblmessages.FolderNameManual, tblmessages.Date, tblmessages.DateManual, tblmessages.Time, tblmessages.TimeManual, tblmessages.Subject, tblmessages.SubjectManual, tblmessages.MessageManual, tblmessages.MultipartMessage, tblmessages.MultipartSection, tblmessages.Message, tblmessages.FileID, tblmessages.DeletedData, tblmessages.DestinationFileName, tblmessages.DestinationFileNameManual, tblmessages.Source, tblmessages.Checked1, tblmessages.Checked2, tblmessages.Amended, tblmessages.DataSet, tblmessages.Hex, tblmessages.Report
FROM tblmessages 
WHERE (((tblmessages.Type)="SMS") AND ((tblmessages.Source)="ME"))
ORDER BY tblmessages.ReportOrder, tblmessages.Hex, tblmessages.Status, tblmessages.Date DESC , tblmessages.Time DESC , tblmessages.MultipartMessage DESC , tblmessages.MultipartSection DESC , tblmessages.Message

tblMessages.Message is the NTEXT field. Apparently I can use the 'cast' function to convert it into a nvarchar so it can sort it, but cannot get the syntax right yet

 

[Galaxiom]

NTEXT is deprecated so not really a solution.

The NVARCHAR(MAX) is an odd construct that stores some of the data in a Large Object outside of the record in the table. Everything exceeding 8000 Bytes (equivalent to 4000 characters) is held in the LOB.

It is almost like the problem is due to the start of the string being fed into the LOB and never coming out again. I wonder if the table holds the first or the last 4000 characters of data once the field capacity is exceeded.

Either way it sounds like you are only geting back the data from the field and the LOB data is lost somewhere along the way.

 

[Banana]

Yeah, that's what I would expect when you were talking about truncating due to the magic behind NVARCHAR(Max).

Just to be complete, you should look for the documentation on how SQL ODBC driver you're using is translating NVARCHAR(Max) ... it should be translating into ODBCs TEXT or Access Memo - it's possible a newer driver may do a better job of doing that or there's a setting that need to be set for forcing NVARCHAR(Max) to be treated as TEXT across the ODBC layer.

I don't see why you need to sort by Message. You can just omit that column and leave the sorting to the other columns which should be sufficient, surely. As a general rule of thumb, don't bother trying to do anything fancy with big columns - sorting, evaluating, using it in a function, etc. This is pretty much true regardless of which server you are using. Best just to SELECT it, and use other fields to sort or filter which records you want and process the content of big column locally.

 

[303factory]

NTEXT is deprecated so not really a solution.

The NVARCHAR(MAX) is an odd construct that stores some of the data in a Large Object outside of the record in the table. Everything exceeding 8000 Bytes (equivalent to 4000 characters) is held in the LOB.

It is almost like the problem is due to the start of the string being fed into the LOB and never coming out again. I wonder if the table holds the first or the last 4000 characters of data once the field capacity is exceeded.

Either way it sounds like you are only geting back the data from the field and the LOB data is lost somewhere along the way.

Unfortunately taking this any further is beyond me, having scoured the net quite thoroughly now there doesnt seem to be a solution to it and im not experienced enough to fix this myself so I'll have to change to NTEXT until the problem is fixed.

 

[303factory]

Yeah, that's what I would expect when you were talking about truncating due to the magic behind NVARCHAR(Max).

Just to be complete, you should look for the documentation on how SQL ODBC driver you're using is translating NVARCHAR(Max) ... it should be translating into ODBCs TEXT or Access Memo - it's possible a newer driver may do a better job of doing that or there's a setting that need to be set for forcing NVARCHAR(Max) to be treated as TEXT across the ODBC layer.

I don't see why you need to sort by Message. You can just omit that column and leave the sorting to the other columns which should be sufficient, surely. As a general rule of thumb, don't bother trying to do anything fancy with big columns - sorting, evaluating, using it in a function, etc. This is pretty much true regardless of which server you are using. Best just to SELECT it, and use other fields to sort or filter which records you want and process the content of big column locally.

Okeydokey, that query was written by my predescessor and I can see no reason why we need to sort by that column anyway so removing it makes everything work.. thanks for your help guys ill post here if the nvarchar(max) issue gest resolved at any point

 

[Banana]

Just out of interest, does the same query work with NVARCHAR(Max)?

 

[303factory]

Just out of interest, does the same query work with NVARCHAR(Max)?

yep the query worked fine, apart from truncating the message field..but the linked table did that too

 

[Banana]

Right. Makes sense.

I guess I should have had added whether running the same SQL statement in different contexts matter:

Passthrough query?
ADO connection?

Do they get truncated, too?

 

[303factory]

Right. Makes sense.

I guess I should have had added whether running the same SQL statement in different contexts matter:

Passthrough query?
ADO connection?

Do they get truncated, too?

The data was not being truncated using ADO connection to read/write to the field. However I cant use that for my subforms linked to the tables as far as I know.. the front end of the client was stuck with the truncated data

didnt try a pass through, i never got them working with bound subforms so they arent much use to me

 

[Banana]

Hm, you can in fact bind an ADO recordset to a form, and you even can bind an ADO recordset to a subform to a parent form bound to DAO recordset. It does mean, though you have to maintain the link manually.

A better solution, though would be to show a list of the records with only first 255 characters where users could then double click on the record and open another form that download the full message via ADO and bound to that popup form for the user to edit & save.