This blog is a continuation of the blog that I had written earlier on one of the error that I had got when I was uploading an excel data in to the database tables. If you have missed reading the previous blog please have a look at it.
This error was fixed after I had enabled the option of “Allow InProcess” for the provider “Microsoft.ACE.OLEDB.12.0” in SQL.
This blog is based on one of the feedback received from the reader, the reader understood the symptom of the problem and how to fix it, but did not understand the underlying meaning.
Here it goes,
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 like in the scenario that I explained in my previous blog, we will have to enable the option.
So it was a new learning from my end based on the feedback received, please read my future blogs and all the feedbacks and reviews will help us learn new things together.
See you in the next blog with something interesting. Till then signing off Anish.