Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using output parameters from Sybase/SQL Server SPs (97 sr2)

    We have an Access 97 system that has links to a Sybase database as well as it's own local tables. The Sybase database has now been brought up to speed so that the Access tables are no longer required.
    My job is to merge the Access data and the Sybase data.
    To avoid identity gaps the primary key in the Sybase table is generated in a stored proc which stores the last id in a table and returns it as an output parameter (@RETURN_ID). I want to use this parameter from an ADO recordset but have no idea how to reference it in code, any suggestions?

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    Ok, I changed to ADODB.Command used the command.parameters.append command.createparmeters milarcy with an adParamOutput for the direction, but still no result...

  4. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    I haven't tried this with Access, but it works quite well with ASP/ADO...<pre>set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "YourStoredProcedure"
    cmd.CommandType = acCmdStoredProc
    cmd.Parameters.append cmd.CreateParameter("return",adInteger,adParamRetu rnValue)
    '
    '....add more Input parameter if needed
    'Ex: cmd.Parameters.append cmd.CreateParameter("@MyValue",adVarChar,adParamIn put,50,strMyValue)
    '
    '
    cmd.Execute

    'Here's the important line - get the return parameter
    intResult = cmd.Parameters("return")

    'destroy the cmd object
    set cmd = nothing</pre>

    Hope this helps!

  5. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    Cheers Mark, it keeps returning 0 so I'm not sure how/whether the stored proc works properly (i'll have to check from the asp frontend). It has to be said that while trying to do it the nifty way I totally ignored the good old fashioned dlookup route, back to good old fashioned techniques for this. (It is a one off botch after all)

  6. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    In the Stored Procedure, are you setting a return value?
    (I assume you're using SQL Server)
    I usually use something like:

    IF @@ERROR <> 0
    BEGIN ROLLBACK TRAN <MyTransactionName>
    RETURN 30 --I usually return 30 for any error
    END
    ELSE
    COMMIT TRAN
    RETURN 1 --I usually return 1 to indicate success
    END

    Hope this helps

  7. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    The SQL is below, the people who wrote it are long gone. (The insert into TEMP_TABLE is my attempt to establish that it works.) Stepping through with CAST(our sql dev tool) puts values into TEMP_TABLE where as Access put NULLs, so something odd is happening. However, this stored proc is called by other stored procs and they do seem to work, as I have an alternative I'm happy :-).







    Create Proc USP_GEN_NEXT_ID (
    @TABLE_NAME VARCHAR(50) =NULL ,
    @RETURN_ID NUMERIC(18) =NULL output
    )
    As
    Begin
    BEGIN TRAN
    SELECT @RETURN_ID = TABLE_ID + 1
    FROM NEXT_ID
    WHERE TABLE_NAME = @TABLE_NAME

    UPDATE NEXT_ID
    SET TABLE_ID = @RETURN_ID
    WHERE TABLE_NAME = @TABLE_NAME

    INSERT INTO TEMP_TABLE (
    RET_VALUE,
    TABLE_NAME
    )
    VALUES(
    @RETURN_ID,
    @TABLE_NAME
    )

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRAN
    RETURN -1
    END
    ELSE
    BEGIN
    COMMIT TRAN
    RETURN 0
    END

    END

  8. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    Are you trying to get the @Return_ID value to return from the SP?

    If so, just replace "RETURN 0" with "RETURN @Return_ID"

    As it stands, it returns 0 for success and -1 for error. The change would return your @Return_ID value on success and still return -1 on error. Assuming your @Return_ID would never be -1 this will work fine with your current logic.

    I am aware of another way to send values back to the ADO Command object, but I've never taken time to try it. If you have a book that covers ADO, you might also consider that as an alternative.

    Hope this helps

  9. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    Yup, tried that soon after your first reply, Access gave me a message saying "can't return Null, will return 0 instead", but never mind DLookup is crude and effective in this case.

  10. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    I'm glad you have a solution that works.

    I'm curious, just for the sake of being curious, why the Return value wouldn't work. I'll bet it has something to do with data types. If the @Return_ID isn't integer, it will be ignored. Also, on the Access side, you might try declaring the variable that will hold the Return value to Variant (so it will accept nulls). Just a thought... (for the sake of futility)

  11. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    My fault, I experimented further, I read your email again, experimented further, read your email again...

    I'd foolishly assumed that as the parameter was set-up as Numeric(18) that I should be using adNumeric, change that to adInteger and it all works... :-)

  12. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using output parameters from Sybase/SQL Server SPs (97 sr2)

    Ahh - I'm glad to hear that works as well! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I don't work with SQL Server every day, so it often takes me several attempts to accomplish most things with it. The good news, however, is that I am always learning from my mistakes and often don't make the same mistake more than 3 or 4 times <img src=/S/wink.gif border=0 alt=wink width=15 height=15>. Maybe I'll have it figured out in the next 5 or 10 years! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •