SQL Functions - Logging

Question

Possible Duplicate:
TSQL How do you output PRINT in a user defined function?

This is driving me crazy.

I have a function I am trying to debug, but I can't insert into a table, execute a print statement, or even raise an error from it.

SQL returns the message

Invalid use of a side-effecting operator 'PRINT' within a function.

How can I Debug this, I just want to know the value of a variable while it is running?

What is exactly is your function doing? Maybe you should be using a stored procedure instead. As the error message states, you can't do what you're trying to do... — Aaron Bertrand, May 23 '12 at 14:12
(surprisingly) `xp_cmdshell` can be called from a function so [might help](http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx) as a temporary debugging measure. — Martin Smith, May 23 '12 at 14:13
@MartinSmith, unfortunately, our dba has `xp_cmdshell` disabled, but it was a good idea! — CaffGeek, May 23 '12 at 14:26
@AaronBertrand, while it's a possible duplicate, that question has no solution that isn't a potentially complicated rewrite of the function. And ironically, now links to this question for a possible solution. — CaffGeek, May 23 '12 at 14:39
@Chad sorry but I disagree with your assessment that xp_cmdshell is a viable solution, particularly since it's off by default and even you yourself can't turn it on - this will also be true for a lot of readers, and there are other workarounds you're already been offered that are a safer way to do this in general. — Aaron Bertrand, May 23 '12 at 14:42

score 6 · Accepted Answer · answered May 23 '12 at 14:23

6

try:

DECLARE @ExecuteString   varchar(500)
       ,@Yourtext        varchar(500)

@ExecuteString = 'echo  '+@Yourtext+' > yourfile.txt)

exec master..xp_cmdshell @ExecuteString, no_output

use: ">" to create/overwrite file, and ">>" to create/append onto file. you might need to use REPLACE(@Yourtext,'&','^&') to escape the & char

answered May 23 '12 at 14:23

KM.

101,727
34
178
212

Even though we have `xp_cmdshell` disabled here, this would normally be a good solution! – CaffGeek May 23 '12 at 14:28
2

@Chad, since you have a table value function, I just build up a debug text string and return it as a column in that table, which you can dump/display in the calling code. – KM. May 23 '12 at 14:30

score 3 · Answer 2 · answered May 23 '12 at 14:20

3

If your function returns a string, you could always interject a return e.g.

ALTER FUNCTION dbo.whatever()
RETURNS NVARCHAR(128)
AS
BEGIN
  DECLARE @foo VARCHAR(128);

  SELECT TOP (1) @foo = name FROM sys.objects ORDER BY NEWID();

  -- temporary debug:
  RETURN (@foo);

  ... continue with function
END
GO

If the variable is numeric or date, you could use RTRIM() or CONVERT() to safely convert it to a string first.

You could do something similar with a table-valued function, just add a column where you can place whatever debug message or variable you want to output.

answered May 23 '12 at 14:20

Aaron Bertrand

272,866
37
466
490

It's a table valued function. – CaffGeek May 23 '12 at 14:22
1

@Chad OK... so add a column to the TVF and insert/update your debug value there before returning. – Aaron Bertrand May 23 '12 at 14:25
@KM., why? I'm not leaving the `xp_cmdshell` in forever, it's quick, and easily removed. Adding a column to the return table, and populating it is a lot of work, especially if I want several values, and the table has many non-nullable fields.. – CaffGeek May 23 '12 at 14:30
@Chad, at a lot of places you can't turn on xp_cmdshell, and I wasn't sure if this was a production issue or not. I find it easier to just concatenate all your values together and return an extra column than try to get access to a file on the database server. – KM. May 23 '12 at 14:31
@KM. I actually can't turn it on, but given the question, it's the simplest solution. – CaffGeek May 23 '12 at 14:34
1

@Chad then how is it a solution if you can't use it??? – Aaron Bertrand May 23 '12 at 14:40
@AaronBertrand, got it switched on for the dev server. – CaffGeek May 23 '12 at 14:46
Well that's great, for you, in your dev environment. Not all folks will be able to do that anywhere. Also you run the risk of forgetting to remove the xp_cmdshell junk when you port the code to other environments. Why is writing to a file on the hard drive better than simply changing the output of the function temporarily? – Aaron Bertrand May 23 '12 at 14:49
@AaronBertrand, you wouldn't forget to remove it, because the other environments if properly setup wouldn't allow xp_cmdshell. I'd prefer to use a `DEBUG` statement or something similar to `PRINT` that would be allowed and perhaps only applicable to results when in management studio, but it doesn't exist...yet. – CaffGeek May 23 '12 at 15:30
3

@Chad But you won't know you left it there until you RUN it. A lot of readers aren't their own QA/deployment/test shop, there are different people with different hats. So it won't necessarily be up to the person that put an xp_cmdshell call into a function to remember to take it out before deployment. PS I'm not trying to convince you, I know you've already decided to do the wrong thing. I'm voicing my objections so future readers have some chance to do the right thing. – Aaron Bertrand May 23 '12 at 15:30
1

And those same people who forget to take out the call to xp_cmdshell would forget to take out their table changes. Either way, sloppy on their part. I'm not doing this for a change that will last days, it was a 1 minute, put in statement, see value, remove statement. – CaffGeek May 23 '12 at 16:03
@Chad I still disagree. They might review the output in the log once, confirm that it's doing what they expect, and go on modifying the function (leaving the xp_cmdshell logging for nothing). With the actual output changing, it's much less likely they'd "forget" since xp_cmdshell doesn't actually change the way the function works, and they would have to fix those changes once they wanted to confirm it's working correctly. Again, do what you want, I still think your excuse is not helpful for future readers. – Aaron Bertrand May 23 '12 at 16:16

SQL Functions - Logging

2 Answers2

Linked