SQL Server 2014 Agent job emails query attachment but no records show?

Question

Hello, I created a SQL Agent job with the following Step:

EXEC msdb.dbo.sp_send_dbmail
@Anton _name = 'DB_Mail_Profile_Name',
@recipients = 'Me@ssss .com',
@Query = 'SELECT
o.name AS ObjectName,
i.name AS IndexName,
o.create_date AS CreationDate,
i.index_id AS IndexID,
u.user_seeks AS UserSeeks,
u.user_scans AS UserScans,
u.user_lookups AS UserLookups,
u.user_updates AS UserUpdates,
p.TableRows
FROM sys.dm_db_index_usage_stats u
INNER JOIN sys.indexes i ON i.index_id = u.index_id
AND u.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON u.OBJECT_ID = o.OBJECT_ID
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p
GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = u.index_id
AND u.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(u.OBJECT_ID,''IsUserTable'') = 1
AND u.database_id = DB_ID()
AND i.type_desc =''nonclustered''
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND o.is_ms_shipped <> 1
ORDER BY (u.user_seeks + u.user_scans + u.user_lookups);' ,
@subject = 'Email Report',
@attach_query_result_as_file = 1,
@Query _result_header=1,
@Query _no_truncate = 1;

But when I open the email .txt attachment query results it only lists:

(0 rows affected)

I ran the query manually and know there are records in this query results.

What am I missing ?

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

Is the reply helpful?

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

BR,
Mia

Answer 2

I ended up creating a new table and inserted the query results into there instead using sp_send_dbmail.

Thanks for all of the replies, much appreciated.

Answer 3

Hi @techresearch7777777 ,

I ran the query manually and know there are records in this query results. What am I missing ?

Do you execute your job?
Please make sure your sql server agent service works well and then after your complete, please make sure your job is executed successfully.
You can view the job history to have a look.
Please reference this and follow the steps to have a test again: send-query-results-sql-server-agent-job

More information: create-a-transact-sql-job-step

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

BR,
Mia

Answer 4

My guess is that the query isn't executed in the database you expect it to be executed. You can trace the execution of the query to verify what database. Note that I'm not talking about the EXEC sp_send_dbmail thing. I'm talking about the SELECT that this proc is executing. You can use the @execute_query_database parameter to control this.

Answer 5

Change

WHERE OBJECTPROPERTY(u.OBJECT_ID,''IsUserTable'') = 1
to
WHERE OBJECTPROPERTY(u.OBJECT_ID,'''IsUserTable''') = 1

and

AND i.type_desc =''nonclustered''
to
AND i.type_desc ='''nonclustered'''