Re: Logging within User_Defined Functions (UDF)

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

Date: Fri, 21 May 2004 07:59:56 -0700

This is not the actual function what i will be using to
house my logging. I used this as an simplified test-
harness. Thing is, I am not performing dynamic SQL
directly in the function thats why i'm exec in an xp. if i
do this through cmdshell, it works fine. Can you suggest
any other way?

>-----Original Message-----
>You can't perform dynamic SQL or DML (INSERT, UPDATE,
DELETE) in a function.
>See Books Online for some of the rules governing the use
of functions and
>limitations on what you can do inside of them...
>
>Why is this a function anyway? Looks like a stored
procedure that you
>accidentally typed "CREATE FUNCTION"...
>
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>
>"Rob" <anonymous@discussions.microsoft.com> wrote in
message
>news:103ab01c43f3e$a2fbf500$a401280a@phx.gbl...
>> 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

  • Logging within User_Defined Functions (UDF)
    ... I'm trying to carry out some logging each time an UDF ... executes.However I understand there are some limitations, ... Ultimately you can only execute ... Only functions and extended stored procedures can be ...
    (microsoft.public.sqlserver.programming)
  • 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: 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)
  • .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)
  • Error Message
    ... After logging on, this error message appears: WJView ... Error: Could not execute Main: The system ... I ran some spyware ...
    (microsoft.public.windowsxp.perform_maintain)
Loading