[DATE] [TIME] functions not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

[DATE] [TIME] functions not working

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2007-12-05 : 14:21:18

Hi,

We are using [DATE] [TIME] functions in SQL Server 2000 agent jobs and SQL Server use to translate it to current data and time functions but in
SS2005 it is not replacing the functions and we are getting filename as "test_DATE_TIME" whereas we expect "test_20071204_130000"
Do we have any new functions as replacement?

Thanks
--rubs

Following is the code we are using:
declare @name nvarchar(100)
declare @name1 nvarchar(100)
set @name1 = 'test_[DATE]_[TIME]'
set @name = 'c:\backup\' + @name1 + '.bak'
backup database test to disk = @name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 14:23:15

Your code does not work in SQL Server 2000 either. Here is what I get on a 2000 machine when I print out @name: c:\backup\test_[DATE]_[TIME].bak

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rubs_65
Posting Yak Master

144 Posts

Posted - 2007-12-05 : 14:26:04

It works as agent job but not from query analyzer. Did you tried from QA or as an job step?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-05 : 14:28:13

Aren't [DATE] and [TIME] being considered string literals here? You are most likely going to have to utilize executable code and concatonate the strings.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 14:29:26

quote:
Originally posted by tfountain

Aren't [DATE] and [TIME] being considered string literals here?

Yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 14:29:50

quote:
Originally posted by rubs_65

It works as agent job but not from query analyzer. Did you tried from QA or as an job step?

What type of job step are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rubs_65
Posting Yak Master

144 Posts

Posted - 2007-12-05 : 14:34:22

It is an T-SQL job and You can create the job with following code:

USE [msdb]
GO
/****** Object: Job [TEST_Daily1] Script Date: 12/05/2007 14:32:38 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [TEST_Daily1] Script Date: 12/05/2007 14:32:39 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'TEST_Daily1' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'TEST_Daily1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TEST_Daily1',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'backup',
@category_name=N'TEST_Daily1',
@owner_login_name=N'windows\hsingh', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [backup database] Script Date: 12/05/2007 14:32:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'backup database',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @name nvarchar(100)
declare @name1 nvarchar(100)

set @name1 = ''TEST_[DATE]_[TIME]''
set @name = ''c:\backup\'' + @name1 + ''.bak''

backup database TEST to disk = @name',
@database_name=N'TEST',
@output_file_name=N'c:\backup\error_Daily_TEST.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every day at 2:00 am',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20051028,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 14:55:27

I have verified that your job is working as you describe in 2000, however this appears to be a bug in SQL Server 2000 as your code should not work due to the literals. Perhaps someone else can explain why this code works in 2000 and is not a bug.

For 2005, you will have to build your string.

Here is what I use:

SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
SET @fileName = @path + @dbName + '\' + @dbName + '_' + @now + @extension

BACKUP DATABASE @dbName
TO DISK = @filename

Here's my backup script:
http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx
WITH INIT

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rubs_65
Posting Yak Master

144 Posts

Posted - 2007-12-06 : 10:44:43

Tara,

Thanks a lot for sharing the code.
It is not a bug in SS2000 but a SQL Agent tokens and we are using it for last few years and they are very helpful to get data and time appended to file names to get timestamp and unique names. Following articles covers them:
http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm
http://technet.microsoft.com/en-us/library/ms175575.aspx
In SS2005 [DATE] syntax is replaced by $(ESCAPE_NONE(DATE)).I am appending a sample code for reference.

Thanks
--Rubs

USE [msdb]
GO
/****** Object: Job [TEST_Daily1] Script Date: 12/06/2007 10:40:28 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [TEST_Daily1] Script Date: 12/06/2007 10:40:28 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'TEST_Daily1' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'TEST_Daily1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TEST_Daily1',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'backup',
@category_name=N'TEST_Daily1',
@owner_login_name=N'windows\hsingh', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [backup database] Script Date: 12/06/2007 10:40:29 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'backup database',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @name nvarchar(100)
declare @name1 nvarchar(100)

SET @name1= ''TEST_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME))''
set @name = ''c:\backup\'' + @name1 + ''.bak''

backup database TEST to disk = @name',
@database_name=N'TEST',
@output_file_name=N'c:\backup\error_Daily_TEST.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every day at 2:00 am',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20051028,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources