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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply