@@SERVERNAME when using sqlpackage.exe in multi instance environment

Question

We are using sqlpackage.exe to deploy a database. In the deploy process, we have a script that creates a loopback linked server:

EXEC master.dbo.sp_addlinkedserver 
  @server = N'loopback'
 ,@srvproduct=N''
 ,@provider=N'SQLNCLI'
 ,@datasrc=@@SERVERNAME -- This is the important part!

The problem we face is that in the deploy process the @@SERVERNAME evaluates to the default instance, not the target server specified, which is a named instance. I assume the source of the problem is that the deploy happens "in the name of" the default instance...?

I tried to find a solution for this problem, e.g specifying a parameter for sqlpackage.exe that would resolve this "issue", but so far no luck. Our quick solution was to first execute the specific script in the target server using SSMS, creating the loopback with the right server, and then deploy, but obviously we are interested in a more "resistant" solution.

Answer 1

I've got the answer on learn.microsoft.com / Questions:

The only way I have found that you can create a linked server using @@SERVERNAME in the script is to delete the definition of the Linked Server as an object and then put that script in a PreDeployment script and it will pick up the ServerName with the instance and create the linked server.

The solution is verified, in PreDeployment script the @@SERVERNAME evaluates to the target server (named instance), not the default one.