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

String manipulation with variable Expand / Collapse
Author
Message
Posted Saturday, November 24, 2012 6:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, Visits: 354
i am trying to use @datestamp variable in simple string , but not luck , i dont want to use another variable for full string as i am getting this kind of string input from other job, is there any way i can simple use the variable @datestamp rather using full statement in variable and then execute?

Declare @Datestamp as varchar(10)
SET @Datestamp=Convert(varchar(8),getdate(),112)
BACKUP DATABASE [ReportServer]
TO DISK = 'D:\MSSQL\BAK\ReportServer +'@Datestamp' +.BAK'
WITH FORMAT, COMPRESSION



Post #1388352
Posted Saturday, November 24, 2012 7:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:24 AM
Points: 5, Visits: 106
You can do this in this way.

Declare @Datestamp as varchar(10)
SET @Datestamp=Convert(varchar(8),getdate(),112)

EXEC('BACKUP DATABASE [ReportServer]
TO DISK = ''D:\MSSQL\BAK\ReportServer'+ @Datestamp+'.BAK''
WITH FORMAT, COMPRESSION')

Post #1388358
Posted Saturday, November 24, 2012 9:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, Visits: 354
Awesome , also while trying to do the same i am stuck in string manipulation for @datestamp and with below it is always giving two quotes ,any way to get around this to get single quotes to generate correct statement?

Declare @BACKUPCMD as varchar(5000)
,@BACKUPDIR1 as varchar(5000)
,@DB as varchar (500)
,@BACKUPFILEEXT as varchar(100)
,@WITHSWITCH as varchar(1000)

select @BACKUPDIR1='d:\mssql\bak'
,@DB='reportserver'
,@BACKUPFILEEXT='.bak'
,@WITHSWITCH='WITH FORMAT, COMPRESSION'

Select @BACKUPCMD='Declare @Datestamp as varchar(10)' + char(13)
Select @BACKUPCMD=@BACKUPCMD + 'SET @Datestamp=Convert(varchar(8),getdate(),112)'+ char(13)
SELECT @BACKUPCMD = @BACKUPCMD + 'Exec(''BACKUP DATABASE [' + @DB + ']' + CHAR(13)
+ 'TO DISK = '+'''' + QUOTENAME(@BACKUPDIR1 + '\' + @DB +'''+@Datestamp+'''+@BACKUPFILEEXT , '''') + CHAR(13)
+ @WITHSWITCH +''''+')'+ CHAR(13)



Post #1388363
Posted Saturday, November 24, 2012 10:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, Visits: 354
nevermind ,found the issue ,it was quotename causing issue , thanks again for the help.


Post #1388366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse