none
Use variable in XML table definition

    Question

  • I am trying to use a local variable with html.  Here is the code:

    BEGIN

     

    -- SET NOCOUNT ON added to prevent extra result sets from

     

    -- interfering with SELECT statements.

     

    SET NOCOUNT ON;

     

     

    DECLARE @tableHTML1 NVARCHAR(MAX) ;

     

    DECLARE @tableHTML2 NVARCHAR(MAX) ;

     

    DECLARE @tableHTML NVARCHAR(MAX) ;

     

    DECLARE @ServerName NVARCHAR(100) ;

     

    DECLARE @SQLStatement VARCHAR(MAX) ;

     

    DECLARE @TableName NVARCHAR(MAX) ;

     

    DECLARE @database varchar(MAX);

     

    SET @database = 'SQLTEST01.'

     

    SET @TableName = @database + 'msdb.dbo.backupset'

     

    print @TableName

     

     

    SET @SQLStatement = ' SELECT td = database_name, '''',

     

     

    + @TableName +

     

    ' WHERE backup_finish_date > (Getdate() - 1) and type != N''L''

     

     

     

    print @SQLStatement

     

     

    SET @tableHTML1 =

    N

    '<H1>Database Backup Report</H1>' +

    N

    '<H2>Date Range ' + convert(char,(Getdate() - 1)) + N' - ' + convert(char, Getdate()) + N'</H2>' +

    N

    '<table border="1">' +

    N

    '<tr><th>Database Name</th><th>Server</th>' +

    N

    '<th>Size</th><th>Backup Date </th>' +

     

    Cast (Cast (@SQLStatement AS VARCHAR(MAX)) as varchar(max) )

     

    +

    N

    '</table>' ;

     

     

     

    SET @tableHTML2 =

    N

    '<H2>Databases with a backup older than 24 hours or not at all</H2>' +

    N

    '<table border="1">' +

    N

    '<tr><th>Database Name</th><th>Backup Finish Date</th>' +

    N

    '<th>Backup Age in hours</th>' +

     

    CAST ((select td = database_name, '',

    td

    = finish_date, '',

    td

    = hours

     

     

    from

     

    ( SELECT database_name,

     

    MAX(msdb.dbo.backupset.backup_finish_date) AS finish_date,

     

    DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS Hours

     

     

    FROM msdb.dbo.backupset

     

     

    WHERE type = 'D'

     

    GROUP BY database_name

     

     

    HAVING (MAX(backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

     

     

    UNION

     

     

    --Databases without any backup history

     

    SELECT

    master

     

    .dbo.sysdatabases.NAME as database_name,

     

    '01/01/01' as finish_date,

    9999

    as hours

     

     

    FROM

    master

     

    .dbo.sysdatabases LEFT JOIN msdb.dbo.backupset

     

     

    ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name

     

     

    WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'

     

     

    )

     

     

    as a FOR XML PATH('tr'), TYPE

     

     

    ) AS NVARCHAR(MAX) ) +

    N

    '</table>';

     

    IF @tableHTML2 is null and @tableHTML1 is not null

     

    BEGIN

     

    set @tableHTML = @tableHTML1;

     

    END

     

    ELSE

     

    IF @tableHTML2 is not null and @tableHTML1 is null

     

     

    Begin

     

    set @tableHTML = @tableHTML2;

     

    END

     

    ELSE

     

    Begin

     

    set @tableHTML = @tableHTML1 + @tableHTML2;

     

    END

     

     

    set @ServerName = (select distinct name from msdb.sys.servers where is_linked = 0) + ' Backup Report';

     

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name

     

    = 'SQL Server Admin',

    @recipients

     

    ='betty.calloway@tmh.org',

    @subject

    = @ServerName,

    @body

    = @tableHTML,

    @body_format

    = 'HTML' ;

    END

    When I use this code here is the results:

    Database Backup Report

    Date Range Dec 8 2009 9:29AM - Dec 9 2009 9:29AM

     

    Database Name

    Server

    Size

    Backup DateSELECT td = database_name, '', td = Server_name, '', td = Convert(varchar, convert(decimal(18,2),(backup_size/1048576))) + ' MB', '', td = Convert(char, backup_finish_date) FROM SQLTEST01.msdb.dbo.backupset WHERE backup_finish_date > (Getdate() - 1) and type != N'L' order by database_name, backup_start_date FOR XML PATH('tr'), TYPE

     



    When I do not use the variable but use the Cast statement I get the table with data in it 

    How can I execute the @SQLStatement within the table definition.  Any help is greatly appreciated.

     

     

    order by database_name, backup_start_date

    FOR XML PATH(''tr''), TYPE '

    td = Server_name, '''',

    td = Convert(varchar, convert(decimal(18,2),(backup_size/1048576))) + '' MB'', '''',

    td = Convert(char, backup_finish_date)

    FROM '

     

    Wednesday, December 09, 2009 5:08 PM

Answers

  • SET NOCOUNT ON; 
    
    DECLARE @tableHTML1 NVARCHAR(MAX) ;
    DECLARE @tableHTML2 NVARCHAR(MAX) ;
    DECLARE @tableHTML NVARCHAR(MAX) ;
    DECLARE @ServerName NVARCHAR(100) ;
    
    DECLARE @SQLStatement NVARCHAR(MAX) ;
    DECLARE @cmd nvarchar(4000)
    
    DECLARE @TableName NVARCHAR(MAX) ;
    DECLARE @database varchar(MAX);
    
    SET @database = 'SQLTEST01.'
    
    SET @TableName = @database + 'msdb.dbo.backupset'
    
    SET @cmd = N' select @x=(SELECT td1 = database_name,'''+ @TableName+''' as td2'
                    + ',backup_size as td3,backup_finish_date as td4'
                    +' FROM '+@TableName
                    + ' WHERE backup_finish_date > (Getdate() - 1) and type != N''L'''
                    + ' for xml path(''tr''))'
     
    EXEC sp_executesql @cmd,N'@x nvarchar(MAX) OUTPUT',@SQLStatement OUTPUT
    
    SELECT @SQLStatement = 
        CAST(
           CAST(@SQLStatement AS XML).query('
                 for $i in //tr
                   return 
                     <tr>
                         <td>{$i/td1/text()}</td>
                         <td>{$i/td2/text()}</td>
                         <td>{$i/td3/text()}</td>
                         <td>{$i/td4/text()}</td>
                     </tr>
              ') AS nvarchar(MAX))
    
    SET @tableHTML1 =
        N'<H1>Database Backup Report</H1>'
       +N'<H2>Date Range ' + convert(char,(Getdate() - 1)) + N' - '
                       + convert(char, Getdate()) + N'</H2>' 
       +N'<table border="1">' 
       +N'<tr><th>Database Name</th><th>Server</th>' 
       +N'<th>Size</th><th>Backup Date </th>' 
       +Cast (Cast (@SQLStatement AS VARCHAR(MAX)) as varchar(max) )
       +N'</table>' ;
    
    SET @tableHTML2 =
        N'<H2>Databases with a backup older than 24 hours or not at all</H2>' 
       +N'<table border="1">' 
       +N'<tr><th>Database Name</th><th>Backup Finish Date</th>' 
       +N'<th>Backup Age in hours</th>' 
       +CAST ((select td = database_name, '',
                    td= finish_date, '',
                    td=hours 
               from(SELECT database_name,
                       MAX(msdb.dbo.backupset.backup_finish_date) AS finish_date,
                       DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS Hours                 FROM msdb.dbo.backupset 
                    WHERE type = 'D'
                    GROUP BY database_name 
                    HAVING (MAX(backup_finish_date) < DATEADD(hh, - 24, GETDATE())) 
     
                    UNION 
     
    --Databases without any backup history 
    
                     SELECT master.dbo.sysdatabases.NAME as database_name,
                        '01/01/01' as finish_date,9999as hours 
                     FROM master.dbo.sysdatabases
                          LEFT JOIN msdb.dbo.backupset 
                     ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name 
                     WHERE msdb.dbo.backupset.database_name IS NULL
                               AND master.dbo.sysdatabases.name <> 'tempdb' 
                 ) as a 
                 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) 
       +N'</table>';
    
    IF @tableHTML2 is null and @tableHTML1 is not null
        set @tableHTML = @tableHTML1;
    ELSE IF @tableHTML2 is not null and @tableHTML1 is null 
        set @tableHTML = @tableHTML2;
    ELSE
        set @tableHTML = @tableHTML1 + @tableHTML2;
    
    
    set @ServerName = (select distinct name 
                       from msdb.sys.servers where is_linked = 0) 
                   + ' Backup Report';
    
     
    
    EXEC msdb.dbo.sp_send_dbmail 
           @profile_name= 'SQL Server Admin', 
           @recipients='betty.calloway@tmh.org',
           @subject= @ServerName,
           @body= @tableHTML,
           @body_format= 'HTML' ;

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    • Marked as answer by BettyLugenia Thursday, December 10, 2009 3:46 PM
    Thursday, December 10, 2009 12:38 PM

All replies

  • SET NOCOUNT ON; 
    
    DECLARE @tableHTML1 NVARCHAR(MAX) ;
    DECLARE @tableHTML2 NVARCHAR(MAX) ;
    DECLARE @tableHTML NVARCHAR(MAX) ;
    DECLARE @ServerName NVARCHAR(100) ;
    
    DECLARE @SQLStatement NVARCHAR(MAX) ;
    DECLARE @cmd nvarchar(4000)
    
    DECLARE @TableName NVARCHAR(MAX) ;
    DECLARE @database varchar(MAX);
    
    SET @database = 'SQLTEST01.'
    
    SET @TableName = @database + 'msdb.dbo.backupset'
    
    SET @cmd = N' select @x=(SELECT td1 = database_name,'''+ @TableName+''' as td2'
                    + ',backup_size as td3,backup_finish_date as td4'
                    +' FROM '+@TableName
                    + ' WHERE backup_finish_date > (Getdate() - 1) and type != N''L'''
                    + ' for xml path(''tr''))'
     
    EXEC sp_executesql @cmd,N'@x nvarchar(MAX) OUTPUT',@SQLStatement OUTPUT
    
    SELECT @SQLStatement = 
        CAST(
           CAST(@SQLStatement AS XML).query('
                 for $i in //tr
                   return 
                     <tr>
                         <td>{$i/td1/text()}</td>
                         <td>{$i/td2/text()}</td>
                         <td>{$i/td3/text()}</td>
                         <td>{$i/td4/text()}</td>
                     </tr>
              ') AS nvarchar(MAX))
    
    SET @tableHTML1 =
        N'<H1>Database Backup Report</H1>'
       +N'<H2>Date Range ' + convert(char,(Getdate() - 1)) + N' - '
                       + convert(char, Getdate()) + N'</H2>' 
       +N'<table border="1">' 
       +N'<tr><th>Database Name</th><th>Server</th>' 
       +N'<th>Size</th><th>Backup Date </th>' 
       +Cast (Cast (@SQLStatement AS VARCHAR(MAX)) as varchar(max) )
       +N'</table>' ;
    
    SET @tableHTML2 =
        N'<H2>Databases with a backup older than 24 hours or not at all</H2>' 
       +N'<table border="1">' 
       +N'<tr><th>Database Name</th><th>Backup Finish Date</th>' 
       +N'<th>Backup Age in hours</th>' 
       +CAST ((select td = database_name, '',
                    td= finish_date, '',
                    td=hours 
               from(SELECT database_name,
                       MAX(msdb.dbo.backupset.backup_finish_date) AS finish_date,
                       DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS Hours                 FROM msdb.dbo.backupset 
                    WHERE type = 'D'
                    GROUP BY database_name 
                    HAVING (MAX(backup_finish_date) < DATEADD(hh, - 24, GETDATE())) 
     
                    UNION 
     
    --Databases without any backup history 
    
                     SELECT master.dbo.sysdatabases.NAME as database_name,
                        '01/01/01' as finish_date,9999as hours 
                     FROM master.dbo.sysdatabases
                          LEFT JOIN msdb.dbo.backupset 
                     ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name 
                     WHERE msdb.dbo.backupset.database_name IS NULL
                               AND master.dbo.sysdatabases.name <> 'tempdb' 
                 ) as a 
                 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) 
       +N'</table>';
    
    IF @tableHTML2 is null and @tableHTML1 is not null
        set @tableHTML = @tableHTML1;
    ELSE IF @tableHTML2 is not null and @tableHTML1 is null 
        set @tableHTML = @tableHTML2;
    ELSE
        set @tableHTML = @tableHTML1 + @tableHTML2;
    
    
    set @ServerName = (select distinct name 
                       from msdb.sys.servers where is_linked = 0) 
                   + ' Backup Report';
    
     
    
    EXEC msdb.dbo.sp_send_dbmail 
           @profile_name= 'SQL Server Admin', 
           @recipients='betty.calloway@tmh.org',
           @subject= @ServerName,
           @body= @tableHTML,
           @body_format= 'HTML' ;

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    • Marked as answer by BettyLugenia Thursday, December 10, 2009 3:46 PM
    Thursday, December 10, 2009 12:38 PM
  • Thank you so very much for this reply.  It works great!
    Thursday, December 10, 2009 3:45 PM
  • SET NOCOUNT ON; 
    
    DECLARE @tableHTML1 NVARCHAR(MAX) ;
    DECLARE @tableHTML2 NVARCHAR(MAX) ;
    DECLARE @tableHTML NVARCHAR(MAX) ;
    DECLARE @ServerName NVARCHAR(100) ;
    
    DECLARE @SQLStatement NVARCHAR(MAX) ;
    DECLARE @cmd nvarchar(4000)
    
    DECLARE @TableName NVARCHAR(MAX) ;
    DECLARE @database varchar(MAX);
    
    SET @database = 'SQLTEST01.'
    
    SET @TableName = @database + 'msdb.dbo.backupset'
    
    SET @cmd = N' select @x=(SELECT td1 = database_name,'''+ @TableName+''' as td2'
            + ',backup_size as td3,backup_finish_date as td4'
            +' FROM '+@TableName
            + ' WHERE backup_finish_date > (Getdate() - 1) and type != N''L'''
            + ' for xml path(''tr''))'
     
    EXEC sp_executesql @cmd,N'@x nvarchar(MAX) OUTPUT',@SQLStatement OUTPUT
    
    SELECT @SQLStatement = 
      CAST(
        CAST(@SQLStatement AS XML).query('
           for $i in //tr
            return 
             
               {$i/td1/text()}
               {$i/td2/text()}
               {$i/td3/text()}
               {$i/td4/text()}
             
         ') AS nvarchar(MAX))
    
    SET @tableHTML1 =
      N'

    Database Backup Report

    '
    +N'

    Date Range ' + convert(char,(Getdate() - 1)) + N' - ' + convert(char, Getdate()) + N'

    '
    +N'' +N'' +N'' +Cast (Cast (@SQLStatement ASVARCHAR(MAX)) asvarchar(max) ) +N'
    Database NameServerSizeBackup Date
    '
    ; SET @tableHTML2 = N'

    Databases with a backup older than 24 hours or not at all

    '
    +N'' +N'' +N'' +CAST ((select td = database_name, '', td= finish_date, '', td=hours from(SELECT database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS finish_date, DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS Hours FROM msdb.dbo.backupset WHEREtype = 'D'GROUPBY database_name HAVING (MAX(backup_finish_date) < DATEADD(hh, - 24, GETDATE())) UNION--Databases without any backup history SELECT master.dbo.sysdatabases.NAME as database_name, '01/01/01'as finish_date,9999as hours FROM master.dbo.sysdatabases LEFTJOIN msdb.dbo.backupset ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name WHERE msdb.dbo.backupset.database_name ISNULLAND master.dbo.sysdatabases.name <> 'tempdb' ) as a FORXML PATH('tr'), TYPE ) ASNVARCHAR(MAX) ) +N'
    Database NameBackup Finish DateBackup Age in hours
    '
    ; IF @tableHTML2 is null and @tableHTML1 is not null set @tableHTML = @tableHTML1; ELSE IF @tableHTML2 is not null and @tableHTML1 is null set @tableHTML = @tableHTML2; ELSE set @tableHTML = @tableHTML1 + @tableHTML2; set @ServerName = (select distinct name from msdb.sys.servers where is_linked = 0) + ' Backup Report'; EXEC msdb.dbo.sp_send_dbmail @profile_name= 'SQL Server Admin', @recipients='betty.calloway@tmh.org', @subject= @ServerName, @body= @tableHTML, @body_format= 'HTML' ;

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China

    I do not fully understand the sample, I just start to learn it, Any related guide to learn?
    Friday, October 01, 2010 1:32 AM
  • Have a look through this article:

    Constructing XML Using FOR XML
    http://msdn.microsoft.com/en-us/library/ms178107.aspx

     

    Friday, October 01, 2010 1:21 PM
    Answerer