none
Problem when using OpenRowSet for importing excel 2007

    Question

  • Hi All,

    I am new to TSQL, and I am facing the following issue at the moment, I have been googling for the whole afternoon but still can't an answer...

    I want to import an Excel 2007 file into my database, I have the following written:

    INSERT Into TempImport
    SELECT * FROM OpenRowSet('SQLNCLI',
                             'Excel 8.0;Database=C:\TestImport.xlsx;uid=user;pwd=passwd',
                             'select * from [Sheet1$]');

    At first I was facing the "SQL Server blocked access to STATEMENT 'OpenRowser/OpenDatasource"...,

    I solved it by edited sp_configure, but now I have this:

    "OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "(null)"."

    I know I can do the same by using the Import and Export Wizard, but the this needs to go into a package so I can't use the wizard......

    I am using MS SQL Server Management Stuido with SQL Server Native Client 10.0.

    Please help...-_-!!

    Wednesday, February 09, 2011 9:06 AM

Answers

All replies