send mail from sproc
-
Okay,
What I decided to do was create a sproc that would gather the data...Then I created a DTS package that:
1. Connected
2. Run SQL Task ( EXEC uspSproc)
3. Then send e-mail
4. Log results and notify me how it went. (In so many words)
Now here's my question in the following code I am trying to accomplish,
1. emailing the rep only one list that "belongs" to them not several emails w/ one record in it.
and (now here's where it gets fancy, lol)
2. allow the rep to click a link that (in the email) that will send a variable to and call an aspx page that pre populates w/ the demographic info (the results set that was e-mailed).
More info:
Its a renewal form and I need to not only pass a variable for the rep's email but also pass a variable to the aspx page so that it pre-populate the form...
Can this be done in SQL (especially in the scenario I described in the beginning) and is the following code even close?
DECLARE @aeid varchar (5)
DECLARE @brokers_id varchar (5)
while @aeid = (SELECT distinct whole_rep_id from tExpLic) AND @aeid <> ' '
while @brokers_id =(select distinct brokers_id from tExpLic) AND @brokers_id <> ' '
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = @aeid,
@query ='dbo.uspGetExpBrokerLic @aeid',
@attachments ='http://webserver/ourcomp_intranet/sec_market/jim_report/profit_report/renew_your_license.aspx @brokers_id',
@copy_recipients ='cdixon@ameritrust.com;skalbag@ameritrust.com',
@subject = 'Folks With Expired License',
@message = 'This is your list of people whose license has expired in a particular state'
END
** Am I even close to achieving what I am trying to do? I am not locked into this solution and am open to other creative, applicable ideas!
TIA,
Christine
Aurora
-
Could you post a small sample of the tExpLic table (and other tables if they are important) with a few rows of sample data that reperesents the nature of the data you have at hand? It would help determining how to build the mail for each rep.
Haven't used xp_sendmail in ages, but I think that by placing the url in the @attachments variable you're attaching (or trying to) the actual physical asp file rather than just providing the link for it.
/Kenneth
-
Uh... not sure I understand everything. Lets try. You have (for simplicity) data about drivers working in 2 companies. At a certain moment, you check for all drivers that have expired license. Each of the two companies then should receive one mail, containing list of their employees whose license needs to be reviewed (list will show only their employees, and all of them in one mail). Is that what you need? It would mean, that every mail has always only one recipient (i.e., it can be several mail addresses actually, but only if there are several addresses listed in your DB for certain company ID)... or else there have to be some criteria how to group several reports into one.
As to the posted code, @query needs to contain a valid SQL statement, like SELECT something FROM sometable. Never tried using SP in @query, if it would work at all then maybe if you write 'EXECUTE dbo.uspGetExpBrokerLic @aeid' - but I don't think it is valid.
Generally, based on how I understand it, I would prefer to build the entire system a bit differently : First, run stored procedure once for ALL rows (no limitation by aeid). Store the results in a table (e.g. Results). Use a view or select with WHERE condition to pull only rows pertinent to a certain company when sending the mail. The mail-sending procedure could be something of this kind (sorry about pseudo code, hope you'll understand it):
DECLARE all the variables
SELECT DISTINCT aeid FROM Results WHERE sent = 0
START LOOP on @aeid (take one aeid after another from the result of previous select)
SET @brokers_id = SELECT brokers_id FROM contacts WHERE aeid = @aeid
SET @Email = SELECT email FROM contacts WHERE aeid = @aeid
SET @link = 'http://webserver/ourcomp_intranet/sec_market/jim_report/profit_report/renew_your_license.aspx' +CAST(@brokers_id AS varchar(20))
SET @SQL = 'SELECT name, pers_no, age, address FROM Results where aeid = ' + CAST(@aeid as varchar(20))
EXEC master.dbo.xp_sendmail
@recipients = @Email
@query = @SQL
@subject = 'Folks With Expired License',
@message = 'This is your list of people whose license has expired in a particular state' + CHAR(13) + @link
UPDATE Results SET sent = 1, datesent = GETDATE() WHERE aeid = @aeid
END OF LOOP on @aeid
I never tried to insert an active link into a message body, but I think you could (same way as compiling the @SQL) compile the @message to include their code. However, it depends on your and their mail settings, whether the link will appear and work as required. If the recipient treats everything as plain text, well, then you're out of luck.
AFAIK you can not use @attachment for this - when sending the mail, xp_sendmail will try to attach the file to the message (not the link, but a physical file). I may be wrong though, never tried this way.
Please understand that I'm only trying to describe the basics and don't know anything about your data structure...
-
Thanks guys for your help...here's how we decided to tackle the issue, forgive my simplistic explanation:
I developed a sproc that will gather the data along with the rep_id....Then my developer developed a report that is accessed via our intranet, inside the code [C#], she calls the stored procedure, which produces a list, it then sends an e-mail to each rep (she used a while loop) which if you click on the brokers name (link) it pre-populates the license renewal form. Pretty cool huh...I did get xp_sendmail to work, though...but it did not look nearly as nice! Nor could I (from within SQL, that I know of) offer the link that would pre-populate a form (very handy, our reps love it)...
I still however, have a task that requires the same concept...I will need to send a list to our reps on a monthly basis of companies that have not ordered from us in 90 days...
Now here's what I've found out:
You can call a sproc in the @query rather then write out the sql...
And yes, you do need the EXEC before it, that was a typo on my part.
but when I run the stored procedure [here's where I need help];
should I store the results in a table, save it as a file of some sort then xp_sendmail @attachments = myresults.doc
AND
I still need to send only the results pertinent to a particular rep...In the table ALL the reps data resides...We discussed a loop but I have yet to get it to work...Here's the code, any recommendations are welcomed...
FYI, this rpt. does not require any forms to be pre-populated so the method we used above is not required...
DECLARE @aeid varchar (5)
DECLARE @emaillist varchar (35)
while @aeid = (SELECT distinct whole_rep_id from tNotCLosed) AND @aeid <> ' '
SET @emaillist = (SELECT distinct employ_email from tNotClosed WHERE whole_rep_id = @aeid)
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = @aeid,
@query = 'EXEC dbo.uspGetCompThatHaveNotOrdered',
@copy_recipients = 'me@myemail.com',
@attachements ='SomeTypeOfFile.doc'
@subject = 'Companies Who Have Not Ordered in 90 Days',
@message = 'This is your list of Companies who have not ordered in 90 days, 30 days from today, their status will be marked as inactive in the database'
END
Let me know what you guys think...
Christine
Aurora
-
OK, about the loop... you have to define start and end, and a way to loop (increase some counter). I can hardly give you a working SQL without knowing your structure, and unfortunately I'm a bit confused by you using @aeid once as report ID and once as emaillist in @recipients, but ... I'll try:
CREATE TABLE #loop (counter INT IDENTITY, aeid VARCHAR(5))
DECLARE @cntr INT
SET @cntr = 1
INSERT INTO #loop (aeid)
SELECT DISTINCT whole_rep_id from tNotClosed
--here starts the loop
WHILE @cntr < (SELECT max(counter) FROM #loop)
BEGIN
SET @aeid = (SELECT aeid FROM #loop WHERE counter = @cntr)
SET @emaillist = (SELECT email FROM somewhere WHERE aeid = @aeid) /*caution - you need a single value here - if you need several addresses, they have to be concatenated into one varchar delimited with semicolon; for this, UDF is easiest solution. This code could return several rows, which is not acceptable.*/
EXEC master.dbo.xp_sendmail
@recipients = @emaillist,
@query = 'EXEC dbo.uspGetCompThatHaveNotOrdered',
/*now I take it you have to differentiate to whom it will be sent and send only pertinent data; that is, you can not simply call a SP without any parameters. You should incorporate @aeid somehow here, or another value that can be derived from the aeid.*/
@copy_recipients = 'me@myemail.com',
@attachements ='SomeTypeOfFile.doc'
@subject = 'Companies Who Have Not Ordered in 90 Days',
@message = 'This is your list of Companies who have not ordered in 90 days, 30 days from today, their status will be marked as inactive in the database'
SET @cntr=@cntr+1
END
OK, this is generally how a loop can be done, trying to stick as close to your problem and environment as possible, given the limited info I have. Some more hints... If you want to send files, probably the easiest way is to create HTML files. You can use procedure sp_makewebtask for this, and then you can attach the created file to the mail either as attachment, or insert it into the body (if size of the file allows that).
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply