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.

It seems that you can wait ages for something to finally be released by Microsoft. For example I started .Net with Visual Studio 2005 beta only to wish for the RTM version and then SP1. SP1 came along only for me to wish that I had 2008. All of this due to bugs and missing/cut features.

It seems that Microsoft is coming to the end of a massive development cycle as there are lots of projects starting to stick their heads above water (Microsoft Firewall).

These are some that I have noticed including some links:

Silverlight 2.0 - Beta Q1 of 2008
ASP.Net 3.5 Extensions - Public release due
IIS 7.0 – Coming early 2008 with the new version of Windows Server
ASP.Net MVC – Preview any time now with the supported release in the first half of 2008

• ASP.Net MVC Framework Part 1 - Overview
• ASP.Net MVC Framework Part 2 - URL Routing
• ASP.Net MVC Framework Part 3 - Passing ViewData from Controllers to Views

Windows Vista SP1 – Just got RC so RTM will not be too far away
Parallel FX – Preview out now

• Programming in the Age of Concurrency - Anders Hejlsberg and Joe Duffy: Concurrent Programming with PFX
• Daniel Moth Parallel Extensions
• Parallel FX Team Blog

Visual Studio 2008 – Out now for MSDN subscribers with the official release being February.
.Net 3.5 – Out now
.Net Source Code – Coming soon
LINQ – Out now

• Architecting LINQ to SQL applications, part 1 - Architecture for LINQ to SQL
• Architecting LINQ to SQL applications, part 2 - What is LINQ?
• Architecting LINQ to SQL applications, part 3 - DAOs and Repositories
• Architecting LINQ to SQL applications, part 4 - Dynamic Queries

Volta - Technology preview
Expression Service Packs – Out now
XNA Game development platform – RTM Coming Soon

I’m sure I have missed some, but even so this is quite an impressive list to release at the same time especially when some of these are linked.

All of this makes me wonder what Microsoft is working on that we have not yet heard about. Oh how great it would be if I could to be a fly on their wall. As this is not possible I guess the next best thing is Channel9

So I guess the wait continues. I’m looking forward to the first half of next year.

While working my way through the MSDN blogs I keep seeing these cool little IT web comics.

This one hit home and made me feel a bit guilty!

Be sure to keep your eye on the Office Offline blog for more installments.

No, Microsoft has not overnight bumped up the price of their OS, but its the price you could pay to show your allegiance to Redmond for a private number plate in the UK.

I searched for MAC and MACOSOX but there was no equivalent

While attending some events at Microsoft’s Reading campus I have seen a couple of the Windows Mobile cars driving around covered in stickers, but I think they missed a trick with the number plate:

image from Jason Langridge

It wasn’t until I read Ryan Boren post about the new beta for WordPress that I realized I had misunderstood what Microsoft’s Live Writer can do. His first bullet point was “Tagging support for Windows Live Writer“!, this was odd as I thought Live writer was just for Microsoft’s Spaces blog site.

I now stand corrected after downloading it and giving it a try. Its quite a cool blogging desktop application that allows you to post to different blog engines. All I had to do was point it to my URL and then it came back after 1 minute with all of the settings/locations and enabled me to start posting.

I did however notice a couple of oddities, the installer looks like it is just waiting for input, it is however just taking a while to install and while it is doing that it offers the change to append additional services to the setup. Not very clear, I even canceled it a couple of times as I could not find the next or finish button.

The other was the fact that there seems to be about 3 visible buttons, menu items or sidebars for the same thing. Check out the screenshot below:

I can count:

• 2 x View
  • Toolbar x 2
• 2 x Tools
  • Toolbar x 2
• 3 x Weblog
  • Menu
  • Toolbar
  • Sidebar
• 4 x Insert
  • Menu
  • ToolBar x 2
  • Sidebar

Not too sure why all of these different links and buttons are needed as they all serve the same purpose.

Yesterday Microsoft and Zend announced some new offerings to temp the development of PHP websites on the Windows 2003/2008 and IIS platform, which is due to be released early next year.

To find out more see Chris’s in-depth post: Microsoft ZendCon/PHP Announcements

Update: Some more info: Microsoft Statement

This is the second most annoying thing during my daily developing with Visual Studio.

I’m not totally sure why someone would add this feature but in Visual Studio you are able to copy blank! So if you are like me and have a lazy finger that for some odd reason hits Ctl+C instead of Ctl+V then this tip will be a life saver (especially if you have already closed the original window).

For more great tips on Visual Studio check out Sara Ford’s blog.

Ever since I discovered Sara’s blog I have been playing around with all kinds of settings even to the extent that my colleagues think that I have gone back in time by recreating the terminal windows.

Just for the record in first place with the most annoying thing is the random crashing, which I have heard Microsoft has addressed in Visual Studio 2008.

Microsoft is releasing the source code and not going open source. This will mean that it will not be a community project as expected with open source projects.

This is still great news but a shame that its a halfway point.

For more info check out this podcast and Robert Scoble’s post.

Yesterday I noticed some of the Microsoft MSDN blogs saying that there going to be an announcement on Scott Guthrie’s website today, but little did I know it would be this BIG.

As you know Java is going/gone open source (I think most of it is now available) and now it looks like we will finial get the change to peek into the DNA of .Net. We will not even have to wait that long! As Scott mentions it will be available at the same time as Visual Studio 2008 and .Net 3.5.

Since I started .Net programming it has been possible to take a quick look into the source using Reflector but never to get the documentation or even the slightest chance of debugging. This will be a great plus to the .Net programmers toolbox.

I wonder if they will allow people to ship their own versions of .Net or at least updates/enhancements. I guess we will have to wait and see, but the answer may have be in that Scott said “release the source code” and did not mention open source