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.

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]

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.

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

Sending Querying Data on Mail as Attached File