Yes thats what we use "CommandTimeout".

For most of the SP's it works like a charm.

But as i mentioned in my earlier posts there are some exceptions - when SP starts...

I have tuned the SP and now it takes only 7 seconds to complete.

But guys i still want to know

How can I stop or limit an SP execution, if it...

Yes, the SP requires tuning and I will do that.

But in general i would like to know

Is there a way to set a limit for SP's Execution time?

Is there...

The SP is used in the application for a certain report.

I haven't used Query Governor before, Can you provide more details?

If you are talking about the option "query_governor_cost_limit" then i...

It's a SunDay!!

1) select New trace with <blank> template

2) Goto "Events Selection" tab -> Expand node "Stored Procedures" -> Select event "SP:Completed"

3) Select the columns you need in the trace...

Have you used the query (posted in previous comments) to fetch deadlock victims?

Or

SQL Profiler Event "Deadlock Graph"

Or

Something else

I am using the query below to fetch this information

SELECT CAST(event_data.value('(event/data/value)[1]',

...

I can fetch the Object names.

But I want to determine the actual SQL statements SELECT/INSERT/DELETE/UPDATE that are involved in deadlock.

The objects point to SP's and they have many lines of...

Update the query to add hint as below

SELECT "PC0".pxObjClass AS "pxObjClass",

--"PC0".pzPVStream AS "PC0pzPVStream",

"PC0".pzInsKey as "pxInsHandle"

FROM pca_work "PC0" WITH (READUNCOMMITTED)

WHERE ( "PC0".BrokerOfficeName LIKE '%%' )

AND ( "PC0".BrokerOfficeLocation...