select not working

Question

select not working

Krishna1

Hall of Fame

Points: 3666
More actions
June 10, 2013 at 10:29 pm

#273638

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

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

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

can you please post table structure and stored procedure ?

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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 2

create table remarks ( remark varchar(1000)

create procedure P1 as

begin

while loop

begin

.....

insert into remarks ()

commit

end

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

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’! **

OTF SSCertifiable Points: 6104 More actions · Answer 4

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.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

Krishna1 Hall of Fame Points: 3666 More actions · Answer 7

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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.