I'm working on an SBS2003 box with SQL Server 2005 and ISA Server 2004. The ISA Server writes activity to a SQL Server log database, and each day the name of the log database changes to reflect the current date in the format of ISALOG_YYYYMMDD_WEB_000.dbo.WebProxyLog. These log databases are retained for 8 days and then automatically dropped.
There are certain records in the daily ISA logs that I want retain, and in that effort I created another SQL database on that server named instance to archive these records. Each morning I run a query which extracts the desired records from the previous day's log database and inserts them into the database I'm using for archiving. That means in my query I have to change the name of the database I'm selecting from to reflect yesterday's date in the format YYYYMMDD.
So in an effort to move closer to automating this process and adding the job to SQL Agent, I'm trying to figure out how to create the name of the daily log file database as a local variable.
This is what I have so far:
DECLARE @Date VARCHAR(8)
SET @Date = CAST(YEAR(GETDATE()-1) AS VARCHAR(4)) + REPLICATE('0', 2 - DATALENGTH(CAST(MONTH(GETDATE()-1) as VARCHAR))) + CAST(MONTH(GETDATE()-1) as VARCHAR)
+ REPLICATE('0', 2 - DATALENGTH(CAST(DAY(GETDATE()-1) as VARCHAR))) + CAST(DAY(GETDATE()-1) as VARCHAR)
DECLARE @Prefix VARCHAR(7)
SET @Prefix = 'ISALOG_'
DECLARE @Suffix VARCHAR(24)
SET @Suffix = '_WEB_000.dbo.WebProxyLog'
DECLARE @File VARCHAR(40)
SET @File = @Prefix + @Date + @Suffix
I'm very new at using local variables, so it doesn't surprise me that the error I'm getting when I run this is "must declare the variable '@File' ".
Any quick tips on where I'm going wrong would be much appreciated.