December 14, 2012 at 6:07 pm
I would like to set a variable that is a sql statement, but the syntax about the quotes is so confusing to me, I cannot make it right:
What I have is this:
SET @sqlcmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''H:\Preupgrade\ Backups\'+@dbname+'_'+@filedate +' WITH NOFORMAT, NOINIT,' +
' NAME = '''+@dbname+@filedate+''', SKIP, REWIND, NOUNLOAD, STATS = 10'
then EXEC sp_executesql @sqlcmd
How can I use single quotes in statement like above?
Thanks
December 14, 2012 at 9:05 pm
This is what you are trying to accomplish:
declare @dbname varchar(128) = 'TestDB',
@filedate varchar(64) = '20121214210000',
@sqlcmd varchar(max);
SET @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ''H:\Preupgrade\ Backups\' +
@dbname + '_' + @filedate + '''' + ' WITH NOFORMAT, NOINIT, ' +
'NAME = ' + '''' + @dbname + @filedate + '''' + ', SKIP, REWIND, NOUNLOAD, STATS = 10';
print @sqlcmd;
December 14, 2012 at 9:52 pm
If the no. of quotes are still confusing for you in the solution provided by Lynn......then you can also use the CHAR() function in sql server to apply quotes by using the ASCII value of a single quote(39) as shown below :
declare @dbname varchar(128) = 'TestDB',
@filedate varchar(64) = '20121214210000',
@sqlcmd varchar(max);
SET @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ' + CHAR(39) + 'H:\Preupgrade\
Backups\' + @dbname + '_' + @filedate + CHAR(39) + ' WITH NOFORMAT, NOINIT, ' +
'NAME = ' + CHAR(39) + @dbname + @filedate + CHAR(39) + ', SKIP, REWIND, NOUNLOAD,
STATS = 10';
print @sqlcmd;
December 15, 2012 at 12:04 pm
Thank you both for this, this makes much clearer to understand!
December 15, 2012 at 1:48 pm
Since this is a backup you are trying to run, here is another option:
declare @dbname varchar(128),
@filedate varchar(64);
declare @diskfile varchar(256) = 'H:\Preupgrade\Backups\' + @dbname + '_' + @filedate;
declare @filename varchar(256) = @dbname + @filedate;
declare @SQLCmd varchar(max) =
'BACKUP DATABASE @dbname
TO DISK = @diskfile
WITH NOFORMAT,
NAME = @filename,
SKIP,
REWIND,
NOUNLOAD,
STATS = 10;';
declare @params @varchar(max) = '@dbname varchar(128), @diskfile varchar(64), @filename varchar(256)';
exec sp_executesql
@SQLCmd,
@dbname = @dbname,
@diskfile = @diskfile,
@filename = @filename;
December 17, 2012 at 12:56 pm
Thanks, that is helpful!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply