Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

select not working Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 10:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
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
Post #1461859
Posted Tuesday, June 11, 2013 12:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
can you please post table structure and stored procedure ?


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1461877
Posted Tuesday, June 11, 2013 1:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
create table remarks ( remark varchar(1000)

create procedure P1 as
begin

while loop
begin
.....

insert into remarks ()

commit

end

end
Post #1461909
Posted Tuesday, June 11, 2013 2:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 2,309, Visits: 2,789
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’! **
Post #1461916
Posted Tuesday, June 11, 2013 4:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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.
Post #1461993
Posted Tuesday, June 11, 2013 5:57 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 3,998, Visits: 3,432
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
Post #1462016
Posted Tuesday, June 11, 2013 6:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 20,682, Visits: 32,281
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.



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)
Post #1462039
Posted Tuesday, June 11, 2013 10:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
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
Post #1462446
Posted Wednesday, June 12, 2013 5:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 3,998, Visits: 3,432
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
Post #1462567
Posted Wednesday, June 12, 2013 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
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
Post #1462571
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse