THE SQL Server Blog Spot on the Web

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

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Capturing the Output (A Month of Activity Monitoring, Part 25 of 30)

THIS CONTENT HAS BEEN MOVED HERE.

Published Monday, April 25, 2011 11:00 AM by Adam Machanic

Comments

 

Antony said:

Hi Adam,

enjoying this series - makes me think i've only scratched the surface of how to use this procedure!

I haven't had a chance to look into the cause, but I've experienced a bug using 10.83 & 10.99 on 2005 (SP3 & SP4) and 2008 R2 (RTM) (not used it on any other environments yet):

When I have a service broker activation procedure running, the program_name for ALL the sessions in the result set is the information regarding the activation procedure. As soon as this procedure stops executing, the program_name column returns to normal for the other sessions.

April 25, 2011 2:00 PM
 

Adam Machanic said:

Thanks for reporting this, Antony. I've fixed the issue on my end, so it won't be a problem in the next release.

Best,

Adam

April 25, 2011 4:10 PM
 

Antony said:

Thanks Adam. Having Activation procedures visible by default is going to be very useful in our environment.

April 26, 2011 4:22 AM
 

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM
 

Kukah said:

Hi Adam,

I've noticed a minor difference when outputing WIA into a table.

[sql-text] is no longer accessible with XML hyperlink.

Other XML hyperlinks like [query_plan] (@get_plans), or [additional_info] (@get_additional_info) work fine.

I though you might want to be aware of this.

Kukah

ps:here is the script i used

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive

@get_additional_info = 1,    

@get_plans = 1,

   @format_output = 0,

   @return_schema = 1,

   @schema = @s OUTPUT

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.quick_debug')

EXEC(@s)

GO

EXEC sp_WhoIsActive

@get_additional_info = 1,    

@get_plans = 1,

@format_output = 0,

   @destination_table = 'tempdb.dbo.quick_debug'

WAITFOR DELAY '00:00:05'

GO 10

September 17, 2012 8:55 PM
 

Adam Machanic said:

Hi Kukah,

It's because you have @format_output set to 0. That turns off the XML formatting. (In addition to numeric formatting for various columns.)

--Adam

September 17, 2012 10:07 PM
 

Kukah said:

That's correct Adam.

Thanks for the answer.

I should have thought about that.

I guess I confused myself because other XML hyperlinks were available.

Kukah

September 19, 2012 11:42 AM
 

Danny said:

Hi Adam,

I have tried your examples above on SQL Server 2005 SP4 and I get the following warning.

Warning: Null value is eliminated by an aggregate or other SET operation.

Can you please provide some help.

Thanks,

Danny

May 9, 2013 9:11 PM
 

Adam Machanic said:

@Danny: That's not anything to worry about. It's just an informational message from the query processor.

May 12, 2013 3:46 PM
 

Greg White said:

I was looking to grab the output into a table from a stored procedure that contained dynamic sql.

At first I thought I was going mad as I got the nested exec error but realised when I found this page that its not a simple solution. Thanks for sharing Adam.

February 3, 2016 7:26 AM
 

sonny said:

Hi Adam,

Would be very useful if WhoisActive results can be export to excel.

No doubt this is one of best tool to capture user activities in real time and I am inserting into table. I need to send to application team and best format is excel.

I have referred to Michael article (http://michaeljswart.com/2012/03/sending-query-results-to-others/) but format did not work out correctly using any of option in the article.

Is it something possible to do so? are you able to help.

Regards

Sonny

February 16, 2017 11:46 PM
 

Adam Machanic said:

@sonny

I don't think a stored procedure can export data to Excel in any reasonable way, and architecturally it's not something I'd ever want to do. I think the best way to get the data into Excel would be some form of embedded VBA that will call the proc directly and render it in Excel in a custom format (probably different than the format that the proc outputs). It's been a long time since I've done any work in that area so I'm not sure of the correct terminology but in my mind I can see it working quite well. Not sure how helpful that is :-)

--Adam

February 17, 2017 9:13 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Privacy Statement