how can i know execution stutus of a stored procedure

hi
i want to know the execution status of a stored procedure . That is i want to know whether the stored procedure was executed succesfully or not.If not i want to get the error messageIt depends on the tasks procedure is going to perform. One way is to use error handling mechanism provided by SQL server.|||you can check the value of @.@.ERROR at the end of your stored procedure and return it.....if there is any error in the stored proc it will be indicated in the value of @.@.ERROR|||you can check the value of @.@.ERROR at the end of your stored procedure and return it.....if there is any error in the stored proc it will be indicated in the value of @.@.ERROR

This is not quite true. If the sproc has multiple steps (for example a select, an insert, another select, and a delete) you will need to check the value of @.@.ERROR after each DML statement, because @.@.ERROR only contains the return value from the last operation.

I encourage developers to save the value of @.@.ERROR into a variable, and then inspect the value of that variable as needed.

How can I kill sleeping processes in SQL Server?

Dear,

Our ASP.NET scripts send SQL statements (as inline SQL or SP) to process the requested job. After the job execution, the process ID stays in the server and waits for next command with sleeping status.Since this process does not go away, next job adds another process and eventually, the server is overloaded with these processes and dies.

How can I kill this sleeping processes?

Regards,

Echo

Off the top of my head if you

EXECsp_who

it will give you the SPID's for all your processes. Armed with that you could then issue  
KILL 42
which will terminate SPID 42
So I would guess that you could set up a job in SQL Server to get a list of all offending SPID's, schedule it to run every hour for arguments sake, and then step through that list issuing the KILL command.
 

HTH|||

Please do take care when using KILL to kill SQL sessions.SPID<=50 are SQL system processes, so you'd better not kill such processes. Always confirm that the session is no longer useful (has been idle for a long time or invovled in blocking/deadlock) before you KILL it. For more information about the KILL command in T-SQL, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp

|||

Iori_Jay:

Please do take care when using KILL to kill SQL sessions

I very much agree with Iori_Jay, and really should have advised caution in my earlier post.

|||

For example, you can use such query to Kill SQL sessions which are sleeping and have been idle more than 1 hour:

DECLARE @.v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND status='sleeping'
AND DATEDIFF(mi,last_batch,GETDATE())>=60
AND spid<>@.@.spid

OPEN c_Users
FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS=0)
BEGIN
PRINT 'KILLing '+CONVERT(VARCHAR,@.v_spid)+'...'
EXEC('KILL'+@.v_spid)
FETCH NEXT FROM c_Users INTO @.v_spid
END

CLOSE c_Users
DEALLOCATE c_Users

|||

Dear all,

Thank U everybody for replying. But I do not want to run any process at SQL server. Can't I manage it from ADO.NET. Is it possible that after executing a process it will automatically die.

I think the problem may related to connection pooling. Can I stop connection pooling of ADO.NET programatically?

Regards,

Sultan

|||

Why do you want to stop connection pooling...Connection Pooling can improve performance for database connections. If you just want to prevent unexpected remaining connecitons, you can just set the connection timeout when you create the connection in your code.

You can also take a look at this article:

Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

How can I get Actual Operation Cost from Actual Execution Plan?

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

There is only Estimated Operation Cost, since the actual operation cost will change from time to time. You may use DBCC to rearange the page allocation, to see if the performance can be improved. However, the view tooks 30 sec. to run does not look good. I would check the view first, to see if there is any thing wrong.|||

Thank you for the response. Because the view is using in production site, we are not able to do tuning on it. We need to figure out why there are big different after deleted records and explain to client. Do you know any way to show us which step or operation spent more time than before. Thanks!!