Linked server error with transaction

Last Post 30 Dec 2008 01:57 PM by timcronin. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timcronin
New Member
New Member

--
04 Dec 2008 08:17 AM
I am trying to write a transaction from one sql server 2005 server to another sql server 2005 server(currently both stand-alone, but will be clustered in the future). Here is my syntax

SET XACT_ABORT ON
begin distributed tran
SELECT newid()as cms_order_num,left(l.costcenter,6) as costcenter,
10 as requesttype,left(l.siteid,5) as instid,left(l.siteid,5) as test into #temporder
FROM medicationOrder o
inner join patient p on p.id = o.patientid
inner join medication m on o.medicationid = m.id
inner join cmssec..sec_user c on c.SU_USER_ID = o.PrescriberId
inner JOIN Location l ON o.ShipToLocationId = l.id
inner join cmsphsql.fac.dbo.facilities f on
f.facid = l.costcenter
left outer join cms_listphnpi s on c.su_user_id = s.su_user_id
where sendtopharmacy = 'true' and senttopharmacy = 'false'



INSERT INTO cmsphdb5.[Pharm].[dbo].[cms_medorderimport]
([cms_order_num] ,[facID],[requesttype],[instid],[siteid],[patientid],[lname],[FName],[birthdate],[sex]
,[EntryID] ,[PHID],[PHLname],[PHFname],[PHLicNo],
[PHDeaNo],[Orderdate],
[StopDate],[BrandName],[genericname]
,[Strength],[DosageForm] ,[SIG],[ExpSIG],[Duration],[QtyPerDose],[Quantity],[PhNPI],[pharmid]
,[EntryDate],[insert_date],[import_rec_status],[mardbname],[profileonly],[nsid],[oldfacid],[ndc_id])

select cms_order_num,costcenter as facid,requesttype,instid, siteid,inmateid,patlname,patfname,dateofbirth,gender,
entryid,prescriberid,phlname,phfname,phlicno,
phdeano,convert(varchar(10),convert(datetime,orderdate),101) + ' '+ convert(varchar(08),convert(datetime,orderdate),108) as orderdate,
convert(char(10),stopdate,101) as stopdate,brandname,genericname,strength,form,sig,expsig,duration,qtyperdose,
quantity,phnpi,pharmid,entrydate,insert_date,import_rec_status,mardbname,
profileonly,'' as nsid,'' as oldfacid, ndc
from #temporder
--update sent records
update medicationOrder
set
SentToPharmacy = 1,
SentToPharmacyDate = getdate()
where medicationOrder.id in (select entryid from #temporder)
drop table #temporder
commit tran



I get the error

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

I thought what I have about the exact abort and distributed tran was all I needed
timcronin
New Member
New Member

--
30 Dec 2008 01:57 PM
Yes on both
You are not authorized to post a reply.

Acceptable Use Policy