Voice of the DBA

Tag: syndicated

The DevConnections Schedule is out

It’s preliminary, but the fall 2012 SQL Server Connections schedule at DevConnections is out. I’ll be speaking Tuesday, Oct 30, 2012, along with Grant Fritchey (b | t), on the SQLServerCentral track.

This is traditionally the “Microsoft day” where sessions are all presented by Microsoft, but this time we’re providing an alternative to those of you attending the conference. I’ll be presenting a session on searching binary data with Full-Text Search and semantic search along with a session on Policy Based Management.

I’m looking forward to the show, and hope to see some of you there.

September 14, 2012
Interesting Culture

A post from Alex Maccaw, programmer at Stripe. He talks about some things he likes about the company, and overall I think there are good ideas in here. In fact, a number of them remind me of my time at IQDestination (my thoughts), and some remind me of things that I think the Investment Advisory Network failed to do.

I don’t agree with everything, and I think the cc to email lists, while a good tracking tool, can get people to drop into the rabbit hole and get lost. If you have good people, you trust them not to do that, but it will happen sometimes.

I really like a couple things.

1. The dream machine.

2. Paper reading

I think the first welcomes the employee into the company and gets them highly motivated and appreciative from the start. The second is a good way to build the habit of learning.

September 14, 2012
Finding DDL Triggers
Triggers are the types of objects in SQL Server that are easy to lose track of. There isn’t an obvious way to tell that a table has a trigger on it and since most tables don’t have triggers, this is one of the things people often miss when troubleshooting unexpected results.

DDL triggers are worse, since they aren’t tied to particular tables, but rather events. How can you find DDL triggers in your environment?

There are a few ways. I’ll show you visually and in code.

The GUI

I like the Management Studio GUI to find information, and to quickly get code written. With SQL Prompt installed, I can get great intellisense that makes it easy to find parameters, names, objects, etc. I don’t like to run the actions from SSMS, but rather use the Script button and save the code, and execute it in a query window.

In looking for server-side triggers, there is a “Server Objects” folder in the tree.

Here is where you find your backup devices, endpoints, linked servers, and server level triggers. In this case, I can expand the folder (shown above) and find the trigger I created recently.

At the database level, there’s a similar structure. Inside of a database, we find there is a programmability folder, which contains all the code items I can create in a database.

In here we can see there is a Database Triggers item, and inside there are two triggers that I setup inside this database.

You have to go look for these triggers, but if you’re wondering if they exist, you can find them here.

Code

The best way to look for triggers quickly is with code. Without resorting to BOL, I suspected there was some DMV that contained trigger code. As you can see below, I was right as typing SSF (a shortcut in Prompt), followed by “master.sys.server_t” got me this result:

If I then examine the results from the server_triggers table, I get my one trigger at the server level.

This is only part of the information needed as the server_trigger_events table has the events that will fire this trigger. I can query that to see I only have one event here:

If I join in the events, then I can clean this up and get this:
```
select
  t.name
, t.object_id
, t.is_disabled
, te.type_desc
 FROM master.sys.server_triggers t
   INNER JOIN master.sys.server_trigger_events te
     ON t.object_id = te.object_id
```
Which shows me the trigger, its ID, and the event’s.
September 13, 2012
Labor Day Bloopers

The raw footage of all the mistakes I’ve made since the Fourth of July.

Part 1

Part 2

September 12, 2012