Run C# code inside a SQL Agent Job

c# sql-server sql-agent-job
Tom picture Tom · Jul 11, 2011 · Viewed 15.1k times · Source

I have a piece of code that needs to run every day at a specified time. The code right now is sitting as a part of my web application. There are 2 stored procedures to get/save data that the code uses.

How can I setup Microsoft SQL Server Management Studio 2008 R2 to execute my code as well as the stored procs in a SQL Agent Job. I have never done this before and cannot seem to find the documentation.

Answer

mattmc3 picture mattmc3 · Jul 11, 2011

The simplest method is to make a .NET console application that is just a shell for your real code sitting in a DLL or webservice or wherever. Then, in your SQL Agent job, create a step that is of type "Operating system (CmdExec)" that calls your console app. Saves you the hassle of SSIS (and that is one major hassle to avoid). I also agree with @Hasanain that a .NET proc might be another reasonable alternative.

enter image description here

One other thing to note. The SQL Agent CmdExec will look for an integer return code, so have your public static int Main(string args[]) {} method return 0 for success and some negative number for failure. Or, if you throw an exception, that'll work just fine too. And the SQL Agent will log the text from whatever you threw since the console app will have written it to stdout/stderr.

Related questions

How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More
How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More
Call a stored procedure with parameter in c#

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database. This a button insert I make work well. private void btnAdd_Click(object …

Read More