The following error message appeared while creating jobs in Deployment Server:
Could not find server '<servername>' in sys.servers. Verify that the correct server name was specified. If necessary, execute sp_addlinkedserver to add the server to sys.servers.
The server hostname was probably changed after installing Microsoft SQL 2005. For some reason this was not updated in the Master database.
Another possibility is that MS SQL is using some different naming conventions, named instances, or is a linked server.
Note: This resolution may not work in SQL servers with > 1 instance of SQL installed.
In order to correct the problem the following steps should be performed from SQL Server Management studio on the Master database:
-- to get the current name of the SQL Server
The output of the rpc.rpc out,use remote collation field in the result is the ORIGINAL-servername. This is usually instance 0 as well.
-- to drop the old SQL Servername
exec sp_dropserver 'ORIGINAL-servername'
The CURRENT-servername is your computer name.
-- to add the new name as the local server
exec sp_addserver 'CURRENT-servername', local
-- configures the server for Data Access
exec sp_serveroption 'CURRENT-servername', 'Data Access', 'True'
Restart the SQL Service for the changes to take effect.
In at least one case, DS 6.8 SP1 had to be reinstalled after this.
In at least one other instance, when we ran sp_helpserver, there were several names in braces ( i.e. <ourserver> ) not just in plain text. We never did figure out what those meant and had to install to another fresh SQL installation to get DS to work correctly. The suspicion is that the server we were pointing to was actually a linked server, not "really" local, and that this somehow caused a problem for DS.
Deployment Solution 6.8, 6.9
Microsoft SQL 2005 (or 2008)