Syntax Check

[Cathie Hagen]

Hi All,

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

Cathie

 

[Pablo Castro [MS]]

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.

 

[Cathie Hagen]

Thanks, that works a treat

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.

 

[Cathie Hagen]

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

 

[Pablo Castro [MS]]

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.

 

[Cathie Hagen]

Thanks very much that works fine

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.