?
Solved

SQL Error: 'Invalid SQL Statement'

Posted on 2003-02-27
6
Medium Priority
?
765 Views
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?
0
Comment
Question by:alzoid69
[X]
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
6 Comments
 
LVL 2

Expert Comment

by:TAshby
ID: 8036039
break is for a while loop:

WHILE
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.

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

Arguments
Boolean_expression

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.

BREAK

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

CONTINUE

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

Remarks
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.

Examples
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
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
   UPDATE titles
      SET price = price * 2
   SELECT MAX(price) FROM titles
   IF (SELECT MAX(price) FROM titles) > $50
      BREAK
   ELSE
      CONTINUE
END
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
FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  
      SELECT @tablename = RTRIM(@tablename)
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
            + @tablename )
      PRINT ' '
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

0
 
LVL 1

Expert Comment

by:brettc
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.

hth
0
 
LVL 8

Expert Comment

by:Danielzt
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.
0
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

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

Accepted Solution

by:
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.
0
 
LVL 5

Expert Comment

by:Tom Knowlton
ID: 9460472
alzoid69,
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 ! --

Thanks,

knowlton
EE Cleanup Volunteer
0

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