DATEADD Issue

I am attempting to automate a monthend snapshot of my data, and the
following code works when I run it from a query window in SQL 2005, but it
generates and error message when I schedule it as a job. The error I get
is...
Msg 1023, Level 15, State 1, Line 10
Invalid parameter 1 specified for dateadd.
I have tried the code below using "Month", "M", 'Month', 'M', 'mm', and more
as the parameters for DATEADD, but all return the same error from job
execution. I'm sure that I'm just missing something simple, but its driving
me crazy.
IF Day(GETDATE()) = 1
BEGIN
DECLARE
@.NewName VARCHAR(12),
@.Current VARCHAR(12),
@.DataFile VARCHAR(100),
@.LogFile VARCHAR(100),
@.ThisDate DATETIME
SELECT @.ThisDate = DATEADD("Month", -1,GETDATE())
SELECT @.NewName = 'BIDW_' + CONVERT(VARCHAR(4),Year(@.ThisDate)) + '_' +
RIGHT('0' + CONVERT(VARCHAR(2),Month(@.ThisDate)),2)
SELECT @.Current = 'BIDW_' + CONVERT(VARCHAR(4),Year(GETDATE())) + '_' +
RIGHT('0' + CONVERT(VARCHAR(2),Month(GETDATE())),2)
SELECT @.DataFile = 'D:\SQLServer2005\Databases\BIDW\' + @.Current +
'_Data.mdf'
SELECT @.LogFile = 'D:\SQLServer2005\Databases\BIDW\' + @.Current +
'_Log.ldf'
EXEC sp_renamedb 'BIDW_Monthend', @.NewName
USE BIDW
RESTORE FILELISTONLY
FROM BIDW_on_Domino1
RESTORE DATABASE BIDW_Monthend
FROM BIDW_on_Domino1
WITH RECOVERY,
MOVE 'BIDW' TO @.DataFile,
MOVE 'BIDW_log' TO @.LogFile
ENDWhenever you are stuck on a problem, just post the problem to everyone in a
newsgroup and within 2 minutes of posting the question, you will realize the
answer. Nevermind everyone, I figured it out. The first part of the DATEADD
parameter needs no quotes at all.
-Brian
"Brian VanDyke" <brianv@.community.nospam> wrote in message
news:%23wcLUNSaGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I am attempting to automate a monthend snapshot of my data, and the
>following code works when I run it from a query window in SQL 2005, but it
>generates and error message when I schedule it as a job. The error I get
>is...
> Msg 1023, Level 15, State 1, Line 10
> Invalid parameter 1 specified for dateadd.
> I have tried the code below using "Month", "M", 'Month', 'M', 'mm', and
> more as the parameters for DATEADD, but all return the same error from job
> execution. I'm sure that I'm just missing something simple, but its
> driving me crazy.
> IF Day(GETDATE()) = 1
> BEGIN
> DECLARE
> @.NewName VARCHAR(12),
> @.Current VARCHAR(12),
> @.DataFile VARCHAR(100),
> @.LogFile VARCHAR(100),
> @.ThisDate DATETIME
> SELECT @.ThisDate = DATEADD("Month", -1,GETDATE())
> SELECT @.NewName = 'BIDW_' + CONVERT(VARCHAR(4),Year(@.ThisDate)) + '_' +
> RIGHT('0' + CONVERT(VARCHAR(2),Month(@.ThisDate)),2)
> SELECT @.Current = 'BIDW_' + CONVERT(VARCHAR(4),Year(GETDATE())) + '_' +
> RIGHT('0' + CONVERT(VARCHAR(2),Month(GETDATE())),2)
> SELECT @.DataFile = 'D:\SQLServer2005\Databases\BIDW' + @.Current +
> '_Data.mdf'
> SELECT @.LogFile = 'D:\SQLServer2005\Databases\BIDW' + @.Current +
> '_Log.ldf'
> EXEC sp_renamedb 'BIDW_Monthend', @.NewName
> USE BIDW
> RESTORE FILELISTONLY
> FROM BIDW_on_Domino1
> RESTORE DATABASE BIDW_Monthend
> FROM BIDW_on_Domino1
> WITH RECOVERY,
> MOVE 'BIDW' TO @.DataFile,
> MOVE 'BIDW_log' TO @.LogFile
> END
>