Prevent compilation of a stored procedure to a linked server

Question

I have a stored procedure, that queries a view on a linked server.

This works fine on prod, but our development server has some firewall restrictions, that prevent the compilation. This is no problem datawise (since it is not very important on dev), but I can't even ALTER the procedure on the development server, because the SQL server tries to compile it / check the syntax / used column names etc. when I alter the procedure (and I receive a timeout).

Is there a way (option, trace flag etc.) that I could use to prevent this syntax check and compile the procedure on the dev server (similar as when a table used in a procedure did not exists, where the SQL Server only throws a warning)?

Answer 1

This can't be done. Deferred Name Resolution only works for table objects. All other objects must exist at the time of creation/modification. See this answer and this older documentation for further info.

As a workaround, you could do one of the following

• Restore a copy of the database from the linked server to DEV and create a loopback linked server (the same name as PROD but points to DEV).
• Script out the database objects from the linked server database, deploy this 'empty' database to DEV and create a loopback linked server. Alternatively, export a DACPAC (schema-only export) of the DB.
• Remove references to the linked server and linked server DBs, essentially remove the 1st and 2nd parts of the four part names. This will only work if no columns in those tables are actually in use by the procedure.
• If there is a DEV copy of the linked server, create a linked server in DEV to point to it.