How to manage SQL Server Agent jobs through Csharp

Please note this is an expansion to the article on HOW TO ALLOW A USER WITH AN SQL LOGIN TO MANAGE SQL SERVER AGENT.

The entire source code, with comments, for this tutorial can be downloaded here.

in the mentioned article we learnt how to set up an SQL user to manage SQL Jobs, in this article we are going to learn how to manage jobs through a C# application,
and we will be using the SQL user created in the mentioned article to connect to the database and actually mange the SQL Server Agent jobs.

  1. We will be creating a class library named “SQLServerJobUtility” which will contain the functionality required to manage SQL Server Agent jobs
  2. We will be creating a console application to make use of this class library

Please NOTE: SQL Server 2008 R2 or later must be installed on your development machine.

What tools am I using ?

  1. Microsoft Visual Studios 2012
  2. Microsoft SQL Server 2012 with Management studio

Read more “How to manage SQL Server Agent jobs through Csharp”

Walking through what’s new in querying SQL Server 2012

Hi,

I’ve just been experimenting with the new T-SQL functions and I’ve compiled a set of queries demonstrating them.

This is mainly a demo of the new functionality and I’ve added some useful hints here and there – if anyone has more information about the performance etc, please comment below.

NOTE: I Know this data structure is terrible…don’t bring that up…it’s a demo.

This demo of SQL functionality contains the SQL code for demo data allowing you to execute each query and tinker around with the examples.
Read more “Walking through what’s new in querying SQL Server 2012”

How to resolve “SqlClrProvider not found” without installing sql server on the client machine or needing a deployment project

While working on a project I needed to start and stop SQL jobs via a web application, this worked great on my local machine but as soon as I published to the client web server I started getting SQLClrProvider not found exceptions.

For some reason this DLL is not readily available on the windows system so it’s not as simple as dropping a DLL into the bin folder, however the DLL does exist in the GAC (on your local machine, or any machine with SQL Server Installed)
Read more “How to resolve “SqlClrProvider not found” without installing sql server on the client machine or needing a deployment project”

How to allow a user with an SQL login to manage SQL Server Agent

I was placed in an interesting situation where I needed to manage SQL Server jobs from a web application, which would sit on a completely different server and I was only allowed to use SQL Logins (not Windows logins) to perform any SQL task, I needed a way to use an SQL server login to manage SQL Server agent jobs.

NOTE: I will be writing a tutorial on managing SQL server jobs via C# but if you’d like me to put a rush on it please let me know in the comments below.

The steps needed to set up an SQL login with sufficient permissions to manage SQL Server jobs are mentioned below.
Read more “How to allow a user with an SQL login to manage SQL Server Agent”