Re: Logging within User_Defined Functions (UDF)

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 16:30:29 +0200

> Why?!!!!

Despite its name, xp_execresultset is an ordinary stored procedure, not an extended stored procedure. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.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

  • 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: 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)
  • Re: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
  • RE: Trying to creatively work around 8000 Character limit with sp_exec
    ... declare @str2 varchar ... > trigger to dynamically re-create a stored procedure within our system. ... > than 8000 characters, contrary to what I have read on this newsgroup ... > to actually execute the output. ...
    (microsoft.public.sqlserver.programming)
  • Re: UniqueIdentifier column
    ... >> I am trying to execute the following stored procedure by the ... > The value you are using is not a valid uniqueidentifier. ... > DECLARE @LockID UNIQUEIDENTIFIER ...
    (microsoft.public.sqlserver.clients)