What, When, Where, How, Who2

Question

Post reply

What, When, Where, How, Who2

SQLRNNR

SSC Guru

Points: 281334
More actions
June 12, 2010 at 11:24 am

#236430

Comments posted to this topic are about the item What, When, Where, How, Who2
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Viewing 15 posts - 1 through 15 (of 51 total)

You must be logged in to reply to this topic. Login to reply

John Cuthbertson SSC Enthusiast Points: 104 More actions · Answer 1

Jason

Looks like you have done some good work.

However, I tried to run the stored procedure and got....

Msg 6841, Level 16, State 1, Procedure who4, Line 28

FOR XML could not serialize the data for node 'processing-instruction(definition)' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

I will try and debug if/when I get time, If any anyone else has a solution already then tell me!

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Nice article, Jason.

I haven't tried your code yet, but looks very neat.

Well done.

-- Gianluca Sartori

andycao Say Hey Kid Points: 698 More actions · Answer 3

Very nice. Code works fine on our SQL2008 (SP1) - 10.0.2531.0 server. Thanks!

SethT3 SSCommitted Points: 1639 More actions · Answer 4

Very well written, Jason! I appreciate the elimination of the DBCC INPUTBUFFER step after all of my sp_who2 steps.

WayneS SSC Guru Points: 95460 More actions · Answer 5

Good article Jason! 😉

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

John Cuthbertson (6/14/2010)
Jason
Looks like you have done some good work.
However, I tried to run the stored procedure and got....
Msg 6841, Level 16, State 1, Procedure who4, Line 28
FOR XML could not serialize the data for node 'processing-instruction(definition)' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
I will try and debug if/when I get time, If any anyone else has a solution already then tell me!

First - thanks.

Second, what version and patch level of SQL Server? I have not encountered this in my testing.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Gianluca Sartori (6/14/2010)
Nice article, Jason.
I haven't tried your code yet, but looks very neat.
Well done.

Thanks Gianluca

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

andycao (6/14/2010)
Very nice. Code works fine on our SQL2008 (SP1) - 10.0.2531.0 server. Thanks!

Thanks - and you're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Seth-95001 (6/14/2010)
Very well written, Jason! I appreciate the elimination of the DBCC INPUTBUFFER step after all of my sp_who2 steps.

Thank you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

WayneS (6/14/2010)
Good article Jason! 😉

Thanks Wayne.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

Jason

Thanks for a job well done. Now to add this new tool to my tool box.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 12

Hey Jason,

Good Job there. I already have this version running in QA right now. 🙂

-Roy

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 13

Nice article Jason! I haven't tested the code yet but can't wait to have a chance to do this. You did a really thorough job.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

bitbucket-25253 (6/14/2010)
Jason
Thanks for a job well done. Now to add this new tool to my tool box.

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events