How do I use Varchar(max) output from a SQL Server 2008 stored procedure in an old ASP/VBScript app?

Question

Here's the sproc:

ALTER PROC [dbo].[cms_createNoteMultipleRecID] (
  @NoteDt smalldatetime, 
  ...
  @NoteIDCSV VARCHAR(max) OUTPUT
)

And here's the VBScript:

cmd.CommandText = "cms_createNoteMultipleRecID"
cmd.Parameters.Append = cmd.CreateParameter("@RC", adInteger, adParamReturnValue)
cmd.Parameters.Append = cmd.CreateParameter("@NoteDt", adDBDate, adParamInput,, NoteDt )
...
cmd.Parameters.Append = cmd.CreateParameter("@NoteIDCSV", adLongVarWChar, adParamOutput )

Seems like adLongVarWChar works for input, as I have used it a few times. But, what's the proper way to use a varchar(max) output in VBScript? As-is, my error states: "Parameter object is improperly defined. Inconsistent or incomplete information was provided." from ADODB.

SQL Server does not support the adDBDate datatype. You have to correct this problem, change the datatype of the @NoteDt parameter to adDBTimeStamp. — Artemination, Mar 05 '15 at 23:16

score 4 · Accepted Answer · answered Dec 09 '10 at 18:46

4

This works:

cmd.Parameters.Append = cmd.CreateParameter("@NoteIDCSV", adBStr, adParamOutput, -1 )

Open to better ideas.

answered Dec 09 '10 at 18:46

alphadogg

12,762
9
54
88

score 2 · Answer 2 · answered Dec 09 '10 at 18:28

2

set the size argument to CreateParameter to -1. I think this is the syntax (can't test at the moment):

cmd.Parameters.Append = cmd.CreateParameter("@NoteIDCSV", adLongVarWChar, adParamOutput, -1 )

answered Dec 09 '10 at 18:28

tenfour

36,141
15
83
142

How do I use Varchar(max) output from a SQL Server 2008 stored procedure in an old ASP/VBScript app?

2 Answers2