How 'allow in process' works for providers in SQL server linked server configuration ? It looks like it's unchecked by default. What is the requirement for keeping this option checked and What are the pros and cons of using it.
SQL Server – Providers in Linked Server Configuration
linked-serversql server
Best Answer
What is Allow Inprocess : as the name says by selecting Allow Inprocess, SQL Server allows the provider to be instantiated or allows the provider to run as an In Process server. When the option is not set, the default behavior is to allow the provider to run outside the SQL Server process.
The question that arises is what is the benefit of running the process inside the SQL Server process or outside the SQL Server process.
Running the process outside the SQL Server Process:
Allowing the provider to run outside the SQL Server Process helps to protect the SQL Server process from errors in the provider, as when the provider runs outside the SQL Server process Updates or inserts involving column data types like (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) are not allowed.
Running the process inside the SQL Server Process:
By setting this option in the SQL Server providers, we will be able to handle the data types like (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) including long columns, image and text data.
In case of excel data we can see that when we import an excel data into the database tables without having predefined datatypes, by default the datatypes of some of the columns may be assigned to nvarchar(max) or ntext etc, so if we do net set the Allow Inprocess it will throw error.
If we don’t have such scenarious it is always advisable to leave the default settings of the SQL Server as it is. Only in cases where there is exception we will have to enable the option.