Incorrect Syntax error. But the generated query is good

Question

Incorrect Syntax error. But the generated query is good

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
April 6, 2015 at 9:45 am

#299257

DECLARE @sql NVARCHAR(1024)
DECLARE @DB VARCHAR(500)
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
--and status <> 0 --NO Option SET
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + ' ' + @DB + ';'+CHAR(13) + CHAR(10) + 'GO'+CHAR(13) + CHAR(10) +'SELECT DATABASEPROPERTYEX (''' +@DB + ''', ''STATUS'') AS ''DBStatus'''
EXEC (@sql)
PRINT @sql
FETCH NEXT FROM DatabaseCursor INTO @DB
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Error: **********************
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
USE AdventureWorks2012;
GO
SELECT DATABASEPROPERTYEX ('AdventureWorks2012', 'STATUS') AS 'DBStatus'
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
USE AdventureWorksDW2012;
GO
SELECT DATABASEPROPERTYEX ('AdventureWorksDW2012', 'STATUS') AS 'DBStatus'
Thanks.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You can't use GO inside a dynamic sql statement. GO isn't a T-SQL keyword, it's a batch generator interpreted by SSMS.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323546 More actions · Answer 2

also, use quotename around your db variable; sharepoint databases often get created with hyphens, and hyphens or spaces int he naem will break your script

SET @sql = 'USE ' + ' ' + quotename(@DB) + ';SELECT DATABASEPROPERTYEX (''' + @DB + ''', ''STATUS'') AS ''DBStatus'''

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Is this what you're after?

SELECT name, state_desc

FROM sys.databases

ORDER BY name;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Thanks Luis and Lowell, it worked.

Hi ED - I initially started with that, but later changed the plan and now I am after checking the Db's which are Online/Suspect , etc.

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

Hi Guys

I modified my approach oif reporting other than Online DBs as below:-

Please suggest if you have any advise.

DECLARE @DBSTATUS TABLE

(

DBNAME SYSNAME,

RECOVERY_MODEL SQL_VARIANT,

STATUS SQL_VARIANT

)

SET NOCOUNT ON

INSERT INTO @DBSTATUS

(DBNAME,

RECOVERY_MODEL,

STATUS)

SELECT NAME,

DATABASEPROPERTYEX(NAME, 'RECOVERY') AS 'RECOVERY MODEL',

DATABASEPROPERTYEX(NAME, 'STATUS') AS 'DB STATUS'

FROM MASTER.DBO.SYSDATABASES

ORDER BY 1

DECLARE @COUNT INT

SELECT @COUNT = COUNT(*)

FROM @DBSTATUS

WHERE STATUS <> 'ONLINE'

IF @COUNT > 0

PRINT '*** SQL INSTANCE HAS NON ONLINE DATABASES. RUN DBCC CHECKDB. ****'

SELECT DBNAME,

STATUS

FROM @DBSTATUS

WHERE STATUS <> 'ONLINE'

SET NOCOUNT OFF

Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I'd completely remove the table variable (unless you use it somewhere else). And I would use the sys.databases view.

SET NOCOUNT ON

SELECT NAME,

recovery_model_desc AS 'RECOVERY MODEL',

state_desc AS 'DB STATUS', *

FROM MASTER.sys.DATABASES

WHERE state <> 0

ORDER BY 1

IF @@ROWCOUNT > 0

PRINT '*** SQL INSTANCE HAS NON ONLINE DATABASES. RUN DBCC CHECKDB. ****'

SET NOCOUNT OFF

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Luis Cazares (4/6/2015)
I'd completely remove the table variable (unless you use it somewhere else). And I would use the sys.databases view.
SET NOCOUNT ON
SELECT NAME,
recovery_model_desc AS 'RECOVERY MODEL',
state_desc AS 'DB STATUS', *
FROM MASTER.sys.DATABASES
WHERE state <> 0
ORDER BY 1
IF @@ROWCOUNT > 0
PRINT '*** SQL INSTANCE HAS NON ONLINE DATABASES. RUN DBCC CHECKDB. ****'
SET NOCOUNT OFF

Agreed.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

I would argue with the premise here, not the query.

A non-online DB is not a call to run CheckDB. If a database is OFFLINE or RESTORING, there's nothing wrong with it. You should be running CheckDB regularly, not when you pick up a suspect database (which should be an incredibly rare event).

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 9

Dear Ed & Gila,

You both are right. Thanks.

I was asked to put up the msg. So I used.

I run dbcc daily in the envitonment through my own script

Cheers.

Thanks.