[SSRS] Get the SQL Query from your RDL via T-SQL

Coworker asked for this.

Basically we wanted to get the queries out of the RDLs on our Reporting Services server.  I saw a post originally that discussed using the tool to pull all the RDLs off of the machine, then parsing them.  Hey, waitasecond....

Immediately found the blog that showed how to use the double-convert to get back the XML.  Once I had that, I started querying the XML to get out what I wanted.  Now why am I getting NULL.....

So, after spending an hour banging my head against the XML and getting back NULL, I went to StackOverflow, whereupon a nice person named Mikael gave me the syntax I was looking for (thanks, Mikael!).  Turns out that you have to ignore the namespace, which means your syntax can get... weird (at least compared to what I normally use).

Overall, though, pleased how it turned out. Please let me know if you have any questions, comments or if there's more stuff I should show.  DataSource coming in a couple weeks; I have the code, just need to integrate it.

(Click on the "View Raw" to get the SQL in a new window.  Thanks!)

[SSIS] Finding your connection strings in SQL Server 2008/R2

"Bad artists imitate - great artists steal" Picasso Banksy

I initially wasn't sure if this was worth posting - if you have the same problem, you've probably come across the same links.  But since I don't see a soup-to-nuts version, here goes.

Problem: you're migrating servers, and need to see what SSIS packages might be affected, so you need the connection strings for all of your SSIS packages.  You could open up each package and check the connections - but that's... inefficient.

If you're storing the packages on disk, you could write a short (power)shell script that would scan the SSIS package files.  But if it's in MSDB, you'd need to pull the package XML out of the system tables, then parse the XML.  This is for 2008/R2 (and if you're not using SSISDB in 2012).  In 2012, SSISDB holds the files differently, and I'm awaiting an answer from one of the MS people on the SSIS team (thanks to Jamie Thomson sqlblog.com/blogs/jamie_thomson/‎ for forwarding my tweet to him)

Code cribbed from somebody, possibly Feodor Georgiev  (I found two links, below)
http://sqlconcept.com/2011/12/06/extracting-ssis-package-definition-from-msdb/
https://www.simple-talk.com/content/print.aspx?article=1408

and the XML parse was a question I asked on StackOverflow.  Thanks to Davmos for the help!
http://stackoverflow.com/questions/16824772/sql-server-query-xml-node-dtsconnectionmanager-dtsname-in-t-sql/16825308?noredirect=1#comment24269324_16825308

[EN] Errorlog emails based off Event Notification

NOTE! THIS DOES NOT WORK.  In writing this, we found there's a bug in EN where ERRORLOG doesn't get sent if the Source is "Server".  Which shutdown qualifies as.  You can search for "shut down", but you'll only get the message when the service restarts - the message gets sent close enough to shutdown that it doesn't make it to the centralized service until after restart.



All of our servers that have Event Notifications get the ERRORLOG event type.  What can we do with that?  Well, one thing we needed was a notice when a machine announced it was shutting down.  We've seen that a few times over the years, where the server decides it's better off restarting, and does it on its own.  This isn't a hard bounce - the error logs actually state that it's a deliberate choice by the server.  Offhand I know we've seen it due to DBCC CHECKDB, but I seem to recall other instances.

On looking at the code, I realize I could change the CTE to use half the IO.  However, I want to make sure I catch ANY instances, and the exist/contains XML query is CASE SENSITIVE.  So, I'm staying with mine for now.

But here it is in case....
 and EventType = 'errorlog'

 AND message_body_xml.exist('/EVENT_INSTANCE[1]/TextData[contains(.,"shutdown")]') = 1

And here's the full email.

set quoted_identifier on

declare @now datetime

select @now = convert(smalldatetime,getdate())

;with cte as 

(

SELECT message_body_xml.value('/EVENT_INSTANCE[1]/TextData[1]', 'varchar(150)') as textdata, * 

FROM ENAudit_Events with (NOLOCK) WHERE insert_datetime >= @now

 and EventType = 'errorlog'

 )

select * into ##listofshutdown from cte where textdata like '%shutdown%'

if (select count(*) from ##listofshutdown) > 0

begin

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'youremailprofile',

    @recipients = 'youremail@dev.null.com',

    @query = 'select * from ##listofshutdown' ,

    @subject = 'Attention - a machine has announced it is shutting down via EN ERRORLOG',

    @query_attachment_filename = 'shutdowninfo.txt',

    @query_result_separator =  ' ',

    @query_result_width = 750,

    @attach_query_result_as_file = 1 ;

end

drop table ##listofshutdown