Few OLEDB issues that I would like to share;
1. Msg 7415, Level 16, State 1, Line 1
Ad
hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You
must access this provider through a linked server.
If you see this message with your OLEDB query,
it means you do not have sufficient rights on the OLEDB source. Please check
your user have enough rights to access the OLEDB provider. The provider must
show in the list of providers as below;
Secondly, right click the provider and check
“Disallow adhoc Access”. It should be unchecked.
2. Msg 7302, Level 16, State 1, Line 1
Cannot
create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for
linked server "(null)".
Check the above issues stated in point 1.
If above settings are good, and still getting this error, then it your syntax.
Please check the correct syntax to query using the Provider. For example, to
query excel file, the correct syntax is;
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=F:\FleName.xlsx',
'select * from [sheet1$]')
For csv, the syntax is;
SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Text;Database=E:\',
'SELECT * from FileName.csv')
Please
note that in case of excel, File name is give in the “Database” property and
sheet name is given in the “Select * from [sheet1$]” statement.
In
case of csv, only path is given for “Database” property and file name is given
in the “Select * from Filaname.csv” part.
3. OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for
distributed queries because the provider is configured to run in
single-threaded apartment mode.
You need to install 64
bit or 32 bit Access driver depending upon the operating system that you have
on the machine where you are running the query to access the excel or csv file.
4. Few columns returning NULL in the dataset
You might see this issue where your values
are turning into NULL. If you look deeply into your data, you will notice that
the values in the column are numbers (not alpha numeric charachters) and the
values that are alpha numeric in the parent file are turning into NULL. This is
because the data type of the column is determined based up on the first few
data values in the column. If these are numbers, the datatype will be set to “int”
and all character values will be saved as NULL.
To solve this issue, you need to do some
changes in the registry as shown in the screen below;
Open registry of the server in which you
are processing the csv / Excel file. Go to the following path;
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access
Connectivity Engine\Engines\Text
We have selected “Text” as we faced this
issue while processing CSV files.
Change “ImportMixedTypes” property value to
“Text”. Save the registry. Execute the query again and you will now see all
your data returning as Text. You can no move your data as per your table
schema.
No comments:
Post a Comment