select not working

  • Dear All

    In procedure i am using Remark table to capture all the steps executed (instead of print). From another session when I try to

    select * from Remark it does not show results. I need to view details from this table to monitor the progress of the stored procedure. In the procedure I have used commit statement after few inserts.

    What I need to do to view results?

    Regards

  • can you please post table structure and stored procedure ?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • create table remarks ( remark varchar(1000)

    create procedure P1 as

    begin

    while loop

    begin

    .....

    insert into remarks ()

    commit

    end

    end

  • With the code you provided it is still not very clear. There are some typo's in it and it is not clear if the "insert into Remarks ()" line is complete. When this line is complete you are not inserting anything in the table, hence the table will allways be empty.

    Could you check if the [Remarks] table is filled when the stored procedure is finished? If not: focus on the "insert into Remarks" statement because that's the point where it goes wrong.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Krishna1 (6/11/2013)


    create table remarks ( remark varchar(1000)

    create procedure P1 as

    begin

    while loop

    begin

    .....

    insert into remarks ()

    commit

    end

    end

    I think you need to post the code as you actually have it to get better help. Feel free to obfuscate object names etc.

    Seeing as you've actually got a While Loop in there, is this not just a case of there being an exclusive lock on the remarks table preventing you selecting from it?

    Just my shot in the dark.

  • Krishna1 (6/11/2013)


    create table remarks ( remark varchar(1000)

    The code above couldn't be the code you actually ran, since the statement is an error and the procedure isn't telling us anything. I think the actual code is necessary for anyone to have a shot at helping you here.

  • Krishna1 (6/11/2013)


    create table remarks ( remark varchar(1000)

    create procedure P1 as

    begin

    while loop

    begin

    .....

    insert into remarks ()

    commit

    end

    end

    Here is a shot in the dark based on the extremely vague code snippet above:

    You show a COMMIT statement so there is probably a BEGIN TRANSACTION statement near the beginning of the code inside the WHILE loop. As you are inserting data into the remarks table, this data is not visible to other processes until the COMMIT statement is executed. If you have multiple writes to remarks inside this loop, this why you aren't seeing anything from the other process when you run a select on the table.

    Now, if you really want help you should post the code for the entire procedure.

  • Hi Lynn

    What you have mentioned is exactly what is present in the procedure. So in such situation we can not view the rows from the Remarks table till the process gets over?

    Regards

    Krishna1

  • We don't see a BEGIN TRANSACTION anywhere, so we can't tell where it is in the procedure.

    When you begin a transaction, other sessions cannot query the table and retrieve those rows inserted but not yet committed. Once a COMMIT TRANSACTION is executed, the rows can be viewed by other sessions. So you should be able to view them from other SSMS windows while the procedure is running.

    Without the code, however, there's no real way to definitively answer your question. There may be other problems we can't see, such as the start of the transaction being inside an IF structure or something else we haven't even considered. This is why people can only take their best guess and can't really work on the problem without making a bunch of assumptions.

  • Krishna1 (6/10/2013)


    Dear All

    In procedure i am using Remark table to capture all the steps executed (instead of print). From another session when I try to

    select * from Remark it does not show results. I need to view details from this table to monitor the progress of the stored procedure. In the procedure I have used commit statement after few inserts.

    What I need to do to view results?

    Regards

    "it does not show results"

    How have you determined that the comments table contains rows?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Krishna1 (6/11/2013)


    Hi Lynn

    What you have mentioned is exactly what is present in the procedure. So in such situation we can not view the rows from the Remarks table till the process gets over?

    Regards

    Krishna1

    I'm just going to say yes here. The writes to the Remarks table are all part of the same transaction as all the other work being done inside the WHILE loop.

    Before saying more I would need to know why you would be needing to read this information while the process is still running. Is it just to have an idea of what it is doing or are you trying to do any processing based on what is written in the Remarks table during the processing.

Viewing 11 posts - 1 through 10 (of 10 total)

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