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

Arklur 21 Reputation points
2020-10-17T16:18:21.967+00:00

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. I would assume there should be a solution so @@SERNERNAME evaluates to the TargetServerName I specify for the tool.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,701 questions
0 comments
Accepted answer
  1. Ben Miller (DBAduck) 951 Reputation points
    2020-10-19T00:48:43.5+00:00

    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.

    I just tested it and it works like you expect it to.

    Sorry for the delay.

    2 people found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2020-10-17T17:02:10.41+00:00

    If you are on a named instance, @@servername is expected to return SERVER\INSTANCE. Anything else would be strange.

    There is one situation where @@servername can return the wrong value, and that is when you have renamed the computer in Windows. In this case @@servername will retain the old name until you fix it. Whereas, as I recall, this does not happen with SELECT serverproperty('Servername').

    But that concerns only the machine part of the name, and the instance name would not be affected. So if @@servername is missing the instance name, that sounds very funny to me.

    However, maybe the same fix you use when the machine name has changed in Windows works here as well: 

    EXEC sp_dropserver SERVERNAME
EXEC sp_addserver 'SERVERNAME\INSTANCE', 'local'

    And restart SQL Server.

    0 comments
  2. Arklur 21 Reputation points
    2020-10-17T17:07:32.917+00:00

    I understand what you are saying @Erland Sommarskog , but the problem is slightly different. If we connect to the named instance, we get the correct value from @@SERVERNAME. The problem is, when we are using the sqlpackage.exe tool to deploy a .dacpac. It seems (and I'm not an expert using the tool), the tool...uses the default instance in the deploy process? At least I have no other explanation why would we see a "wrong" (the linked server points to the default server, not the named instance we put it the TargetServerName parameter) after the deployment.

    What I was thinking there might be a way to tell the tool to "connect to the target server, and deploy from there" (...I'm phrasing this wrong...), so when it gets to the specific script, @@SERVERNAME will evaluate to the target server name, and not the default instance.

  3. Ben Miller (DBAduck) 951 Reputation points
    2020-10-17T20:41:32.347+00:00

    @Arklur when using sqlpackage.exe you can extract a .dacpac and publish a .dacpac to a server.

    When using /Action:Publish you can specify a TargetServerName for the target. This can be a default instance or a named instance.

    Can you share the sqlpackage.exe command line minus sensitive information? Or are you using the loopback linked server as the target?

    If you are creating the loopback server as part of the deploy, then I would not be using @@SERVERNAME as the parameter for @datasrc. I would do something like what Erland has suggested and put the @@servername in the @servername variable so that you are not using a global variable as the datasrc.

    This is likely your problem.

  4. m 4,271 Reputation points
    2020-10-19T06:59:18.66+00:00

    Hi @Arklur ,

    command as this:
    SqlPackage.exe /Action:Publish /SourceFile:"<DACPAC File Path>" /TargetDatabaseName: <DatabaseName> /TargetServerName:"<Server Name>"

    On your side, please change code as next:
    SqlPackage.exe /Action:Publish /SourceFile:"x:\xxx\xxx\xxx\xxx\xxx\xyz.dacpac" /TargetDatabaseName:Dummy /TargetServerName:"localhost\InstanceName"

    More information: how-to-deploy-dacpac-using-sqlpackageexe, deploying-a-dacpac-with-sqlpackage,deploy-a-data-tier-application

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.