October 14, 2011 at 9:18 am
Crazy question indeed... Is there a way to trigger an SQL Job, or run a Stored Procedure (with parameter) by sending an email to the SQL Server somehow?
This is the scenario. The server runs a job every 2 hours checking a table for records with value "ERROR" in status field. When it finds any, sends an email to a couple of users in the company with information about this errors so they can solve the issues that are causing this errors.
I would like to set up something that will allow this user to reply the email, put some data in the SUBJECT (a product code for example) and use that to trigger a Stored Procedure, using that code as parameter, to update the record
Sound more like witchcraft, but I guess there should be some way to do it...
Any ideas?
Thanks in advance
Juan P. Realini
Buenos Aires, Argentina
Web Developer, .Net Developer,
Windows Applications, SQL Server 2000
"A man's gotta do what a man's gotta do... that is, do all his wife says... "
October 14, 2011 at 10:18 am
Freeman-674288 (10/14/2011)
Crazy question indeed... Is there a way to trigger an SQL Job, or run a Stored Procedure (with parameter) by sending an email to the SQL Server somehow?This is the scenario. The server runs a job every 2 hours checking a table for records with value "ERROR" in status field. When it finds any, sends an email to a couple of users in the company with information about this errors so they can solve the issues that are causing this errors.
I would like to set up something that will allow this user to reply the email, put some data in the SUBJECT (a product code for example) and use that to trigger a Stored Procedure, using that code as parameter, to update the record
Sound more like witchcraft, but I guess there should be some way to do it...
Any ideas?
Thanks in advance
Well you don't send emails to a server. You send them an email address. SQL does not have the ability to open Outlook and check for new mail. 😛
Maybe you can create a link in the email that will open a webpage to allow the users to enter whatever it is they need to enter. Then that page calls your stored proc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 10:21 am
Ya, you need a 3rd tier to handle this.
What I can do here and which might available to you is to send a link, which opens the application at the correct location (form, filters, sorting, record...)
Where the user can then do his thing.
October 14, 2011 at 10:23 am
If you really want to go completely to the dark side and use something "incorrectly" you can do this.
Create a report with a parameter. The datasource should be a stored proc.
Then in the proc update the record.
Then in the e-mail put the full link to the url with the populated parameter(s).
A midway could also be to list all the problems in a master report. Then have a link to a drill-through report that does the previous tasks.
/ hack of ssrs 😉
October 14, 2011 at 10:25 am
Ninja's_RGR'us (10/14/2011)
If you really want to go completely to the dark side and use something "incorrectly" you can do this.Create a report with a parameter. The datasource should be a stored proc.
Then in the proc update the record.
Then in the e-mail put the full link to the url with the populated parameter(s).
A midway could also be to list all the problems in a master report. Then have a link to a drill-through report that does the previous tasks.
/ hack of ssrs 😉
I actually was thinking the same thing and bit my tongue before I posted it. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 10:28 am
Hence the preface, HACK and DARK SIDE. 😀
The correct way would be to integrate that in your ERP, but if that's not an option...
October 14, 2011 at 10:46 am
Thanks to everyone for the brainstorming
I was not hoping that SQL Server would receive the email itself... I know it's not a mail server 😉
But I was wondering what ways I had to accomplish this. I think that I will implement the idea of sending the user a weblink (with the parameters included) where he will have to login first and then with the parameters sent call the SP from withing the web application (we have a web app in php which is in fact connected to the SQL Server already)
Thanks a million to everyone again...
😉
Juan P. Realini
Buenos Aires, Argentina
Web Developer, .Net Developer,
Windows Applications, SQL Server 2000
"A man's gotta do what a man's gotta do... that is, do all his wife says... "
October 14, 2011 at 11:25 am
how about a different line of attack:
have the email send a link to a specific web page that requres the specific key (ie a GUID) that comes from the email.
http://www.yourCompanyWebsite/SecretStuff/Quickfix.aspx?accesskey=C7EC1335-6AD6-49EB-A5CE-B45DF43EF622
the web page validates that that key has a potentially pending resolution or something,and the page provides the input parameters that you wanted to handle by email.
then the page submission can do/execute whatever the code should be.
missing, old or invalid keys make the page return an error screen without the access to the data input elements.
gawd i read your follow up post, adn that's exactly what i was thinking, with the weblinks tuff...
ID-TEN-T for me again....
Lowell
October 14, 2011 at 11:48 am
Sounds like a good option, though I don't see exactly how the web app will be able to validate the GUID I send by email from SQL Server to the user...
You managed to confuse my already messed up head... ha, ha!
How would you accomplish this?
Thanks!
Juan P. Realini
Buenos Aires, Argentina
Web Developer, .Net Developer,
Windows Applications, SQL Server 2000
"A man's gotta do what a man's gotta do... that is, do all his wife says... "
October 14, 2011 at 11:51 am
When you send the email you put that guid in a table. Then the webpage checks the table for the existence of that guid when it loads. If it doesn't find it, kick them out. Otherwise, it must be a good request so let them enter their data. You might even have a date updated field in that table to prevent using the link more than once.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 11:54 am
Freeman-674288 (10/14/2011)
Sounds like a good option, though I don't see exactly how the web app will be able to validate the GUID I send by email from SQL Server to the user...You managed to confuse my already messed up head... ha, ha!
How would you accomplish this?
Thanks!
well, i was thinking that whatever process that fires off the warning email would also add a NEWID() to a tracking table, with references to the email and some status column that can be later updated as "PROCESSED" or something;
so the web page would validate agaisnt that tracking table, so old emails don't get reprocessed, etc etc.
Lowell
October 14, 2011 at 12:09 pm
Not that I'd recommend it, since SQL Mail goes away when you migrate to SQL Server 2005+, but I actually toyed with using SQL Mail to query a database and return a result set to the user many years ago.
Here is why I never went beyond the toy stage:
Requires you to install the Outlook client on the server
The emails could get hung requiring a restart of the server
As SQL Server 2005 came closer to release, SQL Mail was depreciated
With that said, if you are looking at migrating to SQL Server 2005 in the near future, there is another alternative:
http://pragmaticworks.com/Products/Business-Intelligence/TaskFactory/SSIS-read-email-source.aspx
Not sure how you would use it, but it is there.
October 14, 2011 at 1:04 pm
Lynn Pettis (10/14/2011)
Not that I'd recommend it, since SQL Mail goes away when you migrate to SQL Server 2005+, but I actually toyed with using SQL Mail to query a database and return a result set to the user many years ago.Here is why I never went beyond the toy stage:
Requires you to install the Outlook client on the server
The emails could get hung requiring a restart of the server
As SQL Server 2005 came closer to release, SQL Mail was depreciated
With that said, if you are looking at migrating to SQL Server 2005 in the near future, there is another alternative:
http://pragmaticworks.com/Products/Business-Intelligence/TaskFactory/SSIS-read-email-source.aspx
Not sure how you would use it, but it is there.
Thanks Lynn...
Well, in fact, that is a way I used some time ago, but now I send the emails from inside the Stored Procedures using master's xp_smtp_sendmail system procedure...
I didn't take a look at your link (I will, I promise... it's just that right now I am finishing something else), but I guess SQL 2005 and 2008 must surely have a way to handle this "imaginary" scenario I set up...
I am already working on a Stored Procedure that will be executed from the web app, validating the GUID created in the way Lowell and mentioned earlier in the thread...
As soon as I have it working I will update letting you guys know how it went...
Juan P. Realini
Buenos Aires, Argentina
Web Developer, .Net Developer,
Windows Applications, SQL Server 2000
"A man's gotta do what a man's gotta do... that is, do all his wife says... "
October 14, 2011 at 1:08 pm
Now... don't know If I should do this... but I posted another question yesterday, with an issue that is driving me nuts...
Could any of you take a look and tell me what you think?
http://www.sqlservercentral.com/Forums/Topic1189933-5-1.aspx
Thanks!
Juan P. Realini
Buenos Aires, Argentina
Web Developer, .Net Developer,
Windows Applications, SQL Server 2000
"A man's gotta do what a man's gotta do... that is, do all his wife says... "
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply