[AOLSERVER] ns_db sp_* API not working with MS SQL server 2005

[Rajesh Nair]

Hi,

I am trying to use the ns_db sp_* API in AOLServer 4.0 with nsfreetds driver
  against MS SQL Server 2005.
I can tell that the driver are all set fine as I can execute Select SQLs
correctly from ADP

But I am unable to call a stored procedure passing in a paramater and
retrieveing the result from the stored procedure.

myproc is a simple stored procedure which accepts a varchar parameter and
insert the parameter in a table.

Here is the simple code snippet 

    <%
    if {[catch {
         #Select the database 
         set db [ns_db gethandle];
         set sql1 "use rajesh";
         
         #ns_db sp_start 
         set ret1 [ns_db sp_start $db "myproc"]; 
        
         #Set an input varchar parameter to the stored procedure
         #This code generates error
         set ret2 [ns_db sp_setparam $db "@param" varchar in "980"]; 
         ns_puts "ret2:$ret2<br>";
    
         #Execute the stored procedure   
         set ret3 [ns_db sp_exec $db ]; 
         ns_puts "<br>ret3:$ret3\n  ";
     } exception] != 0 } {
        global errorInfo;
        set savedInfo $errorInfo;
        ns_puts "<P> EXCEPTION GENERATED<BR> $exception<BR/>"
        ns_puts "<P> EXCEPTION GENERATED<BR> $savedInfo <BR/>"
    }


    %>


When I run this adp, I get the following error 

EXCEPTION GENRATED
Database operation "sp_setparam" failed


EXCEPTION GENRATED
Database operation "sp_setparam" failed while executing "ns_db sp_setparam
$db "@param" varchar in "980"" 


I had expected the ns_db sp_setparam to be the API used to set in and out
parameters for Stored procedures but it is not working as such for me
Is it that I have missed anything or my expectation is wrong?

Any alternate way of doing this? 
I did find that I was able to execute a stored procedure via 

ns_db exec $dbhandle "exec myproc @param='980'"

but in this case I am unable to get the return value from the stored procedure.

Also not sure if this is the right way to execute a stored procedure as this
is very much db specific 

--Rajesh Nair


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> 
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: 
field of your email blank.