Делал для себя, но возможно ещё кому-то пригодится:

--Создание функции
CREATE FUNCTION DBO.GetDateWord 
(
@dt DATETIME
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @str NVARCHAR(100)

SELECT 
@str=CASE DATEPART(dd, @dt)
WHEN 1 THEN 'Первого '
WHEN 2 THEN 'Второго '
WHEN 3 THEN 'Третьего '
WHEN 4 THEN 'Четвертого '
WHEN 5 THEN 'Пятого '
WHEN 6 THEN 'Шестого '
WHEN 7 THEN 'Седьмого '
WHEN 8 THEN 'Восьмого '
WHEN 9 THEN 'Девятого '
WHEN 10 THEN 'Десятого '
WHEN 11 THEN 'Одиннадцатого '
WHEN 12 THEN 'Двенадцатого '
WHEN 13 THEN 'Тринадцатого '
WHEN 14 THEN 'Четырнадцатого '
WHEN 15 THEN 'Пятнадцатого '
WHEN 16 THEN 'Шестнадцатого '
WHEN 17 THEN 'Семнадцатого '
WHEN 18 THEN 'Восемнадцатого '
WHEN 19 THEN 'Девятнадцатого '
WHEN 20 THEN 'Двадцатого '
WHEN 21 THEN 'Двадцать первого '
WHEN 22 THEN 'Двадцать второго '
WHEN 23 THEN 'Двадцать третьего '
WHEN 24 THEN 'Двадцать четвертого '
WHEN 25 THEN 'Двадцать пятого '
WHEN 26 THEN 'Двадцать шестого '
WHEN 27 THEN 'Двадцать седьмого '
WHEN 28 THEN 'Двадцать восьмого '
WHEN 29 THEN 'Двадцать девятого '
WHEN 30 THEN 'Тридцатого '
WHEN 31 THEN 'Тридцать первого '
END
+
CASE DATEPART(mm, @dt)
WHEN 1 THEN 'января'
WHEN 2 THEN 'февраля'
WHEN 3 THEN 'марта'
WHEN 4 THEN 'апреля'
WHEN 5 THEN 'мая'
WHEN 6 THEN 'июня'
WHEN 7 THEN 'июля'
WHEN 8 THEN 'августа'
WHEN 9 THEN 'сентября'
WHEN 10 THEN 'октября'
WHEN 11 THEN 'ноября'
WHEN 12 THEN 'декабря'
END
+' '+
CASE LEFT(DATEPART(yy, @dt),2)
WHEN 19 THEN 'одна тысяча девятьсот '
WHEN 20 THEN 'две тысячи '
WHEN 21 THEN 'две тысячи сто '
END
+
CASE WHEN RIGHT(DATEPART(yy, @dt), 2) IN 
(10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 30, 40, 50, 60, 70, 80, 90)
THEN 
CASE RIGHT(DATEPART(yy, @dt), 2)
WHEN 10 THEN 'десятого'
WHEN 11 THEN 'одиннадцатого'
WHEN 12 THEN 'двенадцатого'
WHEN 13 THEN 'тринадцатого'
WHEN 14 THEN 'четырнадцатого'
WHEN 15 THEN 'пятнадцатого'
WHEN 16 THEN 'шестнадцатого'
WHEN 17 THEN 'семнадцатого'
WHEN 18 THEN 'восемнадцатого'
WHEN 19 THEN 'девятнадцатого'
WHEN 20 THEN 'двадцатого'
WHEN 30 THEN 'трицатого'
WHEN 40 THEN 'сорокового'
WHEN 50 THEN 'пятидесятого'
WHEN 60 THEN 'шестидесятого'
WHEN 70 THEN 'семидесятого'
WHEN 80 THEN 'восьмидесятого'
WHEN 90 THEN 'девяностого'
END

ELSE 

CASE RIGHT (DATEPART(yy, @dt),2)/10
WHEN 2 THEN 'двадцать'
WHEN 3 THEN 'тридцать'
WHEN 4 THEN 'сорок'
WHEN 5 THEN 'пятьдесят'
WHEN 6 THEN 'шестьдесят'
WHEN 7 THEN 'семьдесят'
WHEN 8 THEN 'восемьдесят'
WHEN 9 THEN 'девяносто'
END
+
CASE RIGHT (DATEPART(yy, @dt),1)
WHEN 1 THEN 'первого'
WHEN 2 THEN 'второго'
WHEN 3 THEN 'третьего'
WHEN 4 THEN 'четвертого'
WHEN 5 THEN 'пятого'
WHEN 6 THEN 'шестого'
WHEN 7 THEN 'седьмого'
WHEN 8 THEN 'восьмого'
WHEN 9 THEN 'девятого'
END

END 
+' года'


RETURN @str

END

 

--Тестируем:
SELECT dbo.GetDateWord(GETDATE())

Ну и результат работы:

Четырнадцатого апреля две тысячи десятого года

PS: Набор функций для создания суммы прописью

Начиная с 2005ой версии в SQL Server появилось такое понятие как схема. Схема формально определяется как набор объектов в базе данных, объединенных общим пространством имен. Проще всего представить себе схему как некий логический контейнер в базе данных, которому могут принадлежать таблицы, представления, хранимые процедуры, пользовательские функции, ограничения целостности, пользовательские типы данных и другие объекты базы данных. Этот контейнер удобно использовать как для именования объектов и их логической группировки, так и для предоставления разрешений. Например, если в базе данных есть набор таблиц с финансовой информацией, удобно поместить их в одну схему и предоставлять пользователям разрешения на эту схему (т. е. на этот набор таблиц).

Но возможно вам потребуедся сделать выборку из однотипных таблиц с одинаковым именем, но расположенных в разных схемах. Стандартного средства в T-SQL нет, поэтому написал небольшой динамический скрипт, который выводит результат из нескольких таблиц в виде одного рекордсета.

Для демонстрации создадим новую базу с 10ю разными схемами. Расположим в этих схемах несколько однотипных (имена так же совпадают) таблиц

--Контекст БД мастер
USE master
GO
--Проверяем наличие БД "TestDB" и если она существует-удаляем
IF (SELECT DB_ID('TestDB')) IS NOT NULL
DROP DATABASE TestDB
GO
--Создаём новую БД "TestDB"
CREATE DATABASE TestDB
GO
--Переходим в неё
USE TestDB
GO
--Создаём 10 схем
CREATE SCHEMA Shema1
GO
CREATE SCHEMA Shema2
GO
CREATE SCHEMA Shema3
GO
CREATE SCHEMA Shema4
GO
CREATE SCHEMA Shema5
GO
CREATE SCHEMA Shema6
GO
CREATE SCHEMA Shema7
GO
CREATE SCHEMA Shema8
GO
CREATE SCHEMA Shema9
GO
CREATE SCHEMA Shema10
GO

--В этих схемах создаём однотипные таблицы,
--для наглядности данные содержат имя схемы, таблицы и значене Value"N"
CREATE TABLE Shema1.Table1 (val varchar(255))
INSERT INTO Shema1.Table1
SELECT 'Shema1.Table1_Value1'
UNION ALL
SELECT 'Shema1.Table1_Value2'
UNION ALL
SELECT 'Shema1.Table1_Value3'

CREATE TABLE Shema3.Table1 (val varchar(255))
INSERT INTO Shema3.Table1
SELECT 'Shema3.Table1_Value1'
UNION ALL
SELECT 'Shema3.Table1_Value2'

CREATE TABLE Shema10.Table1 (val varchar(255))
INSERT INTO Shema10.Table1
SELECT 'Shema10.Table1_Value1'
UNION ALL
SELECT 'Shema10.Table1_Value2'
UNION ALL
SELECT 'Shema10.Table1_Value3'
UNION ALL
SELECT 'Shema10.Table1_Value4'

CREATE TABLE Shema2.Table2 (val varchar(255))
INSERT INTO Shema2.Table2
SELECT 'Shema2.Table2_Value1'
UNION ALL
SELECT 'Shema2.Table2_Value2'
UNION ALL
SELECT 'Shema2.Table2_Value3'

CREATE TABLE Shema4.Table2 (val varchar(255))
INSERT INTO Shema4.Table2
SELECT 'Shema4.Table2_Value1'

CREATE TABLE Shema6.Table3 (val varchar(255))
INSERT INTO Shema6.Table3
SELECT 'Shema6.Table3_Value1'

CREATE TABLE Shema5.Table4 (val varchar(255))
INSERT INTO Shema5.Table4
SELECT 'Shema5.Table4_Value1'

CREATE TABLE Shema7.Table4 (val varchar(255))
INSERT INTO Shema7.Table4
SELECT 'Shema7.Table4_Value1'

CREATE TABLE Shema8.Table4 (val varchar(255))
INSERT INTO Shema8.Table4
SELECT 'Shema8.Table4_Value1'

CREATE TABLE Shema9.Table4 (val varchar(255))
INSERT INTO Shema9.Table4
SELECT 'Shema9.Table4_Value1'
UNION ALL
SELECT 'Shema9.Table4_Value2'

А теперь, указав на входе имя таблицы, выберим данные из таблиц с этим именем, но расположенных в разных схемах:

DECLARE @str NVARCHAR(MAX), @Table NVARCHAR(255)
SELECT @Table='Table1'

DECLARE Tables_cur CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME=@Table

OPEN Tables_cur 

FETCH NEXT FROM Tables_cur INTO @Table

	WHILE (@@FETCH_STATUS!=-1) BEGIN
			IF (@@FETCH_STATUS!=-2)BEGIN
				SELECT @str=CASE WHEN @str IS NULL THEN 'SELECT * FROM '+@Table
				ELSE @str+ ' UNION ALL SELECT * FROM '+@Table END
			END
		FETCH NEXT FROM Tables_cur INTO @Table
	END

CLOSE Tables_cur
DEALLOCATE Tables_cur

EXECUTE (@str)

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

1. Shema1.Table1_Value1
2. Shema1.Table1_Value2
3. Shema1.Table1_Value3
4. Shema3.Table1_Value1
5. Shema3.Table1_Value2
6. Shema10.Table1_Value1
7. Shema10.Table1_Value2
8. Shema10.Table1_Value3
9. Shema10.Table1_Value4

Запрос объеденил результат из 3х схем (Shema1, Shema3, Shema10), где таблица = 'Table1'.

На форуме "TechNet RUS SQL Forum" появился вопрос:

Можно ли сразу из БД при помощи MS SQL перевести значения полей в AD в виде пользователей. (К примеру по фамилии имеющихся в БД людей создать пользователей в AD с логином <фамилия>)? (http://social.technet.microsoft.com/Forums/ru-RU/sqlru/thread/54ca41d0-d70c-4318-8b5c-daa7aecce9f5)

В качестве решения подобной задачи предлагаю небольшой скрипт на PowerShell.

Для начала создадим тестовую таблицу в БД с Логином и Паролей пользователей (для демонстрации всего 5 юзеров):

CREATE TABLE Users (UserLogin sysname, UserPassword nvarchar(255))
GO

INSERT INTO Users
SELECT 'SQLUser1', '1234567890'
UNION ALL
SELECT 'SQLUser2', '1234567890'
UNION ALL
SELECT 'SQLUser3', '1234567890'
UNION ALL
SELECT 'SQLUser4', '1234567890'
UNION ALL
SELECT 'SQLUser5', '1234567890

Для работы с AD буду использовать бесплатный набор командлетов (часто называемых также AD cmdlets или QAD cmdlets), доступный с сайта http://www.quest.com/activeroles_server/arms.aspx.

А для создания пользователя команду New-QADUser, полный синтаксис команды доступен в wiki: http://wiki.powergui.org/index.php/New-QADUser

$Domen = "MyServerAD" #Read-Host "Введите адресс контролера"
$User = "MyUser" #read-host "Введите логин"
$Password =read-host "Введите пароль" -AsSecureString;

#Подключаемся к AD
Connect-QADService -Service $Domen -ConnectionAccount $User  -ConnectionPassword $Password;

#Создаём подключение к серверу БД
$SQLConnection = new-object System.Data.SqlClient.SqlConnection("Data Source='MyServerBD';User='MyUser';password='MyPassword';")
#Запрос к таблице с Юзерами
$Query="select * from Test.dbo.Users"
$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $SQLConnection)

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)

$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]
$rdr=$SQLCommand.ExecuteReader()

#В цикле по всему рекордсету перебераю записи с юзерами
While($rdr.Read())
{
#Выводим логин
Write-Host $rdr[0]
#Создаём пользователя в AD
New-QADUser -Name $rdr[0] -ParentContainer "MyServerAD.com/Users"  -SamAccountName $rdr[0] -UserPassword $rdr[1]
}
#Закрываем подключение к Серверу БД
$SQLConnection.Close();
#Отключаемся от AD
Disconnect-QADService;

Скрипт достаточно примитивный, но работает и если его довести до ума, то можно в AD экспортировать и другую информацию о пользователе.

Ранее я писал, как с помощью PowerShell создать группы пользователей AD: http://www.t-sql.ru/post/ADGroupsPoSh.aspx

Практически в любой отрасли существуют свои критические данные, которые требуют шифрования. Начиная с 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

Join Hints (LOOP | HASH | MERGE | REMOTE) - Подсказки оптимизатору запросов на выбор определенной стратегии соединения двух таблиц (используется в SELECT, UPDATE и DELETE).

Оптимизатор запросов SQL Server обычно автоматически выбирает наилучший план выполнения запроса. Поэтому подсказки, в том числе <подсказки_по_соединению>, рекомендуется использовать только опытным пользователям и администраторам базы данных в случае крайней необходимости.

Без явного указания аргумента (LOOP | HASH | MERGE | REMOTE) оптимизатор выбирает, на его взгляд, самый оптимальный план. Но мы всегда можем повлиять на него, если явно укажем подсказку.

Ниже разберем каждый из аргументов подробнее.

Loop Join

Соединение LOOP JOIN, называемое также nested iteration, использует одну таблицу в качестве внешней (на графическом плане она является верхней), а второй в качестве внутренней (нижней). LOOP JOIN построчно сравнивает внешнюю таблицу с внутренней. В цикле для каждой внешней строки производится сканирование внутренней таблицы и выводятся совпадающие строки.

В простейшем случае во время поиска целиком сканируется таблица или индекс (naive nested loops join). Если при поиске используется индекс, то такой поиск называется index nested loops join. Если индекс создается в качестве части плана запроса (и уничтожается после завершения запроса), то он называется temporary index nested loops join. Оптимизатор сам выбирает один из этих поисков.

LOOP JOIN является особенно эффективным в случае, когда внешняя таблица сравнительно невелика, а внутренняя гораздо больше и для неё существуют индексы. В запросах с небольшим объёмом строк, index nested loops join превосходит как MERGE JOIN, так и HASH JOIN. Однако в больших запросах LOOP JOIN часто являются не лучшим вариантом.

Для демонстрации создадим 2 тестовые таблицы:

CREATE TABLE LoopLeftTable (ID INT)
CREATE TABLE LoopRightTable (ID INT IDENTITY PRIMARY KEY)

И посмотрим план запроса:

SELECT * FROM
	LoopLeftTable
	INNER jOIN
	LoopRightTable
	ON LoopLeftTable.ID=LoopRightTable.ID

Как и описано выше оптимизатор выбрал LOOP JOIN. Но если мы вставим в таблицу достаточно большое кол-во строк, то оптимизатор откажется от соединения LOOP JOIN:

INSERT INTO LoopLeftTable
SELECT 1
GO 10000

Оптимизатор при выполнении запроса выбрал HASH JOIN, так как посчитал, что стимость этого соединения будет ниже. Но если мы не доверяем оптимизатору то можем явно указать ему использовать LOOP JOIN:

SELECT * FROM
	LoopLeftTable
	INNER LOOP jOIN
	LoopRightTable
	ON LoopLeftTable.ID=LoopRightTable.ID

Кстати, если сравнить стоимость выполнения запроса выбранного оптимизатором и наш с подсказкой, то можно убедиться, что оптимизатор действительно выбрал верный план. (http://msdn.microsoft.com/en-us/library/ms191318.aspx)

Аргумент LOOP не может указываться вместе с параметрами RIGHT или FULL в качестве типа соединения.

 

Merge Join

Merge Join требует сортировки обоих наборов входных данных по столбцам слияния, которые определены предложениями равенства (ON) предиката соединения. (т.е. если мы имеем предикат соединения "T1.a = T2.b", таблица T1 должна быть отсортирована по T1.a, а таблица T2 должна быть сортирована по T2.b).

Так как каждый набор входных данных сортируется, оператор Merge Join получает строку из каждого набора входных данных и сравнивает их. Например, для операций INNER JOIN строки возвращаются в том случае, если они равны. Если они не равны, строка с меньшим значением не учитывается, и из этого набора входных данных берется другая строка. Этот процесс повторяется, пока не будет выполнена обработка всех строк.

MERGE JOIN может поддерживать слияние "многие ко многим". В этом случае, при каждом соединении двух строк нужно сохранять копию каждой строки второго входного потока. Это позволяет, при последующем обнаружении в первом входном потоке дубликатов строк, воспроизвести сохраненные строки. С другой стороны, если будет ясно, что следующая строка первого входного потока не является дубликатом, от сохраненных строк можно отказаться. Такие строки сохраняются во временно таблице базы tempdb. Размер дискового пространства, который для этого необходим, зависит от числа дубликатов во втором входном потоке.

MERGE JOIN "один ко многим" всегда будет эффективнее слияния "многие ко многим", поскольку для него не требуется временная таблица. Для того, что бы задействовать слиянием "один ко многим", оптимизатор должен иметь возможность определить, что один из входных потоков состоит из уникальных строк. Как правило, это означает, что у такого входного потока существует уникальный индекс или в плане запроса присутствует явным образом оператор (например, сортировка при DISTINCT или группировка), который гарантирует, что строки на входе будут уникальны.

Merge Join — очень быстрая операция, но она может оказаться ресурсоемкой, если требуется выполнение операций сортировки. Однако на больших объёмах при наличии индексов и предварительной сортировке, соединение слиянием является самым быстрым из доступных алгоритмов соединения.

Для демонстрации создадим 2 таблицы очень похожие на те, что были созданы в примере с LOOP JOIN:

CREATE TABLE MergeLeftTable (ID INT IDENTITY PRIMARY KEY)
CREATE TABLE MergeRightTable (ID INT IDENTITY PRIMARY KEY)

Если посмотреть какой план выбрал оптимизатор на пустых таблицах, если в качестве запроса указать:

SELECT MergeLeftTable.ID FROM
	MergeLeftTable
	INNER jOIN
	MergeRightTable
	ON MergeLeftTable.ID=MergeRightTable.ID

то окажется, что опять используется LOOP JOIN.

Можно явно указать оптимизатору использовать MERGE JOIN:

SELECT MergeLeftTable.ID FROM
	MergeLeftTable
	INNER MERGE jOIN
	MergeRightTable
	ON MergeLeftTable.ID=MergeRightTable.ID

Но оптимизатор сам выберет для этих таблиц MERGE JOIN, если наполнить их (таблицы) хотя бы небольшим кол-ом данных:

INSERT INTO MergeLeftTable
DEFAULT VALUES
GO 40

INSERT INTO MergeRightTable
DEFAULT VALUES
GO 40

Тогда первый запрос для этих таблиц без подсказки выполнится по плану с MERGE JOIN: (http://msdn.microsoft.com/en-us/library/ms190967.aspx)

 

Hash Join

Hash Join - более эффективен при работе с большими наборами данных и даже тогда, когда таблицы не отсортированы по столбцам, по которым производится соединение. Hash Join распараллеливается и масштабируется лучше любого другого соединения и сильно выигрывает при большой производительности информационных хранилищ.

Соединение происходит с использованием хеширования, вычесляя хеш записей из меньшей таблицы (Build-таблица) и вставляя их в хеш-таблицу, затем обрабатывается большая таблица (Probe-таблица) по одной записи, сканируя хеш-таблицу для поиска совпадений.

Создадим две таблицы для демонстрации:

CREATE TABLE HashLeftTable (ID INT)
CREATE TABLE HashRightTable (ID INT)

Если посмотреть план запроса:

SELECT HashLeftTable.ID FROM
	HashLeftTable
	INNER jOIN
	HashRightTable
	ON HashLeftTable.ID=HashRightTable.ID

То увидем, что оптимизатор выбрал Hash Join:

Но если оптимизатор выбрал другой план, но мы явно желаем использовать хеш-объединение, то мы так же можем "подсказать" это оптимизатору:

SELECT HashLeftTable.ID FROM
	HashLeftTable
	INNER HASH jOIN
	HashRightTable
	ON HashLeftTable.ID=HashRightTable.ID

Hash Join бывают 3х видов:

• In-Memory Hash Join Когда таблицы небольшого размера и могут полностью быть помещенны в память
• Grace Hash Join Если размер таблиц превышает максимально допустимый объем памяти, то хэш-соединение проводится в несколько шагов.
• Recursive Hash Join Этот вид объединения используется для сложных таблиц и для таблиц, которые являются очень большими и требуют многоуровневое соединение в несколько шагов.

(http://msdn.microsoft.com/en-us/library/ms189313.aspx)

 

Remote Join

Remote Join может быть использован только при операциях INNER JOIN.

Remote Join задает, что операция соединения проводится на странице таблицы, расположенной справа. Данный аргумент удобно использовать в случае, когда таблица, расположенная слева, является локальной, а справа располагается удаленная таблица (Linked Server). Аргумент REMOTE может использоваться в случае, когда в таблице слева содержится меньшее количество строк, чем в таблице справа. Если таблица, расположенная справа, является локальной, то операция соединения также проводится локально. Если обе таблицы являются удаленными, но

расположены в различных источниках данных, то при задании аргумента REMOTE операция соединения проводится на странице таблицы, расположенной справа. Если обе таблицы являются удаленными таблицами в одном источнике данных, то аргумент REMOTE не требуется.

Как и в придыдущих случаях, мы можем явно указать оптимизатору использовать необходимое объединение:

SELECT HashLeftTable.ID FROM
	HashLeftTable
	INNER REMOTE jOIN
	HashRightTable
	ON HashLeftTable.ID=HashRightTable.ID

 

 

 

По теме:

• Loop Join
• Merge Join
• Hash Join