Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

single quote in sql statement Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 6:07 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 1,749, Visits: 3,154
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

Post #1396846
Posted Friday, December 14, 2012 9:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 23,009, Visits: 31,510
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1396863
Posted Friday, December 14, 2012 9:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 1,118, Visits: 1,582
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;



Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1396870
Posted Saturday, December 15, 2012 12:04 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 1,749, Visits: 3,154
Thank you both for this, this makes much clearer to understand!
Post #1396904
Posted Saturday, December 15, 2012 1:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 23,009, Visits: 31,510
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,
@Params,
@dbname = @dbname,
@diskfile = @diskfile,
@filename = @filename;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1396908
Posted Monday, December 17, 2012 12:56 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 1,749, Visits: 3,154
Thanks, that is helpful!
Post #1397393
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse