21 Apr 2010

SQL Server 2005 - Snapshot Publishing Fun!

I tried to set up Publishing from my local SQL Server but it reckoned that the server name I was using was an alias. It also suggested that I use it's 'proper' server name, which I recognised as being the machine's name from ages ago before I changed it.

Turns out when I ran SELECT @@SERVERNAME , it was returning the OLD name.

The fix was to run this script (found here):

-- Use the Master database
USE master

-- Declare local variables
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100)

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)

-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername

-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'

The server needed a reboot afterwards to make this fix work.
If I helped you out today, you can buy me a beer below. Cheers!