ADO recodset problem with nvarchar(max)

  • Hi everybody!

    1. My front end is Access 2003.

    2. My backend - SS2K8

    Problem:

    When I open ADO recordset using stored procedure + ADO.command, fields nvarchar(max) DO NOT return data. Intellisense in VBA recordset field shows just a set of little squares.

    The problem can be solved if I cast nvarchar to Text inside the stored procedure.

    HOWEVER, they say that TEXT datatype is obsolete and is not going to be supporterde in the future.

    Can anybody suggest another way?

    Thanks

  • What driver are you using for the ODBC datasource? Try using SQL Native Client (SQLNCLI10). If it doesn't show up in your ODBC applet you'll need to install it.

    The probability of survival is inversely proportional to the angle of arrival.

  • This is the driver I use

    SQL Server Native Client 10.0 (SQLNCLI10.DLL)

  • I don't believe Access 2003 will handle nvarchar(max)

    The SQL Backend is fine with it, the Access front end is not. So in this case I'd make the client do a cast to nvarchar(max) from ntext when saving, and anothet cast from nvarchar(max) to ntext when retrieving it.

    But you said you're using SQL Server 2k... SQL 2K doesn't support nvarchar(max) either.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • It handles perfectly the other way - when I insert into SQL table and set the parameter

    cmd.CreateParameter("@CaseSummary", adLongVarWChar, adParamInput, -1, IIf(IsNull(txtCaseSummary.Value), Null, txtCaseSummary.Value))

    So Access 2003 handles something at least upstream

  • By the way I'm sorry, I am using Sql Server 2008

  • I have been able to seamlessly handle varchar(max) with older ASP and vb-script code that expect 'text' by using "DataTypeCompatibility=80" in the connection string. There might be a setting in the ODBC applet that lets you specify this.

    Or, you can set up pass through query on the Access side that uses a connection string instead of a DSN.

    The probability of survival is inversely proportional to the angle of arrival.

  • valeryk2000 (2/16/2012)


    It handles perfectly the other way - when I insert into SQL table and set the parameter

    cmd.CreateParameter("@CaseSummary", adLongVarWChar, adParamInput, -1, IIf(IsNull(txtCaseSummary.Value), Null, txtCaseSummary.Value))

    So Access 2003 handles something at least upstream

    Access internally uses Memo to store those values. nvarchar(max) was introduced with SQL 2005, which came out after Access 2003. ergo Access 2003 doesn't understand nvarchar(max) when the type is returned from SQL. I've run into the same problems using Excel 2003 to query databases for managers who want dashboards.

    Upgrade the Access application to Access 2007 or 2010 and you might have better luck, or cast the data from a value that SQL 2008 understands to one that Access 2003 understands is a representation of it.

    Or try what sturner has above. It's basically the same thing, except SQL will return that the type of the field is ntext. And then Access will handle it properly.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Right. I suspect that normal vb/vb script would handle it ok, but Access has problems

  • So casting to text/ntext (what I am currently doing) is the only way to do it?

  • valeryk2000 (2/16/2012)


    So casting to text/ntext (what I am currently doing) is the only way to do it?

    AFAIK unless you can configure the driver somehow to map that for you that is the only way.

    I am no access expert, but I'd look to see if there were a way to customize the connections string it uses for its linked table feature to specify the DataTypeCompatibility option.

    I did not see any way to set that option in the DSN itself, but Access still would need to use a connection string to make the connection even though it is using a DSN and it would be nice it had a way for your app to sett additional options for this string.

    The other options is, as someone else mentioned, is to upgrade your version of Access.

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply