Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Глючная CLR  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
Jun 11 2015 19:18:41
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Есть CLR функция. Написана с единственной целью -- запускать MDX на связанный сервер и выдавать результат в виде колонок с гарантированными именами и гарантированными индексами колонок. (MDX не гарантирует конкретные индексы колонок в ResultSet). Вот её C# код.

+
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

public class Mdx
{
    [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillExecMdx",
        TableDefinition = @"f00 varchar(1024),
f01 varchar(1024),
f02 varchar(1024),
f03 varchar(1024),
f04 varchar(1024),
f05 varchar(1024),
f06 varchar(1024),
f07 varchar(1024),
f08 varchar(1024),
f09 varchar(1024),
f10 varchar(1024),
f11 varchar(1024),
f12 varchar(1024),
f13 varchar(1024),
f14 varchar(1024),
f15 varchar(1024),
f16 varchar(1024),
f17 varchar(1024),
f18 varchar(1024),
f19 varchar(1024),
f20 varchar(1024),
f21 varchar(1024),
f22 varchar(1024),
f23 varchar(1024),
f24 varchar(1024),
f25 varchar(1024),
f26 varchar(1024),
f27 varchar(1024),
f28 varchar(1024),
f29 varchar(1024),
f30 varchar(1024),
f31 varchar(1024)
")]
    public static IEnumerable ExecMdx(string connectionString, string mdxQuery, 
        string f00,
        string f01,
        string f02,
        string f03,
        string f04,
        string f05,
        string f06,
        string f07,
        string f08,
        string f09,
        string f10,
        string f11,
        string f12,
        string f13,
        string f14,
        string f15,
        string f16,
        string f17,
        string f18,
        string f19,
        string f20,
        string f21,
        string f22,
        string f23,
        string f24,
        string f25,
        string f26,
        string f27,
        string f28,
        string f29,
        string f30,
        string f31
        )
    {
        string[] fs =
        {
            f00,
            f01,
            f02,
            f03,
            f04,
            f05,
            f06,
            f07,
            f08,
            f09,
            f10,
            f11,
            f12,
            f13,
            f14,
            f15,
            f16,
            f17,
            f18,
            f19,
            f20,
            f21,
            f22,
            f23,
            f24,
            f25,
            f26,
            f27,
            f28,
            f29,
            f30,
            f31
        };

        DataSet data = new DataSet();
        using (SqlConnection cubeConnection = new SqlConnection(connectionString))
        {
            cubeConnection.Open();
            try
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(mdxQuery, cubeConnection))
                {
                    adapter.Fill(data);
                    DataTable table = data.Tables[0];
                    int[] ordinals = new int[fs.Length];
                    for (int i = 0; i < fs.Length; i++)
                    {
                        if (fs[i] != null && table.Columns.Contains(fs[i]))
                            ordinals[i] = table.Columns[fs[i]].Ordinal;
                        else
                            ordinals[i] = -1;
                    }
                    List<object[]> result = new List<object[]>(table.Rows.Count);
                    for (int rowNum = 0; rowNum < table.Rows.Count; rowNum++)
                    {
                        object[] row = new object[fs.Length];
                        for (int i = 0; i < fs.Length; i++)
                        {
                            if (ordinals[i] >= 0)
                            {
                                row[i] = table.Rows[rowNum][ordinals[i]];
                                if (row[i] is DBNull) row[i] = null; 
                            }
                            else
                                row[i] = null;

                        }
                        result.Add(row);
                    }
                    return result;
                }
            }
            catch (Exception e)
            {
                throw new Exception("An error occurred while retrieving the data", e);
            }
        }
    }

    public static void FillExecMdx(Object rowObj,
        out SqlString f00,
        out SqlString f01,
        out SqlString f02,
        out SqlString f03,
        out SqlString f04,
        out SqlString f05,
        out SqlString f06,
        out SqlString f07,
        out SqlString f08,
        out SqlString f09,
        out SqlString f10,
        out SqlString f11,
        out SqlString f12,
        out SqlString f13,
        out SqlString f14,
        out SqlString f15,
        out SqlString f16,
        out SqlString f17,
        out SqlString f18,
        out SqlString f19,
        out SqlString f20,
        out SqlString f21,
        out SqlString f22,
        out SqlString f23,
        out SqlString f24,
        out SqlString f25,
        out SqlString f26,
        out SqlString f27,
        out SqlString f28,
        out SqlString f29,
        out SqlString f30,
        out SqlString f31
        )
    {
        var row = (object[]) rowObj;
        f00 = new SqlString((string) row[00]);
        f01 = new SqlString((string) row[01]);
        f02 = new SqlString((string) row[02]);
        f03 = new SqlString((string) row[03]);
        f04 = new SqlString((string) row[04]);
        f05 = new SqlString((string) row[05]);
        f06 = new SqlString((string) row[06]);
        f07 = new SqlString((string) row[07]);
        f08 = new SqlString((string) row[08]);
        f09 = new SqlString((string) row[09]);
        f10 = new SqlString((string) row[10]);
        f11 = new SqlString((string) row[11]);
        f12 = new SqlString((string) row[12]);
        f13 = new SqlString((string) row[13]);
        f14 = new SqlString((string) row[14]);
        f15 = new SqlString((string) row[15]);
        f16 = new SqlString((string) row[16]);
        f17 = new SqlString((string) row[17]);
        f18 = new SqlString((string) row[18]);
        f19 = new SqlString((string) row[19]);
        f20 = new SqlString((string) row[20]);
        f21 = new SqlString((string) row[21]);
        f22 = new SqlString((string) row[22]);
        f23 = new SqlString((string) row[23]);
        f24 = new SqlString((string) row[24]);
        f25 = new SqlString((string) row[25]);
        f26 = new SqlString((string) row[26]);
        f27 = new SqlString((string) row[27]);
        f28 = new SqlString((string) row[28]);
        f29 = new SqlString((string) row[29]);
        f30 = new SqlString((string) row[30]);
        f31 = new SqlString((string) row[31]);
    }


    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static string SuggestExecMdx(string connectionString, string mdxQuery)
    {
        const int maxColumns = 32;
        DataSet data = new DataSet();
        using (SqlConnection cubeConnection = new SqlConnection(connectionString))
        {
            cubeConnection.Open();
            try
            {
                StringBuilder sb = new StringBuilder();
                using (SqlDataAdapter adapter = new SqlDataAdapter(mdxQuery, cubeConnection))
                {
                    
                    adapter.Fill(data);
                    DataTable table = data.Tables[0];
                    sb.AppendFormat(@"SELECT * FROM dbo.ExecMdx(N'{0}', ", connectionString);
                    sb.AppendLine();
                    sb.Append("N'");
                    sb.Append(mdxQuery.Replace("'", "''"));
                    sb.AppendLine();
                    sb.Append("',");
                    sb.AppendLine();
                    int c = 0;
                    foreach (DataColumn column in table.Columns)
                    {
                        sb.AppendFormat("N'{0}', -- {1:00}", column.ColumnName, c);
                        sb.AppendLine();
                        c++;
                    }
                    for (int i = c; i < maxColumns; i++)
                    {
                        sb.AppendFormat("NULL{1} -- {0:00}", i, i < maxColumns - 1 ? "," : " ");
                        sb.AppendLine();
                    }
                    sb.Append(")");
                }
                return sb.ToString();
            }
            catch (Exception e)
            {
                throw new Exception("An error occurred while retrieving the data", e);
            }
        }
    }
}


Вот скрипт её деплоя:

+
--USE master 
--GO  
--DROp ASYMMETRIC KEY ExecMdxKey
--GO

--CREATE ASYMMETRIC KEY ExecMdxKey FROM EXECUTABLE FILE = 'C:\sql\ExecMdx.dll'
--GO

--CREATE LOGIN ExecMdxLogin
--FROM ASYMMETRIC KEY ExecMdxKey
--GO

--GRANT UNSAFE ASSEMBLY To ExecMdxLogin
--GO

--USE master 
--GO  
 
--CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\ExternalQueries.dll'   
--CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey   
--GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin 

--GRANT UNSAFE ASSEMBLY To SQLCLRTestLogin
--GO 


use ...;
go

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

GO  
use master
go 
DROP LOGIN ExecMdxLogin ;
DROP ASYMMETRIC KEY ExecMdxKey ;
go

CREATE ASYMMETRIC KEY ExecMdxKey FROM EXECUTABLE FILE = 'C:\sql\ExecMdx.dll';
CREATE LOGIN ExecMdxLogin FROM ASYMMETRIC KEY ExecMdxKey;
GRANT UNSAFE ASSEMBLY TO ExecMdxLogin; 
GO 
use ....;
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ExecMdx')
   DROP FUNCTION ExecMdx;
go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SuggestExecMdx')
   DROP FUNCTION SuggestExecMdx;
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'ExecMdx')
   DROP ASSEMBLY ExecMdx;
go

CREATE ASSEMBLY ExecMdx FROM N'C:\sql\ExecMdx.dll'
WITH PERMISSION_SET = UNSAFE; --EXTERNAL_ACCESS;
GO

CREATE FUNCTION ExecMdx(
@mdx nvarchar(max),
@connection nvarchar(max),
@f00 nvarchar(1024),
@f01 nvarchar(1024),
@f02 nvarchar(1024),
@f03 nvarchar(1024),
@f04 nvarchar(1024),
@f05 nvarchar(1024),
@f06 nvarchar(1024),
@f07 nvarchar(1024),
@f08 nvarchar(1024),
@f09 nvarchar(1024),
@f10 nvarchar(1024),
@f11 nvarchar(1024),
@f12 nvarchar(1024),
@f13 nvarchar(1024),
@f14 nvarchar(1024),
@f15 nvarchar(1024),
@f16 nvarchar(1024),
@f17 nvarchar(1024),
@f18 nvarchar(1024),
@f19 nvarchar(1024),
@f20 nvarchar(1024),
@f21 nvarchar(1024),
@f22 nvarchar(1024),
@f23 nvarchar(1024),
@f24 nvarchar(1024),
@f25 nvarchar(1024),
@f26 nvarchar(1024),
@f27 nvarchar(1024),
@f28 nvarchar(1024),
@f29 nvarchar(1024),
@f30 nvarchar(1024),
@f31 nvarchar(1024)
) 
RETURNS TABLE (
f00 nvarchar(max),
f01 nvarchar(max),
f02 nvarchar(max),
f03 nvarchar(max),
f04 nvarchar(max),
f05 nvarchar(max),
f06 nvarchar(max),
f07 nvarchar(max),
f08 nvarchar(max),
f09 nvarchar(max),
f10 nvarchar(max),
f11 nvarchar(max),
f12 nvarchar(max),
f13 nvarchar(max),
f14 nvarchar(max),
f15 nvarchar(max),
f16 nvarchar(max),
f17 nvarchar(max),
f18 nvarchar(max),
f19 nvarchar(max),
f20 nvarchar(max),
f21 nvarchar(max),
f22 nvarchar(max),
f23 nvarchar(max),
f24 nvarchar(max),
f25 nvarchar(max),
f26 nvarchar(max),
f27 nvarchar(max),
f28 nvarchar(max),
f29 nvarchar(max),
f30 nvarchar(max),
f31 nvarchar(max)
)
AS EXTERNAL NAME ExecMdx.Mdx.ExecMdx;
go


CREATE FUNCTION SuggestExecMdx(
@connection nvarchar(max),
@mdx nvarchar(max)
) 
RETURNS nvarchar(max)
AS EXTERNAL NAME ExecMdx.Mdx.SuggestExecMdx;
go


SELECT dbo.SuggestExecMdx(
N'context connection=true',
N'
SELECT * FROM 
OPENQUERY([PAMIR],
N''
SELECT
	NON EMPTY { [Measures].[Число строк предзаказов], [Measures].[Продажи Сумма руб], [Measures].[Поставка ШТ], [Measures].[Поступления, шт] } ON COLUMNS, 
	NON EMPTY { ([Даты].[ГМД].[2011 Январь]:[Даты].[ГМД].[2014 Декабрь] ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [CustomerOrders] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'')
 '
)
GO

SELECT * FROM dbo.ExecMdx(N'context connection=true',
N'
SELECT * FROM 
OPENQUERY([PAMIR],
N''
SELECT
	NON EMPTY { [Measures].[Число строк предзаказов], [Measures].[Продажи Сумма руб], [Measures].[Поставка ШТ], [Measures].[Поступления, шт] } ON COLUMNS, 
	NON EMPTY { ([Даты].[ГМД].[2011 Январь]:[Даты].[ГМД].[2014 Декабрь] ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [CustomerOrders] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'')
 ', 
N'[Даты].[ГМД].[Год].[MEMBER_CAPTION]',
N'[Даты].[ГМД].[Год].[MEMBER_UNIQUE_NAME]',
N'[Даты].[ГМД].[Месяц].[MEMBER_CAPTION]',
N'[Даты].[ГМД].[Месяц].[MEMBER_UNIQUE_NAME]',
N'[Measures].[Продажи Сумма руб]',
N'[Measures].[Поставка ШТ]',
N'[Measures].[Поступления, шт]',
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
)


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

Если повторно прогнать скрипт деплоя (пересоздать функцию), ошибка пропадает. Никаких изменений.

+


Сообщение 6522, уровень 16, состояние 1, процедура SteamingMonthly, строка 32
A .NET Framework error occurred during execution of user-defined routine or aggregate "ExecMdx":
System.TypeInitializationException: Инициализатор типа "System.Data.ProviderBase.DbConnectionClosedPreviouslyOpened" выдал исключение. ---> System.Threading.ThreadAbortException: Выдано исключение типа "System.Threading.ThreadAbortException".
System.Threading.ThreadAbortException:

System.TypeInitializationException:
в System.Data.ProviderBase.DbConnectionInternal.CloseConnection(DbConnection owningObject, DbConnectionFactory connectionFactory)
в System.Data.SqlClient.SqlConnection.Close()
в System.Data.SqlClient.SqlConnection.Dispose(Boolean disposing)
в System.ComponentModel.Component.Dispose()
в Mdx.ExecMdx(String connectionString, String mdxQuery, String f00, String f01, String f02, String f03, String f04, String f05, String f06, String f07, String f08, String f09, String f10, String f11, String f12, String f13, String f14, String f15, String f16, String f17, String f18, String f19, String f20, String f21, String f22, String f23, String f24, String f25, String f26, String f27, String f28, String f29, String f30, String f31)

.


Вопрос, что в ней не так? Что её не хватает? Или как её исправить? Надо принудительно мусор чистить или что?
29 фев 16, 17:20    [18878612]     Ответить | Цитировать Сообщить модератору
 Re: Глючная CLR  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1451
a_voronin,

у вас неявно вызывается Dispose() для SqlConnection

попробуйте переписать код для явного закрытия открытого коннекта к сиквелу.
Сталкивался с таким что из-за нехватки памяти сиквел может выгружать сборки среды CLR.
29 фев 16, 18:04    [18878830]     Ответить | Цитировать Сообщить модератору
 Re: Глючная CLR  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1451
также интересно посмотреть что показывает на момент запуска функции sys.dm_clr_loaded_assemblies

и еще если у вас сборка работает только внутри сиквела зачем вы ее создаете как unsafe?
29 фев 16, 19:55    [18879263]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить