Stored Procedure Compilation (1 Viewer)

[WayneRyan]

Hello,

I have a SQL Server 2000 instance that has started behaving differently when
saving Stored Procedures.

In the past, when saving an SP, it had to be correct in both syntax and
references to objects. If there was a syntax error, or an object did
not exist, the SP wouldn't save.

That was a good thing.

Lately, as long as an SP has the correct syntax, it will save.

Select SomeColumn
From NonExistant table

The above will not throw an error until run-time.

Is this a selectable switch for the server?

This happens with both Enterprise Manager and the SQL Server Management Studio.

Any ideas?

Thanks,
Wayne

 

[WayneRyan]

It is called Deferred Name Resolution.

It appears that using the SP --> sp_dbcmptlevel 60

http://msdn.microsoft.com/en-us/library/aa259649(v=sql.80).aspx

From Web-Site:

"When a batch or procedure contains invalid object names, a warning is returned when
the batch is parsed or compiled, and an error message is returned when the batch is
executed. "

I need to research the "side effects" of setting this, but it must have been set before
since I used to see the Compiler Errors (warnings).

Any thoughts?

Wayne

 

[SQL_Hell]

Peronally I wouldn't change the compability level to 60 because any code that is 2000 specific will not work properly

compatability levels:

60 = SQL 6.0
65 = SQL 6.5
70 = SQL 7.0
80 = SQL 2000
90 = SQL 2005
100 = SQL 2008

I can't really see why this is an issue for you, do you regulary change store procedures without testing them? I guess the simplest solution is to ensure all stored procs are run post updating