Hi,

I am trying to run a stored procedure from an asp page.

I've confirmed the following query line in Enterprise Manager (SQL2000)

usp_searchAdv @startdate='17/12/2002 0:0:0', @enddate='17/1/2003 23:59:59', @uid='', @id=0, @rep=0, @category=0, @categoryNOT='', @department=0, @status='-1', @priority=0, @defectType='', @freetextTitle='remove', @freetextDesc='remove', @iOrder=1

which returns a valid record set.

I'm trying to run this from the ASP page with the code ....

<%
Set oUSPconn = Server.CreateObject("ADODB.Connection")
oUSPconn.Open "DSN=validDSN;uid=validUser;pwd=validPassword"

Set oUSPcmd = Server.CreateObject("ADODB.Command")
Set oUSPcmd.ActiveConnection = oUSPconn

oUSPcmd.CommandText = strUSPcommandText 'value shown below

oUSPcmd.CommandType = 1 'adCmdText
oUSPcmd(0).Direction = 4 'adParamReturnValue


'create the RS from running the usp
Set rstProbList = Server.CreateObject("ADODB.Recordset")
oUSPconn.CursorLocation = 3 'adUseClient (2=adUseServer)
Set rstProbList = oUSPcmd.Execute
%>

the value of strUSPcommandText is...
{? = CALL usp_searchAdv (@startdate='17/12/2002 0:0:0', @enddate='17/1/2003 23:59:59', @uid='', @id=0, @rep=0, @category=0, @categoryNOT='', @department=0, @status='-1', @priority=0, @defectType='', @freetextTitle='remove', @freetextDesc='remove', @iOrder=1)}

and the error is a strange....

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

E:\DEFECTLOGGING\LIBERUM\WWW\REP\..\runUSP_return. txt, line 21

------
where line 21 is the
Set rstProbList = oUSPcmd.Execute
command

Any ideas or suggestions would be greatfully received.

Mark

removed all the complex code there and replaced the query with

EXECUTE usp_searchAdv @startdate='17/12/2002 0:0:0', @enddate='17/1/2003 23:59:59', @uid='', @id=0, @rep=0, @category=0, @categoryNOT='', @department=0, @status='-1', @priority=0, @defectType='', @freetextTitle='remove', @freetextDesc='remove', @iOrder=1

called it using a simple

Dim rstProbList, start, oUSPconn

set oUSPconn = server.createobject("adodb.connection")
oUSPconn.Open cnnDB
set rstProbList = oUSPconn.Execute(strUSPcommandText)



duh!