SQLCMD -t (Query Timeout Switch) does not work

  • 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!

  • 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'"

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • 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"

  • 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"

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • 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