Escape character issue inside SQL Agent job step

Question

I'm having an issue when trying to use the sp_msforeachdb undocumented procedure in an agent job step. Here is my script to create the job step:

EXEC sp_add_jobstep
    @job_name = N'Weekly Blob Backup',
    @step_name = N'Run Backup',
    @subsystem = N'TSQL',
    @command = N'EXEC sp_msforeachdb '' BACKUP DATABASE [?] TO URL = ''''http://urlgoeshere.blob.core.windows.net/subfolder/SIMSPROCESSOR1_'''' + ''''?'''' + ''''_'''' + CONVERT(VARCHAR(8), CAST(GETDATE() AS DATE), 112) + ''''.bak'''' WITH COMPRESSION, CREDENTIAL = ''''SQLBlobStorageCredential''''''',
    @retry_attempts = 0,
    @retry_interval = 0

When executing I get the following:

Incorrect syntax near '+'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I've been successful when re-writing this to backup a single database. Clearly something isn't right with the escaping of characters, but when I put the 'Exec sp_msforeachdb' statement inside a PRINT() and execute that, everything looks right.

Answer 1

Turns out BACKUP DATABASE doesn't like when you concatenate a string in the TO URL clause. This worked:

EXEC sp_add_jobstep
    @job_name = N'Weekly Blob Backup',
    @step_name = N'Run Backup',
    @subsystem = N'TSQL',
    @command = N'EXEC sp_msforeachdb ''DECLARE @url NVARCHAR(500) = N''''http://urlgoeshere.blob.core.windows.net/simsstorage/'''' + SUBSTRING(@@SERVERNAME, CHARINDEX(''''\'''', @@SERVERNAME, 1) +1, 100) + ''''_'''' + N''''?'''' + N''''_'''' + CONVERT(NVARCHAR(8), CAST(GETDATE() AS DATE), 112) + N''''.bak'''' BACKUP DATABASE [?] TO URL = @url WITH COMPRESSION, CREDENTIAL = ''''SQLBlobStorageCredential''''''',
    @retry_attempts = 0,
    @retry_interval = 0