July 19, 2005 at 10:14 am
I am trying to setup a procedure to stop all traces (max 4). I've figured out how to do this with a cursor, but in keeping with the "cursor is evil" idea, I'd like to do this differently. Any ideas?
Here's my code:
create procedure change_status_traces @status int as
DECLARE @traceid int
DECLARE traceid_cursor CURSOR FOR
SELECT distinct traceid from ::fn_trace_getinfo(default)
OPEN traceid_cursor
FETCH NEXT FROM traceid_cursor
INTO @traceid
WHILE @@FETCH_STATUS = 0
BEGIN
execute sp_trace_setstatus @traceid, @status
FETCH NEXT FROM traceid_cursor
INTO @traceid
END
CLOSE traceid_cursor
DEALLOCATE traceid_cursor
GO
Thanks,
Nancy
July 19, 2005 at 10:31 am
I fully agree with the cursors are bad mentality, but in this case you don't have much of a choice. About the only other option is to create dynamic sql and since we probably aren't dealing with large numbers of traces the cursor is probably your best alternative.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 19, 2005 at 10:34 am
Hi Nancy,
There is always the 'cursorless loop' in your situation:-
Declare @WorkingVariable, @EndVariable
SET @EndVariable = SELECT MAX(distinct traceid)from ::fn_trace_getinfo(default)
SET @WorkingVariable= SELECT MIN(distinct traceid)from ::fn_trace_getinfo(default)
WHILE @WorkingVariable <= @EndVariable
BEGIN
--DO THE WORK
--INCREMENT BEFORE TESTING
SET @WorkingVariable= SELECT MIN(distinct traceid)from ::fn_trace_getinfo(default) WHERE traceid > @WorkingVariable
END
Not too sure if this is actually any faster but it eliminates the use of cursors. I'm sure someone else (maybe 'the bulldozer' ) will come up with a set based solution - but i'm not in that ballpark yet......
Have fun
Steve
We need men who can dream of things that never were.
July 19, 2005 at 11:20 am
Thanks for the suggestions. I've seen solutions on this site before which use a select statement to do some complex things (for example: select @output = @output & "," & input from ...) but were unable to get this to work with a stored procedure. I was hoping someone more experienced than me would know how.
- Nancy
July 19, 2005 at 11:46 am
Nancy,
Your solution is fine. Just for the fun of it
is this what you were talking about
create procedure change_status_traces @status int as
begin
declare @STR varchar(8000)
select @STR = Isnull(@str,'') + ' execute sp_trace_setstatus @traceid = '+ cast(traceid as varchar(20)) + ', @status = ' + cast(@status as varchar(20)) + char(13)+ Char(10)
from (SELECT distinct traceid from ::fn_trace_getinfo(default) ) derived
exec (@str)
end
note: this is not going to provide you with any benefits though!!!
Cheers
* Noel
July 19, 2005 at 12:04 pm
Very cool! I hadn't thought of building a statement and then executing it. I was trying to pass the value directly to sp_trace_setstatus and it just didn't like me.
I think you are right, I'm best off with the script I have, but I will definitely file away the technique for later use.
Thanks,
Nancy
July 19, 2005 at 1:58 pm
Sure!
That's how we all learn
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply