I've tried every avenue on every damn forum suggested, but to no avail!
Need to send results of SQLPERF(logspace), that have been stored in a table, via sp_send_dbmail to recipient.
Step 2 of job is where failure occurs. Please help!
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'MyDBA',
@recipients= '[email protected]',
@subject='Log Warning',
@query='SELECT * from #TempForLogSpace WHERE LogSpaceUsed >80
You can't query from a temp table using database mail. The session that you used to create the temp table (step 1 of your job I assume) has been closed and a new session started when step 2 started. Because the session has been closed the table has been dropped (even if the table hasn't been dropped because it's a new session you don't have access to the other sessions temp table).
Either create a physical table and use that (either in the tempdb database or your database) or put the code which creates the output in the @query with the select * from #TempForLogSpace at the end (a stored procedure will be much easier to deal with in this case).