Is there anyway to ignore code that references linked servers?

Is there an option to ignore linked server errors on the project server. I have objects that reference a linked server from code but will never add new code that uses the linked server, so I don't want it installed on the project server, but the project throws errors is the linked server isn't operational.

Best Answer

  • Kendra_LittleKendra_Little Portland, OR, USAPosts: 56 Silver 5
    Accepted Answer
    Hi tee,

    I suspect that the error you are seeing comes from a view or views which reference the linked server.

    A quick summary of why this is happening: When running a verify in SCA in Visual Studio, or when running a build with SCA, it ensures that all objects can be created in SQL Server successfully. Stored procedures enjoy a feature called "deferred name resolution", which means that the stored procedure can reference objects that don't exist at the time the procedure is created. Views and some limited types of functions don't have this functionality, so when SQL Server tries to create those objects it tries to resolve the items it refers to exist / check that the linked server is there. 

    There are a couple of ways you can resolve this:
    1. Create a synonym that points to the linked server resource, and modify the views and any impacted functions to refer to the synonym. This is a great long-term solution, because possibly you do want to validate code that goes across the linked server in other environments. You can "re-point" the synonym in each environment to whatever you want, and you get a lot of flexibility. Synonyms DO have deferred name resolution, so this also means you don't have to actually create the remote resource if you want.
    2. Filter out the views / functions referencing the linked server from the project. 

    Hope this helps!
    Kendra
    -------------------------------
    @Kendra_Little
    DevOps Advocate at Redgate

Answers

  • teetee Posts: 25 New member
    Thank you for the information on the synonym. I'm not sure if it will prevail but it's worth looking into. I am excluding the views via a filter for testing the pipeline but I won't be able to filter out these objects in the production project.  Appreciate your knowledge share.
Sign In or Register to comment.