Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    47

    Question Unanswered: How to return current status from a stored procedure to my Vb.Net program?

    Hi,
    See if someone could help me with the following...

    I have a stored procedure which I call from my Asp/Vb prog.
    In that stored proc. I have written Insert,Update and many operations.I want my SP to return the status i.e Insert successfull etc. so that I can show it in a progress bar(or Label).I dont want to print msgs using PRINT in SP.Bcoz it prints after the SP is fully executed...

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure about VB.Net, but in the OLEDB days, you defined a command object for the procedure, and a parameter object to accept the return code.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    He's looking for a stream of information from the procky wock.
    I think the only way to do that is with multiple dataset returns but I'm not even certain that is possible.

    Me - I would time the sproc to get an average completion time, multiply by 1.5 and hard code my progress bar to that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The easiest answer is to break the procedure into multiple sub-procedures, and report progress as each part is completed. This does incurr some overhead, since multiple "round trips" to the server occur so it might not be a good solution over a very slow data like like a cellular connection.

    Transact SQL PRINT statements do return results asynchronously, but ADO.NET buffers and processes them synchronously. There is a way to disable that buffering, but I'm not sure how to do it.

    RAISERROR always returns results asynchronously. Severity levels of 1-9 are informational, and do not usually cause the procdure to abort. I have seen considerable variation in how this is processed, so you need to test this carefully in your environment.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan View Post
    ...RAISERROR always returns results asynchronously. Severity levels of 1-9 are informational, and do not usually cause the procdure to abort. I have seen considerable variation in how this is processed, so you need to test this carefully in your environment.
    RAISERROR can return both synch and asynch, depends if you do or do not include WITH NOWAIT, respectively.
    Code:
    declare @i int, @err int
    insert...
    select @i =@@rowcount, @err =@@error
    if @err != 0
       raiserror ('Error: %d', 0, 1, @err)with nowait
    else
       raiserror ('Inserted: %d', 0, 1, @i)with nowait
    
    Last edited by rdjabarov; 12-13-10 at 12:26. Reason: ...formatting...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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