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.

At work we just had the cleaning people in to break, sorry I mean clean our computers and this spawned into an interesting conversation into how effective it was.

Dave aka Mr Google then went off and found an online calculator to work out how many germs we all had!

I gave it a try only to find out that I had the equivalent of 324 toilet seats at my fingertips! Now that’s a happy thought just before lunch

My total was 1,617,840, click below to give it a try:

1,617,840How Many Germs Live On Your Keyboard?

Yesterday I installed the Stumbleupon Firefox toolbar as I ran out of unread blog posts and wanted something random to read. On my first click on the Show next page button I was taken to a great interpretation of the software project life cycle

I’m sure every developer can relate to this in some way.

Tonight I logged into WordPress in order to clean up my spam comments and I noticed a new feature. I know that there was an unexpected release to fix a security issue, so I guess some new code was included when I upgraded

When you select the Akismet Spam tab in the comments section of administration you now get the option to filter the items by their type. I have not had a real comment left since upgrading to see if it is also on the non-spam comments tab.

I looked at the change log and could not find this mentioned, so I wonder what else was included in version 2.3.2.

If I get change tomorrow I might take a peek into the code and see what I can find.