"Ken" <kshap***@sbcglobal.net> wrote in message
news:1158087876.662064.34560@e63g2000cwd.googlegroups.com...
>
> newbie wrote:
>> Is there a way to validate or prepare a sql statement built dynamically.
>> By validating it I mean making sure the table and field names are
>> correct,
>> the data types match, but not actually execute it.
>>
>> Ideas, suggestions ... much appreciated.
>>
>> Gigel
>
> Good Question. I would also like an answer. I do not know of a way to
> do this. The best I know is to tighten and control the sql generation
> process to the degree necessary that requires no validation. You would
> need to validate your metadata exists prior to the generation of sql
> unless you know absolutely sure this is also a given.
>

"newbie" wrote:

> Is there a way to validate or prepare a sql statement built dynamically.
> By validating it I mean making sure the table and field names are correct,
> the data types match, but not actually execute it.
>
> Ideas, suggestions ... much appreciated.
>
> Gigel
>
>
>

> SET PARSEONLY ON
> <your sql ...>
>
> James
>
> "newbie" wrote:
>
> > Is there a way to validate or prepare a sql statement built dynamically.
> > By validating it I mean making sure the table and field names are correct,
> > the data types match, but not actually execute it.
> >
> > Ideas, suggestions ... much appreciated.
> >
> > Gigel
> >
> >
> >

"Erland Sommarskog" wrote:

> newbie (new***@newbie.com) writes:
> > Is there a way to validate or prepare a sql statement built dynamically.
> > By validating it I mean making sure the table and field names are correct,
> > the data types match, but not actually execute it.
>
> The best bet is probably to run the batch bracketed by SET FMTONLY ON and
> SET FMTONLY OFF.
>
> The purpose of SET FMTONLY is to find any result sets the batch may
> create. In this mode SQL Server sifts through the code, so it will find
> misspelled table names (which is the one thing which is hardest to verify).
> Unfortunately, FMTONLY can also cause some false alarms.
>
> Some errors like a mess-up in data types may not turn up until run-time
> as SQL Server unfortunately permit to many implicit conversions.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>