CRL-функции в MS SQL (на примере DNS-ресолвера)

На примере функции, преобразующей URL в IP и обратно, напишу краткую инструкцию по прописыванию собственных CRL-функций в MS SQL (которые рекомендуется использовать если а) имеют место быть очень мощные математические вычисления или б) если по другому очень трудно (как с DNS-запросами).

1. Включаем использование CRL в SQL Server Surface Area Configuration
2. Пишем в текстовом редакторе нашу функцию (в данном случае - DNS-resolver) и записываем ее, например, как SQLCRLFunctions.vb:

Imports System.Net
Public Class CRLFunctions
Public Shared Function ResolveDNS(ByVal Name As String, ByVal dr As Byte) As String
Dim hostName As String
Dim returnValue As IPHostEntry
returnValue = Dns.Resolve(Name)
If dr = 0 Then
Return IPConvert(returnValue.AddressList(0).Address)
Else
Return returnValue.HostName
End If
End Function
Public Shared Function IPConvert(ByVal IPAddress) As String
Dim x As Integer
Dim Num As Integer
IPConvert = "0.0.0.0"
For x = 1 To 4
Num = Int(IPAddress / 256 ^ (4 - x))
IPAddress = IPAddress - (Num * 256 ^ (4 - x))
If Num > 255 Then
IPConvert = "0.0.0.0"
Exit Function
End If
If x = 1 Then
IPConvert = Num
Else
IPConvert = IPConvert & "." & Num
End If
Next
End Function
End Class

3. Создаем пару ключей для подписывания кода (это нужно для того, чтобы коду можно было выдать разрешение на external access или unsafe. Вообще, если коду достаточно разрешений safe, то все предельно упрощается) при помощи программы sn.exe (у меня она нашлась в C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin):

sn -k c:\keyPair.snk

4. Компилируем наш исходный текст в dll:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc /target:library C:\SQLCRLFunctions.vb /keyfile:keypair.snk

Если все хорошо, то где-то там-же получится dll-файл. Мы можем проверить, что он корректно подписан, используя следующую команду:

sn -v SQLCRLFunctions.dll

(4.5 Если используем Serialize Web Service, то дополнительно надо:
sgen /a:SQLCRLFunctions.dll /compiler:/keyfile:keypair.snk
(будет создан SQLCRLFunctions.XmlSerializers.dll)

5. Создаем на SQL-сервере папку c:\sqlscripts и записываем туда наш dll-файл(ы)
6. Далее, в SQL Server Management Studio, выполняем следующие команды:

USE master
GO

CREATE ASYMMETRIC KEY SQLCRLKey FROM EXECUTABLE FILE = 'C:\sqlscripts\SQLCRLFunctions.dll'
CREATE LOGIN SQLCRLLogin FROM ASYMMETRIC KEY SQLCRLKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCRLLogin
GO
REVOKE connect sql FROM SQLCRLLogin
CREATE ASSEMBLY SQLCRLFunctions
FROM 'C:\sqlscripts\SQLCRLFunctions.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
(в случае web-service еще:
CREATE ASSEMBLY [SQLCRLFunctions.XmlSerializers]
FROM 'C:\sqlscripts\SQLCRLFunctions.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO
)
CREATE FUNCTION [dbo].[fn_ResolveDNS](@name [nvarchar](255), @dr [tinyint])
RETURNS [nvarchar](255) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLCRLFunctions].[CRLFunctions].[ResolveDNS]
GO

7. Теперь можно использовать конструкции типа
insert into iptable(ip,name) values('207.46.197.32',dbo.fn_ResolveDNS('207.46.197.32',1)) - для обратного запроса или
insert into iptable(ip,name) values(dbo.fn_ResolveDNS('microsoft.com',0),'microsoft.com') - для прямого.

UPD: Если хотите компилировать dll в Visual Studio, а не в командной строке, удалите Root Namespace в свойствах проекта, в противном случае MS SQL не сможет создать функцию.

Источники:
http://msdn.microsoft.com/en-us/library/k5b5tt23(VS.80).aspx
http://msdn.microsoft.com/en-us/library/ms345106.aspx
http://www.mssqltips.com/tip.asp?tip=1344
http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx