Error MSg 7320, level 16, state 2

Question

I'm updating data using excel file and temp tables and throwing me an error. I did update same data in the past as well without any error.

I've attached screenshot of excel file with columns.

Error:-
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT CUSTNO, ITEM, CUSTPART, PRICE, PDATE, EXPIRESON FROM [Sheet1$] WHERE CUSTNO is not null and ITEM is not null" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Answer 1

Hi @Jinal Contractor ,

1.Error:-OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".

a. Please check if you open SSMS with Administrator and create new linked server for excel file.
We can refer to How to query Excel data using SQL Server linked servers.

b. If no, we can create a new linked server or use the following sql query to extract data from excel file:

    sp_configure 'show advanced options', 1;    
    RECONFIGURE;  
    GO   
    sp_configure 'Ad Hoc Distributed Queries', 1;    
    RECONFIGURE;    
    GO    
    SELECT * FROM OPENROWSET  
    ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Mona\Test\2021\TestFiles\Test.xlsx;', 'SELECT * FROM [Sheet1$]')  
    GO  

2.We can also use Excel Source or Excel Destination to extract data from excel file in ssis data flow task.

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.