0

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.

Arklur
  • 173
  • 1
  • 11
  • `@@SERVERNAME` will always evaluate to the name of the instance that is evaluating it. You can't pass `@@SERVERNAME` as a parameter and expect it to resolved by a remote instance. `@@SERVERNAME` is resolved at the point it is called; which here would be during the `EXEC` statement that is run *locally*. – Thom A Oct 16 '20 at 15:16
  • As for the evaluation, that's what I wrote. As for the parameter, I think you misunderstood me. I don't want to pass `@@SERVERNAME` as a parameter, obviously, I asked if there is a way to somehow modify the "context" of sqlpackage.exe, so when it deploys that script to the **target server**, `@@SERVERNAME` will evaluate to the **target server**. Obviously the goal is to have a script that you can deploy anywhere, even in multi instance environemnts, and get the expected results. – Arklur Oct 16 '20 at 16:01

1 Answers1

0

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.

Arklur
  • 173
  • 1
  • 11