question

Arklur avatar image
Arklur asked ·

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

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-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BenMiller-DBAduck avatar image
BenMiller-DBAduck answered ·

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.

1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry for taking this long time to response back. Thanks for the idea, it worked!

0 Votes 0 · ·
ErlandSommarskog avatar image
ErlandSommarskog answered ·

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.

Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Arklur avatar image
Arklur answered ·

I understand what you are saying @ErlandSommarskog, 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 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You usually specify a connection string with the sqlpackage.exe tool, assume that connectionstring points to the wrong instance...

1 Vote 1 · ·

Hm. Just to be clear, are you referring to the SourceConnectionString parameter? sqlpackage docs


0 Votes 0 · ·

I've never used sqlpackage, so I don't know that it does. But the script you posted initially cannot result in loopback to be defined to point to SERVER when the script is executed on SERVERNAME\INSTANCE.

But maybe sqlpackage is doing something too smart for its own good? Spying on it with Profiler could reveal that.

Else try:

 DECLARE @servername = convert(nvarchar(128), serverproperty('Servername'))
  EXEC master.dbo.sp_addlinkedserver 
    @server = N'loopback'
   ,@srvproduct=N''
   ,@provider=N'SQLNCLI'
   ,@datasrc=@servername


0 Votes 0 · ·
BenMiller-DBAduck avatar image
BenMiller-DBAduck answered ·

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

3 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is how we run sqlpackage.exe to deploy/publish the dacpac:

 SqlPackage.exe /SourceFile:"xyz.dacpac" /TargetServerName:"localhost\InstanceName" /TargetDataBaseName:"Dummy" /Action:Publish /Profile:"Abc.publish.xml" 1 > "xyz.log"

I assume that's what you asked. So we are not using the loopback linked server as target, we are creating it on the fly, to be able to do anonymus transactions.


0 Votes 0 · ·

Thanks for that. I am guessing that the linked server does not get created in the deploy or maybe it does. That would be important I think.

I ran the script you had above with @@SERVERNAME and it creates the linked server correctly so my question is, the source file. Was the source extracted from a default instance?

I am going to run some tests today to see if it can be resolved. If the linked server is created as part of the objects in your .dacpac, let me know.

0 Votes 0 · ·
Arklur avatar image Arklur BenMiller-DBAduck ·

Creation of the linked server is part of the .dacpac. The SourceFile comes from Visual Studio (...as far as I know, but I'm 99% of this).

0 Votes 0 · ·
MiaMiao-MSFT avatar image
MiaMiao-MSFT answered ·

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.

3 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How is this different to what we already do? See my comment above.

0 Votes 0 · ·

Hi @Arklur,

The difference is the Source File should be one path, and if xyz.dacpac is one path, I am not sure whether it is or not, they are same.
If it is not executed on your side, you can try deploy it with ssms.

BR,
Mia

0 Votes 0 · ·

@MiaMiao-MSFT But that will not change the fact that @@SERVERNAME is not available in an object deployment, so the linked server will have a blank server name. The only way to get it to work is to put it in a PreDeployment Script.

0 Votes 0 · ·