Is there a way to force SQL Server 2008R2 to create a stored procedure which includes a linked server query where the linked server does not exist, or does exist but without a data source.
I am fully aware how daft this sounds but it is company policy to deploy all database objects (inc. SPs) to all databases on all servers. However in this case I am unable to create the linked server at all servers.
The error returned is
Msg 7314, Level 16, State 1, Procedure TestLinkedSever, Line 23 The OLE DB provider "SQLNCLI10" for linked server "LinkedServer" does not contain the table ""Database"."dbo"."table"". The table either does not exist or the current user does not have permissions on that table.
I suggest making the stored procedure dynamic SQL based and store the server name in a table. I've been involved in projects where we had to fix linked server names because company standards enforced a name change on upgrade. It's not fun.