Incorrect syntax near 'GO'. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect syntax near 'GO'.

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-11-16 : 18:07:52

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-16 : 18:09:49

The bigger question is WHY ARE YOU DOING THIS!?

You should just use SIMPLE recovery model for these.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-16 : 19:37:48

wow. haven't seen that one for a while.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-16 : 19:46:24

Putting the larger issue aside for a moment...

"GO" is not a part of the SQL language. It is a batch separator that is really only understood by the MS Management Studio parser. When you attempt to sp_ExecuteSQL a "GO statement" the SQL parser (which only understands SQL) has no idea what "GO" is and so it throws a syntax error.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-11-17 : 08:56:21

Any idea how I can still get my code execution working ?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-11-17 : 09:23:12

Works fine by removing the 'GO'.

http://www.sqlservercentral.com/Forums/Topic1207186-338-1.aspx?Update=1

daniel.nospam
Starting Member

11 Posts

Posted - 2011-11-17 : 09:23:50

Get rid of the GOs and issue separate EXEC statements.