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!

In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below

And this is Data into table

I want output in following format

                                  Department                               FirstName
                                   IT                                             amiT,Emp1,Emp5
                                  Admin                                       Shobhit, Emp3,Emp7

and so on

To get this desired result we need to write below mentioned query

1SELECT DISTINCT
2Department,
3EmpNames = substring( ( SELECT ', ' + FirstName
4FROM Employees e2
5WHERE e2.Department = e1.Department FOR XML path(''), elements
6),2,500)
7FROM Employees e1

And the output of this SQL Query would be

Upgrading a database server to SQL Server 2008 R2 involves more than just inserting an installation DVD and clicking your way through the wizard. A lot of planning goes into a SQL Server upgrade. In this article, I will share with you 10 tips that should help your upgrade process to go more smoothly. If you're looking for Windows hosting that support SQL 2008 R2, you may try us to be your partner. Please take a look our hosting plan at here. OK, let we start this article..

1. Be aware of the supported upgrade paths

Before you begin planning an upgrade, you need to be aware of Microsoft’s supported upgrade paths. For example, if you are currently running SQL Server 2005 X64 Enterprise Edition, you can’t upgrade to SQL Server 2008 R2 Standard Edition. Your only options are to upgrade to SQL Server 2008 R2 Enterprise Edition or Datacenter Edition.

2. Run the Upgrade Advisor

The Upgrade Advisor, which is a part of the SQL Server 2008 R2 Feature Pack, is a free utility that is designed to assist you with your SQL Server 2008 R2 upgrade. The tool analyzes your existing SQL Server deployment and informs you of issues that need to be addressed prior to performing an upgrade.

3. Don’t panic over Other Issues

The report generated by the SQL Server Upgrade Advisor often contains a section called Other Issues. This section exists as a way of informing you of possible issues that may exist, but that the tool is incapable of testing. Therefore, the issues that appear in the Other Issues section may not necessarily be present on your network.

4. Figure out what to do about the Notification Services

If you have SQL Servers that are running the Notification Services, you will need to plan how you want to deal with them. The Notification
Services were discontinued starting with SQL Server 2008, and can’t be upgraded to SQL Server 2008 R2.

5. Verify the hardware and software requirements

If you’re considering an in-place upgrade (rather than a migration), it is critically important to verify that your existing SQL Server meets all the hardware requirements for running SQL Server 2008 R2 and that all the necessary software prerequisites are in place. Check out this full list of the hardware and software requirements.

6. Perform a full backup

Although it should go without saying, you should always perform a full server backup prior to performing a SQL Server 2008 R2 upgrade. The upgrade process usually goes smoothly, but things can and sometimes do go wrong. It’s important to have a way to revert your SQL Server to its previous state if the upgrade does not go as planned.

7. Take care when upgrading the database engine

There are a few things that you should do prior to upgrading the database engine to ensure that things go smoothly. First, if you are running the Analysis Services, make sure you upgrade them before you upgrade the database engine. The Analysis Services must be upgraded first.

Just before the upgrade, temporarily disable any stored procedures. During the course of the upgrade, various SQL-related services will be started and stopped. If you have stored procedures that are configured to run when services start, there is a good chance those stored procedures will interfere with the upgrade.

Also check the Master, Model, MSDB, and TEMPDB databases and verify that they’re set to autogrow (and that there is plenty of disk space available). In addition, be sure to disable database replication prior to performing an upgrade.

Finally, even though SQL Server 2008 R2 is designed to preserve the Max Worker Threads setting, Microsoft recommends setting the Max Worker Threads value to 0. This will cause SQL Server 2008 R2 to automatically calculate the optimal value.

8. Be aware of discontinued features

Microsoft has removed the Surface Area Configuration Tool from SQL Server 2008 R2. Most of the tool’s functionality still exists, but it has been rolled into other areas of the application. For example, protocols, connection, and startup options are now found in the SQL Server Configuration Manager. If you use the Surface Area Configuration Tool from time to time, it’s a good idea to deploy SQL Server 2008 R2 in a lab environment so that you can get a feel for what it takes to manage SQL without this tool.

9. Perform a test upgrade

Before you attempt to upgrade a production database server, try the upgrade in a lab environment. Make a full backup of a domain controller, a DNS server, your SQL server, and any other required infrastructure servers and then restore those backups to isolated lab servers. Once SQL is up and running, try out your upgrade plan in the lab. That way, you can handle any issues that come up before you have to perform a real upgrade.

10. Don’t forget to clean up when you’re finished

When the upgrade is complete, run DBCC UPDATEUSAGE on all of your databases to ensure database integrity. You will also need to reregister your servers and repopulate full text catalogs. If you have disabled replication or disabled stored procedures for the upgrade, you will need to put things back to normal.