Run a C# Console Application from SQL Server Agent (Job)?

Question

This might be a very simple question but I have been trying for last 4-5 hours w/o success. :(

I have a C# console application that simply opens an excel file. This excel file has Workbook_Open() event, which runs my macro. My macro simply renames sheet1 to RenameSheet1 in an active worksheet.

I could run my C# project from IDE. I want to run this project from SQL job(SQL server 2008). How do I do it? Please help me get this working. Thanks.

As per SilverNinnjas' suggestions to create a proxy account:

-- Create a credential containing the domain account CORP\PowerUser1 and its password

CREATE CREDENTIAL PowerUser1 WITH IDENTITY = N'CORP\shress2', SECRET = N'P@ssw0rd'
GO
USE [msdb]
GO

-- Create a new proxy called ExcelProxy and assign the PowerUser credential to it

EXEC msdb.dbo.sp_add_proxy 
@proxy_name=N'ExcelProxy',
@credential_name=N'PowerUser1',
@enabled=1

-- Grant ExcelProxy access to the "CmdExec" subsystem

EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'ExcelProxy', 
@subsystem_name =N'CmdExec'

-- Grant the login testUser the permissions to use ExcelProxy

EXEC msdb.dbo.sp_grant_login_to_proxy 
@login_name = N'shress2', 
@proxy_name=N'ExcelProxy'
GO

I m still getting the same error xecuted as user: CORP\shress2.

Unhandled Exception: System.Runtime.InteropServices.COMException: Microsoft Excel cannot access the file 'E:\data_extracts\RenameSheets.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 24. Process Exit Code -532462766. The step failed.

Any reasons why? I am eagerly waiting for any feedback. Thanks a lot.

@SilverNinja, here's my C# 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();
        xlApp.Visible = true;
        xlWorkBook = xlApp.Workbooks.Open("\\\\myserver\\data_extracts\\RenameSheets.xlsm", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

       xlApp.DisplayAlerts = false;
       xlWorkBook.SaveAs("\\\\myserver\\data_extracts\\RenameSheets.xlsm", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


                  xlWorkBook.Close(true, misValue, misValue);
        xlApp.DisplayAlerts = true;

        xlApp.Quit();
    }

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

Answer 1

You just need to pick the appropriate Job Type in the New Job Step editor. You could use either Powershell or CmdExec.

In the Command area, click the Open button to locate your console application compiled executable (exe).

If you have any parameters, add them here - otherwise configure the schedule.

You may have to use elevated permissions. To use elevated permissions, just navigate to Security->Credentials in SSMS and right-click New Credential. Next, configure the Proxy Account under Sql Server Agent->Proxies and right-click New Proxy. Configure the Proxy for CmdExec and use the Credentials you previously setup. In your SQL Agent Job step you can pick this credential to use when running your command.

In the SQL Job Step Command Area, you should type something like this:

excel E:\data_extracts\RenameSheets.xlsm