Old SP codes were used after making change to SP

Database
Teradata Employee

Old SP codes were used after making change to SP

What can be the cause that when the SP is runned with different Users, by one user the correct and actuall definition was runned
     but by the second user the old version of the SP was runned?

4 REPLIES
Junior Contributor

Re: Old SP codes were used after making change to SP

There's another (old) copy of the SP in a different database and the 2nd called this.

 

Check dbc.TablesV for duplicate SP names.

Teradata Employee

Re: Old SP codes were used after making change to SP

Thanks Dnoeth for the quick response . will check and update based on my analysis.

 

Best Regards,

Sunder

Teradata Employee

Re: Old SP codes were used after making change to SP

Hi  dnoeth,

 

Actually the issue at hand is that when the SP was call (it does not matter with which user) it sometimes uses old codes.
i am concerned about the fact that i am  not sure of which code will be runned whenever the SP is called.
The SP has seen been dropped and recreated. However i need an assurance that whenever a SP or Function is created, it is the same version that runned across all nodes.
Secondly i would like to know what happened in those cases where the old SP called were run instead of the current code.

In OLD code it is like
 SELECT coalesce(max(run_id), -1) + 1 FROM ADMINGHMI.t_loadlog WHERE loadtype = 'BED' INTO :run_id_new;
 
 and in new code it is comming like :
 
  SELECT coalesce(max(run_id), 0) + 1 FROM ADMINGHMI.t_loadlog WHERE loadtype = 'BED' INTO :run_id_new;
 
  sometimes the old statement with -1 is comming whenrunning the sp and sometime the new statement is comming 0.

 

Could you please explain me why this is happening.

 

 

Highlighted
Teradata Employee

Re: Old SP codes were used after making change to SP

Did the dbc.tablesV analysis show you where the duplicate procedure was? and, of course, did you drop all other versions?

 

Other than ensuring that there is only one version in common databases like syslib, td_sysfnlib the only way to ensure what version is to hardcode the database prefix.

 

But, if you have cleaned up dupes should not be needed.

 

thanks

 

Dave