I have 2 sql servers, one here the other remote. I am using tsql in a job to move data from local to remote. I need it to be one transaction. The code below works
(i know it needs error checking), but looking for any good url's or examples that might help lead me down a better path. I have noticed when this code hicupped it caused the remote server to hang (can't have this happen). Are they any other gotchas I need to know of. Thanks

begin distributed transaction
INSERT INTO cmsphdb2.Pharm.dbo.PhQueuePatients(FacID, PatID, PatLName, PatFName,
NsID, PhNPI,
PatStatus, SSN, MedRecNo, BirthDate, AdmDate, Sex,
PatMI, rectype,dbname)
SELECT q.FacID, q.PatID, q.PatLName,
q.PatFName,'Main',f.defaultphnpi,1 as patstatus, q.SSN, q.MedRecNo, q.BirthDate,
q.AdmDate, q.Sex,
q.patmi,
q.rectype, q.dbname
FROM OEQMGR.dbo.QueuePatients q
inner join cmsphdb2.fac.dbo.facilities f on f.facid = q.facid
where q.SentFWFlag = 'n'
--update records sent
update
OEQMGR.dbo.QueuePatients
set sentfwflag = 'Y',
sentfwdatetime = getdate()
where SentFWFlag = 'n'
commit transaction