1

Topic: How to obtain the data from representation sys.extended_properties in CLR to stored procedure.

2

Re: How to obtain the data from representation sys.extended_properties in CLR to stored procedure.

permissions you already studied section?

3

Re: How to obtain the data from representation sys.extended_properties in CLR to stored procedure.

Studied. And, to tell the truth, thought that if procedure is fulfilled from user name "sa" that of problems with the rights should not be. But, in practice it turns out, what even if the stored procedure is fulfilled from user name "sa", it still about what does not speak?
It is good, then there is a second part of a question: How to receive value of extended property Mode_DB, for example, through ?
After all add, change, delete property I can stored procedures, and, here, to receive its value, only through system representation.

4

Re: How to obtain the data from representation sys.extended_properties in CLR to stored procedure.

Harlan;
If at you extended property weighs on a database that at  under which context you fulfill the  should be right view definition on basis (or one and the list in help on extended properties, but this most harmless).
Further the instruction of request should be fulfilled in a database on which property hangs.
Alter a bit :

Scm.commandtext = "select db_name () + char (32) + user_name () + char (32) + suser_name () as info";
String s = scm.executescalar ();
SqlContext. Pipe. Send (s);

Also look in what context at you procedure from what login works.
Most likely basis not that on which will adjust property.

5

Re: How to obtain the data from representation sys.extended_properties in CLR to stored procedure.

Procedure answered:
mydb dbo sa
Where mydb - a name of my database. Property Mode_DB on this database is adjusted.
I think, what the owner of a database [dbo] has the right view definition, otherwise, as though it received such information in MSSMS?

6

Re: How to obtain the data from representation sys.extended_properties in CLR to stored procedure.

Harlan;
All right was not too lazy to compile at all:
I do not know, where that you is explicit , here in such variant all works:
[spoiler 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);
}
}
}

[/spoiler]
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
/* an output:
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
/* an output:
1
*/

7

Re: How to obtain the data from representation sys.extended_properties in CLR to stored procedure.

felix_ff. Many thanks for the help!
I tried to allocate property not on a database and on the table. In this variant too all understands.