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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply