Linked Server Error on SQL Agent Job

Question

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

Answer 1

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

• K09: The query is a stored proc , which runs a simple INSERT SELECT. That is all that the Job is doing. I had tested running the INSERT SELECT query separately and that worked. However, when I execute the Stored Procedure I get the error "The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "TestServer" was unable to begin a distributed transaction"
• Molenpad: I've had a couple of issues with this in the past. I found an answer to one such issue here, and the other issue was solved by enabling the RCP settings on the linked server properties.

Answer 2

OK. So the SQL Agent Job is calling a stored procedure. That stored procedure is running the INSERT SELECT query.

Running the INSERT SELECT outside of the stored procedure worked fine.

Executing the stored procedure caused the error

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "TestServer" was unable to begin a distributed transaction

To fix this I had to enable MSDTC on the server where the SQL Job/stored procedure was being called.

I'm still not quite sure why the INSERT SELECT query worked ok outside of the stored procedure.