TSQL - Using variable as DBName for ALTER Command

• Lars Kjeldsen

  SSC Enthusiast

  Points: 150

  More actions

  May 13, 2004 at 2:51 am

  #107923

  Hello,

  Trying to tun the following in query analyzer.....

  DECLARE @xx AS nvarChar(128)

  SET @xx = (SELECT DB_NAME()) --Pick up current dbname

  ALTER DATABASE @xx

  SET RECURSIVE_TRIGGERS ON

  GO

  But get's error....

  Server: Msg 170, Level 15, State 1, Line 3

  Line 3: Incorrect syntax near '@xx'.

  Server: Msg 195, Level 15, State 1, Line 5

  'RECURSIVE_TRIGGERS' is not a recognized option.

  As if is does not recoqnize @xx as valid name. The variable is set correctly (try "SELECT @xx after setting it)

  Any idea's ????

  Thanks,

  Lars

   

• wz700

  SSCertifiable

  Points: 7133

  More actions

  May 13, 2004 at 2:58 am

  #506255

  DECLARE @xx AS nvarChar(128)

  SET @xx = (SELECT DB_NAME()) --Pick up current dbname

  exec ('ALTER DATABASE '+@xx + 'SET RECURSIVE_TRIGGERS ON')

  GO

• Lars Kjeldsen

  SSC Enthusiast

  Points: 150

  More actions

  May 13, 2004 at 3:04 am

  #506256

  Tried it.....

  DECLARE @xx AS nvarChar(128)

  SET @xx = (SELECT DB_NAME()) --Pick up current dbname

  exec ('ALTER DATABASE '+@xx+'SET RECURSIVE_TRIGGERS ON')

  GO

  Gives

  Server: Msg 156, Level 15, State 1, Line 1

  Incorrect syntax near the keyword 'ON'.

  Also tried (adding space before SET)

  DECLARE @xx AS nvarChar(128)

  SET @xx = (SELECT DB_NAME()) --Pick up current dbname

  exec ('ALTER DATABASE '+@xx+' SET RECURSIVE_TRIGGERS ON')

  GO

  Gives

  Server: Msg 156, Level 15, State 1, Line 1

  Incorrect syntax near the keyword 'SET'.

  Server: Msg 195, Level 15, State 1, Line 1

  'RECURSIVE_TRIGGERS' is not a recognized optio

  ??? What is going on ???

  Thanks Lars

• Lars Kjeldsen

  SSC Enthusiast

  Points: 150

  More actions

  May 13, 2004 at 3:13 am

  #506259

  Found solution.........

  DECLARE @xx AS nvarChar(128)

  SET @xx = (SELECT DB_NAME()) --Pick up current dbname

  exec ('ALTER DATABASE "'+@xx+'" SET RECURSIVE_TRIGGERS ON')

  GO

   

  The databasename contains blanks - so added " to the string,

  Thanks for the EXEC help

  Lars Kjeldsen

• Robert A. Shannon III

  SSC Veteran

  Points: 284

  More actions

  May 17, 2004 at 8:13 am

  #506658

  Lars:

  Why not use the built-in SPs to do this?  We do this sort of thing all the time, but always use the system SPs.

   

  DECLARE @vchSQLString VARCHAR(1000)

  DECLARE @vchDBName SYSNAME

  DECLARE @vchOption VARCHAR(100)

  DECLARE @vchOptionValue VARCHAR(50)

   

  SET @vchDBName = DB_NAME()

  SET @vchOption = 'recursive triggers'

  SET @vchOptionValue = 'TRUE'

  SET @vchSQLString = 'EXEC dbo.sp_dboption @dbname = ' + '''' + @vchDBName  + ''',' + '@optname = ''' + @vchOption + ''', @optvalue = ''' + @vchOptionValue + ''''

   

  EXEC (@vchSQLString)

  Sincerely,

  Allen Shannon