none
Getting error while adding HTML mail body

    Question

  • Hi,

    Am getting the error message while trying to add mail part in the below query. Help me to getthe result as HTML mail 

    The below one is a part of the original query

    select  
    d.Dbname,d.dbstatus,d.Recovery_Model, 
    (file_size_mb + log_file_size_mb) as DBsize, 
    d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 
    l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace 
    from #dbsize d join #logsize l  
    on d.Dbname=l.Dbname join #dbfreesize fs  
    on d.Dbname=fs.name 
    order by Dbname 

    ---------Here is my HTML body part which i tried--------------

    DECLARE @table  NVARCHAR(MAX) ;
    SET @table =    N'<H1>DBSize</H1>' +    N'<table border="1">' +  
    N'<tr><th>d.Dbname</th><th>d.dbstatus</th><th>d.Recovery_Model</th><th>DBSIZE</th><th>(file_size_mb + log_file_size_mb) as DBsize
    </th><th>d.file_Size_MB</th></th><th>d.Space_Used_MB</th><th>d.Free_Space_MB</th><th>l.Log_File_Size_MB</th><th>log_Space_Used_MB</th>
    <th>l.log_Free_Space_MB</th><th>fs.Freespace</th></tr>'
    + CAST ( ( SELECT [d.Dbname] AS 'td','',[d.dbstatus] AS 'td','',[d.Recovery_Model] AS 'td','', (file_size_mb + log_file_size_mb) AS 'td','',[FILE_SIZE_MB] AS 'td','',
    [d.file_Size_MB] AS 'td','',[d.Space_Used_MB] AS 'td','',[d.Free_Space_MB] AS 'td','',[l.Log_File_Size_MB] AS 'td','',
    [log_Space_Used_MB] AS 'td','',[l.log_Free_Space_MB] AS 'td','',[fs.Freespace] AS 'td'
    from #dbsize d join #logsize l  
    on d.Dbname=l.Dbname join #dbfreesize fs  
    on d.Dbname=fs.name 
    order by Dbname
    FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) )    +    N'</table>' ;
    EXEC msdb.dbo.sp_send_dbmail @profile_name='GmailAccount', --Change to your Profile Name 
    @recipients='mynamer@gmail.com', --Put the email address of those who want to receive the e-mail  
    @subject = 'DatabaseFileSizes',    @body = @table,    @body_format = 'HTML' ;


    ----Error message Part---

    Msg 207, Level 16, State 1, Line 107
    Invalid column name 'd.Dbname'.
    Msg 207, Level 16, State 1, Line 107
    Invalid column name 'd.dbstatus'.
    Msg 207, Level 16, State 1, Line 107
    Saturday, February 15, 2014 6:41 PM

Answers

  • I asked you twice to double-click on the error message. Did you ever try to do that? Had you done that, that could have save us both some time, as that you would have lead you exactly to the error. I had to work a little to clean up your script - remove spaces around "go" etc - but once I got it so far that the script produced the error, a double-click quickly lead me to the error:

    + CAST ( ( SELECT [d.Dbname] AS 'td'

    You don't have a column called d.Dbname anywhere. You use brackets to encolose column names when they are keywords or include characters that are not legal identifier characters. That is, had you had a column called d.Dbname, you would have needed to include it in quotes. Now you have a column called Dbname in a table aliased with d, and that is something else. All these would be legal:

    d.dbstatus
    [d].Dbname
    d.[Dbname]
    [d].[Dbname]

    But there is little reason to use any but the first.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Shivanq Thursday, February 20, 2014 9:58 AM
    Sunday, February 16, 2014 1:50 PM

All replies

  • Don't know, but the error is on line 107, and there are not 107 lines of code in what you posted, so you may be looking in the wrong place. If you double-click the error message, where do you land?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 7:09 PM
  • Hi Erland,

    Here is the link of original code

    http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Data-log-a36da95d#content

    Saturday, February 15, 2014 7:47 PM
  • I don't see any HTML tags or call to sp_send_dbmail on that page.

    All I know is that you get an error about invalid column names on line 107 in an SQL batch, but since you have not posted the code for that SQL batch, I can't help you any further.

    Did you try double-clicking the error message?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 8:51 PM
  • Erland, 

    The below one is  the original query


         ------------------------------Data file size---------------------------- 
        if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') 
        drop table #dbsize 
        create table #dbsize 
        (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)        default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 
        go 

        insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 
        exec sp_msforeachdb 
        'use [?]; 
          select DB_NAME() AS DbName, 
            CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
            CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
        sum(size)/128.0 AS File_Size_MB, 
        sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
        SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  
        from sys.database_files  where type=0 group by type' 
          go 

        -------------------log size-------------------------------------- 
          if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 
        drop table #logsize 
        create table #logsize 
        (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default         (0),log_Free_Space_MB decimal(30,2)default (0)) 
        go 

        insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 
        exec sp_msforeachdb 
        'use [?]; 
          select DB_NAME() AS DbName, 
        sum(size)/128.0 AS Log_File_Size_MB, 
        sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 
        SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  
        from sys.database_files  where type=1 group by type'         
          go 
        --------------------------------database free size 
          if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') 
        drop table #dbfreesize 
        create table #dbfreesize 
        (name sysname, 
        database_size varchar(50), 
        Freespace varchar(50)default (0.00)) 

        insert into #dbfreesize(name,database_size,Freespace) 
        exec sp_msforeachdb 
        'use [?];SELECT database_name = db_name() 
            ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192         / 1048576, 15, 2) + ''MB'') 
            ,''unallocated space'' = ltrim(str(( 
                        CASE  
                            WHEN dbsize >= reservedpages 
                                THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) *         8192 / 1048576 
                    ELSE 0 
                    END 
                ), 15, 2) + '' MB'') 
        FROM ( 
            SELECT dbsize = sum(convert(BIGINT, CASE  
                    WHEN type = 0 
                        THEN size 
                    ELSE 0 
                    END)) 
        ,logsize = sum(convert(BIGINT, CASE  
                    WHEN type <> 0 
                        THEN size 
                    ELSE 0 
                    END)) 
            FROM sys.database_files 
        ) AS files 
        ,( 
            SELECT reservedpages = sum(a.total_pages) 
        ,usedpages = sum(a.used_pages) 
        ,pages = sum(CASE  
                WHEN it.internal_type IN ( 
                        202 
                        ,204 
                        ,211 
                        ,212 
                        ,213 
                        ,214 
                        ,215 
                        ,216 
                        ) 
                    THEN 0 
                WHEN a.type <> 1 
                    THEN a.used_pages 
                WHEN p.index_id < 2 
                    THEN a.data_pages 
                ELSE 0 
                END) 
            FROM sys.partitions p 
            INNER JOIN sys.allocation_units a 
                ON p.partition_id = a.container_id 
            LEFT JOIN sys.internal_tables it 
                ON p.object_id = it.object_id 
        ) AS partitions' 
        ----------------------------------- 

          if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') 
        drop table #alldbstate  
        create table #alldbstate  
        (dbname sysname, 
        DBstatus varchar(55), 
        R_model Varchar(30)) 

        --select * from sys.master_files 

        insert into #alldbstate (dbname,DBstatus,R_model) 
        select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 
        --select * from #dbsize 

        insert into #dbsize(Dbname,dbstatus,Recovery_Model) 
        select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' 

        insert into #logsize(Dbname) 
        select dbname from #alldbstate where DBstatus <> 'online' 

        insert into #dbfreesize(name) 
        select dbname from #alldbstate where DBstatus <> 'online' 

        select  

        d.Dbname,d.dbstatus,d.Recovery_Model, 
        (file_size_mb + log_file_size_mb) as DBsize, 
        d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 
        l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as      DB_Freespace 
        from #dbsize d join #logsize l  
        on d.Dbname=l.Dbname join #dbfreesize fs  
        on d.Dbname=fs.name 
        order by Dbname 

        ---------Here is my HTML body part which i tried--------------

    DECLARE @table  NVARCHAR(MAX) ;
    SET @table =    
               N'<H1>DBSize</H1>'
             + N'<table border="1">' 
             + N'<tr><th>d.Dbname</th><th>d.dbstatus</th><th>d.Recovery_Model</th><th>DBSIZE</th>'
             + N'<th>(file_size_mb + log_file_size_mb) as DBsize</th><th>d.file_Size_MB</th></th>'
             + N'<th>d.Space_Used_MB</th><th>d.Free_Space_MB</th><th>l.Log_File_Size_MB</th><th>log_Space_Used_MB</th>'
             + N'<th>l.log_Free_Space_MB</th><th>fs.Freespace</th></tr>'
    + CAST ( ( SELECT [d.Dbname] AS 'td'
                     ,''
                     ,[d.dbstatus] AS 'td'
                     ,''
                     ,[d.Recovery_Model] AS 'td'
                     ,''
                     , (file_size_mb + log_file_size_mb) AS 'td'
                     ,''
                     ,[FILE_SIZE_MB] AS 'td'
                     ,''
                     ,[d.file_Size_MB] AS 'td'
                     ,''
                     ,[d.Space_Used_MB] AS 'td'
                     ,''
                     ,[d.Free_Space_MB] AS 'td'
                     ,''
                     ,[l.Log_File_Size_MB] AS 'td'
                     ,''
                     ,[log_Space_Used_MB] AS 'td'
                     ,''
                     ,[l.log_Free_Space_MB] AS 'td'
                     ,''
                     ,[fs.Freespace] AS 'td'
                from #dbsize d join #logsize l  
                on d.Dbname=l.Dbname 
                join #dbfreesize fs  
                on d.Dbname=fs.name 
                order by Dbname
                FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) )    

          +    N'</table>' ;
    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='GmailAccount',    --Change to your Profile Name 
    @recipients='mynamer@gmail.com', --Put the email address of those who want to receive the e-mail  
    @subject = 'DatabaseFileSizes',
    @body = @table,
    @body_format = 'HTML';

    ----Error message Part---

    Msg 207, Level 16, State 1, Line 107 Invalid column name 'd.Dbname'.
    Msg 207, Level 16, State 1, Line 107 Invalid column name 'd.dbstatus'. 
    Msg 207, Level 16, State 1, Line 107........

    Sunday, February 16, 2014 3:07 AM
  • I asked you twice to double-click on the error message. Did you ever try to do that? Had you done that, that could have save us both some time, as that you would have lead you exactly to the error. I had to work a little to clean up your script - remove spaces around "go" etc - but once I got it so far that the script produced the error, a double-click quickly lead me to the error:

    + CAST ( ( SELECT [d.Dbname] AS 'td'

    You don't have a column called d.Dbname anywhere. You use brackets to encolose column names when they are keywords or include characters that are not legal identifier characters. That is, had you had a column called d.Dbname, you would have needed to include it in quotes. Now you have a column called Dbname in a table aliased with d, and that is something else. All these would be legal:

    d.dbstatus
    [d].Dbname
    d.[Dbname]
    [d].[Dbname]

    But there is little reason to use any but the first.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Shivanq Thursday, February 20, 2014 9:58 AM
    Sunday, February 16, 2014 1:50 PM
  • Thank you Erland...

    Thursday, February 20, 2014 9:59 AM