Note: Shortly after I put this article together, I realized that someone else wrote something eerily similar, so I decided not to submit as an article to SQL Server Central or another content aggregator.
Also, the web service I was using stopped working a couple days after I wrote this. :(
Anyway, this should offer a quick and easy guide to setting up a CLR web service call from SQL Server. Just don't bother using the web service I'm providing as an example.
This project uses a SQL Server Common-Language Runtime (CLR) Function which calls a 3rd-party Web Service to format and beautify SQL Server Stored Procedures directly from Query Analyzer. Beautiful code is usually easier to read and allows for easier maintainability.
What do I need?
I am using Microsoft SQL Server 2008 CTP 6 for this example, however it should be backwards compatible with SQL Server 2005. I also have Visual Studio 2005 installed.
Alternatives to this method include SQL Pretty Printer http://www.sqlparser.com/ plugin for Visual Studio.
Disclaimer
I do not recommend using this in a production environment or with confidential stored procedures as it calls a web service and opens the database up to potentially malicious calls. You may need to change the locations where files and databases are created. Use at your own risk.
For this demo, you will need to enable the CLR in SQL Server.
Steps to Implement a CLR in SQL Server
From SQL Server Management Studio, Query Analyzer
--Execute this statement
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
When sending results to text, you may need to set the "Maximum number of characters displayed in each column" property of SQL Management Studio - Query Analyzer to a higher value than the default of 256. I have this set to 8000, but for larger stored procedures you may require more. Better yet, send the results to a file.
From the Visual Studio Command Prompt
Begin by creating the C# class library required for the solution. You may want to use NAnt, MSBuild, Visual Studio or a batch file to automate these tasks.
Step 1: Create the "wiz-dal" (WSDL) class wrapper for the Web Service
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\wsdl /o:SqlFormatterWebService.cs /n:SqlFormatterWebService.
Step 2: Create the sqlformat.cs class
Open up Notepad and save this as the following file:
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sqlformat.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlFormatterWebService.Test;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlFunction]
public static string FormatSql([SqlFacet(MaxSize=-1)]SqlString inputsql)
{
return GetFormattedSql(inputsql.ToString());
}
public static string GetFormattedSql(string inputsql)
{
SQLFormatterWebService sqlformatter = new SQLFormatterWebService();
return sqlformatter.beautifySql("tester","mssql","sql",inputsql,"");
}
};
Step 3: Execute the following from the Visual Studio Command Prompt:
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\csc /t:library sqlformat.cs sqlformatterwebservice.cs
Once the class library is created, the sqlformat.dll file will be available to add as an assembly reference in SQL Server.
From Sql Management Studio - Query Analyzer
Step 4: Create a database to store the CLR function (optional)
CREATE DATABASE [ClrWebServices] ON PRIMARY
( NAME = N'ClrWebServices', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ClrWebServices.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ClrWebServices_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ClrWebServices_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Step 5: Change the database owner to a non-domain service account (optional)
sp_changedbowner 'localhostname\localserviceuser
Step 6: Set database as trustworthy
There are many reasons why you would not do this in a production environment. Alternatives are in this forum posting:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2027982&SiteID=1
USE ClrWebServices
GO
ALTER DATABASE ClrWebServices SET TRUSTWORTHY ON
Step 7: Create the assembly reference for the function
CREATE ASSEMBLY SqlFormat
FROM 'C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sqlformat.dll'
WITH PERMISSION_SET = UNSAFE;
GO
Step 8: Create the assembly reference for the function serializer
The XML Serializer is required when using a Web Service in CLR code.
CREATE ASSEMBLY [SqlFormat.XmlSerializers] FROM 'C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sqlformat.XmlSerializers.dll' WITH PERMISSION_SET = SAFE; GO
Step 9: Create the SQL function wrapper
CREATE FUNCTION SqlFormat(@inputSql nvarchar(max))
RETURNS nvarchar(max)
AS
EXTERNAL NAME SqlFormat.StoredProcedures.FormatSql
GO
Step 10: Test the procedure against a single stored procedure
Use AdventureWorks
Go
set nocount on
go
select ClrWebServices.dbo.SqlFormat(text)
from sysobjects o
inner join syscomments c on c.id = O.id
where xtype='P'
and name = 'uspGetEmployeeManagers'
Conclusion
This article demonstrates one way of calling a web service from a CLR stored procedure, and how a web service may improve and extend the objects created inside SQL Server.
References:
http://en.wikipedia.org/wiki/Web_Services_Description_Language
http://en.wikipedia.org/wiki/Common_Language_Runtime\
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2027982&SiteID=1
http://support.microsoft.com/kb/913668
http://www.setfocus.com/technicalarticles/clrfunctionforsqlserver.aspx
http://sqlblogcasts.com/blogs/simons/archive/2006/11/28/CREATE-ASSEMBLY-Permissions---Could-not-obtain-information-about-Windows-NT-group-user-.aspx
http://sachabarber.net/?p=69
http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1
http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-returning-data-part-2
http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx
