Re: Logging within User_Defined Functions (UDF)

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 10:51:18 -0400

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

  • Re: stored procedure security
    ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
    (microsoft.public.sqlserver.security)
  • Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
    ... Once you go to dynamic SQL, you are outside the ownership / security context ... I wrote a script that GRANTS EXECUTE to ... every stored procedure in the database to and DENY ...
    (microsoft.public.sqlserver.security)
  • OpenStoredProcedure and Parameters
    ... I need to execute a stored procedure ... new stored procedure that inserts records into the table; ... DeleteAdHocQuery(UserName) ... DECLARE @UserName varchar ...
    (microsoft.public.access.modulesdaovba)
  • Re: Confused with security
    ... >Using windows integrated security. ... Your guess is correct - welcome to the pitfalls of dynamic SQL. ... If a stored procedure references a table owned by the same userid that ... Each user that has rights to execute the procedure can ...
    (microsoft.public.sqlserver.server)
  • Re: need some advise
    ... I would opt for creating a stored procedure and executing that. ... > need some advise on the best way to do something, i have written a script ... > that i want to execute from an application that i am writting using ... > declare @name varchar ...
    (microsoft.public.sqlserver.programming)