Re: How to validate a query?
From: paul reed (prreed_at_jacksonreed.com)
Date: 03/22/04
- Next message: Goran Vukusic: "Re: Sort of a loop in SQL - how to do it"
- Previous message: Uri Dimant: "Re: AutoNumber ?"
- In reply to: Stefan Delmarco [MSFT]: "Re: How to validate a query?"
- Next in thread: Stefan Delmarco [MSFT]: "Re: How to validate a query?"
- Reply: Stefan Delmarco [MSFT]: "Re: How to validate a query?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Mar 2004 06:24:42 -0800
Stefan,
Thanks a bunch...but it looks like outlook also removed the .txt files as
well. Could you perhaps e-mail them to my direct e-mail.
prreed@jacksonreed.com?
Thanks in advance,
Paul
"Stefan Delmarco [MSFT]" <StefanDe@online.microsoft.com> wrote in message
news:OFwE72%23DEHA.3696@TK2MSFTNGP10.phx.gbl...
> (Outlook doesn't like .cs attachments, so renamed the .cs file to .txt and
> re-attached.)
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Stefan Delmarco [MSFT]" <StefanDe@online.microsoft.com> wrote in message
> news:eFwdqw%23DEHA.1376@TK2MSFTNGP10.phx.gbl...
> > Hi Paul, a stored procedure is not the right place from which to
validate
> > the TSQL. The problem is that you have conflicting connection
properties.
> > You want the SP (uspValidateDQBQuery) to execute but you don't want your
> > dynamic SQL to execute (you only wanted it parsed with the SET NOEXEC
> > option). As each execute of sp_ExecuteSql is treated as a separate
batch,
> > you don't have the ability to set NO EXEC in one sp_ExecuteSql and then
> > submit another batch (with sp_ExecuteSql) to execute under the previous
> > batches' SET options. In addition, you are limiting yourself to a
maximum
> > of 4,000 characters in the SQL batch to validate. For complex SPs this
> may
> > not be enough and you'll run into truncation problems.
> >
> > The solution is to take your validation out of the stored procedure
> > (uspValidateDBQuery) and do the validation as TSQL batches from your
> client.
> > I've attached the source code to a sample C# console application that
> > demostrates how to do this.
> >
> > If the TSQL batch does not validate, errors will come back as
> SqlExceptions.
> > These will be easy to process on the client (as in the sample code).
You
> > even have the line number available where the error occurred, so you
could
> > highlight this line in your editor.
> >
> > cheers,
> > Stefan
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Paul Reed" <prreed@jacksonreed.com> wrote in message
> > news:uIicbPdDEHA.2052@TK2MSFTNGP11.phx.gbl...
> > > Stefan,
> > >
> > > Hummm. Maybe I am missing something but, at least in QA, if I don't
> > > issue the SET NOEXEC ON and the SQL statement in the same batch...I
> > > don't get the behavior I expect.
> > >
> > > In your previous post you said to issue the SET ON|OFF as separate
> > > batches. Do you mean with a "GO" followed by each?
> > >
> > > When I put all this in a stored procedure with a known invalid SQL
> > > statment, The error code is still showing zero as if the query is ok.
> > > Here is my sproc. Your input is most appreciated
> > >
> > > CREATE PROCEDURE uspValidateDQBQuery
> > > (
> > > @SQLString nvarchar(4000)
> > > )
> > > AS
> > >
> > > SET NOEXEC ON
> > >
> > > EXEC SP_EXECUTESQL @SQLString
> > >
> > > SET NOEXEC OFF
> > >
> > > SELECT @@ERROR
> > > GO
> > >
> > > Paul Reed
> > > www.jacksonreed.com
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> > > Don't just participate in USENET...get rewarded for it!
> >
> >
> >
>
>
>
- Next message: Goran Vukusic: "Re: Sort of a loop in SQL - how to do it"
- Previous message: Uri Dimant: "Re: AutoNumber ?"
- In reply to: Stefan Delmarco [MSFT]: "Re: How to validate a query?"
- Next in thread: Stefan Delmarco [MSFT]: "Re: How to validate a query?"
- Reply: Stefan Delmarco [MSFT]: "Re: How to validate a query?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
