December 2, 2009 at 11:11 am
Have 1 (out of 50 identical ) stored proceedure that is locking the DTC service on SQL 2000 (Windows 2000).
This sp creates a temp table to get and process data from a linked server (SQL Desktop). Ran fine for 3 years ---problem started when we physically moved the server - but on the same subnet.
The SQL runs fine outside of the proc (ie outside the begin / commit). The linked server is in a mfg production enviornment so constraints on poking around on it. Can ping etc... and like stated previously SQL runs fine outside the proc.
I am at a loss -
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create proc dbo.xdatabaseSendDataFromLinkedServer_xdatabasePROD
as
begin
declare @cMachineNetworkName as varchar(255);
set @cMachineNetworkName = 'LinkedServerNN'
-- Get the runs, store them, and update the dtMdTstamp
create table #sendData
([uRunGuid] [uniqueidentifier] NOT NULL)
--***Locks up DTC right here***
insert into #sendData
select uRunGuid
from [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseSendData
where dtCrTstamp = dtMdTstamp
and uRunGuid not in (select uRunGuid from xdatabase.dbo.xdatabaseRun)
-- Start a transaction here and rollback on error
SET XACT_ABORT ON
begin tran
update [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseSendData
set dtMdTstamp = current_timestamp
where uRunGuid in
(select uRunGuid
from #sendData)
-- Copy all the new operator's.
insert into xdatabase.dbo.xdatabaseOperator
select rowguid,uOperatorGuid,cOperatorBID,dtCrTstamp,dtLaTstamp,dtDiTstamp,cOperatorDescription,dtMdTstamp
from [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseOperator
where uOperatorGuid not in
(select uOperatorGuid
from xdatabase.dbo.xdatabaseOperator)
-- Copy the new runs.
insert into xdatabase.dbo.xdatabaseRun
select *
from [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseRun
where uRunGuid in (select uRunGuid
from #sendData)
print 'xdatabaseRun Insert/Update Complete'
-- Copy the elements related to the new runs.
set identity_insert xdatabase.dbo.xdatabaseElement on
insert into xdatabase.dbo.xdatabaseElement
(rowguid,uElementGuid,uRunGuid,dtCrTstamp,dtMdTstamp,uOperatorGuid,nElementID)
select rowguid,uElementGuid,uRunGuid,dtCrTstamp,dtMdTstamp,uOperatorGuid,nElementID
from [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseElement
where uRunGuid in (select uRunGuid
from #sendData)
set identity_insert xdatabase.dbo.xdatabaseElement off
-- Copy the data.
insert into xdatabase.dbo.xdatabaseData
select *
from [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseData
where uElementGuid in
(select uElementGuid
from [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseElement
where uRunGuid in
(select uRunGuid
from #sendData))
-- Everything looks good, make it stick.
commit tran
-- Clean things up
drop table #sendData
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
December 2, 2009 at 12:45 pm
Anything in the error log? Windows application/system logs?
Joie Andrew
"Since 1982"
December 2, 2009 at 2:38 pm
Yup... I guess I better schedule some down time for the linked server so I can see the settings; Run DTCPing etc...
Now that I've written this all down I just bet a $1 DTC is not set to automatic on the linked server.
MS DTC is unable to communicate with MS DTC on a remote system. No common RPC protocol is supported between the two systems. Please ensure that one or more of the following RPC protocols are common to both systems: TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:209, Pid: 3096, CmdLine: C:\WINNT\System32\msdtc.exe
December 2, 2009 at 5:20 pm
Humm would lose my $1.
Now I'm really lost.
If I do a very basic query
begin
select * from linkeddatabase.tablename
end
it runs fine
If I select and run
begin
declare @cMachineNetworkName as varchar(255);
set @cMachineNetworkName = 'LinkedServerNN'
-- Get the runs, store them, and update the dtMdTstamp
create table #sendData
([uRunGuid] [uniqueidentifier] NOT NULL)
--***Locks up DTC right here***
insert into #sendData
select uRunGuid
from [LinkedServer\xdatabasePROD].xdatabase.dbo.xdatabaseSendData
where dtCrTstamp = dtMdTstamp
and uRunGuid not in (select uRunGuid from xdatabase.dbo.xdatabaseRun)
end
It runs and doesn't lock up at the point specified.
:crazy:
Any help would be mucho appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply