I have a problem on SQL SERVER 2022. When I want to select data from an XLSX file, everything works perfectly, but when I want to select data from an older XLS file, I get this message:
The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
DECLARE @path NVARCHAR(MAX) = '\\SRV-SQL\MSSQLSERVER\Data\Test\Test.xls'
SET @path = REPLACE(@path, @@SERVERNAME, 'localhost')
DECLARE @cmd NVARCHAR(MAX) = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=' + @path + ';HDR=NO;'',
''SELECT * FROM [MyTestSheet$]'')'
EXEC(@cmd)