Unable to query nVarChar(Max) field in Access 2010

Question

have used Stack Overflow as a resource hundreds of times, but my first time posting a question for some help!

I've got a table in SQL Server 2005 which contains 4 nVarChar(Max) fields. I'm trying to pull out the data from an Access (2010) VBA Module using ADO 2.8 I'm connecting using SQL driver SQLNCLI10

(I can't use a linked table, as the 'table' I will ultimately be querying is a Table-Valued Function)

When I then print / use the recordset, the data is getting jumbled and concatenated with other fields in the same record - with a bunch of obscure characters thrown in.

The VBA: (various other methods were tried with the same result)

Sub TestWithoutCasting()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Integer

cn.Open "Data Source=ART;DataTypeCompatibility=80;MARS Connection=True;"

Set rs = cn.Execute("SELECT * FROM JobDetail WHERE JobID = 2558 ORDER BY SeqNo ASC")

Do While Not rs.EOF

    For i = 1 To rs.Fields.Count
        Debug.Print rs.Fields(i).Name & ": " & rs.Fields(i).Value
    Next i

    rs.MoveNext
Loop

End Sub

Example Output:

SeqNo: 1
CommandID: 2
Parameter1:     2 Daily Report    é [& some other chars not showing on here]
Parameter2: [Null]
Parameter3: [Null]
Parameter4: [Null]
Description: Daily Report
Active: False

Expected Output:

SeqNo: 1
CommandID: 2
Parameter1: SELECT  Day_Number  ,Day_Text  ,Channel_Group_ID [...etc]
Parameter2: [Null]
Parameter3: [Null]
Parameter4: [Null]
Description: Daily Report
Active: False

So, it's grabbing bits of data from other fields instead of the correct data (in this case, it's an SQL statement)

I then tried casting the nvarchar(max) fields as text at source

View Created:

    CREATE VIEW TestWithCast 
    AS
    SELECT jd.JobID, jd.SeqNo, jd.CommandID
    ,cast(jd.Parameter1 as text) as Parameter1
    ,cast(jd.Parameter2 as text) as Parameter2
    ,cast(jd.Parameter3 as text) as Parameter3
    ,cast(jd.Parameter4 as text) as Parameter4
    ,jd.[Description]
    ,jd.Active

    FROM JobDetail jd

Now, I initially had some luck here - using the same code as above does bring back data - but when I use this code in my main code (which jumps in & out of other procedures); as soon as I've queried the first result of the recordset, it appears to wipe the rest of the records / fields, setting them to Null. I also tried setting the value of each field to a variable whilst the rest of the vba runs before getting the next record - but this doesn't help either.

It almost feels like I need to dump the recordset into a local Access table, and query from there - which is a bazaar workaround for what is already a workaround (by casting as text).

I there something I'm completely missing here, or do I indeed need to cast as text and load to a local table?

Thanks for any help - it's driving me mad!

ps. Hope I've given the right level of detail / info - please let me know if I missed anything key.

EDIT:

Yikes, I think I've done it / found the issue... I changed the driver to SQLSRV32 (v6.01) - and seems to work fine directly against the text casted field. So... why would it work with an older driver but not the newer 'recommended' (by various sources I read) as the one to use. And... will there be a significant drawback in using this over the native client?

EDIT 2:

Ok, I've tried a few drivers on a few machines, in each case with both the TEXT CASTING and Directly to VARCHAR MAX..

[On my windows 7 machine w/ SQLSMS 2008]

SQL Native Client 10.0 - Neither method works reliably with this driver SQL Server 6.01 - BOTH methods appear to work reliably - further testing needed though

[On our production server w/ SQLS 2005]

SQL Native Client (v2005.90) - Does not work at all with varchar(max), but DOES work with text casting SQL Server (v2008.86) - BOTH methods appear to work reliably - further testing needed though

This should make deployment interesting!

Answer 1

I had the same problem, solved it by converting the field to an nvarchar(1000). Would be an easy, compatible solution for your problem if 1000 chars is enough.

Answer 2

It's not a real answer, because I did not test it, but ... You are using a "DataTypeCompatibility=80" parameter in your connection. As far as I know, DataTypeCompatibility=80 refers to SQL Server 2000, where the nvarchar(max) field type was still not implemented.