Ошибка с которой и мне пришлось столкнуться, при установке SQL Server либо SP, требовалась перезагрузка, но после перезагрузки сообщение появлялось опять:

Rule "Restart computer" failed.
A computer restart is required. You must restart this computer before installing SQL Server.

Решается проблема достаточно просто, правкой реестра: Находим ветку "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations" и удаляем или переименовываем ключ PendingFileRenameOperations

 

Этого должно быть достаточно, но если и это не поможет, то проделайте теже действия с ключами ещё пары веток реестра:

• "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Control\Session Manager\PendingFileRenameOperations"
• "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Control\Session Manager\PendingFileRenameOperations"

После этого можно продолжить установку SQL Server.

Периодически на форумах возникает вопрос, подобный этим:

• "Почему SQL Server Management Studio так медленно работает?"
• "SSMS постоянно виснет"
• "Почему Management Studio так долго открывается/закрывается".

Для решения подобных проблем, предлагаю проделать кое какие действия:

1. Убедитесь, что ваша рабочая станция соответствует всем минимальным требованиям, как в плане "железа", так и софта, для работы SSMS (SQL Server Management Studio)
Например для удовлетворительно работы Microsoft® SQL Server® 2008 Management Studio Express потребуется:

Системные требования

• Поддерживаемые операционные системы: Windows 7; Windows Server 2003 Service Pack 2; Windows Server 2008; Windows Server 2008 R2; Windows Vista; Windows Vista Service Pack 1; Windows XP Service Pack 2; Windows XP Service Pack 3
• 32-разрядные системы: компьютер, оборудованный процессором Intel или совместимым процессором с тактовой частотой 1 ГГц или выше (рекомендуется 2 ГГц или выше, поддерживается только один процессор)
• 64-разрядные системы: процессор с тактовой частотой 1,4 ГГц или выше (рекомендуется 2 ГГц или выше, поддерживается только один процессор)
• Минимальный объем ОЗУ 512 МБ (рекомендуется 1 ГБ или более)
• 1 ГБ свободного места на диске

2. Посмотрите версию вашего клиента ( "Help" -> "About" ), возможно стоит обновиться до последнего SP (сервис пака) или существует SQL Server Hotfix, который может решить ваши проблемы. Список доступных версий можно посмотреть по ссылке http://www.t-sql.ru/page/versions.aspx.

3. Возможно какое-то из Ваших приложений «мешает» работе SSMS, например антивирус, убедитесь, что он верно настроен.

 
4. Опять же не поленитесь обновить ваши антивирусные базы и просканируйте компьютер на наличие вредоносного кода (вирусы, черви и т.д.).

5. Запретите загрузку помощи из сети: "Tools" -> "Options" -> "Help" -> "Online" укажите "Try local first, not online".

6. Отключите проверку аннулированных сертификатов в "Internet Explorer" -> "Tools" -> "Internet Options" -> "Advanced" и снять «галки» с Check for server certificate revocation и Check for publisher's certificate revocation
(Подробнее: http://msmvps.com/blogs/gladchenko/archive/2007/01/16/497463.aspx)

7. Отключите отображение экрана-заставки при открытии среды SQL Server Management Studio. Правой кнопкой мыши по ярлыку SSMS и в свойствах добавьте параметр "nosplash" (http://msdn.microsoft.com/ru-ru/library/ms162825(SQL.100).aspx).

8. Отключите автосохранение в реестре (ключ AutoRecover Enabled), ибо SSMS фоново сохраняет информацию ( HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover ).

9. Определите значения переменных %vsspv_visualstudio_dir% и %vsspv_vs_install_directory% , либо в реестре явно укажите пути для параметров DefaultSettingsDirectory и LastResetSettings ( HKCU\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\Profile ).
Например:
"%USERPROFILE%\My Documents\SQL Server Management Studio\Projects"
и
"%USERPROFILE%\Мои документы\SQL Server Management Studio\Common7\IDE\Profiles\sqlshell.vssettings"
соответственно.

10. Сам не пробовал, но рекомендуют заменить видеокарту, возможно, это поможет, если ни один из пунктов выше не решил ваших проблем.

Совсем недавно у меня появилась безумная идея: как организовать автоматический перевод текстов внутри сервера БД.

Возможно, это глупо перекладывать такую логику и задачу на SQL Server, т.к. он предназначен совсем для других целей...но, согласитесь, как было бы здорово, если бы наш сервер сам переводил наши данные на другой язык либо динамически либо по расписанию, пусть даже это будет машинный перевод. Прежде чем приступить к реализации этого безумия, я стал искать какие переводчики доступны в сети, главные критери: бесплатный и более или менее читабельный перевод.

Мой выбор пал на переводчик Microsoft® Translator (http://www.microsofttranslator.com/)

 

 

 

 На странице этого переводчика есть возможность переводить тексты on-line:

 

Но самое главное, что Microsoft предлагает богатый набор инструментов для интеграции переводчика в наши системы (http://www.microsofttranslator.com/Tools/)

 

Microsoft Translator Interactive SDK (http://sdk.microsofttranslator.com/) содержит примеры, как можно работать с этим инструментом:

 

Меня интересует SOAP Interface, пару месяцев назад я писал, как можно работать с Web-службами через CLR (http://www.t-sql.ru/post/WebCLR.aspx), но прежде чем начать писать сборку для перевода текстов внутри БД, необходимо зарегистрировать (если вы ещё этого не сделали) AppID (http://www.bing.com/developers/createapp.aspx), который открывает вам доступ к богатому набору инструментов, в том числе и доступ к Microsoft Translator.

После регистрации и активации AppID, можем переходить к разработке нашей сборки, примеры работы с Web-службой описаны на странице Microsoft Translator Interactive SDK.

Для начала создадим прокси-сборку, для этого необходимо выполнить команду:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\wsdl.exe" /o:Translator.cs /n:WS http://api.microsofttranslator.com/V1/Soap.svc

Для удобства, я создал отдельную папку C:\Translator, и в ней создал BAT-ник с этой командой. После запуска этого батника в папке появился файл Translator.cs.

Теперь создадим наш класс, из которого будет сделана наша сборка: TranslatorCLR.cs

using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Collections;
using WS;

public class WSClass
{
  	[SqlFunction(FillRowMethodName = "FillRow",
        	TableDefinition = "Lng nvarchar(10), Language nvarchar(100)")
    	]

//Получаем список доступных языков
public static IEnumerable GetLanguages(String AppID, string locale)
{
	ArrayList Rows = new ArrayList();
        WS.Soap lsc = new WS.Soap();
	string[] s1=lsc.GetLanguages(AppID);
	string[] s2=lsc.GetLanguageNames(AppID, locale);

	for(int i = 0; i<=s1.Length-1; i++)
	{
	Rows.Add(new object[] {s1[i], s2[i]});
	}

        return Rows;
}

public static void FillRow(
                        Object row,
                        out string Lng, out string Language)
{
	object[] xrow = (object[])row;
	Lng = (string)xrow[0];
	Language = (string)xrow[1];
}

//Перевод текста
public static string Translate(String AppID, string InputStr, string forLanguage, string toLanguage)
{

        WS.Soap lsc = new WS.Soap();
	string s=lsc.Translate(AppID, InputStr, forLanguage, toLanguage);
        return s;
}

//Определение на каком языке текст
public static string Detect(String AppID, string InputStr)
{

        WS.Soap lsc = new WS.Soap();
	string s=lsc.Detect(AppID, InputStr);
        return s;
}

}

Теперь на базе 2х классов можем создать нашу сборку, для этого создаю ещё один батник с кодом

"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CSC" /target:library /out:TranslatorWS.dll *.cs

На выходе получаем библиотеку TranslatorWS.dll.

Последний шаг-создаём сборку сериализации XML для типов в нашей сборке, для этого я опять делаю батник с текстом:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\sgen.exe" /a:TranslatorWS.dll

На выходе ещё одна библиотека: TranslatorWS.XmlSerializers.dll

Теперь регистрируем наши сборки в БД, создаём функции и начинаем работать:

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO

USE TestDB
GO

CREATE ASSEMBLY ClrWebServices
FROM 'C:\Translator\TranslatorWS.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'C:\Translator\TranslatorWS.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION [dbo].[GetLanguages]
(
@AppID nvarchar(40),
@locale nvarchar(10)
)
RETURNS TABLE
(
Lng nvarchar(10),
Language nvarchar(100)
)
AS
EXTERNAL NAME [ClrWebServices].[WSClass].[GetLanguages]
GO

CREATE FUNCTION [dbo].[Translate]
(
@AppID nvarchar(40),
@InputStr nvarchar(max),
@forLanguage nvarchar(5),
@toLanguage nvarchar(5)
)
RETURNS nvarchar(MAX)
AS
EXTERNAL NAME [ClrWebServices].[WSClass].[Translate]
GO

CREATE FUNCTION [dbo].[Detect]
(
@AppID nvarchar(40),
@InputStr nvarchar(max)
)
RETURNS nvarchar(MAX)
AS
EXTERNAL NAME [ClrWebServices].[WSClass].[Detect]
GO

Для начала получим список доступных языков:

SELECT * from [dbo].[GetLanguages] ('****************************', 'ru')

Где вместо звёздочек вы указываете ваш AppID, а 2ой параметр-это язык на которов вы хотите получить описание доступных языков, на выходе вот такая табличка:

1. ar арабский
2. bg болгарский
3. zh-CHS китайский упрощенный
4. zh-CHT китайский традиционный
5. cs чешский
6. da датский
7. nl нидерландский
8. en английский
9. ht Гаитянский
10. fi финский
11. fr французский
12. de немецкий
13. el греческий
14. he иврит
15. hu венгерский
16. it итальянский
17. ja японский
18. ko корейский
19. lt литовский
20. no норвежский
21. pl польский
22. pt португальский
23. ro румынский
24. ru русский
25. sk словацкий
26. sl словенский
27. es испанский
28. sv шведский
29. th тайский
30. tr турецкий

На момент написания этого поста, было доступно 30 языков.

Следующим примером я покажу, как определить на каком языке текст, для этого создам таблицу с 2мя записями на русском и английском и запустим нашу функцию:

DECLARE @s TABLE (val varchar(max))
INSERT INTO @s
SELECT 'Enter some text for the language you want detected'
UNION ALL
SELECT 'Шла Cаша по шоссе'

SELECT *, [dbo].[Detect]('****************************', val) FROM @s

Результат:

 

Ну и собственно, сам перевод данных:

DECLARE @s nvarchar(max)
SET @s='With upcoming release of the latest version of Microsoft SQL Server, SQL Server 2008 R2, Microsoft continues to build on the Information Platform Vision by enabling businesses to deliver near real-time information through rich applications. Not only does SQL Server offers a complete approach to managing, accessing and delivering information across the organization; but now the power of SQL Server is extending to the Cloud through SQL Azure. SQL Azure provides a consistent program model and common tools while offering new business opportunities, high availability, and massive scale through distributed data services.
People are at the center of the Information Platform – IT and Database professionals who support the expanding needs of the business, Developers who build solutions quickly to capture business opportunities and End users who are looking to quickly mine volumes of data for business insights. SQL Server 2008 R2 delivers something unique and empowering to each of these contributors.
'

SELECT [dbo].[Translate] ('****************************', @s, 'en', 'ru')

Результат перевода можете оценить сами:

С предстоящем выпуске последней версии Microsoft SQL Server, SQL Server 2008 R2, Майкрософт продолжает опираться на информационной платформы видение, позволяя предприятиям для доставки вблизи реального времени информацию с помощью многофункциональных приложений. Не только делает SQL Server предлагает полный подход к управлению, доступ к и доставки информации по организации; но теперь власть SQL Server расширение в облаке через SQL лазурь. Лазурный SQL предоставляет модель последовательной программы и общие инструменты предлагая новые бизнес-возможности, высокая доступность и массовом масштабе путем распределенных данных услуг. Люди, в центре платформы информации – база данных и ИТ специалистов, которые поддерживают расширение потребностей бизнеса, разработчики, которые создают решения быстро захватить бизнес-возможности и конечных пользователей, которые хотят быстро шахты объемы данных для бизнес-идеи. SQL Server 2008 R2 обеспечивает что-то уникальное и расширение прав и возможностей для каждого из этих вкладчиков..

Практически в любой отрасли существуют свои критические данные, которые требуют шифрования. Начиная с SQL сервера версии 6.x, для шифрования данных вы можете применять функцию ENCRYPT (в последних версия эта функция уже недоступна), которая использует тот же метод (шифрования), что и опция WITH ENCRYPTION.

Начиная с SQL Server 2000 появилась недокументированная функция PWDENCRYPT, которая возвращает Хэш пароля (PWDENCRYPT — это устаревшая функция, которая может не поддерживаться в будущих версиях SQL Server. Вместо этого используйте программу HASHBYTES. HASHBYTES предоставляет больше алгоритмов хэширования), а так же PWDCOMPARE - Хэширует пароль и сравнивает хэш с хэшем существующего пароля.

Для более сложного шифрования данных до версии SQL Server 2005 приходилось "изобретать велосипед", но начиная с 9ой версии шифрование и дешифровка стали встроенным функционалом. А в SQL Server 2008 появилось прозрачное шифрование.

Но в очередной раз попробуем изобрести велосипед и организовать своё шифрование данных в виде CLR-сборки.

В качестве алгоритма шифрования я использую RSA. Алгоритм используется в большом числе криптографических приложений. На 2009 год система шифрования на основе RSA считается надёжной, начиная с размера в 1024 бита.

За основу взят класс RSACryptoServiceProvider.

Код сборки на C#:

using System;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Security.Cryptography;
using System.Text;

public class RSACLR
{
    [SqlFunction(FillRowMethodName = "FillRow",
        TableDefinition = "publicAndPrivateKeys nvarchar(max), justPublicKey nvarchar(max)")
    ]

//Генерируем 2 ключа: закрытый с публичным и только публичный
    public static IEnumerable GenerateKeys(int KeySize)
    {
        ArrayList row = new ArrayList();
        RSACryptoServiceProvider RSAProvider = new RSACryptoServiceProvider(KeySize);
        row.Add(new object[] { RSAProvider.ToXmlString(true), RSAProvider.ToXmlString(false) });
        return row;
    }

    public static void FillRow(Object row, out string publicAndPrivateKeys, out string justPublicKey)
    {

        object[] xrow = (object[])row;
        publicAndPrivateKeys = (string)xrow[0];
        justPublicKey = (string)xrow[1];

    }

//Шифрование, на входе строка, размер ключа и открытый(публичный) ключ
    public static string EncryptString(string inputString, int dwKeySize,
                             string xmlString)
    {
        RSACryptoServiceProvider rsaCryptoServiceProvider =
                                      new RSACryptoServiceProvider(dwKeySize);
        rsaCryptoServiceProvider.FromXmlString(xmlString);
        int keySize = dwKeySize / 8;
        byte[] bytes = Encoding.UTF32.GetBytes(inputString);

        int maxLength = keySize - 42;
        int dataLength = bytes.Length;
        int iterations = dataLength / maxLength;
        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i <= iterations; i++)
        {
            byte[] tempBytes = new byte[
                    (dataLength - maxLength * i > maxLength) ? maxLength :
                                                  dataLength - maxLength * i];
            Buffer.BlockCopy(bytes, maxLength * i, tempBytes, 0,
                              tempBytes.Length);
            byte[] encryptedBytes = rsaCryptoServiceProvider.Encrypt(tempBytes,
                                                                      true);

            Array.Reverse(encryptedBytes);

            stringBuilder.Append(Convert.ToBase64String(encryptedBytes));
        }
        return stringBuilder.ToString();
    }

//Расшифровка, на входе шифрованная строка, размер ключа и закрытый ключ
    public static string DecryptString(string inputString, int dwKeySize,
                             string xmlString)
    {
        RSACryptoServiceProvider rsaCryptoServiceProvider
                                 = new RSACryptoServiceProvider(dwKeySize);
        rsaCryptoServiceProvider.FromXmlString(xmlString);
        int base64BlockSize = ((dwKeySize / 8 ) % 3 != 0) ?
          (((dwKeySize / 8 ) / 3) * 4) + 4 : ((dwKeySize / 8 ) / 3) * 4;
        int iterations = inputString.Length / base64BlockSize;
        ArrayList arrayList = new ArrayList();
        for (int i = 0; i < iterations; i++)
        {
            byte[] encryptedBytes = Convert.FromBase64String(
                 inputString.Substring(base64BlockSize * i, base64BlockSize));

            Array.Reverse(encryptedBytes);
            arrayList.AddRange(rsaCryptoServiceProvider.Decrypt(
                                encryptedBytes, true));
        }
        return Encoding.UTF32.GetString(arrayList.ToArray(
                                  Type.GetType("System.Byte")) as byte[]);
    }
}

Как всегда минимум кода.

Теперь регистрируем нашу сборку и создаём на её основе 3 функции (получение ключей, шифрование и расшифровка):

--Включаем выполнение пользовательских сборок
SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

--Создаём тестовую БД для демострации
CREATE DATABASE TestDB
GO

--Модули базы данных (например, пользовательские функции или хранимые процедуры),
--которые используют контекст олицетворения, могут обращаться к ресурсам,
--находящимся вне базы данных.
ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO

--Переходим в нашу БД
USE TestDB
GO

--Регистрируем сборку
CREATE ASSEMBLY RSACLR
FROM 'C:\RSA\RSACLR.dll'
WITH PERMISSION_SET = UNSAFE;
GO

--Получение ключей
CREATE FUNCTION GenerateKeys(@KeySize int)
RETURNS TABLE
(
publicAndPrivateKeys NVARCHAR(max),
justPublicKey NVARCHAR(max)
)
EXTERNAL NAME RSACLR.RSACLR.GenerateKeys;
GO

--Шифрование строки
CREATE FUNCTION EncryptString
(
@inputString nvarchar(MAX),
@dwKeySize int,
@xmlString nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
EXTERNAL NAME RSACLR.RSACLR.EncryptString;
GO

--Расшифровка строки
CREATE FUNCTION DecryptString
(
@inputString nvarchar(MAX),
@dwKeySize int,
@xmlString nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
EXTERNAL NAME RSACLR.RSACLR.DecryptString;
GO

Теперь продемонстрирую, как это работает:

--Создадим таблицу, в которой будут "жить" наши ключи
CREATE TABLE MyKeys(id int identity, publicAndPrivateKeys xml, justPublicKey xml)
--Добавим несколько ключей разной размерности
INSERT INTO MyKeys
SELECT * FROM GenerateKeys(512)
UNION ALL
SELECT * FROM GenerateKeys(1024)
UNION ALL
SELECT * FROM GenerateKeys(2048)

--и посмотрим в каком виде они у нас хранятся
SELECT * FROM MyKeys

Для наглядности я выбрал для хранения ключей тип XML

Теперь можно с помощью любой из пар ключей шифровать и расшифровывать данные.

--Создаём таблицу слов
CREATE TABLE MyWords(Val nvarchar(max), EncryptVal nvarchar(max))
--и наполним её данными
INSERT INTO MyWords
SELECT 'Knyazev Alexey', null
UNION ALL
SELECT 'http://www.t-sql.ru', null
UNION ALL
SELECT 'SQL Server', null

--теперь зашифруем с помощью открытого ключа
DECLARE @k nvarchar(max), @k2 nvarchar(max)
SELECT @k=convert(nvarchar(max), justPublicKey) FROM MyKeys WHERE id=1

UPDATE MyWords
SET EncryptVal=dbo.EncryptString(Val, 512, @k)

Расшифровать можно так:

DECLARE @k nvarchar(max), @k2 nvarchar(max)
SELECT @k=convert(nvarchar(max), PublicAndPrivateKeys) FROM MyKeys WHERE id=1

SELECT *, dbo.DecryptString(EncryptVal, 512, @k) FROM MyWords

Вот и весь "велосипед", цель данного примера показать, в очередной раз, как CLR-сборки могут разнообразить и улучшить работу с SQL Server.

У любого разработчика БД рано или поздно возникает потребность вставить файл(ы) в базу. Преимуществ хранения файлов внутри БД уйма, как и недостатков, но сегодня не об этом, а вообще о том, как возможно загрузить/выгрузить файлы. Наиболее интересный и правильный вариант, на сегодня, работы с файлами - это FileStream (появился в SQL Server 2008).

Хранилище FILESTREAM в SQL Server 2008 позволяет приложениям на основе SQL Server хранить в файловой системе неструктурированные данные, такие как документы и изображения. Приложения могут эффективно использовать многопоточные API-интерфейсы и производительность файловой системы, одновременно обеспечивая согласованность между неструктурированными и соответствующими им структурированными данными.

Но всё же до сих пор многих интересует вариант хранения файлов внутри обычных таблиц в полях BLOB (image/varbinary). Вставку файлов в БД и последующую работу с ними легко организовать с помощью нескольких строк на любом языке программирования, но мы попробуем обойтись средствами SQL Server`а.

Для начала рекомендую ознакомиться с статьёй Вставка картинки (файла) в поле таблицы и работа с Blob полями.

Начиная с SQL Server 2005 вставку файла можно сделать с помощью OPENROWSET

INSERT INTO dbo.Files (FileName, [File])
SELECT 'MyPic.bmp' AS FileName, *
FROM OPENROWSET(BULK N'C:\MyPic.bmp', SINGLE_BLOB) AS [File]

Но это только загрузка файла.

Тут же возникла идея реализовать эту задачу с помощью сборки CLR, раз нет в T-SQL стандартного решения, то расширим его самостоятельно с помощью своей сборки. Но раз уж это будит свой функционал, то я решил его дополнить ещё одной фичей…компрессия файла!!! База не резиновая!

Код достаточно легко читаем, но если вопросы останутся, то я всегда рад буду помочь, итак код сборки на C#:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.IO.Compression;

public class FileCompressCLR
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]

    //Загрузка файла в виде бинарника, на входе Полный путь к файлу
    public static SqlBytes LoadFile(string FileName)
    {
        FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read);

        MemoryStream ms = new MemoryStream();
        int sourcebyte = file.ReadByte();
        while (sourcebyte != -1)
        {
            ms.WriteByte((byte)sourcebyte);
            sourcebyte = file.ReadByte();
        }

        file.Close();
        return new SqlBytes(ms);
    }

    //Загрузка файла в виде бинарника с компрессией, на входе Полный путь к файлу
    public static SqlBytes LoadCompressFile(string FileName)
    {
        FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read);
        byte[] buffer = new byte[file.Length];
        file.Read(buffer, 0, buffer.Length);
        file.Close();

        MemoryStream ms = new MemoryStream();

        DeflateStream compress = new DeflateStream(ms, CompressionMode.Compress, true);
        compress.Write(buffer, 0, buffer.Length);

        compress.Close();
        compress = null;

        return new SqlBytes(ms);
    }

    //Выгрузка файла в указанный источник
    public static string SaveFile(string FileName, SqlBytes CompressedFile)
    {
        if (CompressedFile.IsNull)
            return "Error";

        try
        {

            FileStream file = File.Create(FileName);

            int sourcebyte = CompressedFile.Stream.ReadByte();
            while (sourcebyte != -1)
            {
                file.WriteByte((byte)sourcebyte);
                sourcebyte = CompressedFile.Stream.ReadByte();
            }

            file.Close();
        }

        catch (Exception)
        {
            return "Error";
        }

        return "OK";
    }

    //Выгрузка файла в указанный источник с предварительной декомпрессией
    public static string SaveDecompressFile(string FileName, SqlBytes CompressedFile)
    {
        if (CompressedFile.IsNull)
        return "Error";

        DeflateStream decompress = new DeflateStream(CompressedFile.Stream, CompressionMode.Decompress, true);

        try
        {

            FileStream file = File.Create(FileName);

            int sourcebyte = decompress.ReadByte();
            while (sourcebyte != -1)
            {
                file.WriteByte((byte)sourcebyte);
                sourcebyte = decompress.ReadByte();
            }

            file.Close();
        }

        catch (Exception)
        {
            return "Error";
        }

        finally
        {
            decompress.Close();
            decompress = null;
        }    

        return "OK";
    }
}

Компилируем код и сохраняем библиотеку в папку C:\CLR.

Теперь включаем на сервере возможность работы с CLR, создаём тестовую БД, регистрируем сборку на сервере БД, создаём 4 функции для работы с файлами:

--Включаем выполнение пользовательских сборок
SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

--Создаём тестовую БД для демострации
CREATE DATABASE TestDB
GO

--Модули базы данных (например, пользовательские функции или хранимые процедуры),
--которые используют контекст олицетворения, могут обращаться к ресурсам,
--находящимся вне базы данных.
ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO

--Переходим в нашу БД
USE TestDB
GO

--Регистрируем сборку
CREATE ASSEMBLY FileCompressCLR
FROM 'C:\CLR\FileCompressCLR.dll'
WITH PERMISSION_SET = UNSAFE;
GO

--Создаём функцию загрузки обычного файла
CREATE FUNCTION [LoadFile]
(
@FileName nvarchar(MAX)
)
RETURNS varbinary(MAX)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[LoadFile];
GO

--Создаём функцию загрузки файла + его компрессия
CREATE FUNCTION [LoadCompressFile]
(
@FileName nvarchar(MAX)
)
RETURNS varbinary(MAX)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[LoadCompressFile];
GO

--Создаём функцию выгрузки обычного файла
CREATE FUNCTION [SaveFile]
(
@FileName nvarchar(MAX),
@CompressedFile varbinary(MAX)
)
RETURNS nvarchar(10)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[SaveFile];
GO

--Создаём функцию выгрузки сжатого файла
CREATE FUNCTION [SaveDecompressFile]
(
@FileName nvarchar(MAX),
@CompressedFile varbinary(MAX)
)
RETURNS nvarchar(10)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[SaveDecompressFile];
GO

Всё, мы расширили возможности нашего SQL Server`a. Теперь покажу, как работать с этими функциями.

Для начала посмотрим, разницу в размере бинарника, который мы загружаем (в качестве демонстрации использую файл библиотеки CLR):

SELECT
datalength(dbo.LoadFile('C:\CLR\FileCompressCLR.dll')) FileSize,
datalength(dbo.LoadCompressFile('C:\CLR\FileCompressCLR.dll')) CompressFileSize

Результат не может не радовать (сжатый файл в 2 раза меньше):

5632 против 2900

Теперь пример, как вставить в БД сразу несколько файлов:

--Создаём таблицу для демонстрации массовой вставки
CREATE TABLE Files
(
id int identity,
FileName nvarchar(max),
[File] varbinary (max) default null,
CompressFile varbinary(max) default null
)

--Вставка только названий с файлами
INSERT INTO Files (FileName)
SELECT 'C:\CLR\FileCompressCLR.dll'
UNION ALL
SELECT 'C:\CLR\Winter.jpg'
UNION ALL
SELECT 'C:\CLR\Test.txt'

--Загружаем сами бинарники
UPDATE Files
SET
[File]=dbo.LoadFile(FileName),
[CompressFile]=dbo.LoadCompressFile(FileName)

Покажу как выглядит таблица:

 

Теперь удалим из папки все файлы, которые были загружены в таблицу, и попробуем их вытащить из БД

SELECT FileName,
dbo.SaveFile(replace(FileName, '.','1.'), [File]),
dbo.SaveDecompressFile(replace(FileName, '.','2.'), CompressFile)
FROM Files

Для различия файлов я в качестве имени, под которым сохраняется файл на диске, подставляю в имя суффиксы 1 и 2 ( replace(FileName, ‘.’,'1.’) )

Файлы появились и они полностью рабочии!

В одной из предыдущих статей я писал, как читать почту в SQL Server 2005/2008/2008R2 по протоколу POP3 (http://www.t-sql.ru/post/POP3CLR.aspx), там же пообещал, что в ближайшее время покажу, как осуществлять доступ к почте по протоколу IMAP.

Область применения протокола IMAP ( Internet Message Access Protocol ) аналогична области применения протокола POP3: он тоже предназначен для получения почты и используется на участке между MUA получателя и хранилищем сообщений. IMAP предоставляет более широкие возможности работы с почтовыми ящиками, чем POP3: он позволяет работать с несколькими почтовыми ящиками на одном или нескольких серверах IMAP как с файлами и каталогами на собственной машине пользователя. Обычно почтовые ящики сервера IMAP действительно представляют собой файлы в специальном каталоге сервера и его подкаталогах.

Как и в прошлый раз - это будит CLR-сборка (работает достаточно шустро). Но прежде рекомендую ознакомиться с описанием IMAP-протокола.

Полный перечень всех команд, а так же описание протокола можно прочитать в Запросе комментариев (англ. Request for Comments, RFC) — документ из серии пронумерованных информационных документов Интернета, содержащих технические спецификации и Стандарты.

RFC 3501 (IMAP версии 4 издание 1 (IMAP4rev1)), он заменил RFC 2060 (IMAP версии 4 (IMAP4)). Русскую версию спецификации так же легко найти в РУ-нете (http://opds.sut.ru/electronic_manuals/mail/5_IMAP.htm, http://book.itep.ru/4/44/imap4443.htm)

Сервер IMAP ожидает соединения от клиентов на порту TCP 143. После установления соединения сервер посылает свое приветствие клиенту, и начинается диалог, в котором клиент посылает серверу команды, а сервер сообщает о результатах их выполнения или присылает затребованную клиентом информацию. Как и сеанс POP3, сеанс IMAP делится на несколько состояний ( states ). Допустимый набор команд зависит от текущего состояния сеанса. Сеанс может находиться в одном из следующих состояний:

• -неаутентифицированное состояние (Not Authenticated State): клиент должен пройти процедуру аутентификации прежде, чем сможет выполнять большинство команд;
• -аутентифицированное состояние (Authenticated State): клиент аутентифицирован и должен выбрать почтовый ящик, прежде чем сможет работать с отдельными сообщениями;
• -выбранное состояние (Selected State): почтовый ящик выбран;
• -состояние выхода (Logout State): сеанс завершается.

 

 

Переходы, обозначенные цифрами:

1. соединение без предварительной аутентификации;
2. соединение с предварительной аутентификацией;
3. отвергнутое соединение;
4. успешная аутентификация;
5. успешное выполнение команды SELECT или EXAMINE;
6. команда CLOSE или неудачное завершение команды SELECT или EXAMINE;
7. команда LOGOUT или потеря связи.

Сами Команды клиента и ответы сервера IMAP я в этой статье описывать не буду, так как вы сами при желании сможете с ними ознакомиться по ссылкам выше, а сразу приведу код сборки:

using System;
using Microsoft.SqlServer.Server;
using System.Net.Sockets;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;

public class IMAPClr
{
    private static TcpClient IMAP;
    private static NetworkStream ns;
    private static StreamWriter sw;
    private static StreamReader sr;

    [SqlFunction]  

//Выводим кол-во писем
    public static int MailCount(string Host, string User, string Password)
    {

        string s = Connect(Host, 143);
        if (s.Substring(0, 4) != "* OK")
        {
            return -1;
        }

        s = Authenticate(User, Password);
        if (s.Substring(0, 4) != "$ OK")
        {
            return -2;
        }

        sw.WriteLine("$ LOGIN " + User + " " + Password);
        sw.Flush();
        Response();

        sw.WriteLine("$ STATUS INBOX (messages)");
        sw.Flush();

        string res = Response();
        Match m = Regex.Match(res, "[0-9]*[0-9]");

        Disconnect();
        return Convert.ToInt32(m.ToString());

    }

//Кол-во непрочитанных писем
    public static int MailUnreadCount(string Host, string User, string Password)
    {

        string s = Connect(Host, 143);
        if (s.Substring(0, 4) != "* OK")
        {
            return -1;
        }

        s = Authenticate(User, Password);
        if (s.Substring(0, 4) != "$ OK")
        {
            return -2;
        }

        sw.WriteLine("$ LOGIN " + User + " " + Password);
        sw.Flush();
        Response();

        sw.WriteLine("$ STATUS INBOX (unseen)");
        sw.Flush();

        string res = Response();
        Match m = Regex.Match(res, "[0-9]*[0-9]");

        Disconnect();
        return Convert.ToInt32(m.ToString());

    }

    [SqlFunction(FillRowMethodName = "FillRowHeaders"
    , TableDefinition = "MessageID int, MessageHeader nvarchar(max)")]

//Заголовки
    public static IEnumerable GetMessagesHeaders(string Host, string User, string Password, int MessageStart, int MessageEnd)
    {
        ArrayList rows = new ArrayList();

        string s = Connect(Host, 143);
        if (s.Substring(0, 4) != "* OK")
        {
            rows.Add(new object[] { -1, s });
            return rows;
        }

        s = Authenticate(User, Password);
        if (s.Substring(0, 4) != "$ OK")
        {
            rows.Add(new object[] { -2, s });
            return rows;
        }

        SelectInbox();

        while (MessageStart <= MessageEnd)
        {
            rows.Add(new object[] { MessageStart, GetMessageHeaders(MessageStart).ToString() });
            MessageStart += 1;
        }
        Disconnect();
        return rows;
    }

    public static void FillRowHeaders(Object row, out int MessageID, out string MessageHeader)
    {

        object[] xrow = (object[])row;
        MessageID = (int)xrow[0];
        MessageHeader = (string)xrow[1];

    }

    [SqlFunction(FillRowMethodName = "FillRowMessages"
    , TableDefinition = "MessageID int, Message nvarchar(max)")]

//Тело письма
    public static IEnumerable GetMessages(string Host, string User, string Password, int MessageStart, int MessageEnd)
    {
        ArrayList rows = new ArrayList();

        while (MessageStart <= MessageEnd)
        {
            string s = Connect(Host, 143);
            if (s.Substring(0, 4) != "* OK")
            {
                rows.Add(new object[] { -1, s });
                return rows;
            }

            s = Authenticate(User, Password);
            if (s.Substring(0, 4) != "$ OK")
            {
                rows.Add(new object[] { -2, s });
                return rows;
            }
            SelectInbox();
            rows.Add(new object[] { MessageStart, GetMessage(MessageStart).ToString()});
            Disconnect();
            MessageStart += 1;
        }

        return rows;
    }

    public static void FillRowMessages(Object row, out int MessageID, out string Message)
    {

        object[] xrow = (object[])row;
        MessageID = (int)xrow[0];
        Message = (string)xrow[1];
    }

//Подключение к IMAP-серверу
    private static string Connect(string Host, int port)
    {
        try
        {
            IMAP = new TcpClient(Host, port);
            ns = IMAP.GetStream();
            sw = new StreamWriter(ns);
            sr = new StreamReader(ns);
            return Response();
        }
        catch (SocketException ex)
        {
            return ex.Message;
        }
    }

//Отключаемся от сервера
    public static void Disconnect()
    {
        sw.WriteLine("$ LOGOUT");
        sw.Flush();
        IMAP.Close();
    }

//Идентификация пользователя
    public static string Authenticate(string User, string Password)
    {
        sw.WriteLine("$ LOGIN " + User + " " + Password);
        sw.Flush();
        return Response();
    }

//Открываем доступ к указанному почтовому ящику
    public static string SelectInbox()
    {
        sw.WriteLine("$ EXAMINE INBOX");
        sw.Flush();
        return Response();
    }

//Заголовок письма по его номеру
    public static object GetMessageHeaders(int index)
    {
        sw.WriteLine("$ FETCH " + index + " (body[header.fields (from subject date)])");
        sw.Flush();

        return Response();
    }

//Письмо по номеру
    public static object GetMessage(int index)
    {

        sw.WriteLine("$ FETCH " + index + " body[text]");
        sw.Flush();

        return Response();
    }

    private static string Response()
    {
        byte[] data = new byte[IMAP.ReceiveBufferSize];
        int ret = ns.Read(data, 0, data.Length);
        return Encoding.ASCII.GetString(data).TrimEnd();
    }

}

Если у Вас возникнут вопросы по коду, то буду рад ответить в комментариях к этой статье.

Далее регистрируем сборку и создаём набор T-SQL функций для работы с ней:

--Регистрируем сборку
CREATE ASSEMBLY ClrIMAP
FROM 'C:\IMAP\IMAPClr.dll'
WITH PERMISSION_SET = UNSAFE;
GO

--Функция выводит кол-во писем в ящике
CREATE FUNCTION [dbo].[MailCount]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255)
)
RETURNS [int] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ClrIMAP].[IMAPClr].[MailCount]
GO

--Функция с кол-ом непрочитанных писем
CREATE FUNCTION [dbo].[MailUnreadCount]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255)
)
RETURNS [int] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ClrIMAP].[IMAPClr].[MailUnreadCount]
GO

--Функция чтения заголовков писем
CREATE FUNCTION [dbo].[GetMessagesHeaders]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255),
@MessageStart int,
@MessageEnd int
)
RETURNS TABLE
(
MessageID INT,
MessageHeader NVARCHAR(max)
)
EXTERNAL NAME [ClrIMAP].[IMAPClr].[GetMessagesHeaders];
GO

--Функция чтения самого письма (писем)
CREATE FUNCTION [dbo].[GetMessages]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255),
@MessageStart int,
@MessageEnd int
)
RETURNS TABLE
(
MessageID INT,
Message NVARCHAR(max)
)
EXTERNAL NAME [ClrIMAP].[IMAPClr].[GetMessages];
GO

Пара примеров работы с этими функциями:

--Выводим кол-во писем на сервере (всего и новых)
select
[dbo].[MailCount] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword') as MailCount,
[dbo].[MailUnreadCount] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword') as MailUnreadCount

--Выводим 5 первых писем и их заголовки (от кого, дата, тема)
select t1.MessageID, t1.MessageHeader, t2.Message from
[dbo].[GetMessagesHeaders] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword', 1, 5) t1
inner join
[dbo].[GetMessages] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword', 1, 5) t2
on t1.MessageID=t2.MessageID

Пример результата:

 

В одной из своих заметок в блоге я публиковал, как можно с помощью PowerShell заскриптовать все объекты той или иной БД на сервере баз данных (Скриптуем объекты БД (PoSh) ), а на днях в коментариях в этому скрипту появился вопрос, как заскриптовать сами данные, а не только "скелет" базы.

Но прежде чем показать скрипт на PowerShell, перечислю другие возможные способы реализации данной задачи

Во первых - подумайте, действительно ли вам необходимы данные в виде файла, не проще ли (а самое главное правильнее) сделать Бэкап базы

Второй вариант - это T-SQL скрипт, т.к. в сети легко найти подобное решение, то я не стал изобретать велосипед, а предлагаю воспользоваться скриптом Narayana Vyas Kondreddi: SQL Server 2000 либо SQL Server 2005/2008

Ещё одно решение - стандартный клиент для работы с SQL Server 2005 и выше: SSMS (Microsoft SQL Server Management Studio). Для SSMS2005 - это надстройка (ADD-IN) Generate Insert statements from resultsets, tables or database из пакета дополнительных надстроек http://www.ssmstoolspack.com/

При этом в SSMS2008 подобная надстройка добавлена: Tasks->Generate Scripts..., а дальше в Wizard`e отметить, что в скрипт мы хотим включить не только объекты БД, но и данные.

Ну и вариант на PowerShell с использованием SMO:

$server=Read-Host "Укажите имя сервера БД"
$database=Read-Host "Имя БД"
$output_file=Read-Host "Вывести результат в файл (укажите имя файла)"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)

$db = $srv.Databases[$database]

$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $true;
$scripter.Options.DriAll = $true
$scripter.Options.DriAllConstraints = $true
$scripter.Options.DriAllKeys = $true
$scripter.Options.DriChecks = $true
$scripter.Options.DriClustered = $true
$scripter.Options.DriDefaults = $true
$scripter.Options.DriForeignKeys = $true
$scripter.Options.DriIncludeSystemNames = $true
$scripter.Options.DriIndexes = $true
$scripter.Options.DriNonClustered = $true
$scripter.Options.DriPrimaryKey = $true
$scripter.Options.DriUniqueKeys = $true

$scripter.Options.AppendToFile =$true

$scripter.Options.FileName = $output_file
$scripter.Options.ToFileOnly = $true

foreach ($tbl in $db.Tables)
{
foreach ($s in $scripter.EnumScript($tbl.Urn)) { write-host $s }
}

Список пар-ов ScriptingOptions, можно посмотреть на MSDN http://msdn.microsoft.com/ru-ru/library/microsoft.sqlserver.management.smo.scriptingoptions_members.aspx

Кратко суть задачи: ГАРАНТИРОВАННАЯ отправка СМС-сообщений из уже существующих информационных систем, которые используются у нас на предприятии. Первое, что пришло в голову-это отправка через почту (e-mail to SMS) на ящик вида [номер абонента]@[адрес оператора] большинство сотовых операторов предоставляют подобную услугу (например у МТС: 7913ххххххх@sms.mtslife.ru), хоть и не гарантируют 100% доставку всех сообщений таким образом. После того, как объём сообщений, который стал генерить наш сервер, стал достаточно большим, часть сообщений стали "пропадать" либо приходить с большими задержками. Пришлось искать другое решение.

Идеальный вариант - это работа на прямую c СМС-сервером оператора по протоколу SMPP, но это дополнительная головная боль в виде подписания договора с операторами, которые предоставляют такую возможность для корпоративных клиентов и скорее всего - это дорогая услуга.

В качестве ещё одного варианта был поиск посредников, которые предоставляют подобные услуги. Выбор пал на ресурс http://sms-host.ru (не сочтите за рекламу). Они гарантируют доставку и отслеживание состояния СМС-сообщений через Веб сервис, но ПЛАТНО. Ну да ладно, попросили у них тестовый доступ и я сразу же загорелся идеей сделать CLR-сборку для работы с Веб сервисом.

Адрес Веб-сервиса: https://sms-host.ru/service/smshostws.asmx?WSDL.

Описание классов: http://www.sms-host.ru/docs/.

 

SumbitSm

Отправляет коллекцию одиночных сообщений

Test
The test form is only available for requests from the local machine.
SOAP 1.1
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.

POST /service/smshostws.asmx HTTP/1.1
Host: sms-host.ru
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://sms-host.ru/SumbitSm"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Header>
    <Authentication xmlns="http://sms-host.ru/">
      <User>string</User>
      <Password>string</Password>
    </Authentication>
  </soap:Header>
  <soap:Body>
    <SumbitSm xmlns="http://sms-host.ru/">
      <messageList>
        <WsSubmitSm ValidityPeriodSmpp="string" MessageText="string" SenderAddress="string" ReceiverAddress="string" MessageId="guid" />
        <WsSubmitSm ValidityPeriodSmpp="string" MessageText="string" SenderAddress="string" ReceiverAddress="string" MessageId="guid" />
      </messageList>
    </SumbitSm>
  </soap:Body>
</soap:Envelope>
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <SumbitSmResponse xmlns="http://sms-host.ru/">
      <SumbitSmResult>
        <WsSubmitSmResp ErrorDescription="string" ErrorCode="int" PartCount="unsignedByte" MessageId="guid" />
        <WsSubmitSmResp ErrorDescription="string" ErrorCode="int" PartCount="unsignedByte" MessageId="guid" />
      </SumbitSmResult>
    </SumbitSmResponse>
  </soap:Body>
</soap:Envelope>

 

QuerySm

Возвращает состояние сообщений, ранее отправленных с помощью метода SumbitSm

Test
The test form is only available for requests from the local machine.
SOAP 1.1
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.

POST /service/smshostws.asmx HTTP/1.1
Host: sms-host.ru
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://sms-host.ru/QuerySm"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Header>
    <Authentication xmlns="http://sms-host.ru/">
      <User>string</User>
      <Password>string</Password>
    </Authentication>
  </soap:Header>
  <soap:Body>
    <QuerySm xmlns="http://sms-host.ru/">
      <messageList>
        <WsQuerySm MessageId="guid" />
        <WsQuerySm MessageId="guid" />
      </messageList>
    </QuerySm>
  </soap:Body>
</soap:Envelope>
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <QuerySmResponse xmlns="http://sms-host.ru/">
      <QuerySmResult>
        <WsQuerySmResp EndTime="dateTime" BeginTime="dateTime" PartErrorCount="unsignedByte" PartDeliveredCount="unsignedByte" PartSendCount="unsignedByte" StateName="string" StateCode="int" MessageId="guid" />
        <WsQuerySmResp EndTime="dateTime" BeginTime="dateTime" PartErrorCount="unsignedByte" PartDeliveredCount="unsignedByte" PartSendCount="unsignedByte" StateName="string" StateCode="int" MessageId="guid" />
      </QuerySmResult>
    </QuerySmResponse>
  </soap:Body>
</soap:Envelope>

Как видно из документации, задача более чем тривиальная.

Приступим:

1) Создадим папку C:\SMS

2) Создаём прокси-сборку, запустив "

C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\wsdl.exe" /o:SMSHost.cs /n:WS https://sms-host.ru/service/smshostws.asmx?WSDL

На выходе у нас получился файл SMSHost.cs

3) Немного изучив Веб-сервис, пишем нашу сборку, с 2мя функциями-отправка смс и получение состояния отправленного сообщения:

using System;
using Microsoft.SqlServer.Server;
using WS;

public class WSClass
{
            [SqlFunction]

    public static int SendMessage(string User, string Password, string SenderAddress, string ReceiverAddress, Guid MessageID, string ValidityPeriodSmpp, string Message)
    {

	WS.Authentication auth = new WS.Authentication();
            auth.User = User;
            auth.Password = Password;

	WS.WsSubmitSm s = new WS.WsSubmitSm();
            s.SenderAddress = SenderAddress;
            s.ReceiverAddress = ReceiverAddress;
	    s.MessageId = MessageID;
            s.ValidityPeriodSmpp = ValidityPeriodSmpp;
            s.MessageText = Message;

            WS.WsSubmitSm[] ss = new WS.WsSubmitSm[1];
            ss[0] = s;

	WS.SmsHostWs SendSMS=new WS.SmsHostWs();
	SendSMS.AuthenticationValue=auth;

	WS.WsSubmitSmResp Resp=new WS.WsSubmitSmResp();
	Resp=SendSMS.SumbitSm(ss)[0];

	return Resp.ErrorCode;
    }

  public static string MessageState(string User, string Password, Guid MessageID)
    {

	WS.Authentication auth = new WS.Authentication();
        auth.User = User;
        auth.Password = Password;

	WS.WsQuerySm q=new WS.WsQuerySm();
	q.MessageId=MessageID;

	WS.WsQuerySm[] qq = new WS.WsQuerySm[1];
        qq[0] = q;

	WS.SmsHostWs SendSMS=new WS.SmsHostWs();
	SendSMS.AuthenticationValue=auth;

	WS.WsQuerySmResp[] Resp = new WS.WsQuerySmResp[1];
        Resp[0]=SendSMS.QuerySm(qq)[0];

	return Resp[0].StateName;
    }

}

Сохраним этот текст в нашей папке в файл MyCLR.cs

4) Компилируем нашу библиотеку:

"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CSC" /target:library /out:WSSMSHost.dll *.cs

На выходе файл WSSMSHost.dll

5) Создаем сборку сериализации XML:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\sgen.exe" /a:WSSMSHost.dll

На выходе файл WSSMSHost.XmlSerializers.dll

Вроде бы ВСЁ. Дальше регистрируем наши библиотеки и пробуем отправить сообщения/прочитать их состояние.

CREATE ASSEMBLY ClrSMSHost
FROM 'C:\SMS\WSSMSHost.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [ClrSMSHost.XmlSerializers]
FROM 'C:\SMS\WSSMSHost.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

Создаём функции:

CREATE FUNCTION [dbo].[SendMessage]
(
@User nvarchar(255),
@Password nvarchar(255),
@SenderAddress nvarchar(255),
@ReceiverAddress nvarchar(255),
@MessageID uniqueidentifier,
@ValidityPeriodSmpp nchar(16),
@Message nvarchar(max)
)
RETURNS [int] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ClrSMSHost].[WSClass].[SendMessage]
GO

CREATE FUNCTION [dbo].[MessageState]
(
@User nvarchar(255),
@Password nvarchar(255),
@MessageID uniqueidentifier
)
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ClrSMSHost].[WSClass].[MessageState]
GO

А теперь покажу как работать с этими функциями:

--Таблица для тестов
create table MyTable (MyID uniqueidentifier, Phone char(11), MyMessage nvarchar(50))
--Вставим всего 2 записи, для демонстрации
insert into MyTable
select NewID(), '7904ххххххх', 'Message1'
union all
select NewID(), '7904ххххххх', 'Message2'

Ну и отправим сообщения:

select  MyID, MyMessage, [dbo].[SendMessage] ('MyUser', 'MyPassword', 'SMS-Host.ru',
Phone, MyID, '000002233429000R', MyMessage)
from MyTable

где 'MyUser', 'MyPassword' - это логин и пароль на доступ к Веб сервису, а '000002233429000R' - время жизни сообщения (Время, в течение которого сервер будет пытаться отправить сообщение, если получатель недоступен. Если по истечении времени жизни сообщение все еще не доставлено, попытки доставки прекращаются и сообщение считается недоставленным.

Сообщение может стать недоставленным и до истечения времени жизни, если соответствующие сервисы оператора связи сообщили о невозможности доставки (например, номер получателя не существует). По умолчанию время жизни сообщения 2 суток с момента первой попытки отправки.), Для данного поля допустим только интервальный формат времени, то есть в форме YYMMDDhhmmsstnnR - Формат времени в стандарте SMPP v3.4.

Результат запроса: 

1. BF23FCC4-00DD-4309-BF5A-AC4CAA22160E Message1 0
2. 97063992-182E-4816-B648-35B8AE6FFBAE Message1 0

В ответе на запрос каждый элемент содержит атрибут ErrorCode Коды ошибок подразделяются на следующие категории:

Диапазон значений Категория

• 0 Ошибок нет
• 1-10 Текст сообщения не прошел проверку
• 11-20 Номер получателя не прошел проверку
• 21-30 Номер отправителя не прошел проверку
• 31-40 Ошибки при постановке в очередь на отправку (например, если сообщение с таким идентфикатором уже отправлялось)

Отслеживать состояние сообщений можно запросом:

select MyID, [dbo].[MessageState] ('MyUser', 'MyPassword', MyID)
from MyTable

Результат запроса:

BF23FCC4-00DD-4309-BF5A-AC4CAA22160E	Сообщение доставлено
97063992-182E-4816-B648-35B8AE6FFBAE	Сообщение доставлено

 

Таким образом, практически на коленке, реализована задача…надеюсь кому-то этот пост будет полезен. Если у вас возникнут вопросы, то буду рад вам помочь.

Веб-служба, веб-сервис (англ. web service) — программная система, идентифицируемая строкой URI, чьи общедоступные интерфейсы определены на языке XML. Описание этой программной системы может быть найдено другими программными системами, которые могут взаимодействовать с ней согласно этому описанию посредством сообщений, основанных на XML, и передаваемых с помощью интернет-протоколов. Веб-служба является единицей модульности при использовании сервисно-ориентированной архитектуры приложения.

Для демонстрации работы с Web-службами из SQL Server`a, я воспользовался открытым Web-сервисом Центрального банка Российской Федерации, этот сервис привлекателен тем, что информация всегда актуальна, т.к. обновляется с завидной регулярностью и может быть полезна в реальных проектах.

Адрес Веб-сервиса: http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL.

 

Веб сервис для получения ежедневных данных:

• Сальдо операций ЦБ РФ по предоставлению/абсорбированию ликвидности
• Получение новостей сервера
• Операции на открытом рынке
• Операции Банка России на рынке государственных ценных бумаг по поручению Министерства финансов Российской Федерации
• Получение основной информации
• Ставка рефинансирования, золотовалютные резервы, денежная база, денежная масса
• Валютный своп buy/sell overnight
• Динамики ставок привлечения средств по депозитным операциям
• Динамика учетных цен драгоценных металлов и т.д.

Согласитесь, что достаточно ценная информация, а особенно ценна она своей актуальностью. Результат возвращается, как в виде DataSet, так и XMLDocument, но начнём по порядку...

Для начала продемонстрирую на небольшом примере, как работать с этим Web-сервисом из Windows-приложения. Запускаем Microsoft Visual Studio 2008 (но пример прекрасно работает и с 2005ой версией) и создадим Windows Forms Application

.

На форму добавим всего два компонента DataGridView (назовём его dg) и Button (назовём btn). Всё, что будет делать наше приложение - это по кнопке выводить результат(DataSet) в нашу табличку. Теперь необходимо подключить нашу Веб службу к проекту, для этого в Solution Explorer`e правой кнопкой мыши по References и Add Service Reference...

В поле Address добавляем адресс нашей Веб службы(http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL), а в поле Namespace имя, под которым у нас в проекте будет фигурировать служба (WS)

 

 Далее всё более, чем тривиально, перед нами весь набор возможностей данной Веб службы

В данном примере я прочитаю все новости за последние 5 дней, для этого на кнопку вешаю код:

WS.DailyInfoSoap i = new WS.DailyInfoSoapClient();
dg.DataSource = i.NewsInfo(DateTime.Now.AddDays(-5), DateTime.Now).Tables[0].DefaultView;

 

 

Результат вывожу в виде таблицы:

 

Как сами видите нет ничего сложного, далее можно оперативно получить информацию по всем операциям ЦБРФ. Но согласитесь гораздо удобнее считывать информацию фоново, например по расписанию, и сохранять информацию в БД. Для этого мы создадим сборку CLR (Common Language Runtime)

Но...сперва все подводные камни, с которыми и сам столкнулся. Подготовим наш сервер БД, для того, чтобы можно было подключать сборки:

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Создадим тестовую БД и выставим Свойство базы данных TRUSTWORTHY:

CREATE DATABASE TestDB
GO
ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO

Затем на подобии с вариантом для Windows-приложения напишем код для нашей сборки и скомпилируем dll-ку:

using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Collections;
using CLRWS.WS;

public class WSClass
{
    [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
FillRowMethodName = "GetListInfo")]

    public static IEnumerable GetNews(DateTime From)
    {
        CLRWS.WS.DailyInfoSoap i = new CLRWS.WS.DailyInfoSoapClient();
        DataTable t = new DataTable();
        t = i.NewsInfo(From, DateTime.Now).Tables[0];
        return t.Rows;
    }

    public static void GetListInfo(
                    object obj,
                    out SqlInt32 Doc_id,
                    out SqlDateTime DocDate,
                    out SqlString Title,
                    out SqlString Url)
    {
        DataRow r = (DataRow)obj;
        Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
        DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
        Title = new SqlString(r["Title"].ToString());
        Url = new SqlString(r["Url"].ToString());
    }

}

Код компилируется без проблем, и теперь попытаемся подключить нашу сборку, предварительно перенесём её в отдельную папку C:\MyCLR:

CREATE ASSEMBLY ClrWebServices
FROM 'C:\MyCLR\CLRWS.dll'
WITH PERMISSION_SET = UNSAFE;
GO

Но тут же получаем ошибку:

Msg 10301, Level 16, State 1, Line 1 Assembly ‘CLRWS’ references assembly ’system.servicemodel, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.’, which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(failed to retrieve text for this error. Reason: 1815)). Please load the referenced assembly into the current database and retry your request.

Для нашей сборки не хватает ряда библиотек и начиная с system.servicemodel, расположенной в папке C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0, начинаем копировать библиотеки в папку C:\MyCLR. Кромя того, ряд библиотек придётся взять из папки C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727, общее кол-во библиотек можно увидеть ниже:

И теперь вуаля...скрипт отрабатывает без ошибок, создадим функцию для работы с нашей сборкой:

CREATE FUNCTION [dbo].[GetNews]
(
@From datetime
)
RETURNS TABLE
(
Doc_id INT,
DocDate DATETIME,
Title NVARCHAR(max),
Url NVARCHAR(max)
)
AS
EXTERNAL NAME [ClrWebServices].[WSClass].[GetNews]
GO

Ну и пробуем запустить эту функцию, в качестве входного параметра указывается дата, с которой мы берём новости и по сегодняшний день:

SELECT * from [dbo].[GetNews] ('20090101')

Но тут же получаем ошибку:

Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate “GetNews”: System.InvalidOperationException: Could not find default endpoint element that references contract ‘WS.DailyInfoSoap’ in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element. System.InvalidOperationException: at System.ServiceModel.Description.ConfigLoader.LoadChannelBehaviors(ServiceEndpoint serviceEndpoint, String configurationName) at System.ServiceModel.ChannelFactory.ApplyConfiguration(String configurationName) at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address) at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName, EndpointAddress remoteAddress) at System.ServiceModel.EndpointTrait`1.CreateSimplexFactory() at System.ServiceModel.EndpointTrait`1.CreateChannelFactory() at System.ServiceModel.ClientBase`1.CreateChannelFactoryRef(EndpointTrait`1 endpointTrait) at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef() at System.ServiceModel.ClientBase`1..ctor() at CLRWS.WS.DailyInfoSoapClient..ctor() at WSClass.GetNews(DateTime From)

Всё дело в том, что нам нужна информация, которая находится в файле app.config:

 а именно

Для этого изменяем немного нашу сборку, компилируем и пересоздаём её и нашу функцию, вот так выглядит новая версия:

using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Collections;
using CLRWS.WS;
using System.ServiceModel;

public class WSClass
{
    [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
FillRowMethodName = "GetListInfo")]

    public static IEnumerable GetNews(DateTime From)
    {
        EndpointAddress ea = new EndpointAddress("http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL");
        BasicHttpBinding bin = new BasicHttpBinding();

        CLRWS.WS.DailyInfoSoap i = new CLRWS.WS.DailyInfoSoapClient(bin, ea);
        DataTable t = new DataTable();
        t = i.NewsInfo(From, DateTime.Now).Tables[0];
        return t.Rows;
    }

    public static void GetListInfo(
                    object obj,
                    out SqlInt32 Doc_id,
                    out SqlDateTime DocDate,
                    out SqlString Title,
                    out SqlString Url)
    {
        DataRow r = (DataRow)obj;
        Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
        DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
        Title = new SqlString(r["Title"].ToString());
        Url = new SqlString(r["Url"].ToString());
    }

}

После запуска нашей функции с уже новой сборкой

SELECT * from [dbo].[GetNews] ('20090101')

получаем новую ошибку

Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate “GetNews”: System.Configuration.ConfigurationErrorsException: The type ‘Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ registered for extension ‘Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior’ could not be loaded. (c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 189) System.Configuration.ConfigurationErrorsException: at System.Configuration.BaseConfigurationRecord.EvaluateOne(String[] keys, SectionInput input, Boolean isTrusted, FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult) at System.Configuration.BaseConfigurationRecord.Evaluate(FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult, Boolean getLkg, Boolean getRuntimeObject, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSection(String configKey, Boolean getLkg, Boolean checkPermission) at System.C…

Ну эту ошибку легко убрать, достаточно отключить дебагинг: [Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -u

но и после этого ошибка, но уже новая:

Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate “GetNews”: System.ServiceModel.CommunicationException: There was an error in serializing body of message : ‘Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information.’. Please see InnerException for more details. —> System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. —> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host. System.ServiceModel.CommunicationException: Server stack trace: at System.ServiceModel.Dispatcher.XmlSerializerOperationFormatter.SerializeBody(XmlDictionaryWriter writer, MessageVersion version, String action, MessageDescription messageDescription, Object returnValue, Object[] parameters, Boolean isRequest) at System.ServiceModel.Dispatcher.OperationFormatter.SerializeBodyContents(XmlDictionaryWriter writer, MessageVersion version, Object[] parameters, Object returnValue, Boolean isRequest) at System.ServiceModel.Dispatcher.OperationFormatter.OperationFormatterMessage.OperationFormatterBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer) at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer) at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer) at System.ServiceModel.Channels.Message.OnWriteMessage(XmlDictionaryWriter writer) at System.ServiceModel.Channels.Message.WriteMessage(XmlDictionaryWriter writer) at System.ServiceModel.Channels.BufferedMessageWriter.WriteMessage(Message message, BufferManager bufferManager, Int32 initialOffset, Int32 maxSizeQuota) at…

Сериализация XML из объектов базы данных CLR, кромя того о подобной ошибке можно прочитать в базе знаний Майкрософт http://support.microsoft.com/kb/913668.

После всех "заморочек", продемонстрирую, как же всё-таки подключить сборку для работы с Веб-сервисами. Для начала удалим нашу БД TestDB, т.к. всё, что было сделано до этого было лишним. Ну и создадим её заново. Для создания "правильной" сборки нам потребуются несколько утилит:

1) Web Services Description Language Tool (Wsdl.exe) для создания прокси-сборки (C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\wsdl.exe)

2) csc.exe для компилирования прокси-сборки (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CSC.exe)

3) XML Serializer Generator Tool (Sgen.exe) - Создает сборку сериализации XML для типов в указанной сборке (C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\sgen.exe)

Теперь создаём прокси-сбоку:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\wsdl.exe" /o:CBRF.cs /n:WS http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL

на выходе получаем нашу сборку CBRF.cs

Теперь если немного изучить её, то наш финальный вариант CLR-сборки выглядит так:

using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Collections;
using WS;

public class WSClass
{
            [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
		FillRowMethodName = "GetListInfo")]

    public static IEnumerable GetNews(DateTime From)
    {
	WS.DailyInfo i=new WS.DailyInfo();
	DataTable t = new DataTable();
	t=i.NewsInfo(From, DateTime.Now).Tables[0];
        return t.Rows;
    }

        public static void GetListInfo(
                        object obj,
                        out SqlInt32 Doc_id,
                        out SqlDateTime DocDate,
                        out SqlString Title,
                        out SqlString Url)
        {
            DataRow r = (DataRow)obj;
            Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
            DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
            Title = new SqlString(r["Title"].ToString());
            Url = new SqlString(r["Url"].ToString());
        }

}

Сохраним этот код в отдельный файл, например MyCLR.cs

Теперь поместим в одну папку два наших файла: CBRF.cs и MyCLR.cs и скомпилируем их в одну dll-ку, для этого из директории с этими файлами запустим

"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CSC" /target:library /out:WS.dll *.cs

После запуска появится наша библиотека WS.dll

Последним шагом создадим сборку сериализации

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\sgen.exe" /a:WS.dll

на выходе ещё одна библиотека WS.XmlSerializers.dll

ВСЁ!!! Теперь подключим наши библиотеки:

CREATE ASSEMBLY ClrWebServices
FROM 'C:\MyCLR\WS.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'C:\MyCLR\WS.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

Создадим функцию:

CREATE FUNCTION [dbo].[GetNews]
(
@From datetime
)
RETURNS TABLE
(
Doc_id INT,
DocDate DATETIME,
Title NVARCHAR(max),
Url NVARCHAR(max)
)
AS
EXTERNAL NAME [ClrWebServices].[WSClass].[GetNews]
GO

Ну и получим результат:

SELECT * from [dbo].[GetNews] ('20090101')

 

 

Ниже эти две библиотеки в архиве:

WS.rar

Продолжаю тему безполезных примеров CLR-сборок в помощь администратору(разработчику) БД.

В этот раз решил читать почту в SQL Server 2005/2008/2008R2 через протокол POP3. Многие скажут, что сервер БД не предназначен для этого, но ВДРУГ!?

Конечно для этого у нас есть SQL Mail, но учитывая ряд ограничений, о которых чуть ниже, пришлось посмотреть в сторону собственной сборки. Для чтения почты используются, как правило, протокол POP3 либо IMAP(о нём в следующий раз, возможно так же в качестве очередной CLR-сборки).

Ну и для начала всё-таки об ограничениях SQL Mail:

В будущей версии Microsoft SQL Server эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется. Чтобы отправить почту из SQL Server, используйте компонент Database Mail.

...но как читать-то почту!? Ведь Database Mail только отправляет почту через SMTP-протокол...

Служба SQL Mail требует наличия подключения к почтовой станции, почтовый ящик, почтовый профиль и учетную запись пользователя домена Microsoft, используемую для входа на SQL Server (учетная запись пользователя должна быть в том же домене, что и SQL Server). Служба MSSQLServer должна быть запущена под этой учетной записью домена пользователя. С помощью расширенных хранимых процедур службы SQL Mail можно отправлять сообщения из триггера или хранимой процедуры. Хранимые процедуры службы SQL Mail могут обрабатывать запросы, полученные по электронной почте, и возвращать результирующий набор, создавая ответное электронное сообщение.

Эти ограничения заставили взглянуть в сторону изучения протоколов POP3/IMAP.

Разбирём POP3 чуть подробнее:

Краткое, но достаточно емкое описание почтового протокола POP3.

Перед работой через протокол POP3 сервер прослушивает порт 110. Когда клиент хочет использовать этот протокол, он должен создать TCP соединение с сервером. Когда соединение установлено, сервер отправляет приглашение. Затем клиент и POP3 сервер обмениваются информацией пока соединение не будет закрыто или прервано.

Команды POP3 состоят из ключевых слов, за некоторыми следует один или более аргументов. Все команды заканчиваются парой CRLF (в Visual Basic константа vbCrLf). Ключевые слова и аргументы состоят из печатаемых ASCII символов. Ключевое слово и аргументы разделены одиночным пробелом. Ключевое слово состоит от 3-х до 4-х символов, а аргумент может быть длиной до 40-ка символов.

Ответы в POP3 состоят из индикатора состояния и ключевого слова, за которым может следовать дополнительная информация. Ответ заканчивается парой CRLF. Существует только два индикатора состояния: "+OK" - положительный и "-ERR" - отрицательный.

Ответы на некоторые команды могут состоять из нескольких строк. В этих случаях каждая строка разделена парой CRLF, а конец ответа заканчивается ASCII символом 46 (".") и парой CRLF.

POP3 сессия состоит из нескольких режимов. Как только соединение с сервером было установлено и сервер отправил приглашение, то сессия переходит в режим авторизации. В этом режиме клиент должен идентифицировать себя на сервере. После успешной идентификации сессия переходит в режим транзакции. В этом режиме клиент запрашивает сервер выполнить определённые команды. Когда клиент отправляет команду QUIT, сессия переходит в режим обновления. В этом режиме POP3 сервер освобождает все занятые ресурсы и завершает работу. После этого TCP соединение закрывается.

Список команд:

• USER - Когда РОРЗ -сессия находится в состоянии аутентификации (AUTHORIZATION), и клиент должен зарегистрировать себя на РОРЗ -сервере. Это может быть выполнено либо с помощью команд USER и PASS — ввод открытых пользовательского идентификатора и пароля (именно этот способ используется чаще), либо командой АРОР — аутентификация цифровой подписью, на базе секретного ключа. Любой РОРЗ -сервер должен поддерживать хотя бы один из механизмов аутентификации. Аргументом — "name" является строка, идентифицирующая почтовый ящик системы. Этот идентификатор должен быть уникальным в данной почтовой системе РОРЗ -сервера. Если ответом на эту команду является строка индикатора "+OK", клиент может отправлять команду PASS — ввод пароля или QUIT — завершить сессию. Если ответом является строка "-ERR", клиент может либо повторить команду USER, либо закрыть сессию
• PASS - Аргументом команды является строка пароля данного почтового ящика. После получения команды PASS, РОРЗ -сервер, на основании аргументов команд USER и PASS, определяет возможность доступа к заданному почтовому ящику. Если РОРЗ -сервер ответил "+OK", это означает, что аутентификация клиента прошла успешно и он может работать со своим почтовым ящиком, т. е. сессия переходит в состояние TRANSACTION. Если РОРЗ- сервер ответил "-ERR", то либо был введен неверный пароль, либо не найден указанный почтовый ящик
• STAT - После того как клиент успешно прошел процедуру аутентификации в РОРЗ- сервере, и РОРЗ- сервер "закрыл" определенный почтовый ящик только для использования данным клиентом (для тех, кто работал с базами данных, это называется EXCLUSIVE ACCESS LOCK), РОРЗ- сессия переходит в режим TRANSACTION, и клиент может начать работу со своей почтой Команда STAT (без аргументов) используется для просмотра состояния текущего почтового ящика. В ответ РОРЗ- сервер возвращает строку, содержащую количество и общий размер в байтах сообщений, которые клиент может получить с РОРЗ- сервера. Сообщения, помеченные на удаление, не учитываются
• LIST [msg] - Команда LIST может передаваться как с аргументом msg — номером сообщения, так и без аргумента. Если команда содержит аргумент, и сообщение с указанным номером существует, ответом на нее будет "информационная строка", которая содержит номер сообщения и размер сообщения в байтах. Если аргумент не указан — ответом будет список информационных строк обо всех сообщениях в данном почтовом ящике. Сообщения, помеченные на удаление не фигурируют в этом списке
• RETR msg - Используется для передачи клиенту запрашиваемого сообщения. Аргумент команды — номер сообщения. Если запрашиваемого сообщения нет, возвращается отрицательный индикатор "-ERR".
• DELE msg - Аргумент команды— номер сообщения. Сообщения, помеченные на удаление, реально удаляются только после закрытия транзакции при отправке команды QUIT.
• NOOP - Для проверки состояния соединения с РОРЗ- сервером используется команда NOOP. При активном соединении ответом на нее будет положительный индикатор "+ОК":
• RSET - Для отката транзакции внутри сессии используется команда RSET (без аргументов). Если пользователь случайно пометил на удаление какие-либо сообщения, он может убрать эти пометки, отправив эту команду:
• TOP msg n - По этой команде пользователь может получить "n" первых строк сообщения с номером "msg". РОРЗ- сервер по запросу клиента отправляет заголовок сообщения, затем пустую строку, затем требуемое количество строк сообщения (если количество строк в сообщении меньше указанного в параметре "n", пользователю передается все сообщение).
• QUIT - К командам состояния AUTHORIZATION может относиться команда закрытия РОРЗ- сессии — QUIT, если она была отправлена в режиме AUTHORIZATION (например, при вводе неправильного пароля или идентификатора пользователя): Эта команда отправляется без аргументов и всегда имеет единственный ответ "+ОК".

Как видно из описания список команд не велик. В сборку я включил всего 3 команды: LIST(список писем), RETR(чтение письма), DELE(удаление письма). Все другие команды игнорируются, но вы можете сами дополнить мой пример, для этого и выкладываю исходный код сборки:

using System;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Net.Sockets;
using System.Text;

public class POP3CLR
{
    [SqlFunction(FillRowMethodName = "FillRow"
        , TableDefinition = "SERVER: nvarchar(max)")
    ]

    public static IEnumerable POP3Command(string POP3Server, string Port, string User, string Pass, string Command)
    {

        ArrayList rows = new ArrayList();
        if (Command.Length > 3)
        {
            if (Command != "LIST" && Command.Substring(0, 4) != "RETR" && Command.Substring(0, 4) != "DELE")
            {
                Command = "HELP";
            }
        }
        else
        {
            Command = "HELP";
        }

        if (Command == "HELP")
        {

            rows.Add(new object[] {"LIST-список информационных "+
                                   "строк обо всех сообщениях в данном почтовом ящике. "+
                                   "Сообщения, помеченные на удаление не фигурируют в этом списке." });
            rows.Add(new object[] {"RETR msg-Используется для передачи клиенту запрашиваемого сообщения. "+
                                   "Аргумент команды — номер сообщения. Если запрашиваемого сообщения нет, "+
                                   "возвращается отрицательный индикатор '-ERR'." });
            rows.Add(new object[] {"DELE msg-Аргумент команды— номер сообщения. "+
                                   "Сообщения, помеченные на удаление, реально удаляются только "+
                                   "после закрытия транзакции при отправке команды QUIT." });
            return rows;
        }

        TcpClient tcpClient = new TcpClient();
        tcpClient.Connect(POP3Server, Convert.ToInt32(Port));
        NetworkStream netStream = tcpClient.GetStream();
        System.IO.StreamReader strReader = new System.IO.StreamReader(netStream);

        if (tcpClient.Connected)
        {

            byte[] WriteBuffer = new byte[1024];
            ASCIIEncoding enc = new System.Text.ASCIIEncoding();
            WriteBuffer = enc.GetBytes("USER " + User + "\r\n");
            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
            rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });
            WriteBuffer = enc.GetBytes("PASS " + Pass + "\r\n");
            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
            rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });
            WriteBuffer = enc.GetBytes(Command + "\r\n");
            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
            if (Command.Substring(0, 4) == "DELE")
            {
                rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });
            }
            else
            {
                string ListMessage;
                while (true)
                {
                    ListMessage = strReader.ReadLine();
                    if (ListMessage == ".")
                    {
                        break;
                    }
                    else
                    {
                        rows.Add(new object[] { ListMessage + "\r\n\r\n" });
                        continue;
                    }
                }
            }
            WriteBuffer = enc.GetBytes("QUIT\r\n");
            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
            rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });
        }
        return rows;
    }

    public static void FillRow(Object row, out string Server)
    {

        object[] xrow = (object[])row;
        Server = (string)xrow[0];

    }

}

Регистрируем сборку и создаём на её основе функцию:

CREATE ASSEMBLY AssemblyPOP3
FROM 'C:\CLR\POP3CLR.dll'
WITH PERMISSION_SET = UNSAFE
GO

--Создаём функцию
CREATE FUNCTION POP3Command
(
@POP3Server nvarchar(128),
@Port nvarchar(5),
@User nvarchar(128),
@Pass nvarchar(128),
@Command nvarchar(50)
)
RETURNS TABLE
(
[SERVER:] NVARCHAR(max)
)
EXTERNAL NAME AssemblyPOP3.POP3CLR.POP3Command;

Ну и пример работы с этой функцией:

SELECT * FROM POP3Command(
'pop3.MyServer.ru', 110, 'E-Mail@MyServer.ru', 'MyPassword', 'LIST'
)

Если у вас возникнут вопросы, то я буду рад помочь, пишите…