January 22, 2009 at 6:41 pm
PG (1/22/2009)
GilaMonster, GSquared, Jeff Moden,You guys are killing me here 😛 !!! I mean, "Pork chop thru the nose", now that's funny :w00t:.
To quickly end the suspense, I am a less than self-taught SQL noob trying to expand my SQL skillset and allow the database to do as much work as it possibly can. My primary role is developing ASP Intranet Web Applications. Ever since I learned how to create a database-driven web application, I have been using SQL.
Well then... I can say I definitely like your attitude. 🙂 I've got a couple of goodies to help you in your endeavors on how to "concatenate" email address (GSquared just demo'd a method above) and how to "parse strings" AND use those parsed strings to create normalized tables from the data...
Let's get you started on the "concatenation" thing and some of the pitfalls you can run into in the process... please take a gander at the following article...
[font="Arial Black"]Performance Tuning: Concatenation Functions and Some Tuning Myths[/font][/url]
Shifting gears, in order to be able to do some really high speed splits, you need to "avoid the loop". To do that, lemme introduce you to one of my favorite tools. I didn't invent the bloody thing, but I think you'll enjoy the explanation of how it works in the following article...
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
[/font][/url]
You could probably get away with just that article on doing splits... but, since you also work with the GUI, here's an article that just might help you pass parameters from your GUI to a stored proc...
Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays
[/font][/url]
And, if you like those, you might like some of the other "SQL Skills" I wrote about...
[font="Arial Black"]http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/[/font][/url]
... heh... or not.
As you've already found out, we have lots of fun on this forum and there's a lot of great folks like the ones you've already met all the way from Sergiy's extraordinary dry sense of humor all the way up to me blowing a pork chop out of my nose. 😛
When you get a chance, I'd love to see what you have for the CdoSysSendMail proc you talked about. I just got done putting such a thing into some legacy 2k systems that don't have MAPI or any of that good stuff for email with Lotus Notes as the target email server.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2009 at 11:18 am
Many thanks go out to everyone who contributed to this thread!!!
I now have a working INSERT TRIGGER that accurately identifies and concatenates the email
addresses of the Category Experts and sends out only one (1) email each time a record is INSERTed.
Here is the final beast, eagerly waiting for your Expert Suggestions on
"How To Do It Better or More Efficiently".
CREATE TRIGGER trtblSuggestionsCreateDate ON dbo.tblSuggestions
FOR INSERT
AS
SET NOCOUNT ON
declare @tmpuid uniqueidentifier,
@catuid uniqueidentifier,
@category nvarchar(50),
@xName nvarchar(100),
@email nvarchar(100),
@prob nvarchar(4000),
@solu nvarchar(4000),
@crlf char(2)
SELECT @crlf = CHAR(13) + CHAR(10)
DECLARE inserted_suggestion_cursor CURSOR FOR
Select tblSuggestions.Suggestion_ID, tblSuggestions.Category_ID FROM tblSuggestions
INNER JOIN Inserted ON tblSuggestions.Suggestion_ID = Inserted.Suggestion_ID
OPEN inserted_suggestion_cursor
FETCH NEXT FROM inserted_suggestion_cursor
INTO @tmpuid, @catuid
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE suggestion_info_cursor CURSOR FOR
Select c.Category, e.Email, s.Problem, s.Solution
From tblSuggestions s Join tblCategories c
ON s.Category_ID = c.Category_ID Join tblExpertsCategories x
ON c.Category_ID = x.Category_ID Join tblExperts e
ON x.Expert_ID = e.Expert_ID
WHERE s.Category_ID = @catuid AND s.Suggestion_ID = @tmpuid
OPEN suggestion_info_cursor
FETCH NEXT FROM suggestion_info_cursor
INTO @category, @email, @prob, @solu
--WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tblSuggestions SET tblSuggestions.CreateDate=getdate()
WHERE tblSuggestions.Suggestion_ID = @tmpuid
DECLARE @From varchar(100)
SET @From = 'FORMS@domain.com'
--Slightly modified version of GSquared's string concatenation
DECLARE @To varchar(1000)
SET @To = ''
SELECT @To = @To + CASE WHEN LEN(@To)>0 THEN ';' ELSE '' END + Email
FROM tblExperts
INNER JOIN tblExpertsCategories
ON tblExperts.Expert_ID = tblExpertsCategories.Expert_ID
WHERE Category_ID = @catuid
SELECT @To = @To + ';Copied@domain.com'
DECLARE @Subject varchar(200)
SET @Subject = 'Subject'
DECLARE @Body varchar(4000)
SET @Body = 'Concerning Subject Category: ' + @category + @crlf + @crlf +
'EMAIL DESTINATION: ' + @To + @crlf + @crlf +
'PERCEIVED PROBLEM: ' + @prob + @crlf + @crlf +
'SUGGESTED SOLUTION: ' + @solu
--sp_send_cdosysmail from "How to send e-mail without using SQL Mail in SQL Server"
--http://support.microsoft.com/kb/312839
EXEC master.dbo.sp_send_cdosysmail @From,@To,@Subject,@Body
UPDATE tblSuggestions
SET tblSuggestions.Status_ID = 1, tblSuggestions.ContactInfo = '''' + @To + ''''
WHERE tblSuggestions.Suggestion_ID = @tmpuid
END
CLOSE suggestion_info_cursor
DEALLOCATE suggestion_info_cursor
END
CLOSE inserted_suggestion_cursor
DEALLOCATE inserted_suggestion_cursor
SET NOCOUNT OFF
sp_send_cdosysmail from http://support.microsoft.com/kb/312839
January 23, 2009 at 2:17 pm
PG, couple o suggestions.
1. Avoid using triggers. At least without a good advice. Your SQL knowledge and technics are not good enough for that.
When every your procedure will return instant results even on big recordsets and never cause a deadlock then you may try triggers.
2. NEVER use cursors in triggers.
3. NEVER use external calls in triggers. Something wrong with your email server - and whole your system is deadlocked.
For your task:
- create staging table for data to be emailed;
- modify your trigger to populate records in that table. I guess the query you used for the cursor declaration is the right one for INSERT statement;
- create a job which will loop through records in that table and send emails one by one. Schedule it to run every minute.
_____________
Code for TallyGenerator
January 23, 2009 at 2:19 pm
The first thing that comes to mind is why select from the target table joined to "inserted", when you can just select from "inserted"?
The next thing is that you could replace the cursor with an insert statement that would insert the e-mail data into a table, and have a scheduled job go through that table every few minutes and send the necessary e-mails. That way, if there's any problem with the e-mail code, your trigger doesn't hang up and block the inserts.
Third, I'd replace a chunk of the code in the trigger with a function, something like this:
create function ExpertEmails
(@CategoryID_in uniqueidentifier)
returns nvarchar(1000)
as
begin
declare @Emails_out nvarchar(1000)
select @Emails_out = coalesce(@Emails_out + ';' + Email, Email, @Emails_out)
from dbo.tblExperts e
inner join dbo.tblExpertsCategories ec
on e.Expert_ID = ec.Expert_ID
where Category_ID = @CategoryID_in
return @Emails_out
end;
Then you can use that as part of your insert into the queue table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 6:11 pm
Man, I blew another pork chop out my nose when I saw the trigger and the cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2009 at 9:55 am
Jeff Moden (1/23/2009)
Man, I blew another pork chop out my nose when I saw the trigger and the cursor.
WAIT WAIT WAIT.
Blew a pork chop out of your nose?
So.... these pork chops that you seem to have an inexaustible supply of... you never mentioned that they had already been EATEN. Suddenly I no longer feel bad about wasting pork chops.
January 24, 2009 at 12:34 pm
No, no... that's not it... I put them in my mouth to get them all lubricated up before I put them in the cannon. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply