Nicholas Clarke

Yesterday I was trying to think of a way to call a web page via a SQL Agent job and could not find a way to do it.

I could setup a scheduled job on the web server itself and use Windows scheduler to request the page on a set date and time, but all of my other scheduled jobs are within SQL Server, so I thought it would be best to keep them all in one place.

This stumped me until I remembered that its possible to create stored procedures and functions in C# or any other clr language.

In order to create a C# SQL Server function and allow it to connect to a URL I had to do quite a bit of research, so to save you the time I thought I would make a note of the steps I performed:

1 First create a database project in Visual Studio 2005 and then a C# SQL Server function:

using System;
using System.Data;
using System.Net;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
 [Microsoft.SqlServer.Server.SqlFunction]
 public static SqlString WebRequest(string URL)
  {
 	// Create a request for the URL.
 	System.Net.WebRequest request = System.Net.WebRequest.Create(URL);
 
	// If required by the server, set the credentials.
 	request.Credentials = CredentialCache.DefaultCredentials;
 
	// Get the response.
 	HttpWebResponse response = (HttpWebResponse)request.GetResponse();
 
	// Close open connections
 	response.Close();
 
	// Return the status.
 	return new SqlString("Response: " +
					response.StatusCode +
	 				" - " +
					response.StatusDescription);
  }
};

If you deploy the code to the DB now you will get all kinds of security errors, which I had to work through.

2 These are the steps that I had to perform to allow my function to be successfully deployed to the DB and runnable via SQL:

2.1 Change the Permission level to external in the project properties.

Note: To be able to deploy the dll to the database you will also have to set the connection string.

For more information on these settings please see the msdn documentation.

2.2 Using SQL Server Management Studio enable CLR within your database:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CLR integration is required to deploy and run the code above. It can also be enabled using the Surface Area Configuration Tool (SACT).

2.3 Set the database to be trustworthy:

ALTER DATABASE myDatabase
SET trustworthy ON

This tells SQL Server to trust the database. For more info see the msdn documentation.

2.4 Now deploy the CLR function into the database using Visual Studio’s deploy menu option

2.5 That should be everything and you should now be able to run the command below.

SELECT [myDatabase].[dbo].[WebRequest] ('http://--YourDomain--/runthis.aspx')

Hopefully you now get the response from the web request.

This approach works great and allowed me to schedule the running of some .Net code (within a aspx page) that couldn’t be placed in the database itself. As this is a generic web request you can use this approach to request any URL.

Source code for the CLR SQL function:
You will need to set the connection string to be able to deploy the dll.