Re: Logging within User_Defined Functions (UDF)

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

Date: Fri, 21 May 2004 11:06:19 -0400

*sigh*

You're going to get much more useful help if you provide your actual
requirements, what you actually want to do, and what you've actually tried,
instead of making up useless code that steers us in the wrong direction! 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rob" <anonymous@discussions.microsoft.com> wrote in message
news:1042201c43f44$4762a670$a101280a@phx.gbl...
> 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)
    ... 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: Deadlock incidence greatly increased after upgrade from 11.5.1 to 12.5.3 - Suggestions?
    ... They may not execute simultaneously, but each user process only gets a ... Mike Epprecht, Microsoft SQL Server MVP ... >> Mike Epprecht, Microsoft SQL Server MVP ...
    (comp.databases.sybase)
  • Re: how to return the result of execute sql programmatically
    ... You can get the result through an Execute SQL Task, so no ned for ADO if you don't like it. ... I'd like to schadule the package in another server which my password of integrated security might not work. ... oExecSQL.SQLStatement = sSQLStatement ' Clean Up Set oExecSQL = Nothing ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: sp_password
    ... SQL Server MVP ... "Leon Parker" wrote in message ... > Whenever I execute the procedure I get the error message that the ...
    (microsoft.public.sqlserver.security)