I have a JOB in sql server 2005.The job has a step where in it is using a linked server for fetching values from remore server and insert in the current server . The script in the job when executed manually in a query analyser it executes as expected. But the same job when executed it give error message as The table either does not exist or the current user does not have permissions on that table. [SQLSTATE 42000] . Since its existing on the linked server due to which the script execute in the query analyser . But why does it throws error in the sql JOBS
Most probably, as stated in the error message, it could be the user permission issue. The SSMS user would have the necessary rights, but the Job / Task user would not have the permissions. Either, use the same user as used in SSMS for the job or change your linked server configuration to use a specific user credentials while connecting to the linked server. Whatever suits your environment.