0

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)?

  • 1
    On dev server you can restore the database of interest from linked server, create loopback linked server (looking at itself) and this will permit you to pass any parse. If you don't want to do a restore you can trasfer only object definitions from linked server database by generating scripts or by using ssis package (transfer database objects) – sepupic Nov 14 at 13:55
  • Could you go with deleting the stored proc? You say that ALTER requires checks against the linked server - then rebuild it with columns you have available on the dev server – eagle275 Nov 14 at 14:04
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.