SQL Server varchar(MAX) parameter results in "Parameter object is improperly defined"

Question

I have a field in a table that I want to store a potentially long error string. To this end I selected varchar(MAX) as the data type. I have created a stored procedure that is used to enter that data in the table and for the field "ErrorDescription" I used the following parameter definition.

@ErrorDescription as varchar(MAX)

The problem is within the ADO Procedure (within Access 2003) that calls the stored procedure to log the error. I take the error description as a string value and attempt to assign it to the parameter ...

cmd.Parameters("@ErrorDescription").Value = errorDescription

but it fails with the following error.

"Parameter object is improperly defined"

If I change the stored procedure definition to ...

 @ErrorDescription as varchar(255)

Then all works well. How can I define the stored procedure parameter to accept a potentially very long string? Is varchar(MAX) the wrong datatype to use? Thank you.

EDIT I should have mentioned the version of SQL Server I was using. I am using SQL Server 2008.

Answer 1

varchar(MAX) was introduced with SQL Server version 2005. I assume that you are using the 2000 version. If so, then you can wither use varchar(8000) or Text

Answer 2

I would first look at definition of column in database to confirm datatype.

SQL_LONGVARCHAR is the server type that is mapped to the Memo field, which I assume to be the Access data type that you want for this data. If that assumption is correct, it looks like you'll need to change the server data type to SQL_LONGVARCHAR (e.g. create the column as type 'text' instead of 'varchar(max)').

Answer 3

I would guess that Access 2003 doesn't know what the MAX keyword represents, since this was introduced in SQL 2005. I'm not sure if you could trick Access by using the TEXT data type and have SQL correctly map this to a VARCHAR(MAX) column or not, however.