causes error and its not even run, eh? 
Author Message
 causes error and its not even run, eh?

Hi,

I have a SQL Script which can be run periodically against a DB to check for
data errors and suggest the appropriate fix.
This script has to work with DBs that run with different versions of the
same system. Therefore for one data check I put the following statement:

IF EXISTS(
 SELECT SO.[ID], SO.[Name], SC.[ID], SC.[Name]
 FROM sysobjects AS SO INNER JOIN syscolumns AS SC ON SO.[ID] = SC.[ID]
 WHERE SO.[Name] = 'Doc' AND SC.[Name] = 'ParentID'
)
BEGIN
            <more sql here>
END

The theory being that this particular check will only be performed if a
specific attribute of a given relation exists.
However when I attempt to run the whole script against a DB it gives me an
invalid column name error on the sql between the begin/end even though it's
not actually being run.
Is there anyway around this?

Regards,
Peter Row



Sat, 26 Feb 2005 23:23:31 GMT
 causes error and its not even run, eh?

I do not believe so.  The reason you're getting this is because SQL uses a
query optimizer which is very similar to a "just-in-time" compiler.  The
entire query batch must be compiled before any portion of it actually runs.
If the <more sql here> references a column that doesn't exist, the compile
will fail before your IF statement is even considered.

The only thing I can think of that might work (although probably isn't
feasible) is to copy the entire dataset to a temp table, and perform the
work there.  A temp table doesn't have to pre-exist for the query to
compile.

--
Toby Herring
Software Architects, Inc.
MCDBA, MCSD


Quote:
> Hi,

> I have a SQL Script which can be run periodically against a DB to check
for
> data errors and suggest the appropriate fix.
> This script has to work with DBs that run with different versions of the
> same system. Therefore for one data check I put the following statement:

> IF EXISTS(
>  SELECT SO.[ID], SO.[Name], SC.[ID], SC.[Name]
>  FROM sysobjects AS SO INNER JOIN syscolumns AS SC ON SO.[ID] = SC.[ID]
>  WHERE SO.[Name] = 'Doc' AND SC.[Name] = 'ParentID'
> )
> BEGIN
>             <more sql here>
> END

> The theory being that this particular check will only be performed if a
> specific attribute of a given relation exists.
> However when I attempt to run the whole script against a DB it gives me an
> invalid column name error on the sql between the begin/end even though
it's
> not actually being run.
> Is there anyway around this?

> Regards,
> Peter Row



Sat, 26 Feb 2005 23:50:48 GMT
 causes error and its not even run, eh?
Hi,

Thanks for the reply.
But that won't work because the invalid column error would still occur when
trying to get the data.
If only there was a way to tell SQL at the top of the script not to optimise
it. Or if it was doing doing
just-in-time compiling that would be even better cause this probably
wouldn't occur in my script then.


COUNT(.......
so I can report how many items are affected and if any are instruct the
person using the script to run the right fix script.

I tried using dynamic SQL with an EXEC but there doesn't seem to be a way to
return the count from the query in order to set

Bye,
Peter Row


Quote:
> I do not believe so.  The reason you're getting this is because SQL uses a
> query optimizer which is very similar to a "just-in-time" compiler.  The
> entire query batch must be compiled before any portion of it actually
runs.
> If the <more sql here> references a column that doesn't exist, the compile
> will fail before your IF statement is even considered.

> The only thing I can think of that might work (although probably isn't
> feasible) is to copy the entire dataset to a temp table, and perform the
> work there.  A temp table doesn't have to pre-exist for the query to
> compile.

> --
> Toby Herring
> Software Architects, Inc.
> MCDBA, MCSD



> > Hi,

> > I have a SQL Script which can be run periodically against a DB to check
> for
> > data errors and suggest the appropriate fix.
> > This script has to work with DBs that run with different versions of the
> > same system. Therefore for one data check I put the following statement:

> > IF EXISTS(
> >  SELECT SO.[ID], SO.[Name], SC.[ID], SC.[Name]
> >  FROM sysobjects AS SO INNER JOIN syscolumns AS SC ON SO.[ID] = SC.[ID]
> >  WHERE SO.[Name] = 'Doc' AND SC.[Name] = 'ParentID'
> > )
> > BEGIN
> >             <more sql here>
> > END

> > The theory being that this particular check will only be performed if a
> > specific attribute of a given relation exists.
> > However when I attempt to run the whole script against a DB it gives me
an
> > invalid column name error on the sql between the begin/end even though
> it's
> > not actually being run.
> > Is there anyway around this?

> > Regards,
> > Peter Row



Sun, 27 Feb 2005 16:32:13 GMT
 causes error and its not even run, eh?
If your situation allows dynamic SQL (there can be security issues) Then it
might work.

If all you're doing is retrieving a count (this can be expanded to do more,
but I'll to the count for an example.)


CREATE TABLE #Cnt (cnt int)



SELECT cnt FROM #Cnt

--
Toby Herring
Software Architects, Inc.
MCDBA, MCSD


Quote:
> Hi,

> Thanks for the reply.
> But that won't work because the invalid column error would still occur
when
> trying to get the data.
> If only there was a way to tell SQL at the top of the script not to
optimise
> it. Or if it was doing doing
> just-in-time compiling that would be even better cause this probably
> wouldn't occur in my script then.


> COUNT(.......
> so I can report how many items are affected and if any are instruct the
> person using the script to run the right fix script.

> I tried using dynamic SQL with an EXEC but there doesn't seem to be a way
to
> return the count from the query in order to set

> Bye,
> Peter Row



> > I do not believe so.  The reason you're getting this is because SQL uses
a
> > query optimizer which is very similar to a "just-in-time" compiler.  The
> > entire query batch must be compiled before any portion of it actually
> runs.
> > If the <more sql here> references a column that doesn't exist, the
compile
> > will fail before your IF statement is even considered.

> > The only thing I can think of that might work (although probably isn't
> > feasible) is to copy the entire dataset to a temp table, and perform the
> > work there.  A temp table doesn't have to pre-exist for the query to
> > compile.

> > --
> > Toby Herring
> > Software Architects, Inc.
> > MCDBA, MCSD



> > > Hi,

> > > I have a SQL Script which can be run periodically against a DB to
check
> > for
> > > data errors and suggest the appropriate fix.
> > > This script has to work with DBs that run with different versions of
the
> > > same system. Therefore for one data check I put the following
statement:

> > > IF EXISTS(
> > >  SELECT SO.[ID], SO.[Name], SC.[ID], SC.[Name]
> > >  FROM sysobjects AS SO INNER JOIN syscolumns AS SC ON SO.[ID] =
SC.[ID]
> > >  WHERE SO.[Name] = 'Doc' AND SC.[Name] = 'ParentID'
> > > )
> > > BEGIN
> > >             <more sql here>
> > > END

> > > The theory being that this particular check will only be performed if
a
> > > specific attribute of a given relation exists.
> > > However when I attempt to run the whole script against a DB it gives
me
> an
> > > invalid column name error on the sql between the begin/end even though
> > it's
> > > not actually being run.
> > > Is there anyway around this?

> > > Regards,
> > > Peter Row



Sun, 27 Feb 2005 21:46:27 GMT
 causes error and its not even run, eh?
On Wed, 11 Sep 2002 09:46:27 -0400, "Toby Herring"

Quote:

>If your situation allows dynamic SQL (there can be security issues) Then it
>might work.

This won't work either, simply because the select statement of the
exists clause is restricted.

Are you dealing with different *software* versions or different
*databases*? The reason is that the posted query:

SELECT SO.[ID], SO.[Name], SC.[ID], SC.[Name]
 FROM sysobjects AS SO INNER JOIN syscolumns AS SC ON SO.[ID] =
SC.[ID]
 WHERE SO.[Name] = 'Doc' AND SC.[Name] = 'ParentID'

should work on SQL Server 7+. If you are trying to figure out whether
a column name exists, instead of exists, why not modify the statement
below to use count?



 FROM sysobjects AS SO INNER JOIN syscolumns AS SC ON SO.[ID] =
SC.[ID]
 WHERE SO.[Name] = 'Doc' AND SC.[Name] = 'ParentID'


begin
        select 'Using version with Doc table'
end
else
begin
        select 'Using verson without Doc table'
end



Mon, 28 Feb 2005 04:54:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Replication not running even manual Start

2. OLE DB prov in MDAC 2.6/2.7 causes SS2000 to not run deletes properly - help

3. Automatic SQL Server Restart caused job not to run--PLEASE HELP

4. xp_sendmail Causes ODBC tran to ABORT when SQLMAIL is not running

5. xp_sendmail causes ODBC tran to ABORT when SQLMAIL not running

6. Error running SP as Job but not when run from Query Analyzer

7. lp1 reported invalid error status (on fire, eh?)

8. valid errors cause vb to stop running

9. Scripting.Dictionary causes error when running package outside of designer

10. DTS Running DTSRUN Using the NT Scheduler causes Error

11. loading more than 1 crystal report cause a run time error

12. Debug gets results, but run causes error


 
Powered by phpBB® Forum Software