Solved

Script to Remove Email Address of Notification User

Posted on 2012-04-04
26
422 Views
Last Modified: 2012-04-09
I'm not a DB and have to fill in for our DB who just left. Any way we have multiple test and production SQL 2005 - 2008 servers and a total of 100 jobs with some server having 10+ notification users each. Is there a way for me to search a sql server for the user Notification name or part of the name the remove it and it associated email address and replace it with a new name and email address
0
Comment
Question by:compdigit44
  • 13
  • 13
26 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37808055
Check the msdb.dbo.sysoperators table.  It holds the Operators information.  You should be able to execute an UPDATE against it to change the info you need.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 37808076
OK , here is the problem though I'm not a DB and not good with t-sql what would I need to type in in order to do what i listed above in my main question
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37808135
First check the contents of the table (below) and make sure it the record you are looking for is there.
select * from msdb.dbo.sysoperators

Open in new window

Then you can update the record as follows: (Try it out on a non-production DB first to make sure it works as you expect)
update msdb.dbo.sysoperators
set name = '<< New operator name >>', email_address = '<< new email address >>'
where email_address = '<< old email address >>'

Open in new window

0
 
LVL 19

Author Comment

by:compdigit44
ID: 37810704
I'm getting the following error message: "Cannot insert duplicate key row in object 'dbo.sysoperators' with unique index 'ByName'"
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37812151
It seems you are trying to update the Name to a name already on the table.  Name must be unique.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 37812290
now can the script be build to replace the the email address of <old email person's name> with new one ..

For email user and reference to John Does <jdoe@company> would be updated to "SQL Notify Name" <sqldlgroup@company.com> regardless is reference to the new name and email already exist
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37812429
No, if "SQL Notify Name" is already there the only option is to delete the "John Does" entry.

Here is a better way to do that.  It uses a couple system stored procs.
USE [msdb]
GO
-- Update 'John Does' to 'SQL Notify Name'.
--    If 'SQL Notify Name' is already an operator, then 'John Does' is just deleted.
IF  EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = 'SQL Notify Name')
    EXEC msdb.dbo.sp_delete_operator @name='John Does'
ELSE
    EXEC msdb.dbo.sp_update_operator @name='John Does', @new_name='SQL Notify Name', @email_address='sqldlgroup@company.com'
GO

Open in new window

0
 
LVL 19

Author Comment

by:compdigit44
ID: 37812701
Here is another problem. The old users name is tied to operators with multiple names for example of our operators are listed as..

User1, users 2 etc..

How can this scrupt search of any reference to the old users name and change it to whatever
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37812735
Yes, a script can handle that.  But in order to craft the script correctly I would need some sample data with the current Name / Email and the corresponding new Name / Email address.

Something like this:

Current Name/Email: User 1 / user1@company.com
New Name/Email: SQL Notify Name 1 / sqldlgroup@company.com

Current Name/Email: User 2 / user2@company.com
New Name/Email: SQL Notify Name 2 / sqldlgroup@company.com

etc.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 37812782
Does this help....

Current Name/Email: John Doe / jdow@company.com
New Name/Email: SQLDLGroup/ sqldlgroup@company.com

I want to change *all* references to John Doe to be SQLDLGroup name and email address
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37812872
Hopefully this will work for you.  It searches the old user by the email address, then updates the name/email address handling duplicates with a sequence on the name, for example: SQL DL Group (1), SQL DL Group (2), etc.
declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where email_address = 'jdow@company.com'
     order by id
  
update [msdb].[dbo].[sysoperators]
   set name = 'SQL DL Group (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'sqldlgroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id

Open in new window

0
 
LVL 19

Author Comment

by:compdigit44
ID: 37812918
Zero row's were updated...

The old users id remains and well as their email address
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37812943
It's hard for me to provide the correct script without seen the actual data.  Let's try again.  This version searches the user by name:
declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where lower(name) like lower('John Doe%')
     order by id
  
update [msdb].[dbo].[sysoperators]
   set name = 'SQL DL Group (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'sqldlgroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Author Comment

by:compdigit44
ID: 37812979
Samething "0" rows updated
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37812996
Are you replacing the name and email address on the script to the actual values?
0
 
LVL 19

Author Comment

by:compdigit44
ID: 37813035
yes..below is what I'm basically doing..



declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where lower(name) like lower('Joe1 Doe1%')
     order by id
 
update [msdb].[dbo].[sysoperators]
   set name = 'SQLGroup (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'SQLGroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37813046
OK. I see.  Please try this:
declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where lower(name) like lower('Joe%Doe%')
     order by id
 
update [msdb].[dbo].[sysoperators]
   set name = 'SQLGroup (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'SQLGroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id

Open in new window

0
 
LVL 19

Author Comment

by:compdigit44
ID: 37813081
I had to change this line: where lower(name) like lower('Joe%Doe%')

to

where lower(name) like lower('%Joe%Doe%')

We are really close the script ran and update the fields but it over wrote the operator name instead of finding and replacing
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37813102
Regarding "but it over wrote the operator name instead of finding and replacing", please provide more details.  What it updated to? And, what was the expected result?
0
 
LVL 19

Author Comment

by:compdigit44
ID: 37813116
For exmample i had an operator called:

User1, Joe Doe

After I ran the script which was set to look for Joe Doe's ID

the operator now was replaced with the new SQL DL group name

SQLGroupName

The goal was to be:

User1, SQLGroupName
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37813793
OK. I think I have a better understanding of the problem now.  Let's try one more time:
update [msdb].[dbo].[sysoperators]
   set name = rtrim(replace(replace(name, 'Joe', 'SQLGroup'), 'Doe', '')),
       email_address = 'sqldlgroup@company.com'
  where lower(name) like lower('%Joe%Doe%')

Open in new window

0
 
LVL 19

Author Comment

by:compdigit44
ID: 37816134
This is almost perfect... The operator display changes or appends the old users name with the new DL group but it does append the email address. It overwrites it with the DL group.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37816283
Please indicate the current email address value, and expected email address value after the update.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 37816450
For Example..

Operator ID = user1,Joe Doe
email address = user1@copmany.com, JDoe@company.com

The current script set the email address to look like the following:
email address = sgql@company.com

Intended outcome should be: user1@comany.com,sqldl@company.com
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 37816532
OK. Please try:
update [msdb].[dbo].[sysoperators]
   set name = rtrim(replace(replace(name, 'Joe', 'SQLGroup'), 'Doe', '')),
       email_address = replace(email_address, 'JDoe@company.com', 'sqldlgroup@company.com')
  where lower(name) like lower('%Joe%Doe%')

Open in new window

0
 
LVL 19

Author Comment

by:compdigit44
ID: 37823095
It seams to be working perfectly!!!

Thank yo so very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now