Hi All,

I am using an ALTER TABLE statement with using variables. e.g.
"
DECLARE @constrName varchar(255)

ALTER TABLE Test1 DROP CONSTRAINT @constrName
"
it is giving me the following error:
"Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@constrName'."

Is it because of i am using variable in alter stmt.

please guide me on the same. its very urgent. your help will be highly appriciated.

Regards,
Deepak

use
   Exec "ALTER TABLE Test1 DROP CONSTRAINT " + @constrName

Regards
Ganesh

Sorry
Exec 'ALTER TABLE Test1 DROP CONSTRAINT ' + @constrName

double quotes typo

Regards
Ganesh

Its not working. It is giving same error even i am hardcoding the values:
I wrote:
------------------------------------------------------------------------------------------------
Exec 'ALTER TABLE Test1 DROP CONSTRAINT DF_Test1_Val4'

Error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ALTER TABLE Test1 DROP CONSTRAINT DF_Test1_Val4'.
------------------------------------------------------------------------------------------------

I am trying to run the query using query analyzer. Later i will use it with the SQL Task in DTS Package.

You cannot use a variable name directly as a table name. You must use Dynamic SQL, like this: This example is for a trigger but can be used for your particular situation.

DECLARE @Table_Name varchar(70)
Declare @SQL Varchar(500)

DECLARE curTables CURSOR FOR
select name from sysobjects where type = 'u'

OPEN curTables

FETCH NEXT FROM curTables into @Table_Name
WHILE (@@FETCH_STATUS = 0)
  begin
    Set @SQL = 'Alter table ' + @Table_Name + ' Disable trigger all'
    EXEC (@SQL)
    FETCH NEXT FROM curTables into @Table_Name
  end
CLOSE curTables
DEALLOCATE curTables


Jaime E. Maccou
Applications Analyst