Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have written a little ETL application that I wish to run a few lines of TSQL from. If i enter a simple query like "SELECT * FROM MyTable" everything is fine. All single line commands run as expected.

A multiline query like this is also fine:

DECLARE @TableName NVARCHAR(MAX) 
set @TableName = 'MyTable' 
EXECute ( 'DROP TABLE '+ @TableName )

Howevery when I try and run:

DECLARE @TableName NVARCHAR(MAX)
OPEN Tables
FETCH NEXT FROM Tables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC( 'DROP TABLE ' + @TableName   )
 FETCH NEXT FROM Tables INTO @TableName
END

I get a syntax error after TABLE in the EXEC() call. I have spent 6 hours trying to figure this out thinking perhaps I need to escape the single quote or something. I just cannot see the problem.

A set of fresh eyes would be appreciated.

share|improve this question
    
Can you try EXEC(' SELECT 1 FROM ' + @TableName ) instead of DROP? Dropping the table before fetching next into it just may be the cause of the syntax error. –  RMNVM Nov 23 '11 at 10:10

3 Answers 3

Have you printed out the value of @TableName? If the table name is not a valid identifier, you'll want to put square brackets around the name e.g., '[' + @Tablename + ']'). Also, is there possibly a foreign key that references the table you are trying to drop?

share|improve this answer
1  
Does indeed sound like this is the case, you can also use the QUOTENAME() function to place @TableName in square brackets. –  Ally Reilly Nov 22 '11 at 14:18
    
I am not sure where the PRINT prints. Instead I wrote the whole thing to a VARCHR field in another table and it looks fine. THe sqaure barckets are not supported in 2008r2 i read? The FK constraints have already been deleted. This is reported as a SYNTAX error. –  Mike Trader Nov 23 '11 at 0:48

Try stringing the dynamic SQL together before you execute it. I've seen stuff like this all the time.

DECLARE @TableName NVARCHAR(MAX), @sql NVARCHAR(MAX)
OPEN Tables
FETCH NEXT FROM Tables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sql = 'DROP TABLE ' + @TableNAme
 EXEC(@sql)
 FETCH NEXT FROM Tables INTO @TableName
END
share|improve this answer

I just saw it. You are trying to DROP a table variable; not a table. It is as if you were trying to DROP TABLE INT. There is no need to drop a table variable; it just goes out of scope like any other variable.

share|improve this answer
    
That's not a table variable, it's a variable with the name of a table as the value. –  mrdenny Dec 1 '11 at 0:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.