Progress

I have some SP's I run once a month and each SP takes a few mins to run and when I batch em together in one shot, I hate sitting there waiting for them to finish
whats the easiest way to report back the status of the exec?

Ex:
Set NoCount ON
EXEC StatesUpdateZipTableUpdate 'AE'
EXEC StatesUpdateZipTableUpdate 'AK'
EXEC StatesUpdateZipTableUpdate 'AL'
EXEC StatesUpdateZipTableUpdate 'AP'
EXEC StatesUpdateZipTableUpdate 'AR'
EXEC StatesUpdateZipTableUpdate 'AZ'

How would I get it to report the results after each Exec?
In otherwords Id like to create a progress bar......

Just for Query Analyzer..... no bells and whistles

You can add RAISERROR messages WITH NOWAIT option after each EXEC statement to send a message immediately to the client. If you don't use NOWAIT option the message might still be in the server output buffer and it will not get flushed & sent to client before it is full. Apart from this, there is no way to get a progress bar or progress information for the execution. Ex:

Set NoCount ON
EXEC StatesUpdateZipTableUpdate 'AE'
raiserror('After exec #1...', 0, 1) with nowait

EXEC StatesUpdateZipTableUpdate 'AK'
raiserror('After exec #2...', 0, 1) with nowait

EXEC StatesUpdateZipTableUpdate 'AL'
raiserror('After exec #3...', 0, 1) with nowait

EXEC StatesUpdateZipTableUpdate 'AP'
raiserror('After exec #4...', 0, 1) with nowait

EXEC StatesUpdateZipTableUpdate 'AR'
raiserror('After exec #5...', 0, 1) with nowait

EXEC StatesUpdateZipTableUpdate 'AZ'

raiserror('After exec #6...', 0, 1) with nowait

|||

Thats what I needed, infact its what I did before but stuck it at the end of the SP itself.. sometimes It just takes a second set of eyes.....

Thanks!

|||Bummer, didnt work........ Still get all the results at one time|||That could just mean that the SP execution completes fast so the difference in the messages output is not noticeable.|||

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[StatesUpdateZipTableUpdate]

(

@.State Varchar(2)

)

AS

Set NoCount ON

Set Transaction Isolation Level read committed

Declare @.RID Int,

@.RowVal Int,

@.UpdatedRows Int,

@.X_Error Varchar(50)

Set @.UpdatedRows = 0

Select

RecordID = Identity(int,1,1),

Zip_ZipCode,

Zip_SignatureCode

Into #Temp

from tempstatezip

where State = @.State

exec ('Delete From tax...' + @.State + 'ZIP' )

While (Select Count(*) from #temp)>0

Begin

Set @.rowVal = (Select top 1 RecordID from #Temp)

set @.RID = (Select Max(RecordID) from #Temp)

exec ('

Insert INTO tax...' + @.State + 'ZIP

(RecordID,

Zip_ZipCode,

Zip_SignatureCode)

Select

RecordID + ' + @.RID + ',

Zip_ZipCode,

Zip_SignatureCode

From #Temp

Where ' + @.RowVal + ' = RecordID')

Set @.UpdatedRows = @.UpdatedRows + 1

Set @.X_Error = convert(Varchar(5), @.UpdatedRows) + ' Rows have been updated for ' + @.State

Delete From #Temp where @.RowVal = RecordID

END

raiserror (@.X_Error, 0, 1) with nowait

Set NoCount Off

DROP TABLE #Temp

Theres about 78 State I run with each one having an average of 5000 rows that get updated.Each state actually takes about 45 mins to run +/-

Its slow as the linked server is an Access Db......

And youre probably thinking why am I doing it this way? well its the only way I can get my proprietary software to take the update. Theres some unique Identifiers that get generated in the software program upon import of the access db....

Got any ideas on improving this?