Re: SP - output parameter not coming back

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Axel wrote:
> Just one more info = just managed to test with SQL Query Analyzer, I
> changed the test script to include output and I can now verify that
> the SP is working correctly, filling in the output parameter; this
> means the problem is reading it back. Am I missing a step between
>
> Set rs = CmdSP.Execute( ) ' RecordCount can not be used
>
> and
>
> lRecCount = CmdSP.Parameters("@mycount").Value

Yes, you're failing to retrieve the entire resultset, either by moving to
the last record or by closing the recordset. SQL Server will not send the
output parameter value UNTIL all the resultsets generated by your stored
procedure have been sent (if your procedure returns multiple resultsets, for
example, if you don't SET NOCOUNT OFF - you will need to use NextRecordset
to cycle through the resultsets until you've retrieved the last one. Only
then will SQL Server send the output parameter value)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: SP - output parameter not coming back
    ... > Also I have tested the SP using SQL Query Analyzer and it does not ... If it does not work in query analyzer, it has no chance of working in asp ... > If I do not find out about the output parameter I am considering ... You definitely need to work on the stored procedure so that you can see the ...
    (microsoft.public.inetserver.asp.db)
  • RE: open a text file using sp_oamethod
    ... sp_OAGetProperty Fails on Properties From Inherited Interface ... Source: ODSOLE Extended Procedure ... When no OUTPUT parameter is supplied, ... Microsoft has confirmed this to be a problem in SQL Server 7.0. ...
    (microsoft.public.sqlserver.programming)
  • Strange behavior with SQL text field and ADO Parameter
    ... with VB than SQL but I've posted to those newsgroups ... adParamOutput,, 0) ... Alter Procedure Foo_Insert ... When I run the code above the returned output parameter in VB is something ...
    (microsoft.public.sqlserver.programming)
  • Re: Relational-to-OOP Tax
    ... hiding predicate logic and set theory behind a network graph. ... result should be converted from resultsets to objects. ... You can hide the sql through stored procs (or heck even a view if that's ... from hiding the schema or hiding the sql. ...
    (comp.object)
  • Re: Relational-to-OOP Tax
    ... hiding predicate logic and set theory behind a network graph. ... result should be converted from resultsets to objects. ... You can hide the sql through stored procs (or heck even a view if that's ... is why they can't allow SQL statements in the "business layer". ...
    (comp.object)