1

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?

9
  • 1
    I'm not sure it will solve the problem., but Instead of azurecmd.Parameters("@Body")= body try azurecmd.CreateParameter ("@Body", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, -1, body) The -1 represents size for max columns. Commented Jan 10, 2024 at 8:50
  • 1
    Whitespace and Linebreaks are paramount to making readable text; not just in code. Please get into the habit of making good use of both. Poor/bad formatting is not going to help you or others when you need to be able to quickly read and understand your code. Using indentation and line breaks really helps easily distinguish specific code blocks and sections, and make finding errors far easier when a line only contains 10's of characters, rather than 100's. Commented Jan 10, 2024 at 8:56
  • 1
    Also, as an FYI, the prefix 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? Commented Jan 10, 2024 at 8:57
  • 1
    I vaguely remember Parameters.refresh calls some stored procedure behind the scenes sp_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? Commented Jan 10, 2024 at 8:58
  • 4
    Remove the parentheses and this error goes away. However @MartinSmith's comment have caused me to go read up on Parameters.refresh and from what I understand my suggestion would cause a different error since Parameters.Refresh already adds the parameters - so instead of calling CreateParameters instead try to just sepcify the size of the @Body parameter: azurecmd.Parameters("@Body").Size = -1 and keep the azurecmd.Parameters("@Body")= body row (though to be honest, I dislike the concept of "default properties" and would rather explicitly mention the property name instead. Commented Jan 10, 2024 at 10:45

1 Answer 1

1

Using the following works:

azurecmd.Parameters("@Body").Size = -1 
azurecmd.Parameters("@Body")= body
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.