Issues copying data to linked server

I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.

I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):

Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:

EXEC procStmtDataToReno 'B'

and some snippets of the SP:

CREATE PROCEDURE [dbo].[procStmtDataToReno]

@.CoCode as varchar(2)

AS

--various declarations and such

DELETE
FROM [10.1.1.4].OnlineAR.dbo.StatementData
WHERE CompanyID = @.CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )

--code to build temp table

INSERT INTO [10.1.1.4].OnlineAR.dbo.StatementData
SELECT * FROM #Statement
GO

TIAI normally would create a stored procedure on the remote server side and call it via openquery() or sp_executesql. That is the *fastest* way to do and DML.

Anyway, I suggest you take a look at the following article for guidance on resolving 7391 error.

http://support.microsoft.com/kb/306212|||Thanks for that. Because some companies worked, my suspicion all along was that it was "data" related rather than "process" related. I finally stumbled into the answer late last night. I had tried changing the SP to simply select the records, and it ran fine from Query Analyzer. I changed the SP back to an INSERT and executed it again from QA, and it failed, but I finally got a message that made sense:

Server: Msg 8152, Level 16, State 4, Procedure procStmtDataToReno, Line 389
String or binary data would be truncated.
The statement has been terminated.

My problem was as simple as tracking down the field that was wider on the source side than it was on the destination side. The process now works fine. I just didnt know what tool to use to get better info on what was going wrong.

I am interested in your recommendation to set it up on the remote side. I would have thought it was better to have the SP on the source side, so it would only have to transfer the results of the process over the wire. If the SP were on the destination side, wouldnt it have to bring all the data across to work with?|||yes, you have to pass the data to the remote server in order for it to be processed.

e.g.

@.sql='exec my_sp ' + @.inputvar

exec linked.db..sp_executesql @.sql