Query issue between SQL 2000 and SQL 2005

Question

Post reply

Query issue between SQL 2000 and SQL 2005

Amu

SSCommitted

Points: 1691
More actions
January 19, 2010 at 8:15 am

#138127

I have this dynamic query to generate DBCC CHECKDB for all dbs on any given instance which I normally run from query analyzer and take the output and copy to another query window and run.
It was working all along fine with SQL 2000 but SQL 2005 it keeps saying syntax error.
Error in SQL 2005:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'GO'.
Query:
print '-- dbcc check all databases Except TMEPDB on ' + @@servername
print ' '
PRINT 'SET ARITHABORT ON'
PRINT 'SET QUOTED_IDENTIFIER ON'
SELECT 'DBCC CHECKDB (' + '''' + NAME + '''' + ')' + CHAR(13) + 'GO' + CHAR(13)
FROM MASTER..SYSDATABASES
where NAME <> 'tempdb'
ORDER BY 1
Ay help would be helpful?

Viewing 4 posts - 1 through 3 (of 3 total)

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

Just take out the GO in the query and you should be fine. I have checked it to work correctly without GO.

print '-- dbcc check all databases Except TMEPDB on ' + @@servername

print ' '

PRINT 'SET ARITHABORT ON'

PRINT 'SET QUOTED_IDENTIFIER ON'

SELECT 'DBCC CHECKDB (' + '''' + NAME + '''' + ')' + CHAR(13) + CHAR(13)

FROM MASTER..SYSDATABASES

where NAME <> 'tempdb'

ORDER BY 1

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Amu SSCommitted Points: 1691 More actions · Answer 2

Thanks it worked. But Why would not work with "Go" as it is batch seperater?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

It needs to be on a different line. After your DBCC CHECKDB statement completion.

MJ