Incorrect Syntax Keyword 'With'

Question

Incorrect Syntax Keyword 'With'

SQLEE

Ten Centuries

Points: 1077
More actions
August 5, 2010 at 7:12 am

#224376

I'm stuck trying to find what is wrong with my code. It works for some databases and blow up for others with the error message
Incorrect syntax near the keyword 'Case'. [SQLSTATE 42000] (Error 156.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000]
Here is the code:
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 75
SET @Database = 'MyDBname'
SET @cmd = 'DECLARE TableCursor CURSOR FOR
SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try putting [] around the table name, in case there are spaces, prohibited characters or reserved words

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

Adiga One Orange Chip Points: 27284 More actions · Answer 2

I don't see the word CASE in the entire code. Am I missing something here?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Adiga (8/5/2010)
I don't see the word CASE in the entire code. Am I missing something here?

He's probably got a table called 'Case'

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

SQLEE Ten Centuries Points: 1077 More actions · Answer 4

Yep, there is a table with the name 'case'. I'm going with your suggestion to see what happens.

Thanks

SQLEE Ten Centuries Points: 1077 More actions · Answer 5

The script failed again with the same error. I'm not sure if I have done all needed to be done though. What I have changed was this line

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + [table_name] as tableName

Did I missed something else?

Thanks for your help

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yeah, you missed something. That's not the line throwing the error. This one is:

SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

You need to change that so that, once the string is built, the table name is wrapped in []

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

SQLEE Ten Centuries Points: 1077 More actions · Answer 7

I'm sorry I don't get it. Could you give some example or point me to where I can find out some more.

Thanks for you help

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 8

...

SET @cmd = 'DECLARE TableCursor CURSOR FOR

SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName

FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES

WHERE table_type = ''BASE TABLE'''

...

Scott Pletcher, SQL Server MVP 2008-2010

SQLEE Ten Centuries Points: 1077 More actions · Answer 9

The database name was not the issue becasue there aren't any spaces in the name and the name itself isn't a sql reserved keyword. We have some table in the database that are the issue I belive like it is been pointed out earlier in the thread.

Thanks for your attempt to help.

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 10

SET @cmd = 'DECLARE TableCursor CURSOR FOR

SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName

FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES

WHERE table_type = ''BASE TABLE'''

Scott Pletcher, SQL Server MVP 2008-2010

SQLEE Ten Centuries Points: 1077 More actions · Answer 11

Thanks Scott, that was the fix and appreciate your help.

drew.allen SSC Guru Points: 76989 More actions · Answer 12

When creating dynamic SQL, it's a good idea to use the Quotename() function to appropriately quote table names, field names, et al. Quotename will automatically handle escaping the quote characters if they are contained in the string, which helps guard against SQL injection.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA