SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select not working


select not working

Author
Message
Krishna1
Krishna1
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5188 Visits: 2767
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
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

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

create procedure P1 as
begin

while loop
begin
.....

insert into remarks ()

commit

end

end
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8326 Visits: 3718
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 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
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45955 Visits: 10844
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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91351 Visits: 38950
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
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 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
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45955 Visits: 10844
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
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39878 Visits: 20000
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