SQL Server Reference Guide

Troubleshooting is a basic skill for almost every technical professional to keep things stable and performing well. It’s a common activity of locating something that isn’t working, and correcting it. Of course, the difficulty lies within the steps to accomplish those things. I’ve covered basic troubleshooting for the Administration function for a Data Professional, and in this tutorial I’ll cover the basic steps for the Programming function. I’ll show you a simple process you can follow to locate and correct errors in your code.

There are a lot of errors you can get in Transact-SQL (T-SQL) code — from Syntax (which means the spelling, placement and use of programming terms) to logic (the way something works). In addition, you can have “misplacing” errors — like using a variable in the wrong place. In this case, things work, but don’t produce expected results.

Here are a few techniques you can use to prevent, track down and correct errors in your code.

Stop the errors before you get them

The best way to deal with errors is not to get them in the first place. Great, you think, but I’m already here. I’m reading this because I have errors.

Part of this exercise is not to get errors in the future. Sure, you’ll learn better syntax and how to avoid errors merely by correcting them, but you can actually gain a double advantage with a couple of techniques that help to correct an issue, and to keep them from happening in the future. Here’s how.

Read and understand

Whenever you run into an error in your code, make sure it’s an area you understand. The biggest mistakes I see are when someone copies code snippets from somewhere and pastes it into their own code. They don’t understand exactly why the snippet works, and consequently they can’t fix it when it breaks.

In fact, if you’re not sure what the code does in the snippet you’re copying, you could actually cause a major issue in your entire program. The snippet may be harmless and even useful, but it may not. They point is, you’re using it because you don’t know how to do it yourself, so you just don’t know.

That isn’t to say you shouldn’t experiment with sample code. By all means, do that. But do it to learn — not just blindly. the more you know about a code block, the easier it is to correct it.

If you didn’t just copy the code, you still need to understand what it does. Don’t use a system stored procedure, T-SQL Statement or other construct until you read up on it. Check Books Online first, then search the web to find what other people are saying about it. When you get that error, this tactic still works. Read up on each and every part of your statement, and odds are you’ll see the fix right there in the documentation or examples.

Code your comments

Whenever you design a new program, I recommend that you write down block comments:

/* This is a block comment */
/* This is a 
block comment */
-- This is not, and should be avoided at all costs.

The block comments should state what your program or script is going to do.

For the rest of this tutorial, I’ll create a simple database with three tables. The mistakes I’ll demonstrate will be trivial, but they’ll illustrate the processes to find and correct issues.

This simple database will have three tables. A VET table, an ANIMAL table, and a VISIT table. the VET and ANIMAL tables are “parents” — meaning that they don’t depend on each other at all. You can have a VET without an ANIMAL, or enter an ANIMAL without a VET. Here’s the code that does that — of course only run this on your testing system or Virtual Machine:

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE TABLE VET
(
VETID int PRIMARY KEY 
,VetName varchar(50)
)
CREATE TABLE ANIMAL
(
ANIMALID int PRIMARY KEY
,AnimalName varchar(50)
)
CREATE TABLE VISIT
(
VISITID int PRIMARY KEY
,VisitDateTime datetime
,VisitDescription varchar(100)
,VETID int REFERENCES VET(VETID)
,ANIMALID int REFERENCES ANIMAL(ANIMALID)
)

Learning to use the INSERT statement isn’t really that difficult — using the first technique of reading the documentation to understand the format and syntax for the INSERT statement gives me this example of adding Buck and Christina as vets:

INSERT INTO [Test].[dbo].[VET]
([VETID],[VetName])
VALUES (1, 'Buck');
GO
INSERT INTO [Test].[dbo].[VET]
([VETID],[VetName])
VALUES (2, 'Christina');
GO
And I can just repeat that process to place a cat in the ANIMAL table:
INSERT INTO [Test].[dbo].[ANIMAL]
([ANIMALID],[AnimalName])
VALUES (1, 'Cathy');
GO

Now I want to create a new VISIT record, where I’ll connect a veterinarian to an animal, on a specific date. I’d like to do that using a stored procedure, making the process as simple as sending the vet’s name along with the animal, with the date of the visit and some specifics.

If you’re new to doing sub-selects in an INSERT statement, this can be difficult to grasp at first. And here’s where that process of “Coding the Comments” comes in really handy. Here are the comments for this code — and I’ll finish it in a moment as I explain the next problem-solving technique.

/* Make sure I’m in the right database */
/* Create the Stored Procedure */
/* Take in the Parameters to do the insert, along with their data types */
/* Start the INSERT statement */
/* For the VALUES, use the parameters */
/* Need to find the ID of the VET based on the VetName Parameter */
/* Need to find the ID of the ANIMAL based on the AnimalName Parameter */

With those steps “blocked out” in comments, you can now figure out the best way to implement each section. By getting the logic straight first, you avoid at least that type of error. Of course — that assumes that your logic is correct. So that’s a great way to program — but how does it help with troubleshooting code you already have written?

First, check the logic in the comments. If that is sound, then check the code in each block to ensure that the syntax is correct.

If the code doesn’t have any comments, even if it isn’t your code, put the comments in as you learn what each section does. In that way you’ll quickly zero in on which part of the code isn’t working.

Use the tools

An “IDE” is an Integrated Development Environment. It’s a program that helps you developer your code in one or more languages. It’s also designed to keep you away from making errors in the code, or at least it will assist you in tracking the errors down faster.

In SQL Server, you get an IDE built right in to the client tools. In SQL Server 2000 and earlier, this was the Query Editor (QE), and in SQL Server 2005 and higher this is SQL Server Management Studio (SSMS). Each version brings improvements, and by and large you can use a later version to work with earlier servers. For instance, you can use SSMS 2008 R2 against a SQL Server 2005 database.

It’s best to stop and take the “Tutorial” in the “Help” menu on the menu bar. Knowing the tools you have is essential in preventing and tracking down code errors.

These IDE’s help you avoid and track errors in multiple ways. All versions include a color-coding scheme for syntax and object names. That’s the first indication of an error — you look at the code and the SELECTT statement isn’t colored. Remove the extra “T” and it will color code.

If you run code and experience an error in the “Messages” panel, you’ll see the error code, description and line number in red. The error code is the first indication of the major issue, and will often provide a better clue than the error description that follows it. Sometimes the descriptions aren’t really that helpful, but other times they are. In any case, a quick web search on the error code will help you find out where the error might be. Let me remind you — don’t just web search an error code and implement the first fix you find. Situations vary, not only yours but the person submitting the result on your web search. Just use those searches to help point you in the right direction.

You can double-click the red error message to take you to the line where the engine ran into the issue — but understand this might not always be the line where the problem is. Sometimes an error is “masked” by moving through the various layers of the code, so double-clicking the error message might take you a little ahead or perhaps a little behind the actual error, but at least it will get you close. And sometimes it’s right-on the number.

In SSMS 2008 and later, Microsoft added “Intellisense” for the code. While it isn’t perfect, this feature will fill out object names for you, show you the parameters required for a stored procedure and more. Don’t ignore anything on the screen — it’s all there to help you.

Yet another tool built into the IDE, at least in SSMS 2008 and higher, is the Transact-SQL Debugger. I’ve covered that tool in another article, and I’ll mention the process for debugging further in a moment.

You’re not limited in your IDE selections to the tools included with SQL Server. You’ll find other IDE’s that understand T-SQL in Visual Studio, Eclipse, and companies like RedGate and Quest. You can read up on the troubleshooting features these tools have at their respective sites.

Build the code from known to unknown

Whenever you’re faced with a complex task, break it down into a more simple form first. For instance, in the sample code above, I took the INSERT statement that is pretty simple to understand:

INSERT INTO SomeTable
column
, column
, column
VALUES
(‘Value’
, ‘Value’
, ‘Value’)

And then just made my comments say:

/* Replace values with variables */

Before I even go there, I make sure that my INSERT statement works first. In the example of the VET to ANIMAL entry for the VISIT table, before I even build the stored procedure, I make sure that a “manual” INSERT works first:

/* I know Buck has an ID of 1, and 
Cathy has an ID of 1. So this should work: */

INSERT INTO [Test].[dbo].[VISIT]
           ([VISITID]
           ,[VisitDateTime]
           ,[VisitDescription]
           ,[VETID]
           ,[ANIMALID])
     VALUES
           (1
           ,'01/01/2010'
           ,'Checkup.'
           ,1
           ,1)

GOI keep it as simple as possible. When I’m exploring a new feature (or at least new to me) in SQL Server, I try the simplest possible process first (Hello World) and then work from there. I add complexity, working from what I know to what I don’t know.

Next, I try another thing I know, getting the ID from the VET table with using the name:

SELECT VETID 
FROM VET
WHERE VetName = 'Buck'
------
1

And the same thing for the ANIMAL table — I get the ID from there using the Animal Name:

SELECT ANIMALID 
FROM ANIMAL
WHERE AnimalName = 'Cathy'
------
1
Because I commented first, I can now put it all together:
/* Make sure I’m in the right database */
USE Test;
GO
/* Create the Stored Procedure */
CREATE Procedure usp_VetVisitToAnimal
/* Take in the Parameters to do the insert, along with their data types */
@VISITID int
, @VisitDateTime datetime
, @VisitDescription varchar(100)
, @VetName varchar(50)
, @AnimalName varchar(50)
/* Start the INSERT statement */
AS
INSERT INTO [Test].[dbo].[VISIT]
           ([VISITID]
           ,[VisitDateTime]
           ,[VisitDescription]
           ,[VETID]
           ,[ANIMALID])
/* For the VALUES, use the parameters */
     VALUES
           (@VISITID
           ,@VisitDateTime
           ,@VisitDescription
/* Need to find the ID of the VET based on the VetName Parameter */
           ,(SELECT VETID 
			 FROM VET
			 WHERE VetName = @VetName)

/* Need to find the ID of the ANIMAL based on the AnimalName Parameter */
           ,(SELECT ANIMALID 
			 FROM ANIMAL
			 WHERE AnimalName = @AnimalName)
           )
GO
/* Now Try it: */
EXEC usp_VetVisitToAnimal
2
, '1/10/2010'
, 'Teeth Cleaning'
, 'Christina'
, 'Cathy';
GO

Step through the code

But what if this is already done? Again, I’m not writing code, it’s already written and I’m just trying to find the errors. Simply reverse the process. I work from what I don’t know to what I do know. For instance, in the previous code sample, assume for a moment that the VETID in the VISIT table was accidentally created as a datetime data type instead of an int like it should have been. If I grab out those sub-select statements, I’ll see right away where the problem is.

This is also a great place to talk about the T-SQL debugger in SSMS 2008 and higher. I’ve got an article on that here for you to read, but in essence it allows you to “step into” code one line or block at a time. And it will jump over to stored procedures called from stored procedures, which will also help you track down where the errors are in multiple lines of code.

Use Incremental Changes

It would be nice if there were always just one error. But in many cases it’s a matter of more than just one, it’s several. So as you tear the code into simple parts, fix one thing at a time. Ensure that the piece your fixing works in isolation, then put it back into the larger code base. Then move on to the next error, if there is one.

The temptation is to fix everything you can find in one go. You might be completely convinced that those two errors can be fixed at once — but unless it’s something as trivial as a misspelling, you’re better off just taking things slowly. Especially if you’re in a hurry.

Verify the corrections

Don’t try and troubleshoot on production systems, unless you’ve already verified that the problems don’t exist in a test system. your testing system should have as much of the same structure as possible as production, so you can work on testing, verify the fix, and then move on from there. In fact, I have sometimes created a second database on the test server to have a “clean” place to try things.

Whenever possible, I try to have a complete “clean” build script. That means I have a script that creates the database, objects, does the inserts, and so on, from beginning to end. On large systems this isn’t always possible, but I try to have a scientifically clean database to start with. It will make the errors jump out when you do. When you can’t do that, you’ll need to ensure you know where the differences between the production and testing databases are.

After you correct the problem, it’s not a bad idea to write down what you learned and how you solved the problem. You’ll find that your notes from the past are very useful when you have that same problem again later. Odds are it won’t happen again for a while – and you’ll be glad to have that process documentation to refresh your memory in a crisis situation.