Under Server Options -> Linked Server there are the Providers that each of my linked servers use.

If I right click on a specific Provider and modify the Provider Options, such as "Dynamic parameter" or "Nested queries", do I need to restart my SQL service or server for the changes to take effect?

  • 2
    Usually not, but it's dependent on the client library, server os and sql server version. The Oracle 11.2 client could freeze if you changed those options without restarting but usually the libraries are loaded when needed. – Spörri Oct 30 '18 at 16:16
  • @Spörri When you say dependent on SQL Server Version, is that in regards to the remote server that utilizes that Provider or the local server where the Provider is an object of? – J.D. Oct 31 '18 at 17:40
  • 1
    The local server where the provider runs. There are subtle differences between different versions – Spörri Oct 31 '18 at 22:46
  • @Spörri Oh ok. I'm a little confused then though regarding why you mentioned Oracle 11.2 when this is a SQL Server 2008 R2 question?...Just giving a comparative example why it is a good idea to restart after those changes in general? – J.D. Nov 6 '18 at 21:16
  • When you open an linked server connection or change the provider options you are configuring 3rd party components. you do not need to restart the server before those start to work. One exception for this as if you installed the 11.2 oracle client and configured a linked server using that without restarting the machine the sql server process could/would freeze. That was a bug in the oracle client and was meant as a warning – Spörri Nov 12 '18 at 14:34

Your Answer

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

Browse other questions tagged or ask your own question.