Just some update

wanna write in mandarin but then the typing is not suitable for me. so continue in english

Time really passes, yesterday night in penang, I met my good friend  in secondary school.

we didn't meet for 18 years since after form 5. I think we still met at form 6 awhile after that no more.

As he said, when think back it was like yesterday we just graduated form 5. then suddenly we are married with kids. 

i also told him that in my 20 to 30, I spent too much of time playing games. now i need to put in double effort to chase back the time i lost.

Query MS Excel File using MS SQL Script

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',

'Excel 12.0; Database=e:\excelimport\leadmaster.xlsx; HDR=YES; IMEX=1',

'SELECT * FROM [LeadMaster$]');

if you try to run above  SQL script , it show some errors, (after modifying the path and also sheet name).

Such as access denied or cannot find the server. you should try below steps

Go into your MSSQL management server studio,

go to connected-> Service objects-> Linked Servces->Provideres

you should see Microsoft.ACE.OLEDB.xxxx (12.0,15.0 or 16.0) depend which database engine you have installed.

Usually this will not be seen on MS Windows Server if you don't have MS Excel installed.

https://www.microsoft.com/en-us/download/details.aspx?id=54920
go to above link and download the Microsoft Access Database engine Redistributable

then install it. Then you should see the driver

next go to SQL Script and run below

USE [MSDB]
GO

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

it will show

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

now when you run the first SQL script you should be able to list out all the data.

UPDATE ON 2021 MAY 14:

if you get below message:

The OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)" reported an error. Access denied

Try to run the MSSQL server using localAccount, rather that NTservices account