European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

France European SQL 2008 R2 Hosting - HostForLIFE.eu :: How to Change SMTP servers on Database Mail with SQL Server??

clock November 13, 2014 08:43 by author Peter

The database I inheritable when I started my current job sends out a lot of mails. It does therefore using database Mail with SQL Server, and it's a lot of mail accounts and mail profiles outlined. I do know that many of these profiles are used, i think that some aren't, and that I haven't any plan concerning the rest – in the future I'll notice the time to clean up, however to this point there have always been a lot of pressing matters to attend to.

But nowadays the mail administrator told me that thanks to a modification in design, SQL Server had to start using a completely different SMTP server for sending mails. Quite easy task if you've got just a single profile. Just a couple of clicks within the database Mail Configuration Wizard, and done. But continuation those self same mouse-clicks for each profile within the list wasn't my plan of a morning well spent, therefore I made a decision that I’d got to script this. (This ought to be easy – we've just a single SMTP server, therefore I may hit each single mail account and did not get to trouble with exceptions).

Usually, scripts for such a task are terribly easy. You just type a felicitous search string in your favorite Search Engine, check the primary 2 or 3 hits, and you’ll have a script. Sometimes even over one. Carefully inspect the script,copy the script, paste into SSMS, build adjustments for your own scenario, do a new review simply to take care. Bottom line, I did not notice a pre-made script for this task, therefore I put in the effort to put in writing one, so determined to share it with you.

Note that the script below was tested on SQL Server 2008R2 only. also note that it'll update all mail accounts to use the new SMTP server. If you've got a a lot of complicated setup with multiple servers and just some got to change, you may got to add the correct filtering criteria to the wherever clause.

DECLARE @NewServer sysname = 'NotTelling.mail', -- New SMTP server
        @OldServer sysname = 'MySecret.mail',   -- Old SMTP server
        @account_id int;
DECLARE Cursor_MailAccounts CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT account_id
    FROM   msdb.dbo.sysmail_server
    WHERE  servername = @OldServer;             -- Add extra logic here
OPEN Cursor_MailAccounts;

FETCH NEXT
FROM  Cursor_MailAccounts
INTO  @account_id;

WHILE @@FETCH_STATUS = 0
BEGIN;
    EXECUTE msdb.dbo.sysmail_update_account_sp
                @account_id = @account_id,
                @mailserver_name = @NewServer;
   
    FETCH NEXT
    FROM  Cursor_MailAccounts
    INTO  @account_id;
END;

CLOSE Cursor_MailAccounts;
DEALLOCATE Cursor_MailAccounts;

I hope it will works for you!



European Windows 2012 Hosting - France :: Look Further Windows 2012 Powershell 3.0

clock September 24, 2013 08:34 by author Scott

Now that Windows 8 and Windows Server 2012 is available, the same is true for Windows PowerShell 3.0 since it is included in the operating system. Windows PowerShell will also be available for down level operating systems (Windows 7, Windows Server 2008 and Windows Server 2008 R2) shortly, as part of the Windows Management Framework (WMF). In addition to PowerShell, new versions of Windows Management Instrumentation (WMI) and Windows Remote Management (WinRM) is included in the WMF.

What is new?

PowerShell 2.0 brought a whole set of new features including background jobs, remoting and the PowerShell ISE. In PowerShell 3.0 there have been made a great number to these features as well as many new ones. I will go through some of the major news:

Workflows – Based on the Windows Workflow Foundation the PowerShell team have brought workflows into PowerShell. A workflow is a sequence of automated steps or so called activities which performs tasks or receives data from managed devices. This makes it possible for IT Professionals to perform automated tasks against a wide variety of devices, for example software installation. A practical example is the installation and configuration of a Windows Server Failover Cluster, where installation and configuration can be orchestrated from a workflow. Among the feature set of a workflow is the ability to suspend and resume execution, no matter if the reason is planned or a temporary network outage. You can see examples and read more about this feature in this article on the PowerShell team blog.

Enhancements to PowerShell Remoting - Robust sessions is a new feature in PowerShell Remoting which makes it possible for a PowerShell Remoting session, a so-called “PSSession”, to survive a temporary network outage. Delegated administration is another new feature in remoting, where a RunAsAccount can be configured on a remoting endpoint. This makes it possible to delegate tasks to for example helpdesk user, without needing to delegate tasks on the backbone application itself.

Simplified syntax – Especially for beginners, the syntax for various parts of PowerShell might be hard to remember and understand. An example of this is the syntax for the –FilterScript parameter of Where-Object and the –Process parameter of Foreach-Object, which both accepts a so-called script block. In version 1.0 and 2.0 of PowerShell we had to use the $_.propertyname syntax inside this scriptblock. For example Get-Service | Where-Object {$_.Status –eq ‘Running’}. In version 3.0, this still works, but there is an alternate more user friendly syntax as well: Get-Service | Where-Object Status –eq ‘Running’. Here we can see that we did not have to use the curly brackets or the $_. syntax. You should note that you have to use the existing syntax if you are doing more than one comparison, however, this makes it much easier for beginners who are likely to do a single comparison in the beginning. Also experienced users will enjoy this feature since it requires less typing.

More user friendly – A lot of enhancements have been made to make PowerShell more user friendly. A common mistake for new users is not loading the required module for the cmdlet they want to run. For example, if you run Get-ADUser without first running Import-Module ActiveDirectory, you would get an error message stating that Get-ADUser is not recognized. In PowerShell 3.0 there is a new feature called module autoloading, which automatically loads the required module for the cmdlet which is being run. Another features in terms of user friendliness is the new cmdlet Show-Command, as well as the Intellisense feature in PowerShell ISE. You can read more about these two features in this and this article on the PowerShell team blog.

Windows PowerShell Web Service – makes it possible to expose a set of PowerShell cmdlets as a Restful Endpoint via OData (Open Data Protocol). This makes it possible to run PowerShell cmdlets from both Windows and non-Windows devices. Note that this feature is more targeted against advanced users and developers.

Windows PowerShell Web Access – If you have used Microsoft Exchange Server`s webmail functionality, OWA, this feature will look familiar. The sign in page for PowerShell Web Access looks very similar to the OWA sign in page. When logged in, you will be presented with a PowerShell session. This makes it easy to use PowerShell both from a web browser on your computer as well as from mobile devices such as an Iphone or Windows Phone. Note that this feature requires Windows Server 2012. You can find instructions on how to configure this feature in this article on Microsoft TechNet.

Updateable help – Until PowerShell 3.0 the help files that is parsed when you are using the Get-Help cmdlet has been a part of the installation. Updating these files have not been possible, since rolling out help files through the channels for updating the operatingsystem (Windows Update, WSUS) could not be justified. Due to this reason, it was not possible for the PowerShell team to correct errors and enhance the help files after the product had shipped. To overcome this limitation, a new feature named updateable help has been added in version 3.0. There is a new cmdlet called Update-Help you can execute in order to update the help files. If you need to download the files in order to bring them over to a computer not connected to the internet you can use the Save-Help cmdlet. You can read more about updateable help in this article by PowerShell MVP Don Jones.

Microsoft Script Explorer – Technically this is not a part of PowerShell 3.0, but rather a standalone download released in the same timeframe as PowerShell 3.0. Using Script Explorer you can search for scripts and other resources on both Microsoft TechNet as well as 3rd party repositories and local UNC-paths, for example a company repository. Script Explorer can either be run as a standalone application or integrated into the PowerShell ISE as an add-on. By integrating it to the ISE you can copy scripts you find directly in to the editor. Script Explorer will also support Windows PowerShell 2.0.

In addition to the above mentioned features, there has been made a great number of bug fixes and enhancements based on feedback from Microsoft Connect.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in