Killing all queries on all databases

  • I'm trying to kill all queries on all user databases prior to starting an overnight batch. Running this code

    DECLARE @sql VARCHAR(MAX)

    SET @sql = ''

    SELECT @sql = @sql + 'USE master; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET MULTI_USER; ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

    PRINT @sql

    EXEC (@SQL)

    is giving errors (incorrect syntax near GO). However copying the printed sql and running that works. Can anyone suggest why this would be the case?

    Thanks

  • you are getting error because before statement

    FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

    you haven't use SELECT statement and columns names which you required in your output

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thanks but where would you put the SELECT in?

  • magwitch (8/30/2013)


    Thanks but where would you put the SELECT in?

    Before FROM keyword....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I thought thats where you meant, can you give an example? when i've added a SELECT name to just before the FROM it doesn't work.

    Thanks

  • kapil_kk (8/30/2013)


    you are getting error because before statement

    FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

    you haven't use SELECT statement and columns names which you required in your output

    I apologize for my comments........

    I didn't read your query properly..You don't have to put any SELECT keywork before FROM

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Yes, i didn't think that was right. So do you have any idea as to why the @sql will not execute but by cutting, pasting and executing the printed @sql it does run?

    Thanks

  • magwitch (8/30/2013)


    Yes, i didn't think that was right. So do you have any idea as to why the @sql will not execute but by cutting, pasting and executing the printed @sql it does run?

    Thanks

    Your query will generate PRINT statement when you Commented out

    EXEC(@sql)

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • You're getting errors because GO is not a T-SQL command. It's a batch-breaker, designates where the client tool breaks commands apart to send the batches to SQL Server.

    Take the GOs out of your dynamic SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail, works perfectly now.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply