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?
azurecmd.Parameters("@Body")= bodytryazurecmd.CreateParameter ("@Body", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, -1, body)The -1 represents size formaxcolumns.sp_is reserved, by Microsoft, for Special / System Procedures. It should not be used for User Procedures. Doing so comes with a performance cost and the risk of your Procedure simply not working one day after an update/upgrade. Either use a different prefix or (possibly better) no prefix at all. Is the sp_ prefix still a no-no?Parameters.refreshcalls some stored procedure behind the scenessp_procedure_params_rowset. If you use extended events to see the commands being run does it call the same proc in both cases? And does that proc return the same results in both cases?Parameters.refreshand from what I understand my suggestion would cause a different error sinceParameters.Refreshalready adds the parameters - so instead of callingCreateParametersinstead try to just sepcify the size of the@Bodyparameter:azurecmd.Parameters("@Body").Size = -1and keep theazurecmd.Parameters("@Body")= bodyrow (though to be honest, I dislike the concept of "default properties" and would rather explicitly mention the property name instead.