variable inside execute sql call

  • hello

    i try within an exec-commands to recall a previously filled variable.

    usually i can merge them with '+' (concat), within the exec-commands this doesn't work?

    any idea?

    thx and greets

    DECLARE @BackupDrive NVARCHAR(5)

    SET @BackupDrive = (select SUBSTRING(physical_name,1,3) from sys.database_files where state_desc = 'online' and type = 0)

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA/Backup - All Databases Full',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'EXECUTE dbo.DatabaseBackup

    @Databases = ''ALL_DATABASES,-%mdw%'',

    @Directory = ' + @BackupDrive + 'Backup\Backups'',

    @BackupType = ''FULL'',

    ...

  • Well, you certainly need to lose the first and last quotation marks in the @Directory line. Even then, you might still not be allowed to concatenate in place, so you could do something like this:

    SELECT

    @BackupDrive = SUBSTRING(physical_name,1,3) + 'Backup\Backups'

    FROM

    sys.database_files

    WHERE

    state_desc = 'ONLINE'

    AND

    type = 0

    By the way, it's not a good idea to back up to the same drive your database files are on. Performance will suffer, availability will suffer if your backup uses all the space on the disk, and recoverability will suffer if you lose the disk for any reason.

    John

    Edit: I see what you're doing now - @Backup is a parameter of the proc you're calling in the @command part. This shows the importance of formatting you code for readability - if you indent that part then it's more obvious what you're doing. And using the code on the left hand side of your screen will format code in a similar way to how it appears in SSMS.

    I think you still need to build the @command string in advance rather than attempting to concatenate in place. And, of course, what I said about using the same drive still applies.

  • first, thank you very much for the quick response.

    This is only the drive-name in a san-storage, among which are several mount points. so, I would like to save somewhere else, but in a san-storage this is obosolet 😉

    to mask the code I missed, I'll try again and yes, that is correct, I want to appeal to a variable within the @command.

    if you tell me that this is not possible, then I'll have to rebuild it...until now it has not been able to address the variable.

    DECLARE @BackupDrive NVARCHAR(5)

    SET @BackupDrive = (select SUBSTRING(physical_name,1,3) from sys.database_files where state_desc = 'online' and type = 0)

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA/Backup - All Databases Full',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'EXECUTE dbo.DatabaseBackup

    @Databases = ''ALL_DATABASES,-%mdw%'',

    @Directory = ' + @BackupDrive + 'Backup\Backups'',

    @BackupType = ''FULL'',

  • I can't find it documented anywhere, and I don't know whether there are any exceptions, but it looks like you need to build your parameter value in advance. Probably safer to do it that way than risk getting an error message when you're not expecting it.

    John

  • thx John,

    tinkering with something laborious I could finally pass the required string...the ' can bring a lot of confusion 🙂

    DECLARE @BackupDrive NVARCHAR(100)

    DECLARE @MergeComm NVARCHAR(500)

    SET @BackupDrive = (select +'''' + SUBSTRING(physical_name,1,3) + 'Backup\Backups''' + ',' from sys.database_files where state_desc = 'online' and type = 0)

    PRINT @BackupDrive

    SET @MergeComm = N'EXECUTE dbo.DatabaseBackup'

    SET @MergeComm= @MergeComm + N' @Databases = ''ALL_DATABASES,-%mdw%'','

    SET @MergeComm= @MergeComm + N' @Directory = ' + @BackupDrive

    SET @MergeComm= @MergeComm + N' @BackupType = ''FULL'', @LogToTable = ''Y'', @Verify = ''Y'', @Compress = ''Y'', @CheckSum = ''Y'', @CleanupTime = 33'

    PRINT @MergeComm

    EXECUTE dbo.DatabaseBackup @Databases = 'ALL_DATABASES,-%mdw%', @Directory = 'C:\Backup\Backups', @BackupType = 'FULL', @LogToTable = 'Y', @verify = 'Y', @Compress = 'Y', @CheckSum = 'Y', @CleanupTime = 33

  • Beware that if you have more than one data file in your database, your query will fail.

    John

Viewing 6 posts - 1 through 6 (of 6 total)

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