0

I have a procedure with two temporary tables

CREATE TABLE #temp       
  ( databasename VARCHAR(100),  
    objectname VARCHAR(100), 
    index_id INT,
    indexname  VARCHAR(100), 
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100) 
  )       


CREATE TABLE #temp2       
( 
    databasename VARCHAR(100), 
    objectname VARCHAR(100),
    index_id INT,
    indexname VARCHAR(100), 
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100)   
)

INSERT INTO #temp (databasename, 
objectname,index_id, 
indexname,
avg_fragmentation_percent,
indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as 
 ObjectName,
a.index_id, 
b.name as IndexName, 
avg_fragmentation_in_percent, 
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id 
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

INSERT INTO #temp2 (databasename, objectname,index_id, 
indexname,avg_fragmentation_percent,indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as 
 ObjectName,
a.index_id, 
b.name as IndexName, 
avg_fragmentation_in_percent, 
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id 
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

and want to send the result from the procedure to email. The problem is that this query works fine but when i execute this its not finding the temporary tables.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>ID</th><th>Table</th>' +
    N'<th>Index</th><th>Before fragmentation</th><th>After fragmentation</th>' +
    --N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = T1.databasename,'',
                    td = T1.objectname, '',
                    td = T1.index_id, '',
                    td = T1.indexname, '',
                    td = T1.indextype,'',
                    td = T1.avg_fragmentation_percent AS 
                         avg_fragmentation_percent_pre_change, '',
                    td = T2.avg_fragmentation_percent AS 
                         avg_fragmentation_percent_post_change
              FROM #temp as T1
              INNER JOIN #temp2 AS T2
              ON T1.databasename = T2.databasename
              AND T1.objectname = T2.objectname
              AND T1.index_id = T2.index_id
              AND T1.indexname = T2.indexname
              AND T1.indextype = T2.indextype
              WHERE T1.databasename not in  ('master',
                                             'model',
                                             'msdb',
                                             'tempdb',
                                             'reportServer',
                                             'ReportServerTempDB')             
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail @recipients='example@mail.com',
@profile_name='example@mail.com',
    @subject = 'Report',
    @body = @tableHTML,
    @body_format = 'HTML';
  • Where are the insert statements to the #temp and #temp2 tables? – Biju jose Jul 10 at 7:48
  • Please update it in the question. – Biju jose Jul 10 at 7:52
  • i just update the question – disaster Jul 10 at 7:55
  • Sp_send_dbmail opens a separate connection for your query. The #temp table is local to the creating connection. Can you try a global ##temptable instead? – Tibor Karaszi Jul 10 at 8:11
  • 1
    Dont use a global temptable. Please. – Anthony Genovese Jul 10 at 15:16
1

Your insert to #temp and #temp2 works perfectly fine.

I believe you are getting the below error.

Msg 156, Level 15, State 1, Line 70 Incorrect syntax near the keyword 'AS'.

This error is due to the explicit aliasing on the below part of the code.

td = T1.avg_fragmentation_percent AS avg_fragmentation_percent_pre_change, '',
td = T2.avg_fragmentation_percent AS avg_fragmentation_percent_post_change

since you have already aliased it as 'td'. it will become like this

td = T1.avg_fragmentation_percent , '',
td = T2.avg_fragmentation_percent 

Remove the explicit aliasing using AS and run the code and see it works. I did test run and it worked.

  • @disaster, have you done testing to send an email from db mail? since I suspect an issue will be there for your dbmail. Do send a test mail and confirm your dbmail works – Biju jose Jul 10 at 8:40
  • 1
    @disaster, then this should also work fine, so the email is not at all queued or are you getting a blank email? – Biju jose Jul 10 at 8:46
  • @disaster, That can be solved using a CONVERT(VARCHAR(20),T1.avg_fragmentation_percent) – Biju jose Jul 10 at 9:02
  • 1
    @disaster if the answer solved your question, please accept it as to let future readers know that this was the solution for your issue. Thanks! – Randi Vertongen Jul 10 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.