SENDING DAILY EMAIL ATTACHMENT WITH SQL QUERY TO GIVEN USERS.

Question

SENDING DAILY EMAIL ATTACHMENT WITH SQL QUERY TO GIVEN USERS.

ShawnBryan

SSCrazy

Points: 2157
More actions
October 23, 2016 at 2:41 pm

#331219

Dear All,
i would like to send out following query to all email users daily basis,can anyone guide me the direction,
it can be from SQL or external tool,ETC.
SELECT * ,[action_id],DBO.UtcToLocal(creation_date) as Creation_Date,[event_name]
,[person_num] FROM [dbo].[TEST] WHERE preffered_name = 'finance' AND dbo.UtcToLocal(creation_date) >getdate()-50 AND dbo.UtcToLocal(creation_date)>dateadd(dd,-52,convert(date,getdate()))
BR

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

philand3 (10/23/2016)
Dear All,
i would like to send out following query to all email users daily basis,can anyone guide me the direction,
it can be from SQL or external tool,ETC.
SELECT * ,[action_id],DBO.UtcToLocal(creation_date) as Creation_Date,[event_name]
,[person_num] FROM [dbo].[TEST] WHERE preffered_name = 'finance' AND dbo.UtcToLocal(creation_date) >getdate()-50 AND dbo.UtcToLocal(creation_date)>dateadd(dd,-52,convert(date,getdate()))
BR

So, can we assume that you've looked into using sp_send_mail? What problems did you encounter?

--Edit: by the way, 'preferred' is spelt wrongly in your query.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

aaron.reese SSChampion Points: 13498 More actions · Answer 2

Do you want to send the query or the query results?

Like Phil Says - db_sendmail

Or you could use SSIS and SQL Agent

Or you could use SSRS and subscriptions

Or you could use Windows Scheduler and a batch script

Or you could use Powershell

Or you could write a service

Or you could spin up a linux environment and use a CRON job

Ed B Hall of Fame Points: 3666 More actions · Answer 3

I had to do this last week. I found this forum thread and it gave me what I needed.

http://www.sqlservercentral.com/Forums/Topic1465444-279-1.aspx

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

philand3 (10/23/2016)
Dear All,
i would like to send out following query to all email users daily basis,can anyone guide me the direction,
it can be from SQL or external tool,ETC.
SELECT * ,[action_id],DBO.UtcToLocal(creation_date) as Creation_Date,[event_name]
,[person_num] FROM [dbo].[TEST] WHERE preffered_name = 'finance' AND dbo.UtcToLocal(creation_date) >getdate()-50 AND dbo.UtcToLocal(creation_date)>dateadd(dd,-52,convert(date,getdate()))
BR

Please see the following link for details.

https://msdn.microsoft.com/en-us/library/ms190307.aspx

Also, you're query is non-SARGable because of the functions on the criteria columns in the WHERE clause. That will make the query slower and much more resource intensive than it should be. You might also want to adopt a better code formatting habit to make it easier for others to troubleshoot your code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)