Post reply

ADO doesn''t get record set back

  • March 16, 2005 at 8:04 am

    #87818

    I have a problem with a stored procedure that recieves a xml and insert it's data into a table.

    This stored procedure returns a record set.

    For some reason the stored procedure works fine thourough the query analyzer but whe it's called from the application it WORKS but the record set is not returned to the application server.

    This is the code of the sp:

    ALTER          PROCEDURE SP_PKT_AddPermittedCIDFromXML

     @list Varchar (4000)  

    AS

    Declare @ERR_ID INT

    Declare @ERR_Description VARCHAR(300)

    declare @iXml int

    DECLARE @Parameters table

    (PTPC_PTO_ID int , PTPC_CID int , PTPC_UserId int)

    exec sp_xml_preparedocument @iXml OUTPUT, @list

    insert into @Parameters (A, B ,C)

    select * from openxml(@ixml, '/XML/START' , 1)

    with (A int , B int , C int)

    SELECT A ,B , C

    FROM @Parameters

    GO

    THE XML looks like that :

    <XML>

    <START A="18589" B="2" C="1"/> </XML>

    IF I change the location of the select command to before the openxml then the application gets the record set.

    Can any of you help me?

    Thank you

  • March 16, 2005 at 8:06 am

    #547271

    Add SET NOCOUNT ON to the start of the proc.

     

  • March 16, 2005 at 9:07 am

    #547295

    So simple - Thank you so much

  • March 17, 2005 at 1:45 am

    #547465

    To add an explanation : SQL Server only returns the last output to ADO. With NOCOUNT off, this is the 'records affected' message and not the recordset (which was the penultimate output and so 'lost').

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply