variable inside execute sql call

• rootsman

  Grasshopper

  Points: 21

  More actions

  August 20, 2013 at 1:21 am

  #98827

  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'',

  ...

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 20, 2013 at 1:45 am

  #1642965

  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.

• rootsman

  Grasshopper

  Points: 21

  More actions

  August 20, 2013 at 2:36 am

  #1642972

  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'',

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 20, 2013 at 3:52 am

  #1642989

  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

• rootsman

  Grasshopper

  Points: 21

  More actions

  August 20, 2013 at 11:39 pm

  #1643299

  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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 21, 2013 at 1:06 am

  #1643331

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

  John