SPROC compiles fine with missing object

[DugsDMan]

Can someone shed some light on this? I imagine it's just a setting somewhere, but it has me a little worried.

I have a dev environment, running on a virtual server. I created a proc on it and ran the proc. All is well, so I move the proc up to our parallel environment, not a virtual server. The next day, I went through and cleaned up a few joins, then compiled. I received the normal command completed successfully message. After that, I moved the proc up to parallel and compiled. There, it erred saying:

Msg 207, Level 16, State 1, Procedure MyProc, Line 277
Invalid column name 'MyColumn'.

I checked and sure enough, I left out a column I'm trying to use in an update statement. I would expect this to happen, however it doesn't do this in my dev environment.

I even went so far as to change the table name from MyTable to dugsdman..MyTable. In dev, even this compiles fine. There is not a dugsdman database. I know that the proc will error when I try to run it, but I'm curious to find out why my dev env will let it compile though...

It's easy to find info on the Net about sprocs erring, but not so much when trying to find something about one that doesn't.

Any help on this is appreciated.

 

[gmmastros]

http://msdn.microsoft.com/en-us/library/ms190686.aspx

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DugsDMan]

Thanks George. I read the post, but don't see anyway to turn it off/on. If there a way to do this? I'm interested in finding out why it errs in one environment, but not the other. Both are running the exact same version of SQL Server 2005. The parrallel environment was even created by restoring a full backup up the dev environment. This behavior holds true on other databases in the dev env, so it's telling me it's at the server level...

 

[markros]

Should be one of the Database options.

 

[markros]

No, I spoke too soon - I went through all the options and none seemed to apply.

 

[DugsDMan]

Yeah, I went through the options for the database and the server. I don't see anything I can set to turn it off or on. Does anyone know how this behavior can be different between the two?

 

[markros]

I found these two helpful links

http://sqladvice.com/blogs/gstark/a...ion-Effects-Stored-Procedure-Performance.aspx

and

http://www.karaszi.com/SQLServer/info_sp_recompile_set.asp