Send email using SSMS
-
Hi,
I want to know where it can be worked in SSMS.
I created a cube (SSAS) with KPIs, and display the KPIs in SSRS, what can I do to send out the emails to users when the KPI values fall under certain condition by not using PPS or BSM.
Does SSMS handle this? I just need to compare with the database values that if the KPI value less than target then it will send email to users.
Thanks.
-
SSMS = SQL Server Management Studio
This is the set of tools for managing SQL Servers. It is not a scheduling system.
With the base tools from SQL Server, you have a few options.
If you are using Enteprise Edition and Reporting Services, you could use a data driven subscription. Data driven subscriptions run on a schedule you define (say once per hour) and run / distribute reports based on a query you provide. So, you could write an MDX query that checks your KPI and returns the appropriate email addresses to send your report to only if the KPI value meets your criteria. This method would work pretty well.
If you are not using Enterprise Edition, you would have to rely on the SQL Agent to run on a schedule. You would be able to run an MDX query to check your KPI and send an email through the job agent.
Also, you could create an Integration Services package that would check the KPI value and send an email to a group of users. Again, this would need to be scheduled to run regularly - probably with the Job Agent.
-
Thanks very much for your info.
However, I'm not really familiar with the MDX, do you have any simple example to do this? just to check the value of the KPI, is it falling a certain value will do.
if I do it in the SSIS which task should i use?
if I do it in the job agent, where do I need to put the MDX in?
Thanks alot!
-
In SSIS you can use send mail task (under control flow items) or the execute SQL task and you can use: xp_sendmail (a stored proceure to send an email)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply