Sending Info Message back to client w/out context connection

• Steve

  SSCommitted

  Points: 1844

  More actions

  May 27, 2008 at 9:07 am

  #182783

  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

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 27, 2008 at 11:13 am

  #820703

  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]

• Steve

  SSCommitted

  Points: 1844

  More actions

  May 27, 2008 at 11:53 am

  #820725

  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

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 27, 2008 at 12:20 pm

  #820744

  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]

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  May 27, 2008 at 12:22 pm

  #820748

  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?