How Can I Grab A Notification Email Address From A Job?
Dec 9, 2004
Is there a handy-dandy way for me to be able, from a stored procedure, to capture the notification email address for a user associated with a job?
I need to send an email out from a stored procedure, and I want it to be set up to send to the same user that is set up in the job that calls the stored procedure.
the same notification stored proc will be used in multiple jobs, but the jobs are already set up with a notification email to be sent to the defined users "on completion" (error or not). I want a separate email to be sent, but I want to use xp_Sendmail because I have to perform a select who's output I want to appear in the body of the email (and unless I am missing something, I have no control over the body of the standard "job completion" email).
I would prefer to NOT have to "hard code" the user email address in the stored proc that calls xp_sendmail, OR to put it in some user table in the database, when the user I want to send it to will always be the same one defined in at the JOB level in Enterprise Manager.
Any thoughts? Thanks in advance for your instantaneous and informative answer-packed responses! ;)
View 2 Replies
ADVERTISEMENT
Apr 14, 2006
MSDE 2000? Thanks.
View 2 Replies
View Related
Oct 25, 2005
Can you send these to a group of DBA's
View 1 Replies
View Related
Feb 26, 2007
How can I set up the notification email when one of your jobs got failed using Database mail..
I've already set up the database mail ... but I am not quite sure how to use the database mail to send a notification when a job failed...
View 3 Replies
View Related
May 14, 2008
I need to set up an automatic email notification. Right now, I have a subscription that is working. The subscription runs a report and puts the resulting pdf into a folder. I need a way to notify users (preferrably in an Outlook distribution list) when the pdf is created. I'm amazed that this basic feature is not built into the subscription capabilities.
Can anyone please tell me how to best accomplish this? I'm familiar with vb.net if that helps.
Thank you very much.
View 3 Replies
View Related
Dec 25, 2007
Hello
i am new to sqlserver 2000.i have list of email id in a table. need to fetch that emailid and send mail to the appropriate id's.any body knows how to write job schedule for this task.Ideas are welcome
View 7 Replies
View Related
May 8, 2008
We have an identifier of who has performed an ection which populates a table with their email address in the format firstname.surname@domain. I am looknig for some code to split out the first name and surname (and, if possible, capitalise both) so uor audit report can show the actual name instead of the email address.
Has anyone encountered this issue previously and know a solution?
Many thanks in anticipation.
View 6 Replies
View Related
Jun 24, 2004
This sends a success or fail status and length of execution time can it be extended to send error details when it fails
View 1 Replies
View Related
Aug 10, 2004
I am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).
If anyone could help fathom this out from start to finish i would be really grateful.
View 1 Replies
View Related
Oct 17, 2015
I have created a trigger like this below:
CREATE TRIGGER TriggerName ON Table_Name AFTER INSERT AS
DECLARE @SERVICE varchar(40)
DECLARE @STATUS varchar(3)
SET @SERVICE=(SELECT [SERVICE] FROM inserted)
SET @STATUS=(SELECT [STATUS] FROM inserted)
[Code] ....
When I added some a new record to Table_Name with Status='X' nothing happened. I have tried to exec only script below:
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= 'test', @subject = 'Subject!!!!', @profile_name = 'profil'
and I received email notification.
View 7 Replies
View Related
Nov 13, 2007
Hi All
I just wondered that now SQL Mail is classed as a legacy solution for email, does anyone know how to set up Notification Services to send email once a job has completed e.g. Backup or reindex?
I have tried to find a tutorial but with no joy so far!
Thanks
Gopher
View 2 Replies
View Related
Nov 12, 2007
Hi,
I want to setup a SMTP using gmail. How to configure it, it says,
In your email client software, under Outgoing mail, set the SMTP server to smtp.gmail.com.
Set the your username is yourgooglemailname@gmail.com and make sure "Use username and password" is checked.
Also check off "TLS" under "Use secure connection."
I tried to do this in code:
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
htmlMessage = New MailMessage(From, SendTo, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHTML
mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.NetworkCredential("myemail@gmail.com", "mypassword") 'CredentialCache
mySmtpClient.UseDefaultCredentials = False 'Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
mySmtpClient.Credentials = myCred
mySmtpClient.Send(htmlMessage)
however, i cannot do the turning off of TLS in code. I'm often prompted with this error once i run:
The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must issue a STARTTLS command first
Anyone who knows how to do this?
cherriesh
View 1 Replies
View Related
Mar 12, 2015
I'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1
(EmailAddr VARCHAR(255))
INSERT INTO dbo.Table1 (EmailAddr)
SELECT 'Andy.Smith@gmaddr.com'
UNION ALL
SELECT 'Betty.Lee-Jones@gmaddr.com'
[Code] .....
View 3 Replies
View Related
May 14, 2008
Hello,
I will like to know the best practice for grouping email addresses in a table.
We have a basic table that has email address for admin users. Which goes thus
CLIENTS EMAIL
emailID int
username: varchar 20
Client_email varchar 150
The Above table is for our admin users. Enabling them to send out emails to clients. Based on their bills.
We have a second table for mass mailing. Which has emails in groups. Each group is based on the above entry. And groups could range from 5 users up to 100,000 users.
Whats the best way to implement this.
Do I create a seperate column for each email entry, knowing that this table would be extremely big, with a lot of repeated values, ie username etc. Or group them TOGETHER as varchar(max)
PLEASE ADVICE, best practices
thanks
Mary
Yes O !
View 1 Replies
View Related
Jan 28, 2014
There is a Table DISPLAY_DETAILS in which a Column - DISPLAY_NAME, which displays data in it as :
Joe Barnard(123456);Paul Johnson(114454); - Display as Name(Employee Id).
There is a EMployee Master Table EMP_MASTER Which contains Employee Id and Email Address Columns.
From this: Joe Barnard(123456);Paul Johnson(114454); I need to get the Email Address for these Employee Ids - 123456 and 114454
View 3 Replies
View Related
Jul 4, 2007
Hi,I have 2 fields in a database: 'email' and 'website'.In the majority of cases, the website field is not populated even though theemail address is. In 90% of cases, the website will be 'www.' followed bywhatever is after the '@' symbol in the email address.Would anyone be able to help me with the SQL that would take whatever isafter the '@' sign in the email address, add it to 'www.' and populate the'website' field?Thanks!
View 3 Replies
View Related
Aug 2, 2007
Hi
do you know incase a script which extracts domain names from an email address?
like foreg. we have xyz@dmc.com or abce@dmc-int.com
should give
dmc and
dmc-int
View 7 Replies
View Related
Jun 2, 1999
Good morning,
I have a strange problem with a scheduled task failing with the following:
"Unable to send completion notification email to operator with email name '' for task 2780, 'Scheduled Update'"
This job is the same across several servers and the job runs on the other servers. This is a bit frustrating... I cannot seem to find the difference that is causing the problem.
The funny thing is that I am not using SQLMail or anything to notify anyone regardless if the job succeeds or fails.
Any pointers?
Thanks much,
Don
View 2 Replies
View Related
Apr 14, 2006
is it possible? Thanks.
View 1 Replies
View Related
Mar 6, 2007
within sql server management studio... we have set up a few sql scripts to run at 2am every night (sql server agent > under jobs)... although it has been successful each night, we'd like to setup sql server agent to automatically email success and failure notifications to our email boxes.
i see under the properties of sql server agent > jobs > properties of a job > there is section that says Notifications but then it doesnt really have a place to type in which email we want to send them to? some reading material or instructions greatly appreciated
View 5 Replies
View Related
Aug 18, 2007
Hi,
My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.
Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?
Please help me,
Thank you very much,
sp_configure 'database mail xps', 1
GO
reconfigure
GO
-------------------------------------------------------------
-- Database Mail Simple Configuration Template.
--
-- This template creates a Database Mail profile, an SMTP account and
-- associates the account to the profile.
-- The template does not grant access to the new profile for
-- any database principals. Use msdb.dbo.sysmail_add_principalprofile
-- to grant access to the new profile for users who are not
-- members of sysadmin.
-------------------------------------------------------------
DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128);
-- Profile name. Replace with the name for your profile
SET @profile_name = 'TestProfile';
-- Account information. Replace with the information for your account.
SET @account_name = 'vdang';
SET @SMTP_servername = 'smtp.cgdnow.com';
SET @email_address = 'vdang@cdgnow.com';
SET @display_name = 'Vinh, Dang';
-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1);
GOTO done;
END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ;
GOTO done;
END;
-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;
DECLARE @rv INT;
-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ;
GOTO done;
END
-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;
-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;
IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;
COMMIT TRANSACTION;
done:
GO
View 3 Replies
View Related
Sep 26, 2007
I am in process of setting up job failure notification and one of the requirements that I have is to send notification to multiple users. I would appreciate any suggestion. One of the possible ways would be to create some sort of group account on exchange server with all users added as members. Is there anyway though where I could use users' individual accounts and based on that forward notification to them?
Thanks
View 5 Replies
View Related
Jan 24, 2008
We are maintaining a database of drivers, and we are looking for a script or procedure that will automatically notify certain users of the list of drivers who are in need of a recheck or driver's license has expired.
I am new to this , so any help you can give would be greatly appreciated
View 1 Replies
View Related
Feb 13, 2008
i ve a filed named "Email" of datatype .i ve created this field to insert email address in this field.but when i insert an email address like "bilal@yahoo.com" from a textbox wh is in an ASP.net webpage then in the filed i see this "System.Web.UI.WebControls.TextBox".
can u plz tell me how to fix this
Regards
Ahmed Bilal Jan
View 3 Replies
View Related
Oct 1, 2007
Could this query be made more efficient?
It takes ages to run it. It strips of everything from an email address and keeps only the domain.
SUBSTRING(Mailaddress, CHARINDEX('@', Mailaddress) + 1, CHARINDEX('.', SUBSTRING(Mailaddress, CHARINDEX('@', Mailaddress) + 1, 100)) - 1) AS mail_domain
View 4 Replies
View Related
Nov 30, 2006
this is what i am looking for,
A client with an email address that does not end in uk cannot make a room booking
Any ideas
View 9 Replies
View Related
May 10, 2007
ms sql server
data:
idno Email Category
1 a@a.com c
2 b@b.com c
3 b@b.com c
4 b@b.com c
only unique email address record are requierd.
1 a@a.com c
2 b@b.com c
what could be the query for such requirements.
View 5 Replies
View Related
Apr 11, 2007
(NOTE: FROM a NON-IT PERSON) We've implented SQL 2005 and Reporting Services, but our subscriptions only work internally: when we try to send reports via subscriptions to valid external email addresses, we get a message: invalid email address and the entire subscription does not work.. PLEASE ADVISE. IT WILL BE GREATLY APPRECIATED!
View 1 Replies
View Related
Jun 26, 2007
I would like to use ISS to extract the user name and email address out of the active directory. I would like to put it in an MS-SQL 2005 table that I can use stored procedures on. I am hoping I can use a query to do it but I understand there is a 1000 entry limit on the extraction of the Active Directory and I have more than 1000. Could someone point me in the right direction please. Thanks.
View 1 Replies
View Related
Sep 21, 2015
I just moved our SSRS server from one machine to another. It was sending our report emails from a service account. So when my end users get the emailed report it was from a service account (reports@blah.com) .
Now that I moved the DB to another server - the reports get emailed successfully - but they are being sent from my email address!
I have checked the rsreportserver config file. I also checked in the report server smtp setup and they both show the service account. I even checked the subscription owners and accounts.
SSRS is using my account, but I can't find where it's getting it from. Where else can I look?
View 3 Replies
View Related
Sep 12, 2007
Hi,
I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.
How do I get the start time and end time?
thanks a lot
cherriesh
View 6 Replies
View Related
Aug 15, 2002
how do I set up a SQL2000 server to send an alert to both an email address and a pager when a specific event occurs..please help!!! The server is in a different location and we need notification of problems!! thanks
View 1 Replies
View Related
Mar 11, 2014
i have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change.
dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk
Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;
dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;
dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;
dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
w:http://www.yahoo.com/faqs;
View 4 Replies
View Related