Link to home
Start Free TrialLog in
Avatar of Philip van Gass
Philip van GassFlag for South Africa

asked on

A question about syntax

Please view the following T-SQL code and tell what I am doing wrong PLEASE.

DECLARE @dbname varchar(100)
         set  @dbName = 'TSQL2012'
         DECLARE  @backupTypeToRun CHAR(1)
       DECLARE @sqlCommand NVARCHAR(1000)
       DECLARE @dateTime NVARCHAR(20)
         DECLARE @BackUpDirectory varchar(100)
         SET @BackUpDirectory = 'C:\TNT Backup'
       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','-') +'-' +
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
       
       DECLARE @databaseFileName NVARCHAR(200)
       
               SET @sqlCommand = 'BACKUP DATABASE ' + @dbName +  ' TO DISK = ' + '''@BackUpDirectory'''
               SELECT @sqlCommand as command

This results in the following:   BACKUP DATABASE TSQL2012 TO DISK = '@BackUpDirectory'

But I want it to show the value contained by the variable @BackUpDirectory not the name of the variable !
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Try this:
SET @sqlCommand = 'BACKUP DATABASE ' + @dbName +  ' TO DISK = ' + '''' + @BackUpDirectory + ''''

Open in new window

Avatar of Philip van Gass

ASKER

Hi Vitor. That gives a syntax error.

If I do the following the SELECT statement shows as NULL ???

 SET @sqlCommand = 'BACKUP DATABASE ' + @dbName +
               ' TO DISK = ''' + @BackUpDirectory + '\' + @databaseFileName + '_Full_' + @dateTime + '.BAK'''
        SELECT @sqlCommand
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.