Stored Procedure Using Remote Connection gives error in Agent Job

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Mike Dale (MikeDale_at_discussions.microsoft.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 15:51:03 -0700

Hey all,

I created a stored procedure that we use for balancing some datawarehouse data to a particular legacy system. It uses a distributed query through a remote server to a old Oracle RDB database. (The Linked server uses ODBC)

Anyways, the procedure works perfectly fine if I run it in query analyzer. It also works fine if I run it in query analyzer as the <DOMAIN>\<Account> account that the SQL Server Service runs under.

However, I want to use this query in a SQL Server Agent job step to control whether or not an incremental roll-up procedure kicks off each day or not. So, I created a TSQL Step in the Agent job, using the following syntax:

exec DWLottery.dbo.DRAW_INCR_RUN_CHECK

When I test this, the step fails and I get the following error:

Msg 7391, Sev 16: The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. [SQLSTATE 42000]
Msg 7312, Sev 16: Driver not capable.] [SQLSTATE 01000]
Msg 7300, Sev 16: OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000]

Any ideas as to how I can prevent this error, and use this procedure in a SQL Agent job?

Relevant Pages

  • Remote Indexing / Distributed Query
    ... I'm trying to access an Indexing Service located on a remote machine using ... On the first server, the Indexing Server is up & running; ... the Indexing Service is stopped. ... distributed query facility is limited to the server where both SQLServer and Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: faster joins
    ... A Distributed query is a query which involves databases on different ... With in the same server will the query be treated as ... > "Kevin Spencer" wrote in message ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Unable to drop subscription by calling sp from ASP.NET
    ... - Do you mean the merge agent job on the Distributor machine? ... The distributor is a remote Win2k3 server with IIS 6.0 and SQL Server ... The server does not have a user ASPNET, but NETWORK SERVICE, ...
    (microsoft.public.sqlserver.replication)
  • Distributed Query Using NT Auth
    ... I'm trying to get a distributed query to run from Query Analyzer on a client ... Server connection. ... Both SQL services running as serviceacct1 ... MSSQLSvc Server2 1433 ...
    (microsoft.public.sqlserver.security)
  • Re: faster joins
    ... With in the same server will the query be treated as ... distributed query. ... "Kevin Spencer" wrote in message ... >> I have two databases with in same sql server. ...
    (microsoft.public.dotnet.framework.aspnet)