---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第11章 SQL Server 2005における.NETプログラミングの基礎
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 11.4 Visual Studio 2005を使ったCLRデータベースオブジェクトの開発／配置
---------------------------------------------------------------------

SqlContext.Pipe.Send("Hello world!\n");


EXEC ap_First
GO

---------------------------------------------------------------------
-- 11.5 Visual Studio 2005を使わないCLRデータベースオブジェクトの開発／配置
---------------------------------------------------------------------

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ap_MyFirst()
    {
        SqlContext.Pipe.Send("計算が完了しました!\n");
    }
};


Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub ap_MyFirst ()
        SqlContext.Pipe.Send("計算が完了しました!\n")
    End Sub
End Class


csc /target:library ap_MyFirst.cs


vbc /target:library ap_myFirst.vb


CREATE ASSEMBLY MyFirst
FROM 'C:\Sqlclr\ap_MyFirst.dll'
WITH PERMISSION_SET = SAFE;


DROP ASSEMBLY MyFirst;
CREATE ASSEMBLY MyFirst
FROM 'C:\Sqlclr\ap_MyFirst.dll'
WITH PERMISSION_SET = SAFE;


ALTER ASSEMBLY MyFirst
FROM 'C:\Sqlclr\ap_MyFirst.dll'
WITH PERMISSION_SET = SAFE;


GRANT EXTERNAL ACCESS ASSEMBLY TO [LG\dsunderic]
GO
ALTER DATABASE Asset5
SET TRUSTWORTHY ON


CREATE PROCEDURE dbo.ap_MyFirst
AS
EXTERNAL NAME MyFirst.StoredProcedures.ap_MyFirst

---------------------------------------------------------------------
-- 11.6 アセンブリとCLRデータベースオブジェクトのカタログ化
---------------------------------------------------------------------

SELECT * FROM sys.assemblies


SELECT * FROM sys.assembly_modules


SELECT schema_name(sp.schema_id) + '.' + sp.[name] AS [Name]
    , sp.create_date
    , sp.modify_date
    , sa.permission_set_desc AS [Access]
    , sp.is_auto_executed
FROM sys.procedures AS sp
INNER JOIN sys.module_assembly_usages AS sau
    ON sp.object_id = sau.object_id
INNER JOIN sys.assemblies AS sa
    ON sau.assembly_id = sa.assembly_id
WHERE sp.type_desc = N'CLR_STORED_PROCEDURE'


SELECT mdl.product_version
FROM sys.dm_os_loaded_modules AS mdl
WHERE mdl.[name] LIKE N'%\MSCOREE.DLL'

