Inconsistent error with OPENROWSET from XLS

Question

Post reply

Inconsistent error with OPENROWSET from XLS

cphite

SSCarpal Tunnel

Points: 4278
More actions
January 26, 2022 at 2:37 pm

#3977502
We have a process that we run to import parts into our inventory database. Most of the files we get are XLXS which work as expected, but there is one vendor that for whatever reason sends XLS files. We have the following code in a stored procedure that pulls the file into temp table:
```
if object_id('tempdb.dbo.#ins_hw_and_sw') is not null drop table #ins_hw_and_sw 
create table #ins_hw_and_sw
(
[PID] varchar(600),
[Family] varchar(600), 
[Category] varchar(600), 
[Product Type] varchar(600), 
[Product Sub group] varchar(600), 
[Description] varchar(600), 
[Countries of Origin] varchar(600), 
[Country Names] varchar(600),
[Notes] varchar(600)
)
insert #ins_hw_and_sw
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=T:\VENDR_GPL_RAW\ins_RAW.xls;', 
'SELECT *
FROM [HW AND SW$]')
```
Our problem is this: If I run the code above in SSMS manually, either by itself or by running the stored procedure, it works just fine. However, if I run it as part of a scheduled job, it fails with an error saying "The Microsoft Access database engine cannot open or write to the file" even though I am quite sure that the accounts the SQL Server service and SQL Server Agent are running under have full access to the file.
Also, if I execute the stored procedure as part of an SSIS package it works, whether I run the package manually or as part of a scheduled job. This is actually how I'm doing it for now.
The issue only occurs if it's an XLS formatted file; XLXS work as expected.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Things that I would check are:

1 - does the service account have permissions to the folder AND file?

2 - does any other application or service have the file open at the same time as your job is running?

3 - is "T:" a network shared drive or a locally visible drive?

I have a feeling option 3 is the problem. Try using the FQDN (ie \\server\share\) instead of the drive letter (ie T:\). The service LIKELY doesn't have that drive mapped (if it is a network drive) and thus OPENQUERY can't find it. BUT if you run it as YOU, you have the drive mapped so it has no problems opening the file.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 2

Brian - thanks for the reply...

The service account definitely has access; and just to be sure I even tried granting "Everyone" full access to the file and folder, but that didn't make any difference.
That was my first thought but I would expect the same problem regardless of how the script was run. It happens every time if we try to use a SQL Agent Job to run the script or stored procedure. However, we can run the exact same script or stored procedure in SSMS and it works. Or, we can call the script or stored procedure using SSIS and it works - even if we run the SSIS package as part of a SQL Agent Job. Also, the problem only occurs with XLS file, not XLSX. The problem seems very specifically to be when we try reading from an XLS file with a T-SQL step in a SQL Agent Job.
T:\ is a local drive on the machine itself... I will try the FQDN just out of curiosity, though.