Voice of the DBA

Tag: T-SQL

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
Are there that many GUIDs?

Do a lot of people actually use GUIDs as Primary Keys? I haven’t used them much, and I would have thought that more people chose identity keys. It seems that most of the demos and examples I see from bloggers and speakers are constantly using identities.

However an informal survey from Peter Bromberg showed that four times as many people actually had GUIDs as their primary keys. The blog actually says that GUIDs are not a good choice, but I’m not sure I agree with that. You can use sequential GUIDs, and you can avoid making them the clustered key, so I think they can work as well as anything.

There’s nothing inherently wrong with GUIDs, and they should be unique across all of your rows. There have been some reported cases of duplicates, but for most practical purposes, especially in database work, you ought to be able to count on a GUID as unique. They even have the nice capability of being generated by clients, removing the need for an extra round trip when a client needs to insert multiple rows.

I typically don’t use them because they’re long, hard to remember and type, and hard to view on the screen. I can’t easily compare rows in multiple tables, and it’s easier for me to work with integers. I don’t recommend them, but if you are going to use them, be sure you understand the pros and cons, and use them appropriately.

Steve Jones

October 1, 2010
Common SQL Server Mistakes – Multi Row DML Triggers

How often have you seen someone write a DML trigger like this:

create trigger mytrigger on Mytable for insert as

declare @id int
select @id = id from inserted

update xx set yyy = zz
where id = @id

return

There seems to be this common misconception that a trigger fires for each change in a row (insert/update/delete), and that’s not true. As noted in Books Online, triggers fire once for the insert/update/delete. Typically this is an implicit transaction for the statement. If you have multiple statements inside an explicit transaction, the trigger fires once for each insert/update/delete statement in the transaction.

That means that if I change two rows and I have the trigger above, I won’t get the behavior I expect from the trigger. Let’s say that I want to update my inventory table each time I change an order. Imagine that I have this in my orders table:

and this in my inventory table.

If I now write this trigger:

alter trigger orders_update_inventory on orders
  for update
  as

  declare @qty int
     ,    @product int

  select @qty = a.qty – b.qty
    from inserted a
       inner join deleted b
         on a.orderid = b.orderid
  select @product = productid from inserted

  update inventory
   set onhand = onhand – @qty
   where productid = @product

return

and execute this:

update orders
  set qty = 2
   where orderid = ’59CD85CE-984C-4D33-9E23-5F6159848277′

I will find that my inventory table looks like this:

That appears to work, but what happens if we execute this?

update orders
  set qty = qty+1
  where[CustomerID int] = 2

Then we find that the inventory is

In this case productID 1 has had its inventory reduced by 1, but not product ID 2. Why not?
When the trigger fires, there are actually this data in the tables:

Inserted
OrderID OrderDate CustomerID int qty         productid
——– ———- ————– ———– ———–
59CD…    2010-09-22 3              3           1
01B6…    2010-09-22 2              2           2

and deleted

OrderID OrderDate CustomerID int qty         productid
——– ———- ————– ———– ———–
59CD85… 2010-09-22 2              2           1
01B66E… 2010-09-22 2              1           2

However the trigger, in setting the variables to the result of a query could have picked either of the rows, but only one row. SQL Server doesn’t guarantee order without an ORDER BY, so either product ID could have been chosen. As a result, only one of the products had the inventory updated.

A proper trigger would look like this

alter trigger orders_update_inventory on orders
  for update
  as

  select @qty = a.qty – b.qty
    from inserted a
       inner join deleted b
         on a.orderid = b.orderid
  select @product = productid from inserted

  update inventory
   set onhand = onhand – ( a.qty – b.qty)
   from inserted a
       inner join deleted b
         on a.orderid = b.orderid
   where inventory.productid = i.productid

return

Triggers should always be written to handle multiple rows, using the inserted and deleted tables for joins instead of variables. Even if you always just update single rows, coding this way will prevent issues if there is a multiple row change.

September 23, 2010
Community Direction
When Microsoft implemented Connect, I thought it was a great idea. It was a way for real users to submit bugs, and others to see those bugs, voting on them if they thought they were important. It would help Microsoft determine what features and bugs are important and perhaps allocate resources accordingly. However there was a fundamental problem with the system. People would see individual items, and could vote for them, but wouldn’t have an idea of what other items might be listed.

The work on SQL 11 is underway, and recently I got a note from Itzik Ben-Gan asking people to vote for windowing enhancements to the T-SQL language. I’m not sure exactly of all the places that these are useful, but Itzik is one of the smartest people I know and I tend to believe that if he finds these enhancements useful, they are likely going to make T-SQL easier to work with.

But are these items a priority? I am sure they are valuable, but are they more valuable than CREATE or REPLACE? IS it more of a priority than allowing SSMS add-ins? There are any number of enhancements that are listed, but most of us don’t have the time to dig through them all, or even try to determine how important they might be when weighed against other items.

Microsoft can do what they want, and they need to keep one eye on the sales generated from new features. However I wish that they’d reserve a slice of their development efforts for older features and get some community help in choosing which items to work on. I’d love to see a list of the items they are considering, maybe the top 20 features, and let us add votes to pick the 10 they can work on.

We may not sign the purchase orders, but us DBAs really like SQL Server and would appreciate improvements that make our jobs easier.

Steve Jones

PS: Here are Itzik’s items:
September 22, 2010