How to add email address in a t sql code
-
Hi,
i hav t sql code like
insert into....
select ..
from...
where..
order by..
It runs fine and inserts the items into the table.
I want the code to send email to speific persons in my company, after the items are successfully inserted.
How could i do that.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP -
You could use database mail.
If you are inserting from a stored procedure, you could add the code there, otherwise you could send the email from a trigger.
-- Gianluca Sartori
-
Extremely useful post, thanks for sharing!!..
-
I am using sql 2005 sp3..
Do u mean , tht i shld write a stored procedure or i can just do tht in t sql code.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP -
You could do both. Personally, I would write a stored procedure.
-- Gianluca Sartori
-
if your code is centralized, meaning all inserts occur in a single point of entry, like a stored procedure, then adding the email code to that stored procedure makes sense.
if the inserts are ad hoc, meaning that some application performs the INSERT from within it's internal code, then you either want the application to do the email, or you want a trigger to use database mail to do the email.
here's a really lame example:
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
GO
ALTER TRIGGER TR_WHATEVER_NOTIFICATIONS
ON WHATEVER FOR INSERT
AS
BEGIN
SET NOCOUNT ON
--gather the information, making sure you get it from the INSERTED virtual table, and not the full table
DECLARE @CAPTUREDSTRING VARCHAR(max)
--In this example i want a comma delimited list of important facts about what was inserted.
--using the xml technique to make my comma delimited string.
SELECT @CAPTUREDSTRING = [Skills]
FROM (
SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + DESCRIP
FROM INSERTED s2
--WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below
WHERE 1 = 1
ORDER BY DESCRIP
FOR XML PATH('')
),1,1,'') as [Skills]
FROM INSERTED s1
GROUP BY s1.WHATEVERID --- without GROUP BY multiple rows are returned
ORDER BY s1.WHATEVERID) myAlias
--now email the results.
declare @body1 varchar(4000)
set @body1 = 'New Item Notification on the Whatever Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ '
<P> The following new items were inserted into the table:<P>'
+ @CAPTUREDSTRING
+ '
'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default Mail Account',
@recipients='lowell@somedomain.com',
@subject = 'New Item Notification',
@body = @body1,
@body_format = 'HTML'
END --TRIGGER
GO
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
This is my code...
i am gettingg error "Incorrect syntax near @message..
create procedure dbo.rtw1
@receipent nvarchar(100),
@message nvarchar(30)
as
begin
INSERT INTO IV00104
(ITEMNMBR,
SEQNUMBR,
CMPTITNM,
CMPITUOM,
CMPITQTY,
CMPSERNM)
SELECT
IM.ITEMNMBR,
16384,
LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),
UL.UOFM,
1.00000,
0
FROM
IV00101 IM LEFT JOIN IV00104 KT ON IM.ITEMNMBR = KT.ITEMNMBR
JOIN IV40202 UL ON (IM.UOMSCHDL = UL.UOMSCHDL AND
(RIGHT(LTRIM(RTRIM(IM.ITEMNMBR)), LEN(LTRIM(RTRIM(IM.ITEMNMBR))) -
(CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR)))))) = UL.UOFM)
WHERE
IM.ITEMNMBR != '_' AND
IM.ITEMTYPE = 3 AND KT.ITEMNMBR IS NULL
ORDER BY
IM.ITEMNMBR
exec msdb.dbo.sp_send_dbmail
@receipent = 'email@removed ';
@message = 'Kitpriceschange';
end
go
Regards
Sushant Kumar
MCTS,MCP -
the parameters are comma delimited, you have a semi colon before @message.
change to a comma.
also, this param is spelled wrong:
@receipent
and i think you might need more parameters; see the example i pasted for an example.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
This is wat from ur example:-
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default Mail Account',
@recipients='lowell@somedomain.com',
@subject = 'New Item Notification',
@body = @body1,
@body_format = 'HTML'
@profile_name, do we have to mention hte sql domain account from wich its sending mails?
@recipients , if i want to add more persons, then i shld add by a comma or semi colon, for ex xyz@rtw.com;xyz1@rtw.com or wid a ,
@body, if i want the output of hte table to be body, wat shld i enter there
Ya, i changed to comma and error went..
Thxs
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP -
you have to have setup database mail before you can use it.
in SSMS, under the "management" folder, find Database Mail
you have to create a profile, and also at least one account.
I have three profiles for reference.
"Default Mail Account"
"Lowell Private Email"
"Google Gmail Account"
it's wizard based and very straight forward i think.
after creating the profile name, here's an example of my account setup:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ya its already created by my name...
Ok, i got it , wat abt other queries i asked.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP -
--a query as an attachment to the email
declare @body1 varchar(4000)
set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' '
EXEC msdb.dbo.sp_send_dbmail
@profile_name='stormrage as scripts',
@recipients='lowell@somedomain.com',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'qry.txt',
@query_result_no_padding = 1
--a query as inside the body of an html email: note this looks like crap, it's not formatted nicely at all.
--read Format query output into an HTML table - the easy way - Tony Rogerson's technique for a better solution:
--http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspx
declare @body1 varchar(4000)
set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
'
<P>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='stormrage as scripts',
@recipients='lowell@somedomain.com',
@subject = 'SQl 2008 email test',
@body = @body1,
EXEC msdb.dbo.sp_send_dbmail
@profile_name='stormrage as scripts',
@recipients='lowell@hdsoftware.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT name AS [<BR>name],type_desc,create_date,'<BR>'' As [Filler<BR>] from sys.objects where type=''U''',
@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 0,
@query_result_no_padding = 0
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I can't get 1 thing, how to put the value of the tsql code (result is a table) in hte content of the email...
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP -
the code i posted above has both examples: pasting the results of a SQL inline the html body, or as an attachment.
take a look at the lower example i pasted, and specifically this section:
EXEC msdb.dbo.sp_send_dbmail
@profile_name='stormrage as scripts',
@recipients='lowell@hdsoftware.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT name AS [<BR>name],type_desc,create_date,'<BR>'' As [Filler<BR>] from sys.objects where type=''U''',
@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 0,
@query_result_no_padding = 0
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
When i put my code in the @query = '.......'
It says must declare the scalar varaiable @body1
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply