Answered by:
Import data from Excel 2007 to SQL Server 2008 database

Question
-
Hi,
I am trying to import data from Execl 2007 to the SQL Server 2008 tables. I have tried all the tips and tricks given in the net but still failed to achieve suucess.
The final error message I got while trying to use the Linked Server is :
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLLink" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLLink". (Microsoft SQL Server, Error: 7399)
Please help me out on this.
Thanks in Advance.
Thanks
Archan
Friday, April 8, 2011 2:28 PM
Answers
-
What is the query? using OPENQUERY? is that a 64 bit server?
You are getting "Access denied"
Can you run process monitor and run the query. I am hoping that you would see Access Denied somewhere.
Also try- EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
- GO
- EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
- GO
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq- Marked as answer by Archan Mukherjee Monday, April 11, 2011 9:07 AM
Friday, April 8, 2011 2:37 PM
All replies
-
Hi,
I am trying to import data from Execl 2007 to the SQL Server 2008 tables. I have tried all the tips and tricks given in the net but still failed to achieve suucess.
The final error message I got while trying to use the Linked Server is :
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLLink" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLLink". (Microsoft SQL Server, Error: 7399)
Please help me out on this.
Thanks in Advance.
Thanks
Archan
Paresh Prajapati
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | BrijjFriday, April 8, 2011 2:30 PM -
Hi,
I am trying to import data from Execl 2007 to the SQL Server 2008 tables. I have tried all the tips and tricks given in the net but still failed to achieve suucess.
The final error message I got while trying to use the Linked Server is :
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLLink" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLLink". (Microsoft SQL Server, Error: 7399)
Please help me out on this.
Thanks in Advance.
Thanks
Archan
Paresh Prajapati
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Paresh Prajapati
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | BrijjFriday, April 8, 2011 2:33 PM -
What is your SQL Server version (32 bit or 64 bit) and what is your Excel version?
I suggest you to check these two blog posts
Dealing with Microsoft.Ace and OPENROWSET Errors
and
Working with 32 bit providers and 64 bit SQL Server
For every expert, there is an equal and opposite expert. - Becker's Law
My blogFriday, April 8, 2011 2:36 PM -
What is the query? using OPENQUERY? is that a 64 bit server?
You are getting "Access denied"
Can you run process monitor and run the query. I am hoping that you would see Access Denied somewhere.
Also try- EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
- GO
- EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
- GO
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq- Marked as answer by Archan Mukherjee Monday, April 11, 2011 9:07 AM
Friday, April 8, 2011 2:37 PM -
I also realized that when you try to invoke a linked Server query, the oledb provider creates a temporary DSN in the temp directory of the Service Account. The local user who is executing the query will not have permission on this folder to create this file. Hence it may fail with an Access Denied.
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaqFriday, April 8, 2011 2:39 PM -
I can not use the SSIS as per the requriement. I need to only write T-SQL query to achive this goal.
Thanks
Archan
Monday, April 11, 2011 7:38 AM -
This link maninly tals about the SSIS packages. I am not using SSIS for this purpose. I can only use T0SQL.
Thanks
Archan
Monday, April 11, 2011 7:40 AM -
I tried this one also. I have given the user full permission on the TEMP and TMP folder in the OS drive. Still I am getting the same error. Is there any way to achive this w/o link servers. Reading from EXcel 2003 is really easy. Excel 2007 should have been much smoother bit this is hell here.
Anu suggestions....
Thanks
Archan
Monday, April 11, 2011 7:42 AM -
Check the following blog post for configuring Linked Server for Excel 2003 & 2007 both.
Similar MSDN blog post: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3b497b6f-10b6-4359-96bf-2d741799447d/#25e506e0-a298-407b-939b-6e288d667f31
~Manoj (manub22@gmail.com)
http://sqlwithmanoj.wordpress.com
MCCA 2011Monday, April 11, 2011 8:18 AM -
The first link has nothing to do with SSIS.
For every expert, there is an equal and opposite expert. - Becker's Law
My blogMonday, April 11, 2011 2:05 PM