locked
Import data from Excel 2007 to SQL Server 2008 database RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    2
    Sign in to vote

    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

    1. EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    2. GO
    3. EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    4. 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
    Friday, April 8, 2011 2:37 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

     

    Please create linkserver for the excel then try.
    Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    Friday, April 8, 2011 2:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

     

    Please create linkserver for the excel then try.
    Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    I think you can sucess with the SQL Server busineed development studio - Integration service. create the souce for the excel and SQL Server for the destination.
    Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    Friday, April 8, 2011 2:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 blog
    Friday, April 8, 2011 2:36 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    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

    1. EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    2. GO
    3. EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    4. 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
    Friday, April 8, 2011 2:37 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    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/sqlserverfaq
    Friday, April 8, 2011 2:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 2011
    Monday, April 11, 2011 8:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The first link has nothing to do with SSIS.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, April 11, 2011 2:05 PM