SQL Error: 'Invalid SQL Statement'

Posted on 2003-02-27
Medium Priority
Last Modified: 2011-10-03
Here is my SQL statement :

BREAK ON Company.ID ON Address.ID SELECT Company.ID, Name, Phone1, Phone2, Fax, Email, Description, NAICS, Website, POBox, Street, Addr, City, Province, PostCode, Mailing, FirstName, LastName FROM Company, Address, Contact WHERE Company.ID = Address.ID AND Company.ID = Contact.CompID AND Company.Name LIKE 'C%' ORDER BY Company.ID

It was working until I added the BREAK ON

Here is the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

BREAK is a valid SQL statement. Any suggestions?
Question by:alzoid69
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8036039
break is for a while loop:

Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

WHILE Boolean_expression
    { sql_statement | statement_block }
    [ BREAK ]
    { sql_statement | statement_block }
    [ CONTINUE ]


Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{sql_statement | statement_block}

Is any Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block, use the control-of-flow keywords BEGIN and END.


Causes an exit from the innermost WHILE loop. Any statements appearing after the END keyword, marking the end of the loop, are executed.


Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.

If two or more WHILE loops are nested, the inner BREAK exits to the next outermost loop. First, all the statements after the end of the inner loop run, and then the next outermost loop restarts.

A. Use BREAK and CONTINUE with nested IF...ELSE and WHILE
In this example, if the average price is less than $30, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $50, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $50, and then exits the WHILE loop and prints a message.

USE pubs
WHILE (SELECT AVG(price) FROM titles) < $30
   UPDATE titles
      SET price = price * 2
   SELECT MAX(price) FROM titles
   IF (SELECT MAX(price) FROM titles) > $50
PRINT 'Too much for the market to bear'

B. Using WHILE within a procedure with cursors
The following WHILE construct is a section of a procedure named count_all_rows. For this example, this WHILE construct tests the return value of @@FETCH_STATUS, a function used with cursors. Because @@FETCH_STATUS may return -2, -1, or 0, all three cases must be tested. If a row is deleted from the cursor results since the time this stored procedure was executed, that row is skipped. A successful fetch (0) causes the SELECT within the BEGIN...END loop to execute.

USE pubs
DECLARE tnames_cursor CURSOR
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
   IF (@@FETCH_STATUS <> -2)
      SELECT @tablename = RTRIM(@tablename)
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
            + @tablename )
      PRINT ' '
   FETCH NEXT FROM tnames_cursor INTO @tablename
CLOSE tnames_cursor
DEALLOCATE tnames_cursor


Expert Comment

ID: 8036807
You are correct to say that BREAK is a valid SQL statement - and TAshby shows you the correct usage of this word. But you are trying to use BREAK ON which is Oracle specific syntax.

To duplicate the output of the BREAK ON syntax in MSSQL, check out BOL for the ROLLUP keyword.


Expert Comment

ID: 8038184
In Oracle, BREAK ON is not a valid SQL DML command. It a SQL PLUS command. Using for formatting the Select output records.

100% for sure you can not use it with ODBC.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 8041349
I can't find any documantation on BOL or ROLLUP.  Can u give me a link?

Accepted Solution

brettc earned 300 total points
ID: 8041648
BOL(Books On-Line) is/should be installed with the MSSQL. If you don't have access to BOL on your desktop then you can get the help here - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_08_7e40.asp

good luck.

Expert Comment

by:Tom Knowlton
ID: 9460472
No comment has been added lately (214 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to brettc http:#8041648

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --


EE Cleanup Volunteer

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question