Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dan33
Starting Member
2 Posts |
 Posted - 2009-08-14 : 05:29:55
|
| Hi All,I have a set of UDF's which are TVF that perform some reasonably complex logic and return a result set. I needed functions that could run as inline sql and returned a defined set of columns so i had to pick TVF's over SP's.For context these are c# sql clr deployed functions and are essentially mapping my existing tables to the table format of an old database structure to allow existing tools to work with the new database format for the time being.But now i run into the issue that I would really like some form of write access from within my UDF. The reason is that I want to protect the functions with try, catch so that if they fail say processing one small item of data they can log the error and continue (I'm aware it's not always the wisest idea to catch errors and continue anyway, but in these circumstances it would be more efficient to log the fact that say 1 row of data failed to process and log why then not too return any data). So ideally i would like to write to a logging table, but of course you have no write access within a UDF, nor can you call a SP from within a UDF that performs a write task, this will also fail. So any suggestions for a method that I could use to either write or return this log of errors from within the UDF so the user know's what has happens would be most appreciated.Thanks in advanceDan |
|
|
dan33
Starting Member
2 Posts |
Posted - 2009-08-20 : 06:17:31
|
| SOLUTION:For anyone interested after much searching around to no avail and resorting to a nasty workaround involving writing logs to the database sql logs using xp_LogEvent (you can do this in standard UDF) I eventually found a solution.I found a setting in the project which allows you to set permissions from safe to unsafe (god knows how i missed this before). Doing so will allow you to have write access from within your UDF's even though everywhere I searched I was informed UDF's DO NOT HAVE write access including pages with info about sql clr udf's!Presumably this option is also available within standard udf's if you can set the authorisation type for the assemblies.CheersDan |
 |
|
|
|
|
|
error logging in UDF