Re: How to validate a query?

From: Stefan Delmarco [MSFT] (StefanDe_at_online.microsoft.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 08:39:38 -0000

One thing to watch out for is that the SETs have to be in their own batch,
otherwise you won't get the behaviour you expect. That is, execute the SET
NOEXEC ON, then submit your TSQL, then SET NOEXEC OFF. If you submit your
TSQL and the SET NOEXEC in a single batch, your TSQL will actually be
executed. The SET NOEXEC only takes effect in the next batch.

Cheers,
Stefan 

-- 
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stefan Delmarco [MSFT]" <StefanDe@online.microsoft.com> wrote in message
news:e5SUkkYDEHA.2564@TK2MSFTNGP11.phx.gbl...
> Hi Paul, have a look at SET NOEXEC in BOL:
>
> <Snip>
> SET NOEXEC
> Compiles each query but does not execute it.
> ...
> This setting is useful for having SQL Server validate the syntax and
object
> names in Transact-SQL code when executing. It is also useful for debugging
> statements that would usually be part of a larger batch of statements.
> </Snip>
>
> Also have a look at SET PARSEONLY in BOL:
>
> <Snip>
> SET PARSEONLY
> Checks the syntax of each Transact-SQL statement and returns any error
> messages without compiling or executing the statement.
> </Snip>
>
> (If you profile SQL when selecting "Check Syntax" in EM's SP Properities
> window you'll that it uses NOEXEC and PARSEONLY to check the syntax
without
> actually creating the SP or executing the actual SQL).
>
> Cheers,
> Stefan
>
> -- 
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "paul reed" <prreed@jacksonreed.com> wrote in message
> news:eHK$JRXDEHA.2908@TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I have built a dynamic query builder for a project I am on. The user
picks
> > various fields they want retrieved and where criteria and the end result
> is
> > a query string that I build (much like what the query designer does in
> EM).
> > However, one property of my object is to return back a boolean whether
or
> > not the query I built is in fact valid (e.g., not only well-formed SQL
but
> > that it would execute if I tried to use it). What is the best way to do
> > this? I know how to pass this string to a sproc and dynamically execute
> > it...but I'd rather not have to execute the query to find out if it is
> > valid. Is there any type of syntax check sproc that I can pass it to?
> >
> > Thanks in advance,
> >
> > Paul
> >
> >
>
>

Relevant Pages

  • Re: Finally which ORM tool?
    ... manipulate the linq query IF you're executing it at that moment. ... simply because the declaration construction was with 'CHOPS'. ... implement IEnumerablebut had an Execute() method which gave back ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • Re: UPDATE query in Access 2003 raising error
    ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • Re: Primary Key Violation Error Message
    ... I should have read your query more carefully. ... > Dim ws As DAO.Workspace 'Current workspace (for transaction). ... > 'Step 2: Execute the append. ...
    (microsoft.public.access.formscoding)