Solved

Transact-SQL: Echoing stored procedure status during run rather than getting all print statements at end.

Posted on 2003-11-03
6
380 Views
Last Modified: 2008-03-10
Hello,

I have a stored procedure that executes several other stored procedures, some of them with fairly lengthy run-times.  I would like update statements to print between the sub stored procedures.  I have print statements between them but what happens is that all of my 'update the user' statements, both from within the sub stored procedures and the main procedure, are printed when the main stored procedure is completely done running.

I currently run this through SQL Query Analyzer but am more than willing to switch to something else if that is what it takes to get run time updates.

Thanks

CODE from sp:

print "Renaming the old table..."
execute sp_Rename CurrentMonth, @previousMonthName
print "Done."
print "Creating the CDT . . . "
execute Create_CDT_By_Date
print "Done. "
execute Truncate_Analyze_Log
print "Updating the CDT . . ."
execute Update_CDT
print "Done."
execute Truncate_Analyze_Log
print "Indexing the CDT . . ."
execute Index_CDT
print "Done.

0
Comment
Question by:doryllis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Assisted Solution

by:CJ_S
CJ_S earned 45 total points
ID: 9671863
Try placing GO statements in between. Without the GO statement it will be ONE batch

print "Renaming the old table..."
execute sp_Rename CurrentMonth, @previousMonthName
print "Done."GO
print "Creating the CDT . . . "
GO
execute Create_CDT_By_Date
print "Done. "
GO
execute Truncate_Analyze_Log
print "Updating the CDT . . ."
GO
execute Update_CDT
print "Done."
GO
execute Truncate_Analyze_Log
print "Indexing the CDT . . ."
GO
execute Index_CDT
print "Done.
GO
0
 
LVL 3

Assisted Solution

by:SQLMaster
SQLMaster earned 40 total points
ID: 9671893
I think you just can't do this. A probably better way will be to maintain a log table which logs the procedure name and its result.Instead of printing the result, you can insert the row in that table and then query it for your stored procedures results

Cheers
0
 
LVL 2

Author Comment

by:doryllis
ID: 9671924
Sorry, I forgot to mention that I have some variables from the top level that I need to see used in lower level.  When you place GO statements in the whole thing then you lose your variables because they are only for the batch they were declared for.

However, looking at it I see that I only have 2 procedures (short and quick ones) at the very top of this whole sequence that actually use those variables, so for this one I think that answer will now work.

I would like to be able to use variables consistently thorugh out and not have to gang variables at the top order to get feedback like that.  
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 6

Accepted Solution

by:
ChrisKing earned 40 total points
ID: 9672116
this is actually a "feature" of the network transport layer.

it batches up small results sets and wont send them to the client until the buffer is full or the procedure finishes running as it considers the more efficient on the network (which it is) but that does not help you.

sorry, no idea how to override the settings :(
0
 
LVL 2

Author Comment

by:doryllis
ID: 9672248
Thanks...

I was afraid I was asking an impossibility/improbablility so the answers given didn't surprise me. I split the points because all answers were correct ChrisKing gave me the technicality of it And SQLMaster and CJ_S both gave good darn answers.  CJ_S I will be able to use [sorta] on this project but the ultimate answer is. . . it can't be done the way I would like.

Thanks again for your answers.
0
 
LVL 2

Author Comment

by:doryllis
ID: 11181166
I found a better answer.

If you change your settings in Query analyzer to, "Results in Text" then it will display results of queries as they return rather than waiting for all of them to complete to display them in a grid.  
This setting can be reached by right clicking in the query area of your Query Analyzer Window and selecting "Results in Text" to return to your normal grid view right click and say "Results in Grid".

This setting can also be reached through the Query menu bar item.

Thanks,
Dory
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question