SP should not compile but does

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SP should not compile but does

Author

Topic

Pennidren
Starting Member

3 Posts

Posted - 2008-04-10 : 10:46:24

*edited*

I have a script that creates a SP that makes reference to a table that doesn't exist. It fails (as well it should!) on our development instance. It executes without problem on our stage and production sql server 2000 instances.

For example, this executes without issue:


CREATE  procedure [dbo].[SHOULDNOTCOMPILE]
as
insert into TableThatExists
  (FieldThatDoesNotExist)
values
  (null)

There is no FieldThatDoesNotExist field in TableThatExists.

This seems like a configuration difference between the instances, but I am at a loss for what that would be.

Execution of the SP fails on stage and production, thankfully. If it didn't I would question my sanity!

Any help appreciated,
Penn

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 10:51:07

Object references in SP are not verified until execution time, so the compilation of SP won't give any error.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-10 : 10:52:49

i think i would expect it to be created but maybe chuck a warning about dependancies or similar. when it fails to be created, what is the error?

Em

Pennidren
Starting Member

3 Posts

Posted - 2008-04-10 : 11:05:10

OK, actually select * from foo was not a good example because that succeeds in dev too.
Here is a better example:


CREATE  procedure [dbo].[SHOULDNOTCOMPILE]
as
insert into TableThatExists
  (FieldThatDoesNotExist)
values
  (null)

In development this gives the following error


Invalid column name 'FieldThatDoesNotExist'

In stage and production the command completes successfully.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 11:35:08

It will compile Ok if the table does not exist, but it will fail if the table exists but does not have that column.

CODO ERGO SUM

Pennidren
Starting Member

3 Posts

Posted - 2008-04-10 : 11:41:54

Figured it out. And I didn't explain the situation properly even when I tried again, so let me finally explain it correctly.


CREATE  procedure [dbo].[SHOULDNOTCOMPILE]
as
insert into TableThatExists
  (FieldThatDoesNotExist)
select 
  case when FIELD in (select ANOTHERFIELD from TableThatExistsInDev) then 1 else 0 end as [IAmAMoron]

The problem was that TableThatExistsInDev exists in dev but does not exist in stage and prod.

As Harsh said, the object reference is not verified until execution time, at which point it fails.

In development, since it exists, some further compilation is done and the compiler notices that FieldThatDoesNotExist is not in TableThatExists.

To summarize, this is frustrating. It seems obvious that the compiler gives up if it finds one reference it doesn't recognize -- even if it has no impact on compiling other portions.

FieldThatDoesNotExist does not exist, independent of whether TableThatExistsInDev exists or not, so why can't the compiler say as much?

Thanks for the replies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources