Using nvarchar(max) in Azure stored procedure

Question

We are testing moving from a local MSSQL db to Azure.

We have an old ASP classic site which we need to maintain that creates an ADODB command to store email messages using a MSSQL stored procedure. The Body column is of type nvarchar(max).

The ADODB code is as follows:

set azurecon = Server.CreateObject("ADODB.Connection")
set azurecmd = Server.CreateObject("ADODB.Command")
azurecon.Open   sconn
azurecmd.ActiveConnection = azurecon
azurecmd.CommandType = 4
azurecmd.CommandText = "sp_add_mail"
azurecmd.Parameters.refresh
azurecmd.Parameters("@FromAddress")= fromemail
azurecmd.Parameters("@ToAddress")= toemail
azurecmd.Parameters("@Subject")= subject
azurecmd.Parameters("@Body")= body
azurecmd.Execute

And the stored procedure is:

create PROCEDURE [dbo].[sp_add_mail] ( @FromAddress nvarchar(32), 
                                       @ToAddress nvarchar(48),
                                       @Subject nvarchar(160),
                                       @Body nvarchar(max))
AS
    INSERT INTO tbl_Mail(FromAddress, ToAddress, Subject, Body)
    VALUES ( @FromAddress, @ToAddress, @Subject, @Body )

We copied the table and the stored procedure to Azure, but when running the code we receive the following error: Parameter object is improperly defined. Inconsistent or incomplete information was provided.

However if we change the stored procedure from @Body nvarchar(max) to a fixed value for example @Body nvarchar(4000) everything works.

Is there a different requirement when sending nvarchar(max) parameters to Azure?

Answer 1

Using the following works:

azurecmd.Parameters("@Body").Size = -1 
azurecmd.Parameters("@Body")= body