I have a VisualStudio SQL project where I need to set a SQLCMD parameter to the value of a linked server.
Lets say there's the following query in the project:
select * from tableName
I need to change this to:
select * from [$(linkedServer)].tableName
where linkedServer = serverName.dbName.dbo this translates to:
select * from [serverName.dbName.dbo].tableName
which results in error:
Invalid object name 'serverName.dbName.dbo.tableName'.
However the following query runs successfully: select * from serverName.dbName.dbo.tableName.
Is there a way to remove the brackets or another work around for this? The brackets are needed since the value is passed as a SQLCMD parameter.
The reason why [serverName.dbName.dbo].tableName fails is because SQL will treat everything between the [ and ] as a single identifier. So it is looking for an object called tableName in a schema called serverName.dbName.dbo.
When you populate the sqlcmd variable, populate it with the square brackets in place in order to produce a valid 4 part name: [serverName].[dbName].[dbo].tableName
Edit for cases of auto deployment / visual studio intellisense:
If you are doing the automatic deployment substitution via the script VS generates, you can create separate variables for the linked server and database, or you can keep one variable and "trick" the system by using quoted identifiers. Set your variable to serverName”.”dbName"."dbo (note - no opening quote, quotes either side of the period), and in your scripts use "$(linkedServer)"