Re: SP - output parameter not coming back

Thanks Bob,

lots of very interesting reading in all the links that you sent. A lot
of the code I use is copy and paste so I will try and follow your
advice on OLEDB vs ODBC and the one about not using objects in session
variables, should be easy enough to change these. I also enabled SET
NOCOUNT ON again, I had tested it with both options.

I also added the return parameter to my command object (1st param
added)
CmdSP.Parameters.Append _
CmdSP.CreateParameter("RETURN_VALUE", adInteger, _
adParamReturnValue, 4)
and I thought that this would actually solve my problem. but still I do
not get anything useful back from @mycount.

Also I have tested the SP using SQL Query Analyzer and it does not seem
to set the output parameter. Looks like there is either something wrong
in my SP Syntax or some underlying setting of the Server (is there a
setting that disables output parameters - or maybe it has to do with
the passed back recordset?)

I do not want to close the recordset because I want to display the
number of results before the table of results. I also do not want to
copy to a Array. The client side cursor did not help.

If I do not find out about the output parameter I am considering
running 2 separate stored Procedures (1 to get the count of my query in
a separate resultset, the other for the actual search results) but I
know that this is a most unfortunate and expensive solution. At least
this is only a product catalogue so the info in it is not highly
volatile. However it might even be technically better than using a
client side cursor - what do you think?

tia again
Axel

.

Relevant Pages

  • Re: aout cursor staoed procedure and vb6 (ado)
    ... I correct stored procedure name of prefix ... cursor on runing row by row. ... > 2) The RETURN value is not an OUTPUT parameter. ... >> DECLARE curBsDetail CURSOR FOR ...
    (microsoft.public.data.ado)
  • Re: Stored Procedure, Output Value with SELECT Statement
    ... you are best off with the Set NoCount On in your procedure. ... your output parameter will only be available to your client code ... after you have read all of the select cursor on your client connection. ... load the recordset into an array or something so that you can get to ...
    (microsoft.public.data.ado)
  • Re: Passing a table or cursor into a stored procedure
    ... Cursor can be used as OUTPUT parameter only not as Input-Output. ... cursor will alow stored procedure to return a cursor as output. ... >> Hari Sharma ...
    (microsoft.public.sqlserver.programming)
  • Re: Passing a table or cursor into a stored procedure
    ... Cursor can be used as OUTPUT parameter only not as Input-Output. ... cursor will alow stored procedure to return a cursor as output. ... >> Hari Sharma ...
    (microsoft.public.sqlserver)
  • Re: metadata for Storedprocedure
    ... Some DBMSes stored procedures ... may say they return a result set (cursor) via an output parameter, ... Prev by Date: ...
    (comp.lang.java.databases)