CODEWITHSUNDEEP
sql-server-2008vbscriptasp-classicvarcharmax
alphadogg
alphadogg

Reputation: 12900

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

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.

Upvotes: 2

Views: 4858

Answers (2)

alphadogg
alphadogg

Reputation: 12900

This works:

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

Open to better ideas.

Upvotes: 4

tenfour
tenfour

Reputation: 36896

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 )

Upvotes: 2

Related Questions