August 30, 2013 at 5:20 am
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
August 30, 2013 at 5:33 am
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/
August 30, 2013 at 5:40 am
Thanks but where would you put the SELECT in?
August 30, 2013 at 5:46 am
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/
August 30, 2013 at 5:49 am
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
August 30, 2013 at 5:53 am
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/
August 30, 2013 at 6:25 am
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/
August 30, 2013 at 6:36 am
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
August 30, 2013 at 6:39 am
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
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