question

dsk-7659 avatar image
0 Votes"
dsk-7659 asked dsk-7659 answered

Agent job hangs unless logged in remotely to SQL Server

I call .xlsm, which is automatically executed by the SSIS Process Execution task.
Then I register this to the SQL Server Agent job.

It works fine when I am logged in.

However, if it is run when I am logged off, such as during a scheduled run, it hangs up.
I can see the Excel process in Task Manager, but it is not actually running (CPU usage is 0% all the time).

What are the possible causes?
Note that I am not using a user DSN, so that is not the cause of the problem.

Thanks.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Excel needs a desktop. Services do not have desktops. Thus, invoking Excel from a service, for instance an Agent job, is not the best of ideas.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @dsk-7659

A Desktop folder and give write permissions on the Desktop folder seems to be necessary to open file by Excel,
https://social.msdn.microsoft.com/forums/sqlserver/en-US/4321f28c-0a89-4694-a3a4-c0459d002c61/ssis-package-which-reads-excel-files-fails-on-sql-server-agent

Or the SQL Server Agent service account may not have sufficient permissions, check what user will be running the SQL Server agent job when you are not logged in.

And the job created may be just processing the Excel only, may be you should change the SSIS package to process the whole database.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

dsk-7659 avatar image
0 Votes"
dsk-7659 answered

Hi All,
Thanks for the answers.

I took a hint from your answers and the problem was solved.

I stopped calling the .xlsm from the job.
I created a VBS that calls .xlsm and registered it to the job and it worked.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.