Passing and nvarchar(Max) parameter form Access 2013 to a SQL Server SPROC?

I am writing a routine in my Access 2013 front end that calls a SPROC on SQL Server.  One of the params is a user comment that is defined as nvarchar(max) on the SQl side.

I have written access routines that call SPROCS, passing string variables but they are of a defined length.  A param statement passing to a field defined as nvarchar(30) in SQL looks like this:

    Set param6 = .CreateParameter("passedUserName", adVarChar, adParamInput, 30, passedUserName)
    .Parameters.Append param6

Open in new window


What would the syntax be when passing a param defined a nvarchar(max) in the SPROC?

I'm temporarily using
    Set param6 = .CreateParameter("passedUserName", adVarChar, adParamInput, 1000 , passedUserName)
    .Parameters.Append param6

Open in new window

but that has a fixed length of 1000.
LVL 1
mlcktmguyAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
You might have problems with strings over 8000 long any way depending on versions/connection etc. Very long username :)

But you could use adLongVarWChar with a length of -1

cmd.Parameters.Append cmd.CreateParameter("passedUserName", adLongVarWChar, adParamInput, -1 , passedUserName)

have a read of : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ac06f814-1814-4861-b2cd-8d5e507a7ee3/what-is-the-ado-datatype-for-nvarcharmax?forum=sqldataaccess
0
 
Kelvin SparksCommented:
Pass it as if it was the old nText datatype in SQL (a guess) Access 2013 doesn't understand or recognise the varchar(max) datatype.

Kelvin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.