Error Executing the Convert Function. Please assist.

Question

Error Executing the Convert Function. Please assist.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
November 22, 2010 at 7:07 am

#388280

I am getting some weird error while running the below script. The error is typically coming, at the below line:
SELECT @fileDate = CONVERT(VARCHAR(200),GETDATE(),113)
but, I use '112' it is running fine.
=================ERROR Message===================
Msg 3201, Level 16, State 1, Line 11
Cannot open backup device 'D:\backup\AdventureWorks_22 Nov 2010 19:29:35:003.BAK'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
Msg 3013, Level 16, State 1, Line 11
BACKUP DATABASE is terminating abnormally.
================ **************** ===================
Use master
GO
declare @filename varchar(400)
declare @filedate varchar(300)
declare @path varchar(300)
declare @name varchar(100)
Set @path = 'D:\backup\'
set @name = 'AdventureWorks'
SELECT @fileDate = CONVERT(VARCHAR(200),GETDATE(),113)
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
Backup Database AdventureWorks
To Disk = @fileName
go
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

When you use style number 113, you get the date and time with spaces in it. When you use style 112, you don’t have spaces in the results. When you get spaces in the results, you get a file name with spaces in it, which is not aloud.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 2

also a valid file name cannot have colons in it('D:\backup\AdventureWorks_22 Nov 2010 19:29:35:003.BAK'), so you need to make sure the string you are building is valid for a filename.

i usually use one of these two to build a sting similar to what you were doing:

FormattedDate SQL

20101122-09:15:58:667 SELECT CONVERT(VARCHAR(35),@date,112) + '-' + CONVERT(VARCHAR(35),@date,114)

20101122-091558 SELECT CONVERT(VARCHAR(35),@date,112) + '-' + REPLACE(CONVERT(VARCHAR(35),@date,108),':','')

20101122-091558667 SELECT CONVERT(VARCHAR(35),@date,112) + '-' + REPLACE(CONVERT(VARCHAR(35),@date,114),':','')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Thanks a lot Lowell.. I am now using the below string...which is still fine.

[SELECT @fileDate = CONVERT(VARCHAR(300),GETDATE(),112) + '-' + REPLACE(CONVERT(VARCHAR(35),GETDATE(),114),':','')

]

Use master

GO

declare @filename varchar(400)

declare @filedate varchar(300)

declare @path varchar(300)

declare @name varchar(100)

--declare @date datetime

Set @path = 'D:\backup\'

set @name = 'AdventureWorks'

--set @date = GETDATE()

--SELECT @fileDate = CONVERT(VARCHAR(200),GETDATE(),112)

SELECT @fileDate = CONVERT(VARCHAR(300),GETDATE(),112) + '-' + REPLACE(CONVERT(VARCHAR(35),GETDATE(),114),':','')

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

Backup Database AdventureWorks

To Disk = @fileName

go

Thanks.