SQL Server 2008 R2 – Data Collector Component Name Doesn’t Change After Updating Server Name

sql-server-2008-r2

I cloned a SQL Server, and changed the name of the machine and SQL Server, but the old name continues to show up as a component name in the disk usage data collector, which I believe is what's causing that job to fail.

This is part of the error in the job history for that task that includes the old name:

SSIS error. Component name: TSQLQuery_OLD_NAME_MSSQL10_50_MSSQLSERVER_{}_1_Collect

I have already dropped the old name using sp_dropserver and added the new server name using sp_addserver. I verified that SELECT SERVERPROPERTY('MachineName') brings up the correct information, as does sp_helpserver. However, SELECT @@SERVERNAME brings up a NULL value, despite restarting the server.

How do I change the component name?

Best Answer

I cloned an MS SQL server, and changed the name of the machine and SQL server, but the old name continues to show up as a component name in the disk usage data collector, which I believe is what's causing that job to fail

However, SELECT @@SERVERNAME brings up a NULL value, despite restarting the server.

How do I change the component name?

Give this a shot since you state SELECT @@SERVERNAME brings up a NULL value, despite restarting the server.

1. Run the below with the local as the second argument. . .

TSQL

SP_DROPSERVER
GO
SP_ADDSERVER 'NewServerName', local
GO

2. Restart SQL Server (if you can restart the server OS meaning power cycle it)

3. Run the below once it's back up and confirm . . .

TSQL

SELECT @@SERVERNAME 
SELECT SERVERPROPERTY('ServerName')

4. If the values are correct, then rerun your SSIS process and see if that resolves it as well.

SP_ADDSERVER

[ @local = ] 'LOCAL' Specifies that the server that is being added as a local server. @local is varchar(10), with a default of NULL. Specifying @local as LOCAL defines @server as the name of the local server and causes the @@SERVERNAME function to return the value of server.

SQL Server Setup sets this variable to the computer name during installation. By default, the computer name is the way users connect to an instance of SQL Server without requiring additional configuration.

The local definition takes effect only after the Database Engine is restarted. Only one local server can be defined in each instance of the Database Engine.

Related Question