At the moment we have multiple databases hosted on the same SQL Server instance, but we want to separate them on a different servers and use them as linked servers (there are views and SPs that are joining tables from different DBs). Locally, for development purposes, they are hosted under the default SQL instance, and ideally we would like to keep it that way.
This is the scenario we have: We have a solution A with sqlproj for DB A. There is database reference to DB B.
- I've updated the database reference so it is set as 'different database, different server' now.
- I've updated the views and SPs so that they include the server variable now for DB B, so we have [$(ServerName)].[$(DatabaseName)].dbo.[Table1]
- I've updated the publish profiles for different environments
It is all working fine except for publishing the DB locally, as I would like to keep both DB A and DB B on the same default instance.
- Using '.' doesn't work
- Using '(local)' doesn't work
- Using ComputerName as the server name works, but ideally I would avoid this, as it would be different for every developer and we won't be able to use a single publish profile
- Using the following syntax directly in sql works: .[DbName].dbo.[Table1] works, but the publish profile won't except an empty string as a variable value for the server name.
Is there any way I can reference the default instance as a linked server locally?