doryllis
asked on
Transact-SQL: Echoing stored procedure status during run rather than getting all print statements at end.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks...
I was afraid I was asking an impossibility/improbablili ty 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.
I was afraid I was asking an impossibility/improbablili
Thanks again for your answers.
ASKER
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
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
ASKER
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.