November 16, 2011 at 4:10 pm
Why do I keep getting Incorrect syntax near 'GO'?
DECLARE @dbname varchar(255)
DECLARE @sql nvarchar(max)
DECLARE datanames_cursor CURSOR FAST_FORWARD
FOR
SELECT dbname= [NAME] FROM SYS.DATABASES WHERE [NAME] LIKE 'Project%'
ORDER BY [NAME]
FOR READ ONLY
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='USE [' + @dbname + ']' + CHAR(13) + CHAR(10)+
'GO' + CHAR(13) + CHAR(10) +
'BACKUP LOG [' + @dbname + '] with truncate_only' + CHAR(13) + CHAR(10)+
'GO' + CHAR(13) + CHAR(10)+
'DBCC SHRINKFILE(2)' + CHAR(13) + CHAR(10)+
'GO' + CHAR(13) + CHAR(10)
PRINT(@sql)
EXEC sp_executeSQL @sql
FETCH NEXT FROM datanames_cursor INTO @dbname
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor
November 16, 2011 at 4:42 pm
Try not executing the sql and ensure the tsql prints correctly. If it does then copy that into a new query window (the message results from the print) and execute that.
It looks like the Char(10) + char(13) is messing with you
Here is the script cleaned up a bit.
DECLARE @dbname varchar(255)
DECLARE @sql nvarchar(max)
DECLARE datanames_cursor CURSOR FAST_FORWARD
FOR
SELECT [name] FROM sys.databases --WHERE [name] LIKE 'Project%'
ORDER BY [name]
FOR READ ONLY
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='USE [' + @dbname + ']' + CHAR(13) + CHAR(10)+
'GO' + CHAR(13) + CHAR(10) +
'BACKUP LOG [' + @dbname + '] with truncate_only' + CHAR(13) + CHAR(10)+
'GO' + CHAR(13) + CHAR(10)+
'DBCC SHRINKFILE(2)' + CHAR(13) + CHAR(10)+
'GO' + CHAR(13) + CHAR(10)
PRINT(@sql)
--EXEC sp_executesql @sql
FETCH NEXT FROM datanames_cursor INTO @dbname
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 16, 2011 at 4:45 pm
i see that you have posted in SQL 2005 forum, but can you confirm that u are running this query in SQL 2005? From SQL 2008, BACKUP LOG WITH TRUNCATE_ONLY is discontinued ( Source: MSDN - Note under Transaction Log Truncation
November 17, 2011 at 1:28 am
Take the GO out. It's not a T-SQL statement, it's a management studio batch breaker.
p.s. Stop doing this to you logs. If you want the log in auto-truncate, set the DB to simple recovery and leave it alone.
Please read through this - Managing Transaction Logs[/url]
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
November 17, 2011 at 7:07 am
SQLRNNR : It works fine if I copy out the results of the execution and execute but if I uncomment 'EXEC sp_executesql @sql' and execute I get the following ...and the same repeats for all database's.
USE [Project]
GO
BACKUP LOG [Project] with truncate_only
GO
DBCC SHRINKFILE(2)
GO
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'GO'.
ColdCoffee : Im using SQL Server Mgt Studio 2005.
November 17, 2011 at 7:12 am
LOOKUP_BI-756009 (11/17/2011)
SQLRNNR : It works fine if I copy out the results of the execution and execute but if I uncomment 'EXEC sp_executesql @sql' and execute I get the following ...and the same repeats for all database's.USE [Project]
GO
BACKUP LOG [Project] with truncate_only
GO
DBCC SHRINKFILE(2)
GO
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'GO'.
ColdCoffee : Im using SQL Server Mgt Studio 2005.
I would prefer it that way. But as Gail recommended - lose the "Go". You can replace that with a ";"
Also, I would be EXTREMELY cautious about using this. If you need to shrink the logs - then you should probably look at Simple recovery (as Gail said) or find the cause of your log growing large.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 17, 2011 at 7:19 am
Removed the Go and It worked just fine 🙂
Thank You Gail & SQLRNNR.
I am aware as this should not be the right way to get things done, but this is a temp solution till we acquire more space on our DEV server.
We shall be getting rid of this soon.
November 17, 2011 at 7:24 am
Googlers =>
This solution can be viable on a test or dev server. NOT prod.
November 17, 2011 at 7:49 am
LOOKUP_BI-756009 (11/17/2011)
Removed the Go and It worked just fine 🙂Thank You Gail & SQLRNNR.
I am aware as this should not be the right way to get things done, but this is a temp solution till we acquire more space on our DEV server.
We shall be getting rid of this soon.
Good. Again, personally I feel so much more comfortable running the output of the print statement manually. I would get rid of that exec piece altogether.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 17, 2011 at 8:29 am
LOOKUP_BI-756009 (11/17/2011)
I am aware as this should not be the right way to get things done, but this is a temp solution till we acquire more space on our DEV server.
But that doesn't explain why you're truncating the log rather than switching the databases to simple recovery model.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply