Running VBS Script through SQL Agent Job

  • EsquireDeveloper

    Valued Member

    Points: 64

    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.

  • John Mitchell-245523

    SSC Guru

    Points: 148667

    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: 995976

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    Valued Member

    Points: 64

    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: 995976

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    SSChampion

    Points: 14461

    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

    Valued Member

    Points: 64

    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.

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

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