Logging within User_Defined Functions (UDF)

From: Rob (anonymous_at_discussions.microsoft.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 07:19:32 -0700

Hi

I'm trying to carry out some logging each time an UDF
executes.However I understand there are some limitations,
i.e you cannot INSERT data into tables directly from
within a function. Ultimately you can only execute
functions and extended procedures within a function.

So i've tried to use the xp_execresultset xp to insert the
appropriate logging information into a table. using this
xp by itself and not in a function works fine, but as soon
as I copy this bit of code to a normal function, i.e not a
table function, i still get error :

Server: Msg 557, Level 16, State 2, Procedure zzz_Rob,
Line 13
Only functions and extended stored procedures can be
executed from within a function.

Why?!!!!

I know that my last resort would be to use xp_cmdshell and
run an osql statement but that would mean giving users
access to xp_cmdshell which I dont want to do

I really just need to know whether I am barking up the
wrong tree here. please tell me if I simply cannot do what
i want to do. Strange this is that it compiles, just wont
let me execute

Many thanks in advance
Rob

PS: here is a scaled down version of what I want to do but
it gives you an idea...

CREATE FUNCTION dbo.zzz_Rob (@udt nvarchar(4000))
RETURNS int
AS
BEGIN
        DECLARE @cmdstr nvarchar(500)
        DECLARE @db nvarchar(50)

        SELECT @db = convert(nvarchar(50),db_name())

        SELECT @cmdstr = 'SELECT ' + '''' + 'SET NO COUNT
ON INSERT EDM..t_ADM_TickerObjectLog(UserName) VALUES('
                + '''' + '''' + convert(nvarchar,user_name
()) + '''' + '''' + ')' + ''''

        EXEC master..xp_execresultset @cmdstr,@db
        
        RETURN 0

END

Relevant Pages

  • Re: Logging within User_Defined Functions (UDF)
    ... >>SQL Server MVP ... >>> I'm trying to carry out some logging each time an UDF ... Ultimately you can only execute ... >>> Only functions and extended stored procedures can be ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging within User_Defined Functions (UDF)
    ... I am not performing dynamic SQL ... >> I'm trying to carry out some logging each time an UDF ... Ultimately you can only execute ... >> Only functions and extended stored procedures can be ...
    (microsoft.public.sqlserver.programming)
  • Re: Write log file using Thread
    ... It sounds like you are talking about having an asynchronous process for logging stuff so that you can return control to the user instead of blocking the UI thread. ... Actually my programm execute a function and at the end of the function ... i write the duration time to a log file. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Extended stored procedures failues - only under sql server log
    ... I'm talking about "home grown" extended stored procedures. ... For example, 'sa' can execute xp_cmdshell, but CANNOT execute our ... regardless of the fact that its sql server login is ...
    (microsoft.public.sqlserver.programming)
  • .bash_profile ignored in X
    ... During configuration of another machine running Etch, ... that .bash_profile appears to be ignored when logging into X. ... execute the same command, "~/bin" does appear in the path. ... To UNSUBSCRIBE, email to debian-user-REQUEST@xxxxxxxxxxxxxxxx ...
    (Debian-User)