there was an "sa" local login with the Impersonate box checked. This makes sense because that login obviously exists on the linked server. There was also my windows login with the Impersonate box unchecked, but the "sa" remote user and password were populated. I added the NT Authority\System as a local login with the "sa" remote user login and it worked.
The statement which was causing the failure was a BCP command which sent data to a table on the linked server. apparently even though the BCP was called from a stored proc, it runs under the local system (server which SQL resides on) login of NT Authority\System. The BCP command uses the "sa" login for the remote server. So, I REALLY couldn't understand why there would be a login problem.
The weird thing is that this job has been running every day for almost a year. When I implemented it, I never looked at the linked server logins because we query it all the time. If we had Windows auto updates turned on I could blame it on an update changing the linked server logins, but we don't keep auto updates turned on for servers. I don't know what changed but it's working now.
Thanx for your reply. You were right on the money for the cause of the problem!
Sunnyland Farms, Inc.
"We must endeavor to persevere."