This post was originally published here

After you install and configure a BizTalk Server Azure Virtual Machine, the first time you try to run the main BizTalk Server job: Backup BizTalk Server (BizTalkMgmtDb) – of course after you properly configure it – it will fail every time with the following error:

Executed as user: BIZDEMOsaspereira. Could not find server ‘BIZDEMO’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed.).

BizTalk Backup Job Could not find server sys.servers

Note: will not be the only one that you will find but let’s will go step by step and we will address other errors in different blog posts

 Cause

Well, I guess (I’m sure) that this error happens because we are using a default Microsoft BizTalk Server image with all the components already installed: Visual Studio, BizTalk Server and especially SQL Server.

This problem happens because of the SQL Server name present in sys.servers table – maybe that was specified during setup of this image – is defined as “localhost” and not the actual VM name that you created on the Azure Portal and for the job work properly:

  • The SQL server name specified during setup of the Azure Virtual Machine must match the Name in sys.servers of the SQL server

To check the Name present in sys.servers table please run the following SQL Script:

SELECT name FROM sys.servers
GO

Backup BizTalk Server job Could not find server in sys.servers: check the name

Solution

To fix this issue, we need to remove the old server name and add the new server name in SQL. You can do that by:

  • Login on to SQL Server and open SSMS.
  • Connect to the instance and run below query, which will remove the old server name.
sp_dropserver 'localhost'
GO
  • And then run below query to add the new server name, that in my case was “BIZDEMO”:
sp_addserver 'BIZDEMO', local
GO

After you execute these scripts this particular problem will be solved.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira