Michael J. Swart

September 27, 2012

My Short Struggle With SQL Agent

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

You know I’ve always thought of SQL Agent as one of the “Agent Programs” from the movie the Matrix:

So what follows is a mini-lesson about the SQL Agent. I recently got into trouble when I created a SQL Agent Job step. The only thing this step needed to do was to move some text files from one network drive to another.

I just wanted to use the dos command:

move "\\server1\somedir\*.txt" "\\server2\somedir"

But if there were no files copied, the step failed because the return code was 1 instead of 0. It succeeded in copying all the files that were there (all zero of them), but apparently that’s unacceptable.

I then tried using robocopy. Robocopy is the “robust copy” command for Windows. It’s got all the bells and whistles… so I tried:

robocopy "\\server1\somedir\" "\\server2\somedir\" *.txt /mov

but the return code for robocopy is very complicated. Even more so than regular move. I’m not the first to deal with this headache… Check out this topic on SQL Server Central’s forum. The solution there uses a batch script to accomplish the task. If it’s successful, it returns a consistent error code. But doesn’t it seem like there should be a better way?

Only One Successful Return Code For Cmd Steps

Yep, the problem stems from the fact that only one return code is allowed for SQL Agent Job steps that are Operating System commands. That’s shown on this screen:

There can be only one ... error code.

Powershell Beats ’em All

Long story short… I went with a powershell script. It was the simplest:

move-item "\\server1\somedir\*.txt" "\\server2\somedir"

And I don’t worry about return codes here. The powershell script succeeds or it doesn’t. Times when the script fails include when the move would overwrite an existing file or when permissions prevent the file move and that’s what I wanted.

In powershell, simple things are simple. Hard things are possible.

September 26, 2012

A Quick SQL Challenge

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:21 pm

So I was recently involved in an issue where a particular Unicode string was causing some problems in an application. The string was coming from a production database. I was asked to help reproduce this. It boiled down to this:

Can you write a SQL Script which produces a string containing every possible unicode character?

That’s every character from 0x0000 (NUL) all the way up to 0xffff (which is not even legal unicode). If you want a crack at it, start with the code here:

declare @allNCHARS nvarchar(max);
 
/*
Your answer would fit here.
*/
 
UPDATE MYTABLE 
SET Value = @allNCHARS
WHERE Id = 1;

My own answer is in the comments Maybe you can come up with something more elegant.
But it’s a handy string. If you’re in QA, it can help exercise a lot of different text issues (code pages, encodings, conversions etc…).

In my own case, the string actually helped me. I was able to reproduce the issue on a local developer machine. A colleague of mine then did more detective work and squashed that bug, so it was a bit of a tag team effort there.

September 21, 2012

Some Announcements

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication — Michael J. Swart @ 10:36 am

Thought I’d mention a couple things that aren’t technical, but might interest you any way.

I'm hunting wabbits

I’m Changing Jobs

It’s true, I left my position as a Senior Database Developer at Desire2Learn. But I’ve accepted a new position at the same company (because Desire2Learn it just that cool) as a Data Warehouse Architect.

What this means for me: For me it means more focus, more BI, more data integration.

What this means for you: For you (dear blog reader) it means that the topics I write about could shift. I’ve never given myself restrictions on what I could and couldn’t write about. But I expect the new and different challenges I face to lead to new and different blog post topics. My hope is that instead of writing posts explaining how I prefer one technique over another, I’ll be writing posts explaining how I prefer one strategy over another. We’ll see what happens.

I’ve Joined TorPASS

 I’ve been asked to help reboot TorPASS Toronto’s SQL Server User group. I spoke at the first meeting along with Karen Lopez (@Datachick) and I thought it went well. Toronto’s SQL Server community deserves to be more active. I mean it certainly has the talent. I’m encouraged by the start.

What this means for me: I’ll be more involved with the local SQL Server community. My hope is that I get opportunities to speak a little more. Blogging has done wonders for my writing skills. So maybe speaking skills are next.

What this means for you: If you’re local great! I hope to see you come out some time. I’ve already met some great people. Even if you’re from another part of Southwestern Ontario… I’m hoping that maybe the group can hold a few meetings out this way (Kitchener-Waterloo). Stay tuned!

I’m Attending the PASS Summit

The PASS summit is the largest event dedicated to SQL Server. The first time I attended the summit was in 2009. For one reason or another I missed the next two. (I did catch the keynotes online though). Well this year, I’m not missing it. I’m coming to the PASS summit in Seattle and I’m psyched!

What this means for me: umm… the world.

What this means for you: If you’re coming too that’s great! I’d love to meet you. Introduce yourself to me, I look like that guy in the illustration above and if my memory serves correctly, it’s likely that I’ll be wearing some sort of over-sized conference badge with my name on it. Just know two things: (1) I have no dinner or lunch plans and (2) I like to talk about databases.

If you’re not coming to the summit that’s okay too. I know how you feel. Keep reading this blog. I’ll be writing about stuff I hear and learn and giving my take on it.

Cheers!

September 12, 2012

When I Use Nested Joins

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Tags: — Michael J. Swart @ 12:00 pm

I want to explain how I avoid RIGHT OUTER joins in favor of LEFT OUTER joins and how I avoid OUTER joins in favor of INNER joins.

There's no place bitchin-er than Kitchener

Inner joins have no direction, but outer joins do so that we have three kinds of joins:

  • INNER JOIN (JOIN for short)
  • LEFT OUTER JOIN (LEFT JOIN for short)
  • RIGHT OUTER JOIN (RIGHT JOIN for short)

I leave out FULL OUTER JOINS for now because I never use them. And in fact RIGHT OUTER JOINS can always be written as LEFT OUTER JOINS, so in practice I only use just the two kinds of joins:

  • INNER JOIN
  • LEFT OUTER JOIN

Now as a rule of thumb, inner joins are more efficient than outer joins so it would be better to write queries that avoid outer joins. Let’s get to an example. My goal here is to write a query that gives me a list of employees and their director (if any) based on this schema.

CREATE TABLE STAFF
(
    Id NVARCHAR(20) NOT NULL PRIMARY KEY,
    Name NVARCHAR(400) NOT NULL,
    Department NVARCHAR(20),
    Role NVARCHAR(20) NOT NULL
)
 
CREATE TABLE BOSSES
(
    EmployeeId NVARCHAR(20)
        REFERENCES STAFF(Id),
    BossId nvarchar(20)
        REFERENCES STAFF(Id),
    PRIMARY KEY (EmployeeId, BossId)
)

BOSSES is a table that contains not just direct reports, but all direct and indirect reports (making it handy for this query).

Using a RIGHT JOIN

The answer is fairly straightforward. I join the BOSSES table with the STAFF table to give me all the directors and their reports:
Two inner joins

select S_Employee.Name, S_Boss.Name as Director
from STAFF S_Boss
join BOSSES B
    on B.BossId = S_Boss.Id
join STAFF S_Employee
	on B.EmployeeId = S_Employee.Id
where S_Boss.Role = 'Director'

But wait, this isn’t a complete list of employees. What about those in the company that don’t report to any director (not even indirectly). This is where the right outer join comes in:
An inner join and a right join

select S_Employee.Name, S_Boss.Name as Director
from STAFF S_Boss
join BOSSES B
    on B.BossId = S_Boss.Id
RIGHT join STAFF S_Employee
    on B.EmployeeId = S_Employee.Id
    and S_Boss.Role = 'Director'

Notice that the S_Boss.Role filter can’t belong to the where clause any more. If it did, we’d lose director-less employees again and we’d be back where we started.

Using LEFT JOINS Only

That works… but for the sake of style, let’s only use left joins. I prefer using only left joins in this case because logically, the results are meant to be the set of employees. So I like to start with that list of employees and then join in the bosses if necessary. That’s why I start with the set of employees as the first table. The other joined tables aren’t the stars of this query; they’re only there to help provide that director attribute. So I have this:
Two left joins

select S_Employee.Name, S_Boss.Name as Director
from STAFF S_Employee
left join BOSSES B
    on B.EmployeeId = S_Employee.Id
left join STAFF S_Boss
    on B.BossId = S_Boss.Id
    and S_Boss.Role = 'Director'

But you notice that I’m now using two left joins… Really I only want the one outer join that I was using in the first example. Turns out I can do that:

Using Nested Joins

Well that just looks like this:
One left join

select S_Employee.Name, S_Boss.Name as Director
from STAFF S_Employee
left join BOSSES B
    join STAFF S_Boss
        on B.BossId = S_Boss.Id
        and S_Boss.Role = 'Director'
    on B.EmployeeId= S_Employee.Id

This is logically equivalent to the right join but it uses left joins instead and only uses one outer join. Great!

A lot about this example is only about style. Maybe you prefer the right join example and think that I’m breaking some grammar rule here. It feels like I’m splitting an infinitive or something. Let me know what your preference is.

Powered by WordPress