Re: DBDATE format issues

david@smooth1.co.uk wrote: > Make sure the session that creates the stored procedure or > does update statistics on the stored procedure has > the correct DBDATE setting. I haven't tried GL_DATE yet, but in our investigation, we have definitely had the correct DBDATE setting from within the session that was creating the SP and/or updating statistics. I've opened a case with tech support, but they haven't found the cause of the problem yet.

the date format of the stored procedure would be set by the session that last optimised the stored procedure So, what can optimise a stored procedure 1/ update statistics - you have already verified this has not happened 2/. the session that creates the procedure - you have already verfified this has not happened 3/ if the systables.version of a table changes , and update statistics has not been run again, any procedure that references that table will be re optimised the first time it is executed. things that can change the version number of a table include (but not limted to) adding columns, changing user privilidges I suspect that maybe number 3 has occurred and the proceidure has been reptimised by something other that the update statistics. I would guess that if you run the update stats the problem goes away for a while and then when something else changes the version number of the table the problem may come back again therefore the DBDATE setting of taht session will be picked up

No luck there either poet. I verified my DBDATE setting, ran update stats on the whole database, then reran the same execute function command with the same result. Then I went a step further. Since this is on a non 24x7 reporting database, I was able to again verify DBDATE, shutdown and restart IDS, run update stats, and I am still receiving the -1205 error, invalid month in date. The quest continues...