October 2, 2015 at 12:05 pm
Hi,
I'm using SQLCMD to export queries to flat files and I'm wanting to ensure long running queries will be stopped after a certain amount of time.
I found on MSDN that SQLCMD supports the '-t' parameter, which is supposed to timeout the query.
I'm running the command below with the -t switch, but it doesn't timeout after 5 seconds as I would expect. It will continue to run, until I manually kill it.
SQLCMD -S localhost -d adventureworks2014 -t 5 -q "SELECT * FROM Person.Person"
I don't see where the -t switch is deprecated. Any ideas? Am I doing something wrong?
Appreciate any help!
October 2, 2015 at 1:16 pm
I think you are waiting for the result to get displayed in your powershell window. the query itself probably finishes before 5 sec.
I run your example and it does receive results for longer than 5 sec. If i run the same query in SSMS, it takes 1 second.
try this instead.
sqlcmd -S localhost -d master -t 5 -q "waitfor delay '00:10'"
October 2, 2015 at 1:32 pm
Thank you for the reply.
When I try and create a bad query (very bad :-D) that takes longer than 5 seconds to run, it still does not stop after 5.
SQLCMD -S localhost -d AdventureWorks2014 -t 5 -q "SELECT * FROM Person.Person p1 INNER JOIN Person.Person p2 ON p1.PersonType = p2.PersonType"
October 2, 2015 at 1:46 pm
I still think it's due to the results being returned.
this should time out for you after five seconds when it clearly would run longer
SQLCMD -S localhost -d AdventureWorks2014 -t 5 -q "SELECT p1.FirstName into #temp FROM Person.Person p1 INNER JOIN Person.Person p2 ON p1.PersonType = p2.PersonType"
October 2, 2015 at 1:57 pm
You are correct! Thank you.
Do you know of a way to kill the connection, so that SQLCMD returns the 'Timeout expired' quicker?
This will eventually be called through a Stored Procedure and I need to kill long running queries.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy