Sending Querying Data on Mail as Attached File

Sending Querying Data on Mail:

Sometimes we need to send data fetched from query on mail automatically. You can 

create report and after running reports you can send mail data fetched from report.

Alternative is send query data on mail and schedule this job. Now you don’t need to 

run reports and mail.

You can follow some basic steps to achieve this

·        Configure database mail

·        Write your query as here for example

  SELECT

     customerid as Customer_ID,[name] as Name,

      COUNT(name) as No_of_Login,MAX(date1) as Last_Login_Date

  FROM [db_omx_customer].[dbo].[tblIP] group by customerid,name

  order by No_of_Login,name desc

Results like:

·        Use msdb.dbo.sp_send_dbmail stored procedure to send mail

·        When you configure database mail you were provide a profile name that you need to pass with msdb.dbo.sp_send_dbmail.

As

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'mailtest',

    @recipients = sample@sample.com',

    @body_format = 'HTML',

    @query = 'SELECT

     customerid as Customer_ID,[name] as Name,

      COUNT(name) as No_of_Login,MAX(date1) as Last_Login_Date

  FROM [db_omx_customer].[dbo].[tblIP] group by customerid,name

  order by No_of_Login,name desc' ,

    @subject = 'Customer Login Details',

    @attach_query_result_as_file = 1;

When you run above query result will be sent on sample@same.com as attached text 

file containing fetched data by query.

To send data on mail automatically now you need to schedule a job  that will run at 

particular time and send data to mail id.

·        To schedule a job make it as Stored procedure for easiness as

CREATE PROC [dbo].[usp_sendadataonmail]

as

begin

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'mailtest', -- profile name 

    @recipients = 'sample@sample.com', -- recipients mailid 

    @body_format = 'HTML',

    @query = 'SELECT

     customerid as Customer_ID,[name] as Name,

      COUNT(name) as No_of_Login,MAX(date1) as Last_Login_Date

  FROM [db_omx_customer].[dbo].[tblIP] group by customerid,name

  order by No_of_Login,name desc' ,

    @subject = 'Customer Login Details',

    @attach_query_result_as_file = 1; -- result sent in attched file. 

END

·        Now create a job with query

 Exec sampledatabase.dbo.usp_sendadataonmail

Ref: Creation and Scheduling Job

Now data with attched file automatically will be send to mail provided by you

automatically at scheduled time.