Functions and Recompile

Hi,
I just had a strange occurrance on one of my SQL 2000 SP3 machines. (Win 200
3)
I changed the underlying table structure of two table, inserting a field in
the middle of the tables. All of the functions that referenced these did no
t
recompile when I next ran them. They starting giving me errors with data
conversion (Explicit from Varchar to money not allowed). This continued
until I explicitly ran sp_recompile on the function. I had to do this to al
l
of my functions that referenced this table to get them to work.
On another server I did the exact same thing to the exact same tables (the
dev system) and
all of the functions automatically recompiled on the next run.
My question I guess is...
Is there a setting to turn off/on auto recompile? I didn't think there was,
but that's the only reason I can think of the difference.
Or does anyone else have any ideas?
RyanHello Ryan,
Do you know if you turned off Auto update statistics on one database but
not the other?
Aaron Weiker
http://aaronweiker.com/

> Hi,
> I just had a strange occurrance on one of my SQL 2000 SP3 machines.
> (Win 2003)
> I changed the underlying table structure of two table, inserting a
> field in the middle of the tables. All of the functions that
> referenced these did not recompile when I next ran them. They
> starting giving me errors with data conversion (Explicit from Varchar
> to money not allowed). This continued until I explicitly ran
> sp_recompile on the function. I had to do this to all of my functions
> that referenced this table to get them to work.
> On another server I did the exact same thing to the exact same tables
> (the dev system) and all of the functions automatically recompiled on
> the next run.
> My question I guess is...
> Is there a setting to turn off/on auto recompile? I didn't think
> there was,
> but that's the only reason I can think of the difference.
> Or does anyone else have any ideas?
> Ryan
>|||Both of the DBs have auto update statistics set to on.
Thanks for the idea tho'.
Ryan
"Aaron Weiker" wrote:

> Hello Ryan,
> Do you know if you turned off Auto update statistics on one database but
> not the other?
> --
> Aaron Weiker
> http://aaronweiker.com/
>
>
>|||Hello Ryan,
Only other idea I have would be to search through the MS KB and see if there
is anything there about it. But it is definately a pretty odd occurance.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/
> Both of the DBs have auto update statistics set to on.
> Thanks for the idea tho'.
> Ryan
> "Aaron Weiker" wrote:
>