29Dec 2009 by Grant Fritchey 6 Comments

The Other Server

SQL Server, T-SQL
I had a fun support call I need to share. A developer called up to tell me that a particular dev instance was offline. He informed me that the server SQL08\DEV01 (the names have been changed to protect the innocent) server was completely inaccessible. I knew that multiple development teams would shortly be calling and that I'd better get on this issue most riki-tik. I quickly typed the connection string into Management Studio and watched in confusion as the server instance popped up on my screen. It was fine. I did a number of checks, looking for active connections, recent connections, errors in the log, indications of a recent reboot... Nothing. I called the developer back and told him that the server was fine. He called me again in two…
Read More
23Dec 2009 by Grant Fritchey 1 Comment

A Lack of Excitement

PASS, PowerShell, SQL Server, T-SQL, Tools
I usually use all the problems, crashes, and issues that I run into at work as grist for my mill, aka, material to blog about. But lately, we haven't been crashing & burning much <knock wood, turn three times, throw salt over my left shoulder, spit>. But it was suggested that may be I should mention why that is. The fact of the matter is that I've been spending a lot more time working on methods for monitoring our systems so that we avoid more of the stupid stuff, full disks, failed backups, long running agent jobs, etc.. I've blogged before about our use of Microsoft's Operations Manager for monitoring our servers and how we've built custom rules and monitors to keep an eye on things. I've also mentioned how…
Read More
22Dec 2009 by Grant Fritchey 4 Comments

Apology

Misc
I've been somewhat light in my blogging of late. Unfortunately, I just haven't hit any interesting or difficult problems lately that were worth sharing. I'll try to break one of our production systems in an interesting way very soon so that I've got something to write about, even if it's how to look for a new job.
Read More
15Dec 2009 by Grant Fritchey 17 Comments

What is Your Greatest Weakness

Misc
I was tagged earlier this week by Tim Ford to comment on my biggest weakness. I wish I could say that it's the color yellow (stupidest weakness, ever), or kryptonite. I don't have to wear ruby-quartz glasses everywhere (and good thing too). Certainly getting tied up by a man isn't my weakness (eeewww). And while I do agree that with great power comes great responsibility, I don't feel responsible for everything. I'm not suffering from a terminal heart condition (or shards of metal in my blood stream). So, I've avoided all the obvious (or silly) weaknesses. No, unfortunately, my weaknesses, and they are myriad, varied, and far too numerous, are pretty much of the straight human variety, despite my rather obviously being a mutant. Trying to pick one that stands out,…
Read More
08Dec 2009 by Grant Fritchey 4 Comments

T-SQL Tuesday #1: Date/Time Tricks

SQL Server, T-SQL
I'm going to try out Adam Machanic's idea for a blog party. The topic this month are Date/Time tricks. Instead of supplying a trick for Date/Time, I'm going to caution you about the tricks that you use. Let's take a simple issue. You want to pull back data from a table, let's use the Production.TransactionHistoryArchive in AdventureWorks2008, for a given month of data. Before we run the query, let's create an index on the table: CREATE INDEX ixTest ON Production.TransactionHistoryArchive (TransactionDate) The query itself is pretty simple. This is one mechanism that will retrieve the data for the month of July in 2003: SELECT tha.TransactionID FROM Production.TransactionHistoryArchive AS tha WHERE DATEPART(yy,tha.TransactionDate) = 2003 AND DATEPART(mm,tha.TransactionDate) = 7 In theory you should be able to use the index that was created earlier,…
Read More
02Dec 2009 by Grant Fritchey No Comments

Dr. DeWitt’s Key Note at the PASS Summit

PASS
If you missed this, here's your chance to make it up. If you were there, and like me, you need to rewatch it about six to eight times to try to understand everything that was presented, here's your chance. Dr. DeWitt's key note was probably the high point of the Summit or at least in the top 5. It's not to be missed.
Read More
02Dec 2009 by Grant Fritchey No Comments

SQL Standard Update

Misc
Andy Warren in the latest PASS Connector has posted an update on where we're at with the SQL Server Standard. I'm so happy that Andy has been keeping this out in front of people. It provides some impetus to get the work done. Except for the authors thanks (and they're receiving $500, so they should say thank you), there's very little feedback on the Standard to show whether or not people are interested, if the goals and ideas are worthy... In other words, you guys need to let us know what you think about the thing. Two more articles are in the hopper to be published. Another has gone into copy edit. We're technical editing two others. That's five more, so you guys can expect to see another ten weeks…
Read More
01Dec 2009 by Grant Fritchey 4 Comments

How do -You- use SQL Server

nHibernate, SQL Server, T-SQL, Tools, Visual Studio
I've been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That's a tough one. It would make a much shorter, and easier, blog post to describe the things we don't use it for. However, keeping with the spirit of these tags, I'll try to lay out it. For those that don't know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we'll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70's and 80's propped up by ancient men with pocket protectors, spit, bailing wire…
Read More
30Nov 2009 by Grant Fritchey No Comments

SQL Saturday #34/New England Data Camp v2.0

Misc
It's official sports fans. Well, it's been official since last week since Adam Machanic set up the web site. The New England SQL Server Users Group and the Southern New England SQL Server Users Group are again jointly hosting a full day of SQL Server goodness on January 30th. The call for sponsors and speakers is open. We already have several local luminaries lined up to present including Aaron Bertrand on Management Studio Tips & Tricks and Scott Abrants on Automating Database Deployments with Visual Studio. Please register to spend a day with your peers, learning and networking. It's being held at the Microsoft Waltham office, a great facility. We should have a full day with lots to do and learn.
Read More
30Nov 2009 by Grant Fritchey 6 Comments

SQL Server XQuery For Idiots

SQL Server, T-SQL
I'm still struggling with learning XQuery. My latest little revelation was small, but vital. The difference between: @inXML.nodes('/rss/channel/item') and @inXML.nodes('/rss[1]/channel[1]/item') Is the difference between a 10 minute query and a 4 second query. Kind of shocking really. My understanding is that XQuery assumes there are multiple possible paths that look like "/rss/channel" so it searches over and over again through the XML to see if it can find them. But by identifying it as a root, showing that only one possible path is available, it stops stumbling around in the dark and simply reads the data. It's a little thing, but it made an enormous difference. I've still got a long way to go in learning how to use XPath within XQuery. UPDATED: I modified the title so that it's…
Read More