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

Откуда: Новосибирск
Сообщений: 75
У некоей базы данных определил расширенное свойство Mode_DB.
В MSSMS выполняю запрос:
SELECT count(*) FROM sys.extended_properties WHERE Name='Mode_DB' and class=0 and major_id=0 and minor_id=0

Результат: 1
В VS определяю проект для SQL-сервера с 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 SqlInt32 ExampleClr ()
    {
        SqlConnection SC = new SqlConnection("context connection=true");
        SqlCommand SCM = SC.CreateCommand();
        SC.Open();
        SCM.CommandText = "SELECT count(*) FROM sys.extended_properties WHERE Name='Mode_DB' and class=0 and major_id=0 and minor_id=0";
        int i = (int)SCM.ExecuteScalar();
        SC.Close();
        return new SqlInt32(i);
    }
}

Компилирую, разворачиваю, выполняю... Результат: 0
Думаю, что процедура исполняется в контексте другой таблицы (например "master") и интереса меняю запрос на запрос к таблице из целевой базы данных, результат выдаётся адекватный.
Т.е. проблема при обращении к таблице sys.extended_properties.

Не будет ли любезен многоуважаемый All подсказать, в чём проблема и как получить значение свойства Mode_DB не через представление, а скажем, через хранимую процедуру?
==
А баги ползали и нагло шевелили усами... © Не моё.
7 сен 17, 14:40    [20778829]     Ответить | Цитировать Сообщить модератору
 Re: Как получить данные из представления sys.extended_properties в CLR хранимой процедуре.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Раздел permissions вы уже изучили?
7 сен 17, 14:46    [20778855]     Ответить | Цитировать Сообщить модератору
 Re: Как получить данные из представления sys.extended_properties в CLR хранимой процедуре.  [new]
Harlan
Member

Откуда: Новосибирск
Сообщений: 75
Изучил. И, честно говоря, подумал, что если процедура выполняется от имени пользователя "sa" то проблем с правами быть не должно. Но, на деле получается, что даже если хранимая процедура выполняется от имени пользователя "sa", это ещё ни о чём не говорит?
Хорошо, тогда остаётся вторая часть вопроса: Как получить значение расширенного свойства Mode_DB, например, через хранимку?
Ведь добавить, изменить, удалить свойство я могу хранимыми процедурами, а, вот, получить его значение, только через системное представление.
7 сен 17, 15:07    [20778952]     Ответить | Цитировать Сообщить модератору
 Re: Как получить данные из представления sys.extended_properties в CLR хранимой процедуре.  [new]
felix_ff
Member

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

Если у вас расширенное свойство весит на базе данных то у потльзователя под контекстом которого вы выполняете свою хп должно быть право view definition на базу (или одно и списка в справке по расширенным свойствам, но это самое безобидное).

Далее сама инструкция запроса должна выполняться в базе данных на которой висит свойство.
Видоизмените немного свою хп:
Scm.commandtext = "select db_name() + char(32) + user_name() + char(32)+ suser_name() as info";
String s = scm.executescalar();
SqlContext.Pipe.Send(s);


И посмотрите в каком контексте у вас работает процедура от какого логина.
Скорее всего база будет не той на которой настроено свойство.
7 сен 17, 15:56    [20779161]     Ответить | Цитировать Сообщить модератору
 Re: Как получить данные из представления sys.extended_properties в CLR хранимой процедуре.  [new]
Harlan
Member

Откуда: Новосибирск
Сообщений: 75
Процедура ответила:
mydb dbo sa
где mydb - имя моей базы данных. Свойство Mode_DB на этой базе данных настроено.

Думаю, что владелец базы данных [dbo] имеет право view definition, иначе, как бы он получал такую информацию в MSSMS?
8 сен 17, 08:02    [20780453]     Ответить | Цитировать Сообщить модератору
 Re: Как получить данные из представления sys.extended_properties в CLR хранимой процедуре.  [new]
felix_ff
Member

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

Ладно даже не поленился скомпилировать:

не знаю, где то вы явно накосячили, вот в таком варианте все работает:

+ CLR

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

namespace CLRProcedures
{
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static SqlInt32 ExampleClr(SqlString name)
        {
            SqlConnection SC = new SqlConnection("context connection=true");
            SqlCommand SCM = SC.CreateCommand();
            SC.Open();
            SCM.CommandText = "SELECT count(*) FROM sys.extended_properties WHERE Name=@Name and class=0 and major_id=0 and minor_id=0";
            SCM.Parameters.AddWithValue("@Name", name.Value);
            int i = (int)SCM.ExecuteScalar();
            SC.Close();
            return new SqlInt32(i);
        }
    }
}



SQL:
create assembly sf 
from 'C:\temp\CLRProcedures.dll'
go

create procedure test_sf 
@Name nvarchar(255)
as external name sf.[CLRProcedures.StoredProcedures].ExampleClr
go

select * from sys.extended_properties where class = 0
/* вывод:
class	class_desc	major_id	minor_id	name	value
0	DATABASE	0	0	MY	test
*/
declare @i int
exec @i = test_sf 'MY'
select @i
/* вывод:
1
*/
8 сен 17, 10:56    [20781153]     Ответить | Цитировать Сообщить модератору
 Re: Как получить данные из представления sys.extended_properties в CLR хранимой процедуре.  [new]
Harlan
Member

Откуда: Новосибирск
Сообщений: 75
felix_ff. огромное спасибо за помощь!
Я попробовал разместить свойство не на базе данных а на таблице. В этом варианте тоже всё понимает.
8 сен 17, 12:08    [20781398]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить