Local Variable Question

  • SparTodd

    SSChasing Mays

    Points: 600

    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.

  • matt6288

    SSCertifiable

    Points: 5058

    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)

  • SparTodd

    SSChasing Mays

    Points: 600

    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.

  • matt6288

    SSCertifiable

    Points: 5058

    Sorry, it should look like this:

    execute sp_executesql @sql

  • SparTodd

    SSChasing Mays

    Points: 600

    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'.

  • rjv_rnjn

    SSCrazy

    Points: 2963

    I'm not sure if this is the cause of the error but with dynamic sql I always use

    EXEC (@sql)

  • matt6288

    SSCertifiable

    Points: 5058

    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.

  • SparTodd

    SSChasing Mays

    Points: 600

    Thanks, rjv. That was it.

  • SparTodd

    SSChasing Mays

    Points: 600

    Yep, that worked, Matt. I appreciate your patience helping me learn through this. 😉

Viewing 9 posts - 1 through 9 (of 9 total)

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