question

technette avatar image
technette asked

Database Email

I have to create a process where I am pulling data from two different systems based on dates and matching employee names. Then, I will send an automated email to functional managers based on the matches. What's the best way to handle this with SQL database?
databasedatabase-email
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

by "two different systems" do you mean different databases on different servers?
0 Likes 0 ·
Yes, Two Different Databases on Different Servers. Thank you for responding Ken
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
One possible approach is to creat a [linked server relationship][1], then create the procedure to generate the lists for each manager and use [database mail][2] to actually send the mail wtih your result set. If you are sending different result sets to different managers, you may need to put it in a loop to get all the relevant managers. [1]: http://msdn.microsoft.com/en-us/library/aa213778(v=SQL.80).aspx [2]: http://msdn.microsoft.com/en-us/library/ms175887.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Timothy. This give me a great start. I think I can finish scoping the remaining technical requirements for this environment from here.
0 Likes 0 ·
KenJ avatar image
KenJ answered
You could join the data (sorted) from the two servers together in SSIS with a Merge Join Transformation and send the appropriate emails from there. [ http://msdn.microsoft.com/en-us/library/ms141775.aspx][1] [1]: http://msdn.microsoft.com/en-us/library/ms141775.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for your input KenJ! We have many SSIS solutions here and the Team has been complaining about the errors. I would like to try two options. One, SSIS package and the other using Managed Extensibility Framework and possibly create a UI for administration and displaying errors.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.