Thanks Jack, Ramesh and all others for your replies. I will ask client to re-install SP2.

Below are the servers information where job is running and where it is getting failed:

Query output on server where job is running fine

Query - select @@version

Query Output -

Microsoft SQL...

There is no problem related to parameter as the same code is running fine in our local server and at another client's server

I ran the query - SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Below is the result:

9.00.3042.00SP2Standard Edition

I dont know how to check CU but running @@version gives the following result:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 ...

Yes we can independently run the stored procedure

Yes both servers are using service pack 2