How to invoke SQL Stored procedure autmatically

Question

How to invoke SQL Stored procedure autmatically

IQ1

Right there with Babe

Points: 765
More actions
March 27, 2018 at 1:54 pm

#344518

Hi All,
I am doing the following process manually every week, I would like to automate this as much as possible.
I am using Windows 2012 and SQL Server 2012.
1) I invoke a stored procedure which creates a csv file.
2) I open up the csv file and do manual formatting like date columns, sorting by last_date column , etc
Instead I would like the raw csv file to be sent to me by email using Windows Task scheduler or any other process , once I have the file I can continue to refine it and use it,
Please suggest any solutions to this exercise
Thanks in advance
IQ

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

IQ1 - Tuesday, March 27, 2018 1:54 PM
Hi All,
I am doing the following process manually every week, I would like to automate this as much as possible.
I am using Windows 2012 and SQL Server 2012.
1) I invoke a stored procedure which creates a csv file.
2) I open up the csv file and do manual formatting like date columns, sorting by last_date column , etc
Instead I would like the raw csv file to be sent to me by email using Windows Task scheduler or any other process , once I have the file I can continue to refine it and use it,
Please suggest any solutions to this exercise
Thanks in advance
IQ

That all could be in a job.
First job step, run the stored procedure.
Second job step, sp_send_dbmail to send the file to you.

Sue

IQ1 Right there with Babe Points: 765 More actions · Answer 2

Is there any format I should be using when invoking the sp_send_email procedure. Do I need to have the permissions for my user account ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

See the documentation:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sue_H SSC Guru Points: 90860 More actions · Answer 4

IQ1 - Tuesday, March 27, 2018 2:06 PM
Is there any format I should be using when invoking the sp_send_email procedure. Do I need to have the permissions for my user account ?

Yes you need to provide the appropriate values for sp_send_dbmail and you need permissions to execute the stored procedure as well as the Profile used needs to be available to you. You would need to find out those values as no one here can see how you have database mail setup. The basics of sp_send_dbmail would be:
EXEC sp_send_dbmail @recipients='YourName@domain.com', @Profile_name = 'Profile you have access to', @subject='whatever you want for the subject', @body='whatever you want for the text in the body of the email', @file_attachments='X:\PathTo\File.csv';

Sue

IQ1 Right there with Babe Points: 765 More actions · Answer 5

Thanks Sue, I will try this out and let u know how it goes by tomorrow. Thanks for your help

DesNorton SSC-Insane Points: 24424 More actions · Answer 6

IQ1 - Tuesday, March 27, 2018 1:54 PM
Hi All,
I am doing the following process manually every week, I would like to automate this as much as possible.
I am using Windows 2012 and SQL Server 2012.
1) I invoke a stored procedure which creates a csv file.
2) I open up the csv file and do manual formatting like date columns, sorting by last_date column , etc
Instead I would like the raw csv file to be sent to me by email using Windows Task scheduler or any other process , once I have the file I can continue to refine it and use it,
Please suggest any solutions to this exercise
Thanks in advance
IQ

You could also modify your proc to do the formatting and sorting for you

How to post data/code on a forum to get the best help.