September 21, 2009 at 8:54 am
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
SELECT *
FROM @File
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.
September 21, 2009 at 8:59 am
You can't "select * from" a varchar variable but you can do something like this. The syntax might be incorrect. I can't test right now but you can look it up in BOL
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
DECLARE @sql VARCHAR(1000)
SET @sql = 'select * from ' + @file
PRINT @sql
sp_executesql (@sql)
September 21, 2009 at 11:29 am
Thanks for the tip, Matt. Its almost there...just have to figure out the "incorrect syntax near 'sp_executesql'" message. The good news is that if I remove that line, it definitely prints the correct sql command.
September 21, 2009 at 11:40 am
Sorry, it should look like this:
execute sp_executesql @sql
September 22, 2009 at 9:00 am
No worries, and thanks for the additional input.
I'm still doing something wrong, and researching the resolution via BOL and other sources, but the error I'm getting now in messages when running the T-SQL query is:
select * from ISALOG_20090921_WEB_000.dbo.WebProxyLog
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 13
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
September 22, 2009 at 9:12 am
I'm not sure if this is the cause of the error but with dynamic sql I always use
EXEC (@sql)
September 22, 2009 at 9:26 am
Yet another syntax problem I missed. Change the @sql datatype to NVARCHAR(1000).
I think that will fix your problem.
rjv_rnjn is correct also. I went down a more complicated path than needed for this problem. You can use the exec (@sql) line instead of execute sp_executesql @sql
sp_executesql is more for if you have to use parameter substitution.
September 22, 2009 at 9:30 am
Thanks, rjv. That was it.
September 22, 2009 at 9:33 am
Yep, that worked, Matt. I appreciate your patience helping me learn through this. 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply