Linked server error with transaction

Last Post 30 Dec 2008 01:57 PM by timcronin. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
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

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 = o.patientid
inner join medication m on o.medicationid =
inner join cmssec..sec_user c on c.SU_USER_ID = o.PrescriberId
inner JOIN Location l ON o.ShipToLocationId =
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],
,[Strength],[DosageForm] ,[SIG],[ExpSIG],[Duration],[QtyPerDose],[Quantity],[PhNPI],[pharmid]

select cms_order_num,costcenter as facid,requesttype,instid, siteid,inmateid,patlname,patfname,dateofbirth,gender,
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,
profileonly,'' as nsid,'' as oldfacid, ndc
from #temporder
--update sent records
update medicationOrder
SentToPharmacy = 1,
SentToPharmacyDate = getdate()
where 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
New Member
New Member

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

Acceptable Use Policy