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.).
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
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
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.