Voice of the DBA

Author: way0utwest

Data Portability

It seems that there is a never ending list of formats that data professionals need to deal with. As I read through posts and articles talking about Integration Services, I’m struck by how many unique solutions people come up with to move data in and out of Excel. They know how to handle multiple worksheets, headers, practically anything that people put into Excel. If there’s a pattern in there, chances are some SSIS worker has a solution.

This week I saw that Facebook had announced a data portability initiative, giving users the ability to download all their Facebook data into one file. The file can include images, movies, posts, events, etc. and gives you a way to not only back up your data, but perhaps use it for other applications. I’m sure it’s only a matter of time before we have an SSIS package that can load up your information into a SQL Server database. I suspect someone will do it just for the challenge, and not because they have a practical use for it.

Facebook seems to have been under attack constantly from groups that are concerned about the privacy policies and features of a company that has so much data about individuals. However this new feature seems to show that the company isn’t looking to lock you and your data into their own system. You can now backup your data and shut down your account if you with to move on to another service.

Recognizing that data is important to users and allowing some level of portability is a feature that I wish more companies would embrace. I’d love to be able to easily move my medical records, my financial investment history, or even my credit data to my own systems as a backup. Or even as a way to document the current state and perhaps ensure that my data is correct.

Steve Jones

October 11, 2010
Return Values from XP_CMDSHELL
Let’s be clear: I don’t recommend the use of xp_cmdshell as a general tool. It ought to be used when you have no alternatives, and you should carefully control access and what this can do. Opening a shell from SQL Server can be dangerous for your server.

That being said, I do think there are places that xp_cmdshell works great and if you need to use it, how do you tell if the commands you executed were successful?

For those of you that have worked in command lines, or remember DOS, there is a thing called an Errorlevel, that is returned by programs when they exit. For many of us that used to program in DOS, we always checked ERRORLEVEL when exiting a program, and we made sure that our program always returned a 0 if it exited without an error. That’s a standard that has been in place as long as I can remember working with computers.

How can we use this? If I execute this in a command window:

I get an instance of Notepad on my screen. When the CD.exe application completes, it sets the errorlevel to 0. Technically it returns a 0 to cmd.exe, which sets the errorlevel to that value.

What about this:

In this case, nothing happens. However if I switch the command to:

Then I get notepad again.

How can we use this in SQL Server? When xp_cmdshell exits, it returns the errorlevel from it’s shell. Let’s test it. First, enable xp_cmdshell
```
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
```
Now execute
```
DECLARE @i INT
EXEC @i = xp_cmdshell 'dir'
SELECT @i
```
This returns the current directory (\Windows\System32 in this case) and a 0.

Now if we change to our other example:
```
DECLARE @i INT
EXEC @i = xp_cmdshell 'cd tim'
SELECT @i
```
We get these results

Since that’s not a valid path on my system, the DOS error is returned as a result, and the errorlevel is returned in the variable assignment.

You can use this to test and see if your commands execute before you do something, like try to parse a directory listing that isn’t there.
October 11, 2010
Differences

Are women the same or different as men? They’re different, without a doubt, and I have very much enjoyed reading the thoughts of a few women that I greatly respect this week in our guest editorials. Each of these ladies has eloquently expressed their views on women in the workplace and today I am looking to wrap up this week of focusing on women in technology with my own thoughts on the topic.

Women are different than men. I think it’s wonderful and I am thankful that we are different. The synergies that make this an amazing world come from the variety of people in it. It doesn’t matter if they’re men or women, having white or brown skin, speaking English or Chinese, or whether they like chocolate or cheese. It’s the infinite variety of people that make this a better world.

We have to learn to embrace that. Despite our prejudices, we have to extend that tolerance to see the advantages of working alongside that different than yourself. As pointed out in one of Jen McCown’s references, studies have shown that diverse teams of men and women perform better. They are more productive, and they develop ideas that are more often used by others.

Cultures around the world treat women differently than men. We view them differently and we often separate them out. Look at sports, where so many of us bond in our youth. Men are often physically superior, and participate in different, often more violent sports. That seems to have carried over into business at times. We mistake physical superiority at a game implying that a person better able to handle business. I don’t think that’s true, and I think it takes time, discussion, and awareness to combat that.

Then there’s sex. No one touched on it this week, but sex, love, emotions evoked by others are a part of our workplace. They shouldn’t be, but how can you get away from it? We can’t help being attracted to whoever we are attracted to, and we often don’t handle that well. It doesn’t matter if our feelings are reciprocated or rebuffed; we seem to struggle with them. Even if we don’t act on it, the locker room jokes and attitudes that many men display can carry over into their interactions with women. I have no idea how to get past this, and I think it’s something that we have to somehow learn to live with, and try not to let it interfere with our professional attitudes.

We don’t hire someone just based on their skill at a job. If we did, we could just devise some test and hire the person with the best score, or the score above some level. We are hiring someone to do more than just write code; we’re hiring them to work with others, interact with them, and get along. The means that we will subjectively make decisions about who we’d like to work with.

Which means getting past our prejudices. Understanding our human frailty in judging people and embracing our diversity.

Steve Jones

October 8, 2010
Career Warfare – Book Review

I picked up Career Warfare on the recommendation of a friend that had seen the Modern Resume presentation. I grabbed it as an e-book and have been reading it for the last few months, at a fairly slow pace (for me).

I often read a book in a week, but in the case of this one, I would read part of a chapter, and spend a day or so thinking about it. Digesting the information, and seeing how it might be applicable to my career, and my brand.

The author, David D’Alessandro rose to CEO of John Hancock Insurance, and you have to keep that in mind. The book is written as a tool for how you might advance your career in corporate America, and it includes lessons for upper management, many of which don’t apply to most of us. Items like dealing with the press aren’t something the average person needs to think about.

However there is some great advice in there about how you should grow your career, and the impact that you have on your career based on your actions.

I highly recommend it, and there are a couple of great pieces of advice in there. Most importantly, you are always building your brand. Slowly, surely, but every day you go to work, or interact with people professionally, you are building your brand. I like that he stresses honesty and integrity as well.

There are some things I don’t necessarily agree with, like not bringing your spouse to social events, or not drinking at all, but if you are attempting to rise to the C-level ranks, perhaps that’s good advice.

October 8, 2010