[tips] using OPENROWSET to avoid "An INSERT EXEC statement cannot be nested."

Found this, looks like from Paul Ibison (replicationanswers.com).  In this case I _did_ need it for replication, but this could be useful whenever you are run into the error "An INSERT EXEC statement cannot be nested.".

One note - depending on how the original piece of code works, you may or may not need the SET FMTONLY OFF;.  (In this case I do).  If you do, be aware that the code is run TWICE.  For this, it works fine, but keep it in mind.

USE eif_workspace
GO
create table dbo.repmonitor (
[status] int null,
warning int null ,
subscriber sysname null ,
subscriber_db sysname null ,
publisher_db sysname null ,
publication sysname null ,
publication_type int null ,
subtype int null ,
latency int null ,
latencythreshold int null ,
agentnotrunning int null ,
agentnotrunningthreshold  int null ,
timetoexpiration  int null ,
expirationthreshold  int null ,
last_distsync  datetime null ,
distribution_agentname  sysname null ,
mergeagentname  sysname null ,
mergesubscriptionfriendlyname  sysname null ,
mergeagentlocation  sysname null ,
mergeconnectiontype  int null ,
mergePerformance  int null ,
mergerunspeed float null ,
mergerunduration int null ,
monitorranking  int null ,
distributionagentjobid  binary(30) null ,
mergeagentjobid binary(30) null ,
distributionagentid  int null ,
distributionagentprofileid int null ,
mergeagentid int null ,
mergeagentprofileid int null ,
logreaderagentname sysname null
)
go

Insert Into aud.dbo.repmonitor

SELECT *

FROM OPENROWSET('SQLNCLI', 'Server=yourservername;Trusted_Connection=yes',  'set fmtonly off;  exec distribution..sp_replmonitorhelpsubscription @Publisher=@@servername,@publication_type=0')

[Extended Events] Finding new connections and saving to a asynchronous bucketizer/histogram

We're trying to get rid of our .Net SQLClient Data Provider apps.  Well, trying to get rid of the useless name.  How do we do that?  By figuring out which servers they're coming from and which databases they're hitting, and giving that to our systems folk so they can find the connection strings and add Application Name.

My first thought was EN...  ha!  No, it won't work for that, I don't think.
My second thought were traces.  Better, but we'd need to just get audit_login, then we'd have to parse it out, etc.
So my third thought was to use XE.  Lo and behold, it works!  Get the client host names, and save them to a bucket.  Then, as they connect, either add to or increment the number for that bucket.

-- If the Event Session Exists, drop it first
IF EXISTS (SELECT 1
FROM sys.server_event_sessions
WHERE name = 'UnknownAppHosts')
DROP EVENT SESSION UnknownAppHosts
ON SERVER;

-- Create the Event Session
CREATE EVENT SESSION UnknownAppHosts
ON SERVER
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_hostname)
WHERE ([sqlserver].[client_app_name] LIKE 'Microsoft SQL Server Management%')
)
ADD TARGET package0.histogram
( SET slots = 50, -- Adjust based on number of databases in instance
 filtering_event_name='sqlserver.login',
 source_type=1,
 source='sqlserver.client_hostname'
)
WITH(MAX_DISPATCH_LATENCY =1SECONDS);
GO

-- Start the Event Session
ALTER EVENT SESSION UnknownAppHosts
ON SERVER
STATE = start ;
GO

-- Parse the session data to determine the databases being used.
SELECT  slot.value('./@count', 'int') AS [Count] ,
        slot.query('./value').value('.', 'varchar(20)')
FROM
(
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions AS s
ON t.event_session_address = s.address
WHERE   s.name = 'UnknownAppHosts'
 AND t.target_name = 'Histogram') AS tgt(target_data)
CROSS APPLY target_data.nodes('/HistogramTarget/Slot') AS bucket(slot)
ORDER BY slot.value('./@count', 'int') DESC

GO


-- Start the Event Session
ALTER EVENT SESSION UnknownAppHosts
ON SERVER
STATE = STOP ;
GO

[EN] My exclusion list for events

During my Event Notification presentation, I said I'd provide a list of my filters.  Hope these help.


exclusion_set exclusion_type excluded_value
a ApplicationName Quest Diagnostic Server (Monitoring)
b DatabaseName tempdb
c EventType update_statistics
d ObjectName _WA_Sys%
f EventType OBJECT_CREATED
f EventSubClass 0
g EventType OBJECT_ALTERED
g EventSubClass 0
h EventType OBJECT_DELETED
h EventSubClass 0
j PropertyName show advanced options
k EventType OBJECT_CREATED
k ObjectType 21587

ac ApplicationName Red Gate Software Ltd SQL Prompt%
ac EventType ERRORLOG


a: Quest Spotlight, which tends to create a large number of objects.
b: tempdb, so I don't get temp tables.
c: update_statistics, only needed if you're using DDL_TABLE_VIEW_EVENTS but don't want stats. Type out the extra characters in your deploy, it'll be easier on your systems
d: system-created statistics, which would show up in OBJECT_CREATED
f/g/h: For OBJECT events, it filters EventSubClass 0 , which occurs when the OBJECT change first runs.  (there is also a EventSubClass 1 or 2 returned, success/failure)
j: We have a couple apps which flip it incessantly.
k: this should be a dupe of D - ObjectType 21587 is statistics (as per: http://msdn.microsoft.com/en-us/library/ms180953.aspx)
ac: because SQL Prompt runs a trace flag (3604) that shows up in your database.

Here's how I looked at the errorlog messages:

SELECT message_body_xml.value('/EVENT_INSTANCE[1]/TextData[1]', 'varchar(100)'), *
FROM EventNotificationRec..ENAudit_Events
WHERE EventType = 'errorlog' AND
message_body_xml.exist('/EVENT_INSTANCE[1]/ApplicationName[contains(.,"Red Gate Software Ltd SQL Prompt")]') = 1

That gives a good example on querying the XML.  The "exist" (MUST be lower case!) basically sees if the App Name contains that phrase, and also returns the TextData field, up to the first 100 characters.

[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

[PASS] everything from my presentation today on Event Notifications

Thanks to all who attended.  Here's my Share with all the code samples and slide deck from today.  I'll be cleaning things up in the next couple of hours, replacing updated code and the like. 
Everything's current now - the "script 1-5" are the scripts shown during the demonstration.  The SSMS report and SSRS report have been added, and my automation script is the current version.  The Parse_EN_Messages was updated in November and hasn't changed since.

At the very least, find yourself a testbox and run Script 1 - that shows how it all works, and is a great Proof Of Concept.  You could also change the trace group to DDL_SERVER_LEVEL_EVENTS and see all the various things tracked.


Thanks again!
TBD


https://docs.google.com/folder/d/0B3a287PS_UJIcnY3Q1pvX3p1eEE/edit?usp=sharing

And my original blog post on it, which I keep updating:
http://thebakingdba.blogspot.com/2012/10/master-of-all-i-survey-using-event.html

--
and the video (Thanks to Mike for handling the video duties!)
mms://passmedia.sqlpass.org/share/dba/MasterofAllISurvey_03272013.wmv

[PASS] I'm presenting March 27, 2013!

Howdy, all!  I am pleased to be presenting on Event Notifications this week.  It's a soup-to-nuts on tracking code changes and more, across your environment.  I honestly think most people reading this would find it handy.  We've found it essential in our environment.

More information at dba.sqlpass.org

Date: March 27, 2013 

Noon Eastern time – www.timeanddate.com/worldclock/fixedtime.html

Topic: Master of All I Survey - Tracking Code Changes Across Dozens of Servers Seamlessly and Automatically – sponsored by Quest Software

Presenter: Michael Bourgon

Abstract:

Everyone's been bitten by it - rogue changes made on servers, be it SPs, index rebuilds, or even configuration changes. What if you knew? What if you could look historically across servers and track down what changes were made when, by who, and from where? It exists, it's already built into MSSQL, it's easy to implement and it's been there waiting for you for 7 years. Come learn what Event Notification is and why you'll want it in your environment.

Bio:
Michael Bourgon is the Senior Production DBA for Emdeon Pharmacy Services.  He started work with Sybase on RS/6000 over 15 years ago, and has been meddling with some form of the SQL Server codebase ever since.  He's always looking for a better way to be a Lazy DBA - spending far too many hours automating processes in order to accomplish that.  In his spare time he listens to weird music, reads Science Fiction, and tries to ensure his daughter grows up a nerd, too. 

 Door prize: There will be a raffle for a $50 Amazon gift certificate.  You do not need to register for the meeting, but if you want to enter the raffle, you must register at www.livemeeting.com/lrs/8000181573/Registration.aspx no later than 5:00 PM EDT on March 26th.

Live Meeting Link: www.livemeeting.com/cc/8000181573/join

[SSMS] Fun fact - Emacs!

Was going through my freshly formatted install, and put 2012 SSMS on it.  Then went digging to change the comment/uncomment shortcuts, and (of course) change Ctrl+O to it's old Query Analyzer function: New Database Engine Query (new window, choosing database connection at that time).  I honestly don't know how people DON'T have this mapped.  Do you seriously click each time, or go File->New->Database Engine Query?

Lo and behold...
Edit.EmacsWordCapitalize
Edit.EmacsSetMark
Edit.EmacsScrollLineTop

and so on, and so forth.  Forty-three commands in total.  Impressive, if only because Emacs is the quintessential best/worst about Linux.

I'm both amused and annoyed by this:
1) You went to all the effort to emulate Emacs, but couldn't bother setting up a base shortcut for Database Engine Query, which you use all the frickin' time?
2) All the other glaring issues with SSMS, but you had time to emulate Emacs?
and of course, the most Egregious:
3) No VI mode!

[RDP] Finding and logging people off from command line

Thanks to Tony for this!

Obviously, you can use the Terminal Services Manager (now the Remote Desktop Services Manager) to find people and log them off.  This is a command line way to do the same thing.  Easier, possibly, and doesn't (to my knowledge) require the Manager pack be downloaded & installed.

To list users:
query session /server:yourservernamehere

To kick off users:
RESET SESSION 1 /SERVER:yourservernamehere
or
rwinsta /SERVER:yourservernamehere 1

(replace 1 with whatever the ID is in the results from the QUERY)

[Russian] Olga's Potato Perushky / Perushki / Piroshki / Piroshki

*Phew*  For a minute there, I thought this was the last of the Great Olga Recipes (I pronounce it that way, to my friends).  I still have a couple left.

It's a hard call as to which of Baba's recipes are my favorite.  Which, honestly, is why I've been working on this for the past 5 years.  She's 95 now; I want to make sure these recipes are preserved for me, my family, and the rest of my family (shout-out to the cousins!).  We're extraordinarily lucky she's still around for my daughter.  And I'm extraordinarily lucky that I figured out that I needed to learn these recipes.

Anyhow - this is about as fine a fried potato food as it's possible to get.  Yes, it takes some time to make.  Holy heck, though, it's worth the time and effort.

Cook time: about an hour:
Prep time: 3-4 hours
0: Read completely before doing.  Just because the dough is "A" doesn't mean that's the first thing to do.  In fact, here's my order:
1) Cut & fry onions
2) Make dough, let rise
3) Cut and boil potatoes
4) assemble potato & onion mixture
5) cut/assemble
6) fry


A: The Dough
6 cups sifted Gold Medal All-Purpose flour.  Right around 2 pounds.
1/2 stick butter room temperature or melted
3 tbl Mazola oil
1.5 tsp salt
1.5 packages Active Dry yeast
1.5 cups warm milk ("little warm") - since it's for proofing, 100-110 F.
2 tbl sugar
3 Jumbo Eggs (4 Extra Large)

• Proof yeast: once your milk is up to temp, add yeast and stir well so that it's all wet.  Let sit 5-10 minutes (honestly, while you get everything else lined up is fine).
• Beat eggs with sugar and salt. Whisk or fork will work.
• Now mix all ingredients together in a large bowl until it comes together.  At this point, it'll probably be a bit wet. 
• Knead one minute.  As you do this, it will firm up.  It won't be tight, it won't be soft, and it WON'T be sticky.  My hands come out of this perfectly dry.  Texture-wise, a little plasticine.
• Put back in bowl, cover with a slightly-damp tea towel, and stick in the oven (which doesn't get turned on) with the light on inside.  This provides enough heat, the towel enough moisture.  Let rise until double

B: The filling

5-6 medium russet potatoes

3-4 medium size yellow onions. (Personally, I use 3-4 Texas 1015 onions - large and sweet).  

1/2 stick butter or 3 tbl Mazola.

1.5 teaspoons salt (for onions)

1.5 teaspoons salt (for potatoes)

• Chop onions fine and add salt.  I'd say a plain chop; cut the stem end off, cut the onion in half, straight down from pole to pole, remove peel, cut each half in 1/4ths from the root end (leaving connected), then slice perpendicular to those cuts, about 1/4" thick.  Here's a page, hopefully it stays up for a while: http://localfoods.about.com/od/preparationtips/ss/choponion_6.htm 
• Simmer onions in 1/2 stick butter, medium heat, until they turn golden brown or mahogany in color.  This can take a few hours on medium (4-5 on my stove).  You can get it down to an hour or so if you turn up the heat to 8 or so until they fry for a bit and the water comes out, then turn down to medium/medium-low.  This is, honestly, the part that takes longest.  Alton Brown does an episode about caramelizing onions for French Onion Soup; his have nothing on Olga's.  I also found this page which seems close.  http://allrecipes.com/howto/caramelizing-onions-step-by-step/ 
• Dice potatoes into 3/4" to 1" cubes. Boil in a large pot with 1.5 tsp salt.  Time is probably 20 minutes or until tender to the form; you will be mashing them.
• Once everything's cooked, mix together: onions with all the oil/butter, potatoes, and a healthy several teaspoons of black pepper.  
• Mash.  You want, honestly, like mashed potatoes. 
• Taste at this point, add salt and more pepper if necessary.

C: The assembling

• Take a hunk of dough out and roll into a 2" tube.  Cut into 1" pieces.  
• At this point you have two options.  One is to flatten them out into rounds, however thin you can get them (my notes say 1/8" thick, but that seems really thick).
• Alternatively, drop the 1" wide, 2" long piece of dough into a pasta maker.  My pasta maker goes from 1-7.  I use 6: 7 is too thick, and 5 winds up leaving holes in the dough.
• Once stretched out sufficiently (or as SOON as it comes out of the pasta maker - it shrinks!) put 1 tsp potato mixture (or 1 heaping table spoon, yes, the smallish spoon you use to eat with) in the center.  Alternatively, use a 60 or 70 disher.  I use the 60, but it's a little harder to seal them.  
• Fold the circle in half and crimp the edges.  Make sure the crimp is good.  The potato mixture should be a ball that fills up the center of your half-circle. That's fine.  Now place this half-circle perpendicular to the table, curved side down, and push gently on the table so that it flattens in the opposite direction.  Repeat 100 times or so.

C1: Alternative assembly directions

This is from my Meat Perushky, which is very similar.  Since I'm using the same filling amount, it should work just the same:

For each perushky, pull off 24-26 grams of dough. 
Two ways to make:
1) stretch out by hand
2) roll and cut
My grandmother stretches it out to a 3.25" circle, thinner at the edges. This is a little heavy on the bread for my taste, so I roll out with a rolling pin until about 3.5 - 4.0 inches around, then use the top of a 3.25" restaurant tumbler (do a Google image search - about 2.75 inches at the bottom and straight sides) to cut out a circle. When I weight it, it comes out to about 18g.

Now take this circle of dough, stretch it out a little, and immediately drop in a dollop of meat potato.  I use a #70 disher - Baba uses a heaping tablespoon. Put this in the middle, then bring the sides together, forming a half-circle. Pinch down repeatedly to seal, squeezing hard with your fingers. Now flatten - put perpendicular to table, round side facing down, and push. It'll wind up as a small round object. Check for seam splits and close them.

D: The Frying:
1 quart Wesson (probably less)

• In a frying pan, heat 1-1.5 inches of oil to about 300 Fahrenheit, or until you can toss in a piece of dough and have it "fry gently".  
• Add 6-10 perushky (depending on your pan), SEAM SIDE DOWN, and allow to fry for a minute or so, until the bottom is Golden Brown & Delicious (GBD).  Flip over.  Once cooked on both sides, pull out and add more.  We've found that adding one after you remove one is the best way to avoid over-heating the oil.  

Serve: with Borscht.  Or on a plate. Or as they come out of the frying pan.  Up to you.  They don't keep readily - stick em in a big container and they all go limp on the outside.  Still eminently edible, but a pale shadow of coming out of the fryer.  Oven will perk them up decently, though .

[Powershell] Running sp_blitz against multiple servers in parallel, saved to table

(update 2013/06/13: I've superceded this with a new framework that will run almost any piece of code, powershell or sql, saving the results to a table, and running with multiple threads. Please give it a look!
http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html )

(update 2013/02/09: for some reason, running with only 6 threads doesn't bring back all the servers.  Using 20, however, does.  Odd, and not expected, and obviously I need a better fix)

(update 2013/02/08: if results from sp_blitz aren't saved for a particular server, run the invoke-sqlcmd2 command for sp_blitz against just that server, manually. For sp_blitz we've found a couple bugs and are sending them to Brent Ozar to fix.  Right now, if there are any errors thrown, even if there are results, then invoke-sqlcmd2 doesn't work right.)


So, I recently found out WHY sp_blitz is awesome, and why "30 second takeover" is a horrid subtitle for it. (I always thought that it meant "break into a SQL server in 30 seconds", so I never went - NOPE).

Anyhow, sp_blitz itself is handy as heck - gets a list of common problems that a particular server has, when it gets handed to you and you basically "takeover" and become its DBA in a matter of 30 seconds.

So, the next thing I wanted to do was save the results to a centralized table.  Actually raced a coworker to do it - he did one in SSIS, naturally I wanted it in Powershell.  Later we plan to do "server thunderdome" and see whose runs fastest.

NOTE: this is almost exactly the same code as in my earlier post http://thebakingdba.blogspot.com/2012/12/powershell-run-query-against-multiple.html . The biggest change is the addition of a ServerName field (which was a lot harder to figure out than you'd think, so many thanks to Chad Miller for the fix, and Graimer for a shorter version).  Given it wasn't a simple thing (for me) to add, and what I'm doing with it, I figure it'd be worth another post.

Future changes: I think I'm going to modify it so it looks at a folder, running each script from that folder and saving it to a table named for that script.  Also getting it to return errors; between invoke-sqlcmd2 and the scriptblock, it basically eats any errors and you never see them.  Need to get that working better.

So, first, create a table to hold the data:

CREATE TABLE [dbo].[BlitzResults](
[Priority] [tinyint] NULL,
[FindingsGroup] [varchar](50) NULL,
[Finding] [varchar](200) NULL,
[DatabaseName] [varchar](50) NULL,
[URL] [varchar](200) NULL,
[Details] [nvarchar](4000) NULL,
[QueryPlan] [xml] NULL,
[QueryPlanFiltered] [nvarchar](max) NULL,
[CheckID] [int] NULL,
[ServerName] [varchar](50) NULL
)



Make sure to save the invoke-sqlcmd.ps1 and the write-datatable.ps1 scripts to a folder (c:\sql_tools, change it however you want).

Now create this script:

#Run SQL query against multiple servers in parallel, saving results to a central table
# 1.00 2012/12/13 mdb / TBD.  Initial release
# 1.10 2012/12/13 mdb / TBD.  Adding throttling scheduler
# 1.11 2012/12/13 mdb adding comment on WHY you want the wait-job
# 1.20 2012/02/07 mdb adding a ServerName field to the datatable. Now we can do sp_blitz w/o modifying it!
# Code cribbed and lessons learned courtesy of: Hey Scripting Guy,
# Aaron Bertrand (the bit with $args[0], his "sad panda face" post)
# Kendra Little
# Throttling scheduler is from "start-automating" Stackoverflow.
# Additional Servername field from Chad Miller and Graimer (stackoverflow)
# Errors are mine, not theirs.
# Please keep this header and let me know how it works. thebakingdba.blogspot.com
# Prerequisite: invoke-sqlcmd2 and write-datatable, courtesy of Hey Scripting Guy & Chad Miller
# Long header courtesy of .... um, me.
clear
. C:\sql_tools\invoke-sqlcmd2.ps1;
#get list of servers that meet our criteria; our code will run against these
$serverlist = invoke-sqlcmd2 -serverinstance "ftw-test-08" `
-query "SELECT server FROM yourlistofservershere WHERE active = 1 and version >=9 order by server"
foreach ($server in $serverlist)
{
    #job running code; allows us to multithread.  -le 8 means 8 jobs run at once. As one drains, the next picks up
    $jobsrunning = @(Get-Job | Where-Object { $_.JobStateInfo.State -eq 'Running' })
    if ($jobsrunning.Count -le 8)
    {
        start-job -argumentlist $server.server -scriptblock `
        {
        #a scriptblock is a wholly separate 'environment'; have to reinvoke functions and reintroduce variables
        . C:\sql_tools\invoke-sqlcmd2.ps1;
        . C:\sql_tools\write-datatable.ps1;
        $server2 = $args[0]

        $quer = invoke-sqlcmd2 -serverinstance $server2 -database "master" `
            -query "exec master.dbo.sp_blitz" -As 'DataTable'

        $quer.Columns.Add("ServerName")

        $quer | %{$_.ServerName = $server2}

        Write-DataTable -ServerInstance "repositoryserver" -Database "dba_stuff" -TableName "BlitzResults" -Data $quer
        }
    }
    else
    {
        $jobsrunning | Wait-Job -Any  #as soon as any job finishes, push the next up
    }
}

#Now that we're finished, cleanup and get rid of any errant jobs  
get-job | wait-job -timeout 120         #wait 120 seconds or until all jobs are done, whichever comes first

get-job|Remove-Job -force              #cleanup and remove the jobs