Sql-server – Linked Server Error on SQL Agent Job

linked-serversql serversql-server-2008-r2sql-server-2012sql-server-agent

I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine.
Why is this? Is it just SQL Agent that requires MSDTC?
I don't believe it is permissions issues as the job runs under a service account that is in sysadmin role.

Message

Executed as user: SERVERS\quinsql_sa. The operation could not be
performed because OLE DB provider "SQLNCLI10" for linked server
"TestServer" was unable to begin a distributed transaction. [SQLSTATE
42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server
"TestServer" returned message "The transaction manager has disabled
its support for remote/network transactions.". [SQLSTATE 01000] (Error
7412). The step failed.

The involved versions are SQL Server 2008R2 and SQL Server 2012. The SQL Job is on the 2008R2

Best Answer

I've not seen this exactly before, it does seem strange that you can run the query manually but not as an agent job. This is not unheard of though SQL Server Central has a similar post a couple of years ago, but has some reasonably complex queries involved.

What is the query you're trying to run doing?

As Tom V said, it would be helpful to know what version of SQL Server you're both running the query from, and where the linked server is connecting to

When you run the query manually:

  • Is it everything that the Agent job is doing (There isn't more than one step in the Agent job)?
  • Are you using the same account as the Agent job is run as?

As a start, it's worth checking your MSDTC configuration on both servers. I don't know if it'll be that in this case but it's best to try and eliminate simple things first. Have a look at these sites to get some details on that:

https://stackoverflow.com/questions/10130767
MSDN

Related Question