May 27, 2008 at 9:07 am
Hello
I am trying to send an informational message back to my SQL Client when I do not need a SQL Context Connection.
The following code produces an "Data access is not allowed in this context. " error
[SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]
public static SqlBoolean apifDataValidationByType(SqlString data, SqlString moreData)
{
SqlBoolean ret = new SqlBoolean(false);
/*Do some work with the args*/
SqlContext.Pipe.Send("Some Information");
return ret;
}
When I change
[SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]
to this
[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.None)]
I get "Object reference not set to an instance of an object"
So do I have to set my DataAccess to read and create a connection to send the informational info back to my client, or is there another way to accomplish this?
Thanks in advance.
Steve
May 27, 2008 at 11:13 am
Not sure if this is your problem, but I don't think that you can do that from a CLR function. SQLFunctions have a great number of restrictions on them. Try it from a CLR stored procedure first (much fewer restrictions).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 11:53 am
Yeah, I have seen numerous examples of a CLR stored proc, the problem is that I need a function.
I was hoping to pass an informational message back to the client without the use of an exception when I got data in my args that I did not expect.
Steve
May 27, 2008 at 12:20 pm
Sorry, I am pretty sure that the only ways for you to return information from a function are through the Function return value itself or else as an exception. SQL Server is pretty aggressive about trying to prevent any "side-effects" from functions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 12:22 pm
I don't think you have access to the SQLContext.pipe from within a UDF. That's one of those ugly limitations the UDF's seem to have.
Since you have a function, your best bet might be to change its return type to carry more than just the true or false, but to also have the message you want back.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy