Seize the Moment

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: April 29, 2011

Today I had a bit of regret slap me in the face.  That face slap came from participation in a SQL Quiz on twitter that was hosted by Paul Randal (Blog | Twitter).  The questions being thrown out there were deep technical internals type of questions.  These weren’t necessarily the type of questions that you would see in an interview and were for fun.

I say it was a bit of a face slap because I had an opportunity to attend an Internals training session presented by SQLSkills in Dallas but was unable to attend.  It made me wonder how much more I would have been able to answer had I actually attended the course.  If you have an opportunity to attend such an event – DO IT!

From the set of questions today, I learned quite a bit.  The knowledge and wealth of information that you can gain by attending one of these events has got to be substantially more than what is presented in the measly ten questions posed in these Pop Quizzes that Paul has conducted.

Now I need to find my way into the Bellevue course.

Physical Row Location

Categories: News, Professional, SSC
Comments: No Comments
Published on: April 29, 2011

SQL Server 2008 has presented us a couple of options to aid in becoming better DBA’s.  You can see this evidenced in many ways in the product.  A couple of the things that make me think this is the case boils down to two functions that are new in SQL 2008.  I learned about these while trying to learn how to do something else.  It just so happens that these functions could possibly help me in the other process (I’ll write more about that later when I have finished it).

These new functions are: sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker.  The two functions are really very similar.  The first of the two does as the name implies and formats the physical location, while the second of the two provides a table output of the location.  If you look at the sp_helptext of both, you can see that they only have minor differences.

[codesyntax lang=”tsql” title=”physlocformat”]



[codesyntax lang=”tsql” title=”physloccracker”]


When you look at these two functions, you can easily say that they are similar right up until the end where they diverge in functionality.  The first casts the data into the “formatted” version, while the cracker simply outputs to a table.

Use of these functions is also quite easy.

[codesyntax lang=”tsql” title=”usage”]


These functions can prove to be very helpful in your troubleshooting or dives into Internals.  Check them out and enjoy.

SQLSat 83 – Johannesburg

Comments: 1 Comment
Published on: April 28, 2011

Awesome news.  I submitted a presentation for SQLSat 83 in Johannesburg South Africa for May 7, 2011.  I saw that they were opening up the presentation schedule a little bit to allow for remote presenters and I decided to give it a try.

I got an email notification yesterday that my presentation has been accepted and I will be presenting – woohoo.  If you are interested, you can see the presentation schedule here.  My presentation will be similar to the last time I presented at SQLSaturday in Salt Lake City – but with some refinements and hopefully a better overall presentation.  I will be sharing some of the tools/scripts I use to help learn more about an environment as I inherit a database.

Here’s the fun part – I will be presenting from my home in Utah which is 8 hours behind Johannesburg.  At the time of my presentation (12:15 PM there) it will be 4:15AM local for me.  That should make for an interesting presentation if I am groggy. 😉

If you are in the area at the time of the event (in Johannesburg), I highly recommend you make it out to some sessions.  From looking at the lineup, there appears to be some really good stuff to be presented.  As we get closer to May 7th, I will post more information.  For now, I need to go clean up my slidedeck and get it submitted.

Parting note:  I am glad I was selected for this SQLSaturday.  If you read my SQLPeople interview (here), you may recall that I lived in the area for a couple of years.  I only passed through Johannesburg when returning home but have always wanted to go back and visit.  It has been quite some time and I have yet to make it back.  At least now I can do it virtually!

A SQL Experiment – Mentoring

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: April 26, 2011

News Flash

The SQL Community is about to get stronger.  Why?  There is a great new initiative that was recently launched.  Steve Jones and Andy Warren are teaming up to conduct this initiative.  As they aptly named it, the new initiative is called “The Mentoring Experiment.”

How many times have you wished you had somebody to show you the ropes?  Have you ever started a new job and been thrown to the wolves to try and figure things out on your own?  Many of us wish we had a mentor at some stage or another in our career – even if it is somebody more experienced with the current systems or applications in your current environment.

What is  a mentor?

A mentor is defined as 1) a wise and trusted counselor or teacher, or 2) an influential senior sponsor or supporter.  A mentor might also be referred to as a guide, counselor, master, or adviser.

A mentor is a person, in other words, that can be trusted to give you good advice and information.  This is a person to whom you should be able to bounce questions off of in order to learn more.  This person is somebody with more experience and/or knowledge, in the given domain, than you currently have.  And a mentor is a person who is willing to give a little service or volunteers to give of him/herself for the betterment of another individual.

What is the Goal?

Much the same as a mentor should have a Goal in regards to the padawan to help that person get better, Andy and Steve have a goal of improving mentoring in the SQL Server community.  There is a plan in place for this experiment.  Furthermore, there are multiple stages to this experiment.  The first stage is focused on a smaller group of mentors/mentees and the relationship that evolves between the two.

From the information gathered, they hope to be able to provide better information to mentors to determine if maybe there is something that a mentor is missing and needs to learn as well.

You can become involved in this project by submitting a short application.  Not everybody will be selected, but you can’t be selected if you don’t try.  I hope there will be plenty of community support for this experiment.  I look to provide support where I can.

SQLPeople Interview

Comments: No Comments
Published on: April 25, 2011

Have you heard about this new project out there called SQLPeople?  It is a new (relatively) project that is the brainchild of Andy Leonard.  Andy is trying to help the community to get to know each other better than they already do.

Part of this initiative is to conduct interviews and post them to on a regular basis.  Well, Friday April 22nd was the day that my interview was published.  It’s a standard set of questions to be answered by each person on the site.  You can read about me and my answers here.

T-SQL Tuesday #17 – APPLY Knowledge

Comments: 3 Comments
Published on: April 12, 2011

We have another opportunity to write as a part of TSQL Tuesday today.  This month Matt Velic (Blog | Twitter).  Matt has proposed a challenge that was derived from a comment on twitter.  The challenge this month is to write about “Apply.”

Admins Apply Within

As an administrator of databases, do you use Apply?  Not only can Apply be used to help return result sets as a part of your application, it can come in quite handy from an administration point of view too.  It can be used to help retrieve the text of currently executing code or code stored in cache.  Apply can be used to help retrieve index information and it can come in quite handy when performing a security audit.

Some Back Story

Just a few days ago, I posted some scripts to help find role membership of logins at the server level.  I wasn’t satisfied with the query and decided to rewrite it as a part of my entry for this months blog party.  You can read up on that script here.  My dissatisfaction with the query was that it felt klugy.  I wanted to write the query with something other than the Union and to present fewer rows to user.  So, I took another look at the query and purpose for the query and decided to use a combination of Apply and Pivot.

My new take on the script is simple, I will create a result set that will show a 1 for each server role for which a login is a member.  Each login will only have 1 row, opposed to the row per server role membership from the prior query.

The Script

In this example, the bulk of the heavy lifting is done through the pivot.  You will see that the Apply only seems to play a minor role in this script.  It is an important role nonetheless.  All logins (unless you have altered the public role) are also a member of the public role.  That membership is not displayed through the views I have used in this query.  Due to that handling of the public role, and to ensure that the membership in that role is not forgotten, it must be accounted for in the query.

This query will add a new column for the public role to those columns already presented via the pivot.  In this column we will show that every login returned via the query is a member of the public role.  But how is that done?  Using the apply, in really basic terms, acts like a join to this subquery that I use for the public column.  I am taking the value of 1 for column public, and applying that value to all of the results from the Pivot portion of the query.  I am using the Cross version and there is one more version – called Outer.  In my testing, both the Outer and the Cross Apply will return the same results for this particular query.

Some common uses for Apply are in conjunction with TVFs.  Should you use the Apply with a function where the “Join” criteria would be more tightly defined, then the use of Cross V. Outer will produce different results.  Just as with an Outer Join, the Outer would return results for all records in the “outer” or “left” part of the join whether there was a matching record in the TVF or not.  In the same sort of setup, the Cross Apply would only return records that had a match in the TVF.


Again, this is an oversimplification of the Apply statement.  My main goal was to present a use for Apply.  Apply can be a useful tool in the hands of the database administrator.  Examine it and learn what other potential it may have for some of your administration needs.

This is just one piece of the puzzle when performing a security audit.  Getting this information quickly when requested for an audit can be very helpful.  The output format is quite simple and very conducive for many auditors and management.  A simple output report for the Server Roles in your environment is merely one example of the usefulness of Apply.

SSIS Job Ownership

Comments: 2 Comments
Published on: April 11, 2011

I was strolling along one day when I saw somebody asking how to find out who owns a maintenance plan.  That evolved into finding out who owns the the job associated with the maintenance plan.  All of this in SQL 2005 and SQL 2008.

Well, we were stumped for a bit trying to figure the link between the job tables in the msdb database and the ssis table in the same database.  Linking the two together is not very obvious and we struggled with it for a bit.  After some research and trying this that and the other, I was able to come up with the below script.

[codesyntax lang=”tsql”]


This script is set to work out of the gate with SQL 2008.  Should you want it to work with SQL 2005 the change is simple.  Change the sysssispackages table to sysdtspackages90.  As you can see, the query joins the SSIS table to the jobsteps table with a pretty nasty string extraction.  There are other ways of extracting this information (I’m sure of it).  This works quite well for what it is intended.

Using this script, you can find out the jobowner, the packageowner, and the packagetype.  This is pretty good information to have on hand if you have several ssis packages that are stored in msdb and are run from a job.  One thing this script does not yet handle is if the SSIS file is stored on the file system.  Note that I only coded it so far to work with files stored in SQL.  When looking in the jobsteps table, you can tell the difference quickly by seeing that those stored in msdb have a /SQL at the beginning of the command string.  Those in the filesystem have a /FILE en lieu of that /SQL.

In a future revision I will work on parsing the package name out of that string that represents those stored in the file system.  And despite that nasty join, this runs quickly on my systems.  I am open to suggestions or other solutions that can provide this kind of insight.

S3OLV April Change

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: April 7, 2011

Earlier this week I announced the meeting for the Las Vegas User Group.  I blasted some of you with emails – along with people in our mailing list.  Those plans are now changed.  No longer will the group be meeting on Thursday April 14th.  Instead we will be having our meet and greet on April 12th.

This meeting change came about at the request of the Azure group at Microsoft.  They will be in town and they wanted to have a little event with our group and the .Net group in Las Vegas.  So, here are the updated meeting details for the S3OLV April 2011 Meeting.

The Microsoft SQL Azure team will be in town on Tuesday, April 12th and would like to present toS3OLV members on SQL Azure, followed by a private social event at the Eye Candy Lounge.

The evening will include an overview of SQL Azure, product demos, usage scenarios (including BI), free Azure trial passes for attendees, and a social gathering afterwards at the Mandalay Bay with free drinks and appetizers.  Come meet the team, learn about SQL Azure, and socialize, network and have fun at the private social gathering following the presentation. There will be plenty of free giveaways, a drawing for an XBOX Kinect, drinks, appetizers, and more!

Event Details:

Where: Mandalay Bay, room “Palm A”

When: 6:30-8pm (overview and demos), social gathering at Mandalay Bay “Eye Candy” Lounge immediately following (private party, must have wristband to attend)

SQL Azure is a relational database built on SQL Server technology available as a service in the cloud.  For more details, please visit

Since this event will be held in Las Vegas, it should go without saying that the meeting times are in PST.

Updated invitations were sent out to locals on the mailing list.  Also, you should have received a cancellation notice if you received the original e-vite.

We hope to see many of you there.

S3OLV April 2011

Comments: No Comments
Published on: April 5, 2011

Are you ready to learn again?  The Las Vegas SQL Users Group is ready to have our April meeting.  The meeting is to be held April 14, 2011 at 6:30 PST.

This month we have a BI presentation lined up and some top notch talent to present to us.  Erika Bakse will be teaching some excellent tips and tricks revolving around MDX and SSRS.

Reporting Services is a powerful tool that can make designing reports a snap…most of the time. But every once in a while you have to format a report very specifically, and the data just isn’t available in the form you need it to do that.  Enter Clever Queries!  Learn about how to use named sets, dummy members, and other MDX tricks to craft your data query in a way that allows you to conquer the trickiest report layouts.

This month, Erika will be presenting in person at the Learning Center.  But don’t be dismayed if you cannot attend in person.  We will be broadcasting the event via Livemeeting once again.

To access the meeting via livemeeting, just follow these steps:

To join the meeting? Follow these steps:

  1. Copy this address and paste it into your web browser:
  2. Copy and paste the required information:
    Meeting ID: F598S6

We hope to see you there (either in person or via livemeeting).

SQL Server Role Membership

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: April 5, 2011

How well do you know the security in your SQL instances?  Do you know who has sysadmin level permissions?  SQL Server provides a few methods for you to find out who is a member of which roles at the server level.

For those that like to point and click, you can always navigate through the GUI (SSMS) to determine which users or groups have been granted access to the sysadmin fixed server role.  For those that want something a bit faster, you can use a script to return this information for you.  Just as with most things TSQL, there are numerous different ways of writing this script.  Here are some of those methods.

Verifying Server Role membership

[codesyntax lang=”tsql”]


With this script, I am querying the sys.server_role_members and sys.server_principals views.  For simplicity sake, I am also using the SUSER_NAME() function to derive the role name.  Note that I threw in a union all to get back the ‘Public’ group membership.  The public group is a special group that does not appear when querying the sys.server_role_members view – but everybody is a member.

An Alternative

[codesyntax lang=”tsql”]


This one is quite simple as well.  Note that I am not employing the use of the SUSER_NAME function but have used another join in its place.  I am also only interested in adding the public role at this time to SQL Users, Windows Users and groups that are not disabled.  That information in the where clause is optional and is present to demonstrate the ability to quickly pare down the results.

Another Option

This is really the easiest of the three queries.

[codesyntax lang=”tsql”]


I am still employing the union statement to populate the public role.  Notice the difference in the first half of the query though.  I am simply using the SUSER_NAME function for both principal_ids being retrieved from the server_role_members view.  This is a little easier to follow and write.  Performance considerations put this last query as the most efficient on my systems with the first query shared being a close second.

All of these will return your group memberships quickly and in a manner that is quickly understandable (names instead of numbers).  The use of a query such as these would be a stepping stone into auditing the permissions that are in place on your server.  It is also great to quickly validate who has sysadmin access and to use that to confirm that the account should have sysadmin access.

«page 1 of 2

April 2011
« Mar   May »

Welcome , today is Monday, February 17, 2020