Allow InProcess in SQL Server

Posted: February 20, 2013 in SQL Server

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.

“The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” reported an error”

This error was fixed after I had enabled the option of “Allow InProcess” for the provider “Microsoft.ACE.OLEDB.12.0” in SQL.

OLEDB Fix 3

OLEDB Fix 3

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.

About these ads
Comments
  1. […] allows linked server to run within context of SQL Server. More info on this option may be found at Anish Shenoy‘s […]

  2. Mike Banner says:

    Hi Anish
    I am using SQL Server 64 but and having to extract data (unfortunately) to Access 2010 using Microsoft.ACE.OLEDB.12.0. I also have text fields in my data.
    When the InProcess flag is checked I get this error
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server
    It does not get fixed if I try reserving 4gb of MTL memory.
    However if I UNCHECK the Allow Inprocess box it works. It also manages to extract Text fields OK.
    So is the Microsoft advice wrong about using this option with Text fields?
    Thanks
    Mike Banner

  3. Mike Banner says:

    Anish
    Re: the previous post I meant SQL 64-bit of course.
    The error message did not occur immediately but after a few minutes of successful processing, and appears to relate to some kind of memory error perhaps.

  4. Anish Shenoy says:

    Hi Mike,

    The blog post is regarding getting the excel data into SQL database. In SQL if you have to import the data from excel which is having datatype (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) including long columns, image and text data, then we will not be able to handle it without setting this option.
    In Access 2010 i am not sure about setting the InProcess option.

    Regards,
    Anish

  5. msranj10 says:

    thanks for sharing this article.. helpful

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s