THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

How to log when a function is called?

This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb

--Create the table
USE tempdb
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
--Here is the proc
LogMeNow (SomeValue) VALUES(@SomeValue)
--And here is the function
CREATE FUNCTION fnBla(@id int)
@SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL

Now call the function a couple of times

SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)

And look inside the table


What if you were to run this?

SELECT dbo.fnBla(4),* FROM sys.sysobjects

See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!

I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

Published Thursday, May 8, 2008 2:25 PM by Denis Gobo



Alex said:

Filthy even...

I'm kind of having trouble thinking of a reason to log function calls at all.  It seems anything requiring logging ought to be done through procs, triggers or both.  I guess someone has found a reason though :(

May 8, 2008 3:04 PM

Adam Machanic said:

How about using a SQLCLR UDF that uses a trusted connection back to localhost?  Arguably a teeny tiny bit cleaner than xp_cmdshell -> OSQL (but just the tiniest bit).

May 13, 2008 1:56 PM

Aamir Hasan said:

well  if you will enable the xp_cmdshell and your database is over the internet will be dangerous

August 29, 2011 1:58 PM

nabilmubarak said:

We are financial builders and we give out no credit check loans to everyone ranging from personal, commercial, business loans with our amounts ranging from min $5,000- max $10,000,000 with a fixed and very low interest rate of 3%. Do you want to own a company? Do you want to own a Home? Is Your Company having financial problem? Do you have a contract/project and need money to fund it? Apply for a loan today and get financed within 3-4 working days. HOW TO APPLY: send us the below information through e-mail ( )

Your Name:...............

Your Country:...............

Your Occupation:...............

Loan Amount Needed:...............

Loan Duration...............

Monthly Income:...............

Your Telephone Number:.....................

Business Plan/Use Of Your Loan:...............

Email Us:

September 5, 2016 4:15 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement