String manipulation with variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » String manipulation with variable

String manipulation with variable

Rate Topic

Display Mode

Topic Options

Author

Message

sqlquery-101401

Posted Saturday, November 24, 2012 6:17 PM

SSC 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

kogutg

Posted Saturday, November 24, 2012 7:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 5:24 AM
Points: 5, Visits: 97

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

sqlquery-101401

Posted Saturday, November 24, 2012 9:51 PM

SSC 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

sqlquery-101401

Posted Saturday, November 24, 2012 10:45 PM

SSC 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 »

Permissions