Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The 5 First SQL Errors to Check For

By Andy Owl,

As a trainer and programmer using T-SQL I see the same errors repeated over and over again. This inspired me to list out the 5 DOH errors which - no matter how many times you make them - just keep on recurring.

Common SQL Error 1 - too few or too many commas

This is - by far - the most common mistake made in writing SQL (to the extent that I've taken to coughing loudly behind delegates who transgress). The following query is typical, containing as it does an extra comma:

-- list out the name and release date for each film
-- in a table
SELECT
FilmName,
FilmReleaseDate,
FROM
tblFilm

Just as bad is to miss out a comma:

-- list out the name and release date for each film
-- in a table
SELECT
FilmName
FilmReleaseDate
FROM
tblFilm

In this latter case, however, the SQL won't generate an error; instead, Management Studio (SSMS) will assume that the second column name is an alias for the first, and display a single column with the wrong header:

 

Do yourself a favour: check your commas before you do anything else!

Common SQL Error 2 - part of a command already selected

How many times have I seen this? If you have part of a query selected - even if it's only a single word - SSMS will run the selected text rather than the entire query. So for the example below, if you press F5 or click on the EXECUTE button you won't get a good result!

The moral? Before you run your query, make sure that you have either nothing selected, or that you've selected an entire command. 

Common SQL Error 3 - You're Using the Wrong Database

If you run a query and SSMS tells you that it doesn't recognise your table, there could be a good reason for this!

Perhaps SSMS can't find the table TBLFILM in the above error because there is no such table in the current database! Either add the command:

USE Movies 

Or change to the correct database using the dropdown at the top of SSMS:

Either way, SQL Server should then be able to find your table.

Common SQL Error 4 - Order of Commands

There's nothing wrong with each part of the following SQL command:

-- show for each film director the average run
-- time for their films, including only films
-- which won at least 1 Oscar and only directors
-- whose average film length was more than 2.5 hours
SELECT
d.DirectorName,
avg(FilmRunTimeMinutes) AS [Average length]
FROM
tblDirector AS d
INNER JOIN tblFilm AS f
ON d.DirectorId = f.FilmDirectorId
GROUP BY
d.DirectorName
HAVING
avg(FilmRunTimeMinutes) > 150
WHERE
f.FilmOscarWins >= 1
ORDER BY
[Average length] DESC

What there is something wrong with is the order. Can you spot the problem? Use the following mnemonic to help:

  • Sweaty
  • Feet
  • Will
  • Give
  • Horrible
  • Odours

This shows that the WHERE clause should come between FROM and GROUP BY - but it's oh-so-easy to overlook this.

Common SQL Error 5 - Try Again!

If you're tried all of the other 4 things above, and you're absolutely convinced your query is error-free, try executing it one more time. SSMS does - just occasionally - report an error where none exists. However, this only happens once: if you run your query twice and it still generates an error, then it's you who's at fault, not sQL Server!

Total article views: 12859 | Views in the last 30 days: 2
 
Related Articles
FORUM

select query

select query

FORUM

full cycle structure...command select *

full cycle structure...command select *

FORUM

Select query

Select query

FORUM
FORUM

'ROOT' directive in SELECT query

'ROOT' directive in SELECT query

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones