Run a query in a job and output to a csv file and email

Question

Post reply

Run a query in a job and output to a csv file and email

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
June 7, 2016 at 8:16 am

#319359

Hi All,
I would like to have the below query run in a SQL job and export the output as .csv file and send via email.
How can i automate this steps in a SQL job.
can someone provde the steps or script to accomplish this? Thanks in advance.
--Script to list all current members for their REFERRALS history
SELECT DISTINCT
n.ID,
n.LAST_NAME,
n.FIRST_NAME,
n.FULL_ADDRESS,
n.MEMBER_TYPE,
n.BIRTH_DATE,
n.JOIN_DATE,
n.HOME_PHONE,
n.EMAIL,
a.ACTIVITY_TYPE,
a.TRANSACTION_DATE
FROM Name n
INNER JOIN Activity a ON n.CO_ID=a.id
WHERE a.activity_type = 'REFERRED' AND
n.MEMBER_TYPE NOT IN('NM-CH','NM-F','NM-MP','NM-OT','NM-SP')
order by a.TRANSACTION_DATE desc
Regards,
SQLisAwe5oMe.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can use sp_send_dbmail to send the results of a query by e-mail.

John

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

I was able to get this to work by using this sample query....but the output in excel file looks all messy....any idea, how to get the output as same as the output to Grid format on query window.

DECLARE @Delimiter Char(1)

SET @Delimiter = CHAR(9)

EXEC MSDB.dbo.sp_Send_DBMail

@Recipients='My.Email.Address@company.com',

@Subject='Some Audit',

@Body='Attached is some audit information.',

@Query='SELECT Left(Name, 50) AS Name, Left(Description, 50) AS Description FROM MSDB.dbo.SysJobs',

@Attach_Query_Result_As_File = 1,

@Query_Result_Header = 1,

@Query_Attachment_Filename = 'Audit.xls',

@Query_Result_Separator = @Delimiter

Regards,
SQLisAwe5oMe.

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 3

What sort of file do you want? A csv file or a tab delimited file? As you mention csv but the script you are posted is using tab as the delimiter

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

FridayNightGiant (6/7/2016)
What sort of file do you want? A csv file or a tab delimited file? As you mention csv but the script you are posted is using tab as the delimiter

I would like it in excel format if its possible...if not, csv file is fine....but I want each columns to be displayed in separate columns just like the query grid output in query window.

What do I need to change to make it happen..

Regards,
SQLisAwe5oMe.

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 5

Your tab delimited script does that. You may want to change the file extension to be csv instead of xls to stop excel from complaining but it will put the data into columns.