x

linked server

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
more ▼

asked Feb 28, 2012 at 11:03 AM in Default

jhunu gravatar image

jhunu
90 29 30 31

Yeah, Linked Server? Whats next? :) I think you forgot to add details.
Feb 28, 2012 at 11:07 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
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.
more ▼

answered Feb 28, 2012 at 11:19 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

The job has appropriate rights?? Do you know who is the owner of the Job/specific task? Is it the same user used in the SSMS? Please keep in mind that Jobs by default would run under SQL Service Agent service account, if no specific user is supplied.
Feb 28, 2012 at 11:44 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x108

asked: Feb 28, 2012 at 11:03 AM

Seen: 699 times

Last Updated: Feb 28, 2012 at 11:44 AM