Ryan Adams Blog

SQL, Active Directory, Scripting

Things have been too quiet here on the blog, but there’s a reason (a few actually).  This year has been nuts for me, but it’s all good stuff!  We just moved, and moving a family is not fun.  It was a long process from selling the old house to searching and finding a new one.  Like I said, the moving part is not fun but we couldn’t be happier now that we’re mostly settled in.  Unlike the old house, this neighborhood actually has a community and the house is right across the street from 1 of 4 neighborhood parks.  Now I can finally work on getting my oldest daughter’s training wheels off without getting run over by cars!

It’s also been a busy SQL community year for me.  I’m the President of the PASS Performance Virtual Chapter, serve on the board of directors for the North Texas SQL Server User Group, serve as a PASS Regional Mentor, and serve on the PASS 2013 Nomination Committee.  It’s a ton of work, but it’s rewarding to help others and I love every second of it!  Of course I have a day job that keeps me hopping as well.

One of the most exciting things to happen is all the speaking engagements I have been fortunate enough to do.  This is where a lot of my blogging time has gone…into creating new presentations.  There is nothing more fun and exciting than teaching someone something new or explaining things so they truly get it.  I was chosen to be a Technical Lead on SQLCruise this year (Tim Ford must have lost his mind).  It was an amazing experience to be able to dive deep with attendees after hours.  I am truly humbled to speak at these events among absolute SQL rock stars.  So where will I be?  Here we go…

If you’re going to be at any of these events, I hope you’ll come see me!  Expect the blog to start back up soon.  I’ve been writing posts, they are just in draft until I can put the finishing touches on them.

SQLCruise Technical LeadSummit2013 24HOP DevConnections SQLSat

Holy Schnikes!PASS Summit 2013

I am beyond excited to announce that I have been accepted to speak at the annual SQL PASS Summit 2013 in Charlotte, NC.  This will be my first time to speak at this event and I can’t wait!  The session I will be presenting is titled “How Active Directory Affects SQL Server”.

I have been working with SQL Server since the 2000 release and I have been working with Active Directory since it hit the shelf.  Prior to becoming a full time production DBA for Verizon, I was an AD guy and actually helped design and architect their worldwide Active Directory infrastructure.  I couldn’t be happier that this was the session that got picked from my submissions.  It’s a perfect melding of my two favorite technologies and I have a unique and rare perspective of both how they can work together and how they can work against each other.  For instance, have you enabled Instant File Initialization for your SQL Server instance?  Good!  Did you go back and make sure the setting was still there 90 minutes later?  If you haven’t then you better go double check it because Active Directory has a mechanism to not only change it, but persist that change.  If Active Directory was SQL Server’s kid then it just pulled a Ferris Bueller.

If you are planning to attend the conference make sure to add my session to your schedule.  I will explain the mechanism I alluded to in the above example along with all the other things good and bad that it can do, plus much more.  Here is the abstract:

If you have ever had a Kerberos or SSPI context error, then you won’t want to miss this session. SQL Server has a large surface area and Active Directory can influence a big part of it. I will discuss AD DNS configuration, Group Policy Objects, Kerberos (of course), and how all of them affect your SQL Server. By the end of the session you’ll have a check list of things to discuss with your domain administrator when you return to work.

Policy Based Management has 4 evaluation modes and if you are not already familiar with them you can go HERE to get more information.  Today I want to talk about the “On Change: Prevent” mode and why you don’t always see it as an option.

The options you see available are dependent on the facets used in your check condition.  In order for the “On Change: Prevent” option to be available your condition must check something that raises a DDL event.

Let’s use Auto Shrink as an example and start by verifying if it raises a DDL event or not. We’ll use the old DDL trigger method to see what happens.  Here’s the code to create the trigger, and it just does a simple PRINT statement back to the screen if it fires:

CREATE TRIGGER trg_CatchShrink
PRINT ‘Auto shrink was changed and fired the trigger’

Now that we have our trigger setup let’s use the below code to enable Auto Shrink and see if it fires.  Make sure to change the database name to your TEST database.

USE [master]

If you’re following along and running the code then you will have seen the text of the PRINT command in your output.  We have now verified that enabling Auto Shrink on a database does indeed raise a DDL event.  So now your homework is to go create a policy that checks to make sure Auto Shrink is not enabled.  Are you done, yet?  That’s okay I’ll just give you the answer.  If you create a policy that checks the Auto Shrink setting then you will see that the “On Change: Prevent” option is NOT available.

So why is that option not available when we have proven that changing the Auto Shrink setting does raise a DDL event?  It’s because you can only use facets where ALL properties of that facet raise a DDL event.  That’s right.  Every single property in a facet has to raise a DDL event for the “On Change: Prevent” option to be available.  Now don’t get too excited because you know Auto Shrink is in more than one facet and you think you can just use another facet.  Surprise!  None of those facets support “On Change: Prevent” either, but good for you for knowing it was in more than one place.

So which facets support the “On Change: Prevent” evaluation method?  The following script will tell you:

FROM [msdb].[dbo].[syspolicy_management_facets]
WHERE execution_mode & 1 = 1

Let’s not forget to clean up after ourselves and remove our trigger as well as the Auto Shrink setting we changed.

DROP TRIGGER trg_CatchShrink

I recently had to rename my laptop on which I have two SQL server instances.  I have a default instance and a named instance.  I actually have had the laptop renamed for awhile without any issues, but it’s not production and I don’t use it every day.  I first encountered the error while creating a schedule for a new SQL Agent job.  The error I received said that the server could not be found and provided the server name that it could not connect to.  I immediately noticed that it showed the previous machine name.  If you run into this error, then you need to update the sever name in sys.servers.  Here is how to make the update.

Default Instance

sp_dropserver 'PreviousName'
sp_addserver 'NewName', local

Named Instance

sp_dropserver 'PreviousNameInstanceName'
sp_addserver 'NewNameInstanceName', local

Now that sys.servers has been updated you need to restart SQL server for the changes to take effect.  Once SQL comes back up, you can verify success by running the following and ensuring the results match your new server name.


In early February I received an email from LinkedIn that I have one of the top 1% most viewed profiles for 2012.  That’s really cool and I had no idea!  In fact, I couldn’t remember the last time I had updated it.  This prompted a serious, “Dude you better get out there and do some updating!”  It’s more time consuming than you think for those of us perfectionist type people, which is why you are just now getting this post.  I’m not entirely happy with it, but it’s in much better shape now.

Feel free to go check it out HERE if you have some spare time, but if you want to keep up with me in real time then you should follow me on Twitter, HERE.

LinkedIn Top 1% most viewed profiles of 2012

LinkedIn Top 1% most viewed profiles of 2012

I blogged about this back in September of 2010, but technology changes so it’s time to revisit this again.  Now I’m not anything even close to a marketing expert, but I’ve been the Director of Logistics and Communications for the North Texas SQL Server User Group for the past 2 years (I just started a new 2 year term as the Director of Programs).  I’ve also been the Director of Marketing for the PASS Performance Virtual Chapter for the last year until becoming the President this year.  Since I started those positions, NTSSUG’s average has gone from around 50 to around 80 (in fact, we had 87 last month), and the virtual chapter went from 40 in January to 235 last month.  Now that’s what I call exciting.  I’ve helped put on a SQLRally, several SQLSaturdays, an all-day virtual training event, and currently serve as a PASS Regional Mentor for the South Central region.  So I’m not a marketing expert, but I have gained some experience with all my volunteer activities.

Here are the things that I have learned and work for both local user groups as well as virtual ones.  Remember that every group and area can be different and what works in one place might not in another, so your mileage may vary.

  • Email – Discussion Lists
    • I send 4 emails per month.  The first two I send 1 week prior to the meeting.  The second two I send the morning of the meeting.  This gives people enough notice to get it in their calendars and that last minute reminder for those who need it.
    • Why two?  I send one to the group discussion list and one via the PASS website.  If you’re not running a PASS user group then the latter does not apply.  This means I get the news out to anyone who finds my chapter via the PASS website and also the current members.  It sounds simple but I see some established groups that just send to their discussion list and not from the website.  They are completely missing all the new members that signup on the chapter site.
    • Why have a discussion list and not just send email to all members on the PASS chapter site?  The chapter emailer is just a way to get news out to those who have signed up on the site.  It does not allow for 2 way communication because members cannot reply back.  I suggest creating a discussion list where members can ask technical questions.  Google Groups is my personal choice here.  It’s easy, everything gets archived, and you can always go back and search the site for past threads.
    • If you don’t have an email registration on your site like the PASS chapters, then I suggest creating a second list just for meeting announcements.  This gives people the choice to reduce the noise of the discussion list and only get meeting and event announcements.
    • Be consistent.  For me, I live and die by my Outlook calendar and I sync it to all my devices.  Add reminders in your calendar to make sure these emails get out on time.  As soon as you get the speaker and meeting information, go ahead and pre-stage it in your drafts.
    • I want you to notice that I didn’t call it a distribution list, but a discussion list.  That’s your goal; to create and foster technical creativity.  If things get quiet then throw out a question to keep it lively.  Maybe find an unanswered question on a forum and pose the question to your discussion list with a link back to the forum thread.
  • LinkedIn
    • Create a group on LinkedIn.  This is a professional networking site, so its scope is already what you are wanting to target.
    • Every month you should create a discussion in your group.  Don’t just post a link to your site.  Make it easy and post all the details along with a link.
    • Once you create the discussion you should click the “like” button.  When your connections see the discussion they will also see that you liked it, and that peaks their interest.
    • Make it a manager’s choice.  The newest discussions are listed at the top which means if your group is even slightly active your meeting announcement gets pushed off the screen.  A manager’s choice discussion gets put in its own box at the top of the right hand column.  This ensures that it is always visible.  The box only holds one item, so make sure to remove the previous month.
    • My last suggestion is to click on the discussion after it’s posted and share it.  Once you are in the discussion you’ll see a “Share Discussion” box at the top of the right hand column.  This lets you advertise the discussion topic through LinkedIn, Twitter, Facebook, and Google+.  You will notice that the first option is LinkedIn and that seems redundant since you just posted it, but it’s not.  What you did was create a discussion in your group and nowhere else.  Clicking this link lets you advertise it on your activity feed, and also post it in your other groups.  Posting it to other groups that might have an interest can make it seen by a lot of people.
    • LinkedIn used to have an events function but it was removed in December of 2012.  It was a pain to create an event and then a discussion to advertise the event you just created, but it was cool to automatically see which of your connections were planning to attend.
  • Twitter
    • Create a Twitter account for your group.
    • Tweet about your meeting, especially the week of the meeting.
    • Use a hash tag specific to your group, so others can find it and follow along.
    • If it’s a PASS chapter, then also use the #sqlpass hash tag.
    • Be as inventive as you can within 140 characters.  Words like “FREE” and phrases like “$100 Amazon gift card” can go a long way.  It’s cheesy, but it works.
  • CommunityMegaphone.com
    • This is a surprisingly unknown marketing resource.  I constantly have chapter leaders telling me they didn’t know about this.  You can create a calendar event on this site for your meeting and Microsoft pulls community events from the site to put in their MSDN newsletter. That’s a huge technical audience!
    • Note that the site says it has to be done 3 weeks before the meeting.  There are a lot of chapters out there that have a hard time getting speakers, especially that far ahead of time.  Don’t let that stop you!  Put it in the calendar anyway with a link to your site for further information.
    • Make sure to put this in your personal calendar to create an event on this site every month.
  • Website
    • This one is obvious and that’s because it’s your group’s front door.  This is where new members are the most likely to find you and where current members know they can easily find meeting information.  The site gives your group a face and personality.
    • Make sure pertinent meeting information is always right up front and clear.  Don’t forget things like directions, and throwing in an interactive Google map is all too easy these days.
    • People need reminders, so why not make it easy and give them one.  Add an iCal event to your site that they can easily open and save to their personal calendar.
    • If you are using a PASS chapter website, then I highly recommend starting to use the new Events Module if you are not already.  Things like an iCal are already baked in for you.  It also automatically moves your events to an archive module that you can put on your archive page.
      • It’s not immediately obvious, but you can use HTML in all the fields while creating a new event to customize the look and formatting.
      • HTML customization is not limited to the creation of a new event, but is also available in the module itself.  It’s a template and you change what it shows and how it gets shown.
      • In the past you might have noticed that as soon as the event start time hit, the event would get moved to the archive page.  That’s really not cool if you are a virtual chapter and someone is looking for the LiveMeeting link any more than 1 second past the start time.  It also makes your home page look out of whack if you don’t have the following month already entered.  That was recently changed and I am now told this only happens 6 hours after the meeting.
      • If you think this new module is awesome and makes your life easier then I encourage you to send a simple, “Thank You” email to the folks that made it happen.  Thank Allen Kinsel who drove the chapter tools initiative while he was on the PASS board.  Thank Wesley Chang, Benny Su, and Jason Lau in IT at PASS HQ for developing and implementing it.  I’ll make it easy for you.  CLICK HERE, and just hit send with a simple already filled out email.  If you want to elaborate, feel free to tell them you want an RSS feed for the module.  We can automate all of the above once we get that….hint….hint

You’ll notice that I don’t have Facebook listed.  The reason is that I have only seen one group say that worked for them.  The main reason is that most professionals use Facebook for personal stuff and keep their professional life on LinkedIn.  This just goes to show that not every method works for every group or part of the country.  Go try it out and see if it works for you.  If it doesn’t work; just delete it.  Go try other things, and if you find one that really works then make sure to let me know.

I hope this post comes in handy and helps some folks out with growing their groups.  It takes time to see the benefits and it certainly takes time to do this every single month (I know, I did it for 2 chapters at the same time).  If your group grows as a result of these ideas then let me know.  Any time we have successes in the community I love to hear about it; it’s just encouraging.

This post is a live blog of the PASS Summit 2012 keynote from day 2 on 11/8/2012.  This post is intended to be a live note taking.

Douglas McDowell opens day 2 by giving an overview of the health of the PASS organization.  This year PASS has added an additional community evangelist, and better IT support.  PASS continues to be financially healthy.

Doug introduces the newly elected board members Wendy Pastrick, James Rowland-Jones, and Sri Sridharan.  We congratulate and thank them all for their commitment and sacrifice.  Doug also gives a huge thank you to the PASS HQ team.  They work tirelessly to put on PASS events.

Thomas LaRock takes the stage to talk about the way PASS has changed to recognize our volunteers.  They have added an Outstanding volunteer award and mention the runners up for the PASSion award.  The runners up were Jesus Gil and Amy Lewis.  Jen Stirrup is announced as the 2012 PASSion award winner.

Quentin Clark Microsoft Corporate Vice President is up next.  He’ll be talking on “The Data Lifecycle: Turning Data into Business Value”.

We are watching map reduce pulling data from Hadoop for analysis with PDW.  The idea here is to combine any data no matter where it comes from, whether it is on premise or not, or Hadoop, or SQL.  We are digging through the data now and eyes are glazed over or closing.

If you are watching this live and wondering why I’m not updating it’s because nothing eventful is happening.  Lots of people have glazed over eyes and some are leaving.

If you haven’t abondoned me yet…you should.  We are nearing the end with nothing exciting in view.  Twitter is buzzing with this being the most boring keynote ever.

This post is a live recap of the keynote at the PASS Summit 2012 conference.

Bill Graziano opens Summit 2012 with 3894 attendees.
Bill recognizes local and virtual chapter leaders as well as SQLSaturday event organizers.
The PASS BA conference is announced to be in Chicago in April.

Bill introduces Ted Kummert who will talking about “Accelerating insight on any data”.
Ted announces that SP1 for SQL 2012 is now available.

Ted announces Hekaton that is in memory query processing.
Columnstore indexes will now be updateable and cluster aware in the next version of SQL Server
Sean Bice is brought on stage to demo Hekaton. We are seeing 2000 queries a second get a 9x boost with Hekaton and once he puts the stored procedure in memory we see a 29x boost. There are no hardware or software changes in the comparison.

The next version of PDW will be available in the first half of next year.
Christian Kleinerman is brought on stage to demo the next version of PDW.
Ted gives Christian a hard time about making a second typing mistake in the demo and the crowd has a good laugh.

Polybase is announced to combine relational and non-relational data.

Amir Netz is brought on stage to show Excel 2013 as a complete end user self service BI tool with Powerview and PowerPivot now baked in.
Amir is very entertaining as he demos powerview in Excel 2013 using movie and entertainment data from around the world.
The crowd loves Amir. He has tons of energy is entertaining and hilarious. He uses data with movie history to show that Angelina Joline would make the most money with animated films where you can’t see her. The crowd applauds.

and we are wrapped up and headed off to learn all we can today.

I’m not a politically minded person and this is not the type of post I would normally write.  However, I’m deeply involved with the PASS organization (read chronic volunteer) and I have a vested interest in it’s success.  The reason I decided to write this is because I have very strong feelings about two of the candidates and the success they will bring to the organization.

Sri Sridharan (Blog|Twitter)

I know what you’re thinking, “You guys both work together on the board for the North Texas SQL Server User Group, so that figures as much.”  The fact that we help run a PASS user group with several others would never be reason enough for me to recommend someone on that premise alone.  What it does do, however, is allow me to know someone’s character and motives far better than reading their campaign platform online.  Sri is both a friend and colleage with impecable character.

Sri is an extremely dedicated person.  When he finds a passion for something he follows it through to completion with an eye for perfection.  Although he aims for perfection in everything he does, he understands compromise and that nothing is perfect.  It’s those goals and that balance that give him the edge on other leaders.  You can see this through his leadership of the North Texas SQL Server User Group, the three SQLSaturday events hosted within a 12 month period, and SQLRally 2012.

He has a drive and passion that is unparalleled.  He has the distinct ability to not only see the whole picture, but the potential of that picture in the future.  Many people with that vision tend to forget the small things along the way, but Sri also has the uncanny sense to see those things and work them into the overall vision.

There is no doubt in my mind that Sri would become a coveted asset to the PASS board and the entire SQL Community.  The above abilities combined with his diverse educational background make him an ideal fit.

Allen Kinsel (Blog|Twitter)

I have gotten to know Allen over the past 4 or 5 years and he is about as passionate as they come.  This man bleeds and sweats PASS.  Allen has served a two year term over the PASS Summit portfolio and I don’t think there is a question about how wildly popular and successful that event is.  You might think to have that success he must have had his head down and just focused on that one thing, but you would be wrong.  What really gives Allen that success is his ability to see the whole organization and how all the pieces play together.  Did you know that letting the top rated speakers from SQLRally Orlando get guaranteed speaking spots at last year’s Summit was his idea?  SQLRally was not his portfolio, but he saw how they could compliment each other to grow community speakers.

Right now he is on year one of the Chapter portfolio.  Ask any current or past board member and they will tell you it takes a year to get your feet wet on a new portfolio.  They will tell you it’s not until around the second year that you start to see the vision and how to enact good change.  Allen, however, has managed to make actionable changes already within the first year of having this portfolio.  He piloted an awesome program for Regional Mentors to travel to user groups to better understand their challenges and also give them the most requested thing for a chapter, speakers.

He has also been working tirelessly to enhance the http://www.sqlpass.org website for chapter leaders.  A few of his changes have been released already and if you are a chapter leader you know how useful they are.  He has a lot more new features planned and in the works and I’m hoping he gets to serve another term to finish out those visions.  I do marketing/communications for a local chapter and also a virtual chapter, and I know the time commitment in advertising those meetings and events.  It sounds like a quick copy/paste thing, but it’s not because every social media outlet has a different format. The tools Allen is working on will alleviate and even automate that.


Above all else, I would encourage everyone to vote.  If you choose wisely to vote then I urge you to consider Allen and Sri as strong contenders to help usher in a new era for PASS.

By now you’re wondering why I have only talked about two of the candidates when you get to vote for three.  I voted for a third (my wild card), but I felt strongly enough about these two people to make my vote and backing public.  That’s a big deal for me and not something I take lightly.

Happy Voting!

I ran across the following error in SQL Reporting Services 2008 R2 a few weeks ago:

The report execution has expired or cannot be found. [rsExecutionNotFound]

This report contains a very large dataset, so my first inclination was that there was some query tuning and indexing review that needed to be done.  The first thing I did was fire up SQL Server Profiler to verify the query and its parameters.  I kicked off the report and started watching Profiler.  The report cranked away forever, which was the original complaint.  Guess what I saw?  NOTHING!  This report sat there and cranked away and I never saw it query the source DB once.

Now we have a mystery.  I opened the report in BIDS to snoop around some more, and decided to preview it from there.  What did I get?  It ran just fine, although it did take way too long.  At least it actually rendered a report, though.

It’s time for some research. The only things I found on this error were to increase the default report timeout setting and to check the report security context.  I knew it wasn’t the timeout setting, because it rendered the report much faster than that in BIDS.  I could also run all the report queries in SSMS in way less than that amount of time.  I also double checked the security setup and context and nothing was out of whack.

Lucky for me I had another report out there that was almost identical and did not experience this issue.  There were two differences between them.  The first was a date parameter and the second was an ID parameter that was not contained in the second report.  The date parameter was just a default RS parameter and not coming from a query for default values.  The second ID parameter, however, had a query for its values and when the report executed these values were used in an “IN” clause.  It turns out that this parameter was the culprit.  The “IN” clause could not handle the amount of values that were being passed.

I talked with management about the report to review their specific requirements.  It turns out they did not need the option to select IDs, so I removed that parameter and BINGO!  That fixed the issue.

So if you get this error, check your report parameters for anything absurdly large or that might need some tuning.  I should also mention that errors in RS are notorious for not pointing you in the right direction, but there is a reason.  By default full errors are not output to the web to avoid exposing things that should not be publicly available.  You can change that setting, but that is a post for another day.

It drives me nuts not knowing the full reason for an issue or how to track it down faster next time.  Here is another tip to get to the bottom of this faster, that I learned along the way.  I never saw the report hit the DB in Profiler, but if I had run the report from BIDS and watched Profiler I would have seen the root cause of the overloaded “IN” clause right away.  If you have an RS error of any kind on your RS site, I advise you to run it through BIDS and watch Profiler to see the root error from the start.