SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database v12 Preview で SQLCLR を使用する

leave a comment

SQL Database の v12 Preview では、SQLCLR を使用することができるようになりました。
ということで少し使ってみたいと思います。

私はコードを書くのが苦手なので、TaaS せんせーが後日素晴らしい SQLCLR のアセンブリを作ってくれると思います。

あと、えろす師匠、鰻奢ってください。

以下のようなシンプルな SQLCLR のコードを例として。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString SqlFunction(string Param1)
    {
        // Put your code here
        return new SqlString (Param1.ToUpper());
    }
}

 

プロジェクトのターゲットの SQL Server については SQL Server 2014 としています。

# 現時点の VS では SQL Database をターゲットとすると CREATE ASSEMBLY の構文サポートでエラーとなるようですので。

image

また、SQL Database の SQLCLR については SAFE にしか対応していないようです。

EXTERNAL_ACCESS や UNSAFE には対応をしていませんので、サーバーのリソースにはアクセスすることができません。

image

 

VS の環境が英語版になってしまっていますが、この辺は日本語に読み替えていただければ。

SQL Database の SQLCLR ですが、ファイルベースではアセンブリをロードできないため、すべてをクエリで完結する必要があります。

CREATE ASSEMBLY (Transact-SQL)

CREATE ASSEMBLY を 「client_assembly_specifier」ではなく、「assembly_bits」で指定して、アセンブリを SQL Server に登録します。


ビルドの中にプロジェクトを発行するためのメニューがありますので、まずはこちらをクリックします。

image

ダイアログが表示されたら、SQL Server の接続情報とアセンブリを配置する DB 名を設定し、「Generate Script」 (スクリプトの生成) をクリックします。

ここでは最終的に配置する SQL Database ではなく、テストで使用している SQL Server 2014 の環境を指定しています。

# 現状、SQL Database を指定した場合、エラーとなるようですので。

image

これにより、クエリベースでアセンブリを配置するための T-SQL が生成されます。

image

クエリ内には CREAETE ASSEMBLY の文がありますのでこの箇所を使用します。

CREATE ASSEMBLY [V12CLRTEST]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103007BD796540000000000000000E00002210B010B000006000000060000000000008E2500000020000000400000000000100020000000020000040000000000000006000000000000000080000000020000000000000300608500001000001000000000100000100000000000001000000000000000000000003C2500004F00000000400000B002000000000000000000000000000000000000006000000C000000042400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000094050000002000000006000000020000000000000000000000000000200000602E72737263000000B0020000004000000004000000080000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000C0000000000000000000000000000400000420000000000000000000000000000000070250000000000004800000002000500782000008C030000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300100110000000100001100026F0500000A730600000A0A2B00062A1E02280700000A2A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C0000003C010000237E0000A80100006801000023537472696E67730000000010030000080000002355530018030000100000002347554944000000280300006400000023426C6F620000000000000002000001471502000900000000FA2533001600000100000008000000020000000200000001000000070000000400000001000000010000000200000000000A0001000000000006003E0037000A006600510006009C0089000F00B00000000600DF00BF000600FF00BF000A004301280106005801370000000000010000000000010001000100100019000000050001000100502000000000960070000A0001006D200000000086187C001000020000000100820019007C00140029007C001A0031007C00100039007C00100041005F01240011007C00280009007C001000200023001F002E000B0032002E0013003B002E001B0044002D000480000000000000000000000000000000001D01000004000000000000000000000001002E000000000004000000000000000000000001004500000000000000003C4D6F64756C653E00563132434C52544553542E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C46756E6374696F6E002E63746F7200506172616D310053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500563132434C5254455354004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500537472696E6700546F557070657200000003200000000000A86FB62F01CCA1459FD02A409F8891AA0008B77A5C561934E08905000111090E03200001052001011111042001010804010000000320000E042001010E04070111090801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000007BD7965400000000020000001C0100002024000020060000525344536DB04966FD544044B11C9D3D66FDB0FF01000000633A5C55736572735C41646D696E6973747261746F725C446F63756D656E74735C56697375616C2053747564696F20323031335C70726F6A656374735C4461746162617365335C4461746162617365335C6F626A5C44656275675C563132434C52544553542E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006425000000000000000000007E25000000200000000000000000000000000000000000000000000070250000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000540200000000000000000000540234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004B4010000010053007400720069006E006700460069006C00650049006E0066006F0000009001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000040000F00010049006E007400650072006E0061006C004E0061006D006500000056003100320043004C00520054004500530054002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000056003100320043004C00520054004500530054002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000903500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
GO

V12 の環境であれば、上記の CREATE ASSEMBLY が実行され、SQL Database にアセンブリが登録されます。

以下のクエリでアセンブリの登録状況が確認できます。

SELECT * FROM sys.assemblies
SELECT * FROM sys.assembly_files

image

それでは関数化して実際に利用してみたいと思います。

# 以下の関数化のクエリの VS で生成されています。

CREATE FUNCTION [dbo].[SqlFunction]
(@Param1 NVARCHAR (MAX))
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [V12CLRTEST].[UserDefinedFunctions].[SqlFunction]

これで、登録したアセンブリを使用した関数を実行することができます。

image

通常の関数では対応できなかった箇所の取り回しがよくなる感じでしょうか。

私はコード書くのは苦手なので「それ、TaaS でできるよ」で逃げようと思いますが。

Share

Written by Masayuki.Ozawa

12月 21st, 2014 at 11:29 pm

Posted in SQL Database

Tagged with

Leave a Reply