I am a 3rd party developer working with a legacy MSSQL instance. I cannot change global settings on the account. However, it sometimes occurs that my queries conflict with other transactions & mine block theirs. Is it possible to set a time limit on my own queries such that if a response is not returned in 30 seconds, it just kills my query? These are all called as USPs.
If you are running these manually - then you can cancel the execution...if they are executed through SQL agent you can manually cancel the agent job.
But...short of issuing a kill statement on the spid there is no way to automatically stop a query.
The better solution would be to identify why your code is taking so long and fix that problem - next is to identify why you are blocking other processes and work with the DBA to figure out how to improve your code (add indexes if needed), and the last resort would be to set the transaction isolation to read uncommitted.
I did see something previously about LOCK_TIMEOUT being set on a query basis, however I was never able to get it to work (on MSSQL 2012) Is it possible to set a timeout for a SQL query on Microsoft SQL Server? - Stack Overflow.
But if it's not possible, that's what it is.
why are there locks? fix that and you don't need to do one offs. no need to do bandaid fixes. why is it timing out, what are other processes that are locking things up. heal the whole thing
What are the queries you are writing look like? are you using set based queries or are you chunking things to limit locks. Are you writing reports and joining many tables on an OLTP system?