March 5, 2009 at 8:37 am
Hi Jack,
Running:
sys.sp_configure@configname = 'Database Mail XPs'
Returned:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
Sam Marsden
March 5, 2009 at 8:41 am
The code you posted is missing a space between sp_configure and @configvalue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2009 at 8:45 am
Jack Corbett (3/5/2009)
The code you posted is missing a space between sp_configure and @configvalue.
I ran it exactly how you gave it to me and it returned an error so i removed the space and it returned a different one. Before we go any further, after quickly reading Intro to Database Mail in SQL 2005 i have noticed that the server is only running the express edition of SQL server 2005, am i wrong or it this going to cause me some problems?
Sam Marsden
March 5, 2009 at 9:04 am
mmmmmmm ran:
sp_configure @configname = 'Database Mail XPs'
returned:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79
The configuration option 'Database Mail XPs' does not exist, or it may be an advanced option.
Valid configuration options are:
any ideas?
Sam Marsden
March 5, 2009 at 9:06 am
sam.marsden (3/5/2009)
The configuration option 'Database Mail XPs' does not exist, or it may be an advanced option.
You need to enable advanced options first
exec sys.sp_configure 'show advanced options', 1
reconfigure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 9:10 am
Ahhh, Express edition does not support Database Mail, http://msdn.microsoft.com/en-us/library/ms165636(SQL.90).aspx.
In this case you would need an outside process to generate the email. I would probably create an email_queue (id, Call_Num, processed) table that I would populate using a trigger. Then I'd create a Windows service that creates and sends the emails.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2009 at 9:14 am
pants lol, oh ok so still use a trigger to populate my new table and then somehow auto send the email from there?
Sam Marsden
March 5, 2009 at 9:18 am
That's how I'd do it with Express edition. Someone else may have a better solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2009 at 9:22 am
ok cool, well in that case what i'm after is a trigger to cope the fields from one table to another lol
Sam Marsden
March 5, 2009 at 9:37 am
This assumes you have created a table called MailQueue that has, at minimum columns ToAddress, MailSubject and MessageBody.
This is to give you an idea, it will obviously need modifying
CREATE TRIGGER trg_SendEmail ON SCCall AFTER INSERT
AS
BEGIN
INSERT INTO MailQueue (ToAddress, MailSubject, MessageBody)
SELECT Cust_Email, 'A problem has been logged to you', 'Problem ' + Call_Calt_Code + '. Description: ' + Call_Problem + '. Please resolve at your earliest convenience'
FROM SCCust INNER JOIN inserted i on SCCust.Cust_Num = i.Call_Cust_Num
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 9:41 am
Create a table that's something like this:
create table dbo.EmailQueue (
ID int identity primary key, -- Just to have something here
DateAdded datetime not null default(getdate()), -- When was it added
SendTo varchar(100) not null, -- E-mail of intended recipient
constraint CK_SendTo check(SendTo like '%_@_%._%'), -- Simple format check
SendCC varchar(100) null, -- Any desired CCs
constraint CK_SendCC check(SendCC like '%_@_%._%' or SendCC is null), -- Simple format check
Subject varchar(100) not null,
Body varchar(max),
DateSent datetime null);
That's you're e-mail queue. You can modify the basic idea here as needed, but you'll probably want at least all of these columns. Others you might include would be a column for error codes and/or a column for the status of the e-mail (pending, sent, error sending). Those are up to you and your needs.
Then you'll need to build a process that will go through the table and send the e-mails, and mark the DateSent column, plus any status or error columns you might add.
To insert data into the table, you might have a trigger like:
create trigger MyTable_EmailQueue on dbo.MyTable
after insert
as
insert into dbo.EmailQueue (SentTo, Subject, Body)
select
MyRelevantEmail,
'Insafe Helpdesk - New Call Logged ' + [Call_Num],
[Call_Num] + '
' + [Call_Site_Post_Code] + '
Response requested: ' + [Call_Calt_Code] + '
' + [Call_Problem]
from inserted;
I don't know how you'll connect the data to the relevant e-mail address in your system. Probably a join to another table based on the call data.
Does that help?
- 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
March 5, 2009 at 10:01 am
thats great guys thank you for your help i think i'll be able to cope - there's just one last thing that baffling me - how will i transfer the customer email field from the SCCust table into the mail queue table as the email field doesn't appear anywhere in the sccall table?
Sam Marsden
March 5, 2009 at 10:08 am
sam.marsden (3/5/2009)
thats great guys thank you for your help i think i'll be able to cope - there's just one last thing that baffling me - how will i transfer the customer email field from the SCCust table into the mail queue table as the email field doesn't appear anywhere in the sccall table?
If you look at the sample trigger I wrote, you'll see that I join the inserted table to the customer table to get the email.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 10:10 am
ah thanks Gail - that'll teach me for being a plank!
Sam Marsden
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply