CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
kbvishnu
kbvishnu

Reputation: 15630

How can I know the execution status of a stored procedure in MS SQL Server

I have a long stored procedure and how do I know it execution status? Something like 50% completed etc. I searched a lot and didn't get any satisfied result.So hope that it is not possible.I am using MS SQL Server 2008. IF it is not available may I know the reason why it is not available ?

Upvotes: 5

Views: 12642

Answers (4)

Ivan Golović
Ivan Golović

Reputation: 8832

If you have a .NET client application you can receive approximate progress messages sent by server by using following syntax in C#:

    using (SqlConnection conn = new SqlConnection(...))
    {
        conn.FireInfoMessageEventOnUserErrors = true;
        conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
        using (SqlCommand comm = new SqlCommand("dbo.sp1", conn) 
               { CommandType = CommandType.StoredProcedure })
        {
            conn.Open(); 
            comm.ExecuteNonQuery();
        }
    }

    static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
        // Process received message
    }

While messages on the server side can be sent as described by Janis in one of previous answers:

    RAISERROR('33%%..', 10, 0) WITH NOWAIT

Upvotes: 2

Taryn
Taryn

Reputation: 247640

There is no feature built in to SQL Server to check the status of a stored procedure but you can write something that will do something similar. In our case, we created a logging function that post a message after each process within a stored proc.

Let's say you have a stored proc that runs multiple queries:

SELECT *
FROM yourTable

UPDATE ...
SET ...

DELETE 
FROM ...

You can put in place after each query/process a step that will post data to a logging table:

SELECT *
FROM yourTable

-- log query
INSERT INTO LogTable (DateComplete, Status, TaskId)
VALUES (getdate(), 'Complete', 1)

UPDATE ...
SET ...

-- log query
INSERT INTO LogTable (DateComplete, Status, TaskId)
VALUES (getdate(), 'Complete', 2)

DELETE 
FROM ...

-- log query
INSERT INTO LogTable (DateComplete, Status, TaskId)
VALUES (getdate(), 'Complete', 3)

You can take this even further by using a TRY...CATCH block around your queries which you can then have different messages on whether or not the process was successful or failed.

Upvotes: 1

Jānis
Jānis

Reputation: 2266

You can use: 

RAISERROR ('Some comment, immediate', 0, 1) WITH NOWAIT

to print message immediately after this statement. (it does not generate error message).

Upvotes: 5

Widor
Widor

Reputation: 13275

No, it is not available at least in the form you describe. This is because it's very hard to know both what a Stored Procedure is going have to to do internally (what if it calls other stored procs?) and how long it will take, until it tries to do it.

Sure, you could examine the proc and see it does SELECT * FROM [TableName]

But then you'd need to know the size of the table, the data types being returned, the disk access speed, whether the query has been cached, etc. etc.

Then, what if another process locks a record unexpectedly and it takes longer than usual? You don't know how long that lock will be held - so how do you calculate a percentage of an unknown variable (time)?

The best you can do is put 'checkpoints' in your proc to either print messages or send emails or log in a table after each discrete step, e.g.

print getdate()
print 'Selecting from [Users]'

Select * from [Users]

print getdate()
print 'Selecting from [Clients]'

Select * from [Clients]

print getdate()
print 'Finished'

Upvotes: 3

Related Questions