SQL job failed to run a C#.net console application

Question

I have a simple excel file that has the following code:

Private Sub Workbook_Open()
   MsgBox "Hello World!"
End Sub

I tried to run a sql job to open this excel file but it failed, became unresponsive. On googling, I found the reason 'why' SQL job wouldn't open excel file Job On Sql Server Agent does not complete, but it does in BIDS?

So I thought of creating a simple console application in C# which would simply open the excel file and run my macro. Here's my code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading;


namespace T_OpenExcel
{
class Program
{
    static void Main(string[] args)
    {

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        //Excel.Worksheet xlWorkSheet;

        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(@"E:\data_extracts\TestHelloWorld.xlsm", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        RunMacro(xlWorkBook, new Object[] { "TestHello" });


        Thread.Sleep(5000);

        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();
    }

    private static void RunMacro(Excel.Workbook xlWorkBook, object[] p)
    {
        //throw new NotImplementedException();
    }
}
}

I could successfully run this code in IDE. Now I want to run this from SQL job in SSMS, sql server 2008.

I grabbed T_OpenExcel.exe file from my C# project( T_OpenExcel-->bin-->Debug-->T_OpenExcel.exe). I created a SQL Job.Here are some of my details:

Step name: OpenHelloWorldExcel Type:Operating system(CmdExec) Run as: SQL Server Agent Service Account Command: C:\Users\shress2\Documents\visual studio 2010\projects\T_OpenExcel\T_OpenExcel\bin\Debug\T_OpenExcel.exe

On running this job, I get the following status Start Job 'TestHelloWorld' Status Success Execute job 'TestHelloWorld' Status Error

On viewing history, it shows: Message Executed as user: GSOPS4\SYSTEM. Unhandled Exception: System.Runtime.InteropServices.COMException: Microsoft Excel cannot access the file 'E:\data_extracts\TestHelloWorld.xlsm'. There are several possible reasons:
The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook. at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) at T_OpenExcel.Program.Main(String[] args) in C:\Users\shress2\documents\visual studio 2010\projects\T_OpenExcel\T_OpenExcel\Program.cs:line 23. Process Exit Code -532462766. The step failed.

I checked E:\data_extracts\TestHelloWorld.xlsm directory and found it working. I made sure my xlsm file is not being used by anyone. I couldn't figure out why its failing to run it. Any help is greatly appreciated. Thanks in adv!

David David · Accepted Answer · 2012-05-21T16:43:55

The problem appears to be that you're attempting to show a messagebox while opening the Excel file, but you're automating it, so there's no human to click the button on the messagebox. The rest of the code can't execute because the file is sitting there waiting for someone to click the button.

On subsequent runs, the server can't access it because it's still sitting there, invisible to normal users, waiting for the button to be clicked. hence the errors.

Short version: Don't proompt for user input on an app that will be run unattended.

SQL job failed to run a C#.net console application

1 Answers