Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonHello Simon,
The attachment didn't work here. Please contact me at ktegels@.develop.com
about this one.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
Simon
Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.co m:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception.
Force of habit really, just so I can see what the exception was a bit easier.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon
|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.co m...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>
|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
sql

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
--
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

get trigger schema within CLR code

Hi.

I am trying to get the schema in which the trigger is created within the CLR code.

1)create a new schema MySchema.

2) created a table MySchema.MyTable

3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.

Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.

So how do dynamically get the trigger's schema name ?

Thanks for your help.

Nach

There is no way to get the schema of the currently executing trigger from clr. With T-SQL, you code would have to create dynamic sql based on the current trigger, or rely on the implict schema name binding that occurs.

There are alternatives
1. Use execute as user and have the specified user have a default schema of MySchema
2. Create a T-SQL trigger that calls your clr trigger converted to a proc, passing it the @.@.ProcId of the T-SQL trigger as a parameter, and you can then get object_name & schema id from the clr procedure