Syntax Check

  • Thread starter Thread starter Cathie Hagen
  • Start date Start date
C

Cathie Hagen

Hi All,

Is there a way to do a syntax check on the statement without executing it?

Cathie
 
If you're running against SQL Server, you can use "SET PARSEONLY ON|OFF".
Check out books online for details on PARSEONLY. The short story is that you
can execute that before your statement so the server parses the statement
and generates an error if there is a syntax error, but the statement never
actually gets executed. Make sure you revert it to OFF before trying to
execute something for real.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks, that works a treat :)

Pablo Castro said:
If you're running against SQL Server, you can use "SET PARSEONLY ON|OFF".
Check out books online for details on PARSEONLY. The short story is that
you
can execute that before your statement so the server parses the statement
and generates an error if there is a syntax error, but the statement never
actually gets executed. Make sure you revert it to OFF before trying to
execute something for real.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
 
Actually I spoke too soon. SET PARSEONLY ON still executes the query when
run in SQL Query Analyser. I have tried SET NOEXEC ON but that only checks
the syntax and doesn't report back if the Column or Table names are invalid.

Any other ideas?
Cathie
 
Sorry, I should have clarified. SET PARSEONLY ON|OFF will only actually
parse the statement; binding won't happen, so you won't get errors that are
not related to syntax such as invalid table names.

If this is a select query, you can use SET FMTONLY ON, which will return an
empty resultset instead of returning all the values. This also works for
trivial DML statements (they won't cause side-effects), but I don't remember
off the top of my head whether there are potential problems with relying on
SET FMTONLY ON for DML.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks very much that works fine

Pablo Castro said:
Sorry, I should have clarified. SET PARSEONLY ON|OFF will only actually
parse the statement; binding won't happen, so you won't get errors that
are
not related to syntax such as invalid table names.

If this is a select query, you can use SET FMTONLY ON, which will return
an
empty resultset instead of returning all the values. This also works for
trivial DML statements (they won't cause side-effects), but I don't
remember
off the top of my head whether there are potential problems with relying
on
SET FMTONLY ON for DML.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
 
Back
Top