Post reply

Changing Domain so I have to change the email address on xp_sendmail

  • July 10, 2007 at 4:02 pm

    #117837

    We are in the process of changing domain name.  Unfortunately I have to then change all the xp_sendmail emails in all our stored procedure.  I was wondering if anyone had a script that would cursor through the database grabbing each stored procedure and if there is an xp_sendmail change the emailing addresses to the new domain.

    I have been struggling on creating my own and getting very frustrated. 

    Your help is extremely appreciated as for if I cannot get this script to work I will have to manually go through every sp and there is a lot of othem

     

    Thanks

     

     

  • July 10, 2007 at 11:55 pm

    #718499

    just do a select from syscomments table to know which procedures use them and then replace those with the new email address. Also check the sysjobsteps table to find if you have used them in jobs and make the corrections there too.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 11, 2007 at 9:57 am

    #718689

    Thanks that will work.  I am still going to keep writing the cursor just for fun

  • July 11, 2007 at 10:16 am

    #718702

    For the future, can I recommend you look into a different method?

    I typically use a lookup table for e-mail addresses, where you have the group title in a column, and the corresponding e-mail address(es) in another column. Your stored procs can then lookup the title and grab the appropriate addresses as needed. It not only helps with domain changes, it also comes in handy when there are layoffs, normal turnover, etc.

  • July 13, 2007 at 5:19 pm

    #719439

    Using Active Directory distribution groups is also handy. Then you can make the AD admins make the changes.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply