Replication Error - SQL Server replication requires the actual server name to make connection to the server

Sometimes when we try to configuration relpication we might receive the below error which says that "SQL Server replication requires the actual server name to make connection to the server"

This can happen during the situations

1. When you are connected to the server in object explorer using the IP address
2. When the actual server host name hosting the SQL server database engine is changed

In the first case we can solve this issue by connecting to the server with the host name.

In the second case, to fix the issue we need to update the system catalogs by dropping and adding the server and restarting the SQL Server services. The updation of system catalogs can be done using the below queries.

USE master 
GO
EXEC sp_dropserver 'OldServerName'

USE master 
GO
EXEC sp_addserver 'NewServerName',local

Happy New Year 2012

Wishing you all a very happy and prosperous new year.
A Special Query for you all on this new year day :)

SET NOCOUNT ON
Select CHAR(72) + CHAR(97) + CHAR(112) + CHAR(112) + CHAR(121) + 
  SPACE(1) +  
  CHAR(78) + CHAR(101) + CHAR(119) +
  SPACE(1) +
  CHAR(89) + CHAR(101) + CHAR(97) + CHAR(114) +
  SPACE(1) +
CHAR(50)+CHAR(48)+CHAR(49)+CHAR(50) AS 'Surprise'
SET NOCOUNT OFF

Granting Read-Only and administrative access to Central Management Server (CMS)

In one of my previous post, I had discussed about the Central Management Server and how to Register it.
In this post, I will be telling how to give read-only access and a Administrative access to an existing CMS.

Grating the Read-Only and Administrative access to CMS is very simply and the activity includes adding the right database users to right groups in msdb database.

Granting Read-Only access:

USE [msdb]
GO
CREATE USER [DBUserName] FOR LOGIN [LoginName]
GO
EXEC sp_addrolemember N'ServerGroupReaderRole', N'DBUserName'
GO

Granting Administrator access:
This access is usually give to the DBA's

USE [msdb]
GO
CREATE USER [DBAUserName] FOR LOGIN [DBALoginName]
GO
EXEC sp_addrolemember N'ServerGroupAdministratorRole', N'DBAUserName'
GO

Give access to a non sysadmin user to run Profiler

Sometimes as a DBA, you come across a situation where you need to give access to a non sysadmin user to run profiler on a particular SQL server.
If you try to run the profiler using the user who does not have sysadmin access then you will get the below error.

Here is the solution how to grant access to a non sysadmin user to run profiler.
Using Query: 

-- To Grant access to a Windows Login
USE master;
GRANT ALTER TRACE TO [Domain\WindowsLogin]

-- To Grant access to a SQL Login
USE master;
GRANT ALTER TRACE TO [SQL User]

Using SSMS:

1. Expand the Server in object Explorer
2. Expand "Security" folder and then "logins"
3. Right-Click on the login to which you need to give access and then go to "Properties" of that login
4. Go to "Securables" Tab
5. Select the server you want to add the permission
6. In the "Permission for <Server Name>" block, click on "Grant" check box for "Alter Trace" and click "OK"
7. Once this is completed, the permission should appear in the "Effective" Tab

Note: The Granter should be a sysadmin user.  

Convert Rows to Column using COALESCE() function

Using the below query we can convert the rows to column sperated by a delimiter.
In the query I am using ';' as the delimiter and you can change the delimiter of your choice by replacing ';'.
Data from Table:

Query:

Use AdventureWorks2008R2
GO
DECLARE @eMailList nvarchar(max) 

SELECT @eMailList = COALESCE(@eMailList + ';', '') + 
   CAST(eMail AS nvarchar(max))
FROM Employees

Select @eMailList as eMailList

Output of the above Query: