Hi all,

I have a problem calling a stored procedure in SQL Server where there's an OUTPUT parameter that is of type nText. I can't seem to figure out how to pass this parameter as nText to the server - it can pass as nVarChar with no problem, but not as nText.

The stored proc looks like this:

CREATE PROCEDURE test 
  @cName nText OUTPUT,
  @bBit as bit,
  @iResult int OUTPUT
AS

Set @iResult = 10
GO

actually come to think of it I'm not sure how SET an nTEXT value to be used as an output parameter since you can't assign nText - you can only query for it, but SQL Server supports this (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp) if anybody knows how to set this let me know. I'm doing this for some generic code so it needs to work with any type hence the question.

The code to call it is here:

FUNCTION ADOStoredProcedure()

SYS(3101,65001)

LOCAL oConn as ADODB.Connection
oConn = CREATEOBJECT("ADODB.Connection")
oConn.ConnectionString = THIS.cAdoConnectionString
oConn.Open()

LOCAL oCommand as ADODB.Command
oCommand = CREATEOBJECT("ADODB.Command")
oCommand.CommandType= 4  && Stored Proc
oCommand.ActiveConnection = oConn
oCommand.CommandText = "TEST"  && Stored Proc name

*** Or wrappered
LOCAL oParameter as ADODB.Parameter 
oParameter = CREATEOBJECT("ADODB.Parameter")
oParameter.Type= 203  && adVarWChar
oParameter.Value = "English"
oParameter.Size = 90000000
oParameter.Direction = 3  && Fails with 3 but works with 1
oCommand.Parameters.Append(oParameter)

*this.AdoAddParameter(oCommand,"English","Name","OUT",203)
this.AdoAddParameter(oCommand,.t.,"BitParm")
this.AdoAddParameter(oCommand,0,"nResult","OUT")

lnAffected=0
? oCommand.Execute(@lnAffected,,128)     && No result set

*** Gets unspecified error

SYS(3101,0)
RETURN

AdoAddParameter() is just a wrapper around the Parameter object to simplify adding parameters. I use the parm object explictly for the nText parm to make it obvious what's being set.

The code above fails with Unspecified error - it never hits SQL Server, but seems to fail inside of ADO. If I change the parameter type to nVarChar(202) and set the size to 4000 the call works fine. It also works with nText as an IN parameter.

It looks like ADO is not allowing this call to go through... Does anybody know if this is not supported?