Running VBS Script through SQL Agent Job

Question

Running VBS Script through SQL Agent Job

EsquireDeveloper

Old Hand

Points: 376
More actions
February 17, 2020 at 4:20 am

#3726307

Hello All,
Glad to have found this board.
I have a VB Script that is exporting a query to an excel file. It works perfectly when I run it from the command line. When I create a SQL Server Agent Job to execute the same script, it fails.
The type is 'Operating System (CmdExec)' and I'm running it with a Proxy I created. I'm simply calling the vb script that I have saved on a network drive:
cscript "\\network\folder\folder\"VBScript.vbs
It executes, and when I look at the Job Activity log I see the following error:
Microsoft VB Script Error: Active X component can't create object 'Excel.Application' -- referencing the following line of code:
OApp = CreateObject("Excel.Application")
Again -- this works perfectly fine when I run it from the command line, but errors out here. Perhaps there's something basic I'm missing. Thanks in advance for any advice you might have.

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Probably permissions. When you run it from the command line, it runs in the context of the account you are logged in as. When you run it from a job, it runs in the context of the SQL Server Agent account, or whatever proxy you have chosen.

John

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 2

When you ran the code, it was probably from your desktop, which probably has a copy of Office on it (at least Excel). When the job runs, it runs as the SQL Agent login... from SQL Server on SQL Server. IT probably doesn't have a copy of Office or Excel.exe (and all the DDLs that go with it) on it and should never have.

I don't remember what the work around was and I don't have a link available for the solution so you'll have to search for it but, if you run across a solution that says you have to install Excel on your database server, you've run across a bad solution and I implore you to keep searching. You might also want to ask the same question on an Excel forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EsquireDeveloper Old Hand Points: 376 More actions · Answer 3

Thanks John and Jeff for your input,

I do see the difference between using SQL Server Agent and a proxy -- I created a simple script that simply changes the name of a file. This job did not work with SQL Server Agent, but did work when I used a proxy. I created a proxy using my own credentials.

But even using the proxy does not seem to help in creating an instance of Excel. I imagine the proxy I created would have the proper permissions, as I'm using my credentials. I'll keep searching for a solution, but if something else occurs to you, let me know.

Thanks Again.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

EsquireDeveloper wrote:

Thanks John and Jeff for your input,
I do see the difference between using SQL Server Agent and a proxy -- I created a simple script that simply changes the name of a file. This job did not work with SQL Server Agent, but did work when I used a proxy. I created a proxy using my own credentials.
But even using the proxy does not seem to help in creating an instance of Excel. I imagine the proxy I created would have the proper permissions, as I'm using my credentials. I'll keep searching for a solution, but if something else occurs to you, let me know.
Thanks Again.

Changing the name of a file is an awful lot different than not being able to find Excel.exe on the server (and, again, it should never be able to).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16017 More actions · Answer 5

first thing - your script requires Excel to be installed - if this is on a Server it is not supported, and it most likely breaks Microsoft Licensing. even if you can license it you will require a second license for each user that may eventually be able to connect to this server (directly or indirectly)

Second - even if your company decides to go with this option - there are things that need to be done for a Workstation application to be able to work on a server - one of them is related to a folder that needs to be created and permissions granted to SQL Server user/proxy so it can accessed that folder (folder desktop on systemprofile ... enough for you to search for it)

if you need to create excel files look at alternatives - either Microsoft ACE Driver, a third party product (cozyroc for example) or Open XML which can be installed on the server and is supported.

EsquireDeveloper Old Hand Points: 376 More actions · Answer 6

Thank You All For Your Responses --

I was able to get an Export to Excel working using an SSIS Package. I'm combining this with other VB Scripts, as well as some T-SQL Scripts to create the automated job I'm after.