Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select not working


select not working

Author
Message
Krishna1
Krishna1
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 556
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
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
Krishna1
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 556
create table remarks ( remark varchar(1000)

create procedure P1 as
begin

while loop
begin
.....

insert into remarks ()

commit

end

end
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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
OTF
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 4128
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
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10264 Visits: 9571
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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24200 Visits: 37964
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Krishna1
Krishna1
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 556
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
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10264 Visits: 9571
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8972 Visits: 19022
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search