7

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();
    }
}
}
Nemo
  • 1,111
  • 6
  • 28
  • 45
  • 2
    I would imagine that the user account that is used by default to run SQL Server Agent Jobs does not have sufficient permissions to interact with something that has an interactive GUI as Excel has. Using Aspose.Cells within a Console application could be a solution. – Uwe Keim May 21 '12 at 19:37
  • 1
    Does your project use SQL? If not, you would be better off with a Scheduled Task. – jrummell May 21 '12 at 19:43
  • I tried using Task Scheduler. It runs perfectly fine there. The problem is we are using SQL JOB to automate the process of feeding excel file(in my case my C# application generates) to a third party reporting tool. That's the only reason to make it run from SQL job. Thanks. – Nemo May 21 '12 at 20:01
  • 1
    My guess is that the `E:` drive is a mapped network drive. You probably mapped it under your user, but the job is running under `CORP\shress2` or whatever. Try specifying the UNC path to the file instead- something like "\\server\path\to\file\". – Chris Shain May 21 '12 at 21:19
  • Hi Chris Shain, I specified UNC Path in my C# console application to "\\\\myserver\\data_extracts\\RenameSheets.xlsm" and compiled/ran the code successfully. Now my question is on SQL Job. Under Command Area, it points to the path to my compiled exe which is like this : C:\Users\shress2\Documents\visual studio 2010\projects\T_OpenExcel\T_OpenExcel\bin\Debug\T_OpenExcel.exe I m still getting the same error. :( – Nemo May 21 '12 at 21:33
  • The problem isn't getting to your EXE. The job is sucessfully getting to it and running it. The problem is that your running EXE isn't able to get to the XLSM on the E: drive when it is being run by the SQL Job Agent. if the path shown in the error is the correct path for your XLSM then the problem is almost certainly that the Job Agent CmdExec isn't running your EXE with the right privileges/permissions. – RBarryYoung Jul 17 '18 at 20:30
  • To be specific, have you tested your code running *on* the server where SQL Server is running, using the same credentials that you are specifying for the CmdExec? – RBarryYoung Jul 17 '18 at 20:33
  • Firstly, all of the paths defined in the job have to be _as seen from the SQL Server_, and _as seen by the SQL Agent run account_. Why does your error message mention `E` drive but your code not mention `E` drive? Possibly the problem is with your macro and that is just being passed up. Break up the problem - try opening a blank spreadsheet with no macro first and get that working – Nick.Mc Nov 20 '18 at 07:35

3 Answers3

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
SliverNinja - MSFT
  • 31,051
  • 11
  • 110
  • 173
  • @SilverNinja, Thanks for prompt response. I did that. But it failed.I used CmdExec. Under Command: I pointed to the exe file that was generated by VS 2010 for my C# console application. Is that correct? I m failing while executing the Job. It says 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. – Nemo May 21 '12 at 19:40
  • Did you add your **custom credentials** - by default **SQL Agent Service Account** cannot access the filesystem. You need a [proxy account configured](http://www.sqlservercentral.com/Forums/Topic1088319-391-6.aspx#bm1099165). – SliverNinja - MSFT May 21 '12 at 19:41
  • 1
    @SilverNinja, In the Command Area, when I could either paste the path to my compiled exe, or as you said browse to the .exe file for my project. When I paste the path, it looks fine though it fails at the end. When I browsed to the path, it gives me gibberish code. (copied-pasted here-) MZ� Is this something that's causing the problem on accessing the file? Please let me know. Thanks. – Nemo May 21 '12 at 19:53
  • See edits above...you need to tell **CmdExec** to open the file with Excel. **CmdExec** cannot open XLSM files. – SliverNinja - MSFT May 21 '12 at 20:11
  • @SilverNinja- I understand that I cannot use CmdExec to open XLSM files. Does that mean I cannot provide path to me .exe file (compiled from my c# console application)? This is my path to .exe file that I pasted under Command Area C:\Users\shress2\Documents\visual studio 2010\projects\T_OpenExcel\T_OpenExcel\bin\Debug\T_OpenExcel.exe – Nemo May 21 '12 at 20:16
  • Pray tell me that you do not actually intend to have that file path on your real sql server instance? – Brian White May 21 '12 at 20:19
  • @Brain White, I m not quite sure but I guess that my file path does not exist in my real SQL Server instance. Had it existed, it should be able to pull up from open button under Command area in my SQL Job. Right? When I browsed to my file path from open button in commnad area under my SQL job, it fetched that gibberish code MZ�. Am i correct? Please let me know. I need to make this work. :( – Nemo May 21 '12 at 20:22
  • I m not sure about using a proxy account. Any help is greatly appreciated. Thanks again for your follow ups. – Nemo May 21 '12 at 20:26
  • To configure a [**Proxy Account**](http://msdn.microsoft.com/en-us/library/ms189064%28v=sql.105%29.aspx), see edits above. – SliverNinja - MSFT May 21 '12 at 20:38
  • @SilverNinja, please take a look at my edits..(original post above). – Nemo May 21 '12 at 21:18
  • Could it be possible you have the workbook open already and you need to close it? Maybe you should share the code in `Program.cs` – SliverNinja - MSFT May 21 '12 at 21:35
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/11535/discussion-between-nemo-and-sliverninja) – Nemo May 21 '12 at 21:49
0

New job

CmdExec

Now run exe file from it

It will give exception.

change exe code.

you cannot have remote server path make it local to same sql server \\myserver\data_extracts\RenameSheets.xlsm to d:\data_extracts\RenameSheets.xlsm or use file path where bin exe file path is copied . Path.GetDirectoryName(Application.ExecutablePath) eg. value: C:\Projects\ConsoleApplication1\bin\Debug\RenameSheets.xlsm

Make sure only one exe is running otherwise there will be error file is already opened

run exe code It will work. Command prompt cannot access network path .

xlApp = new Excel.Application(); xlApp.Visible = true; xlWorkBook = xlApp.Workbooks.Open("C:\Projects\ConsoleApplication1\bin\Debug\RenameSheets.xlsm

", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

   xlApp.DisplayAlerts = false;
   xlWorkBook.SaveAs("C:\Projects\ConsoleApplication1\bin\Debug\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;
Jin Thakur
  • 2,711
  • 18
  • 15
-3

just need to pick the appropriate Job Type in the New Job Step editor. You can use either Powershellor 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

Akshay Kolhapure
  • 5
  • 5