October 26, 2011 at 1:43 pm
Just installed sql 2008 and trying to access it from old sql 2000 servers. Essentials of the problem are below:
--on SQL2008:
create table mytable (id int, txt char(1))
go
insert into mytable values(1,'a')
insert into mytable values(2,'b')
go
create procedure usp_test
as
select id,txt from mytable
go
--on sql2000 (it has sql2008 as a linked server):
if object_id('tempdb..#extraction') is not null drop table #extraction
create table #extraction (
[id] int,
[txt] char(2))
go
insert into #extraction exec [SQL2008].dbname.dbo.usp_test
The process is hanging (status 'running') until I restart a Distributed Transaction Coordinator on sql2000
If I am starting exec [SQL2008].dbname.dbo.usp_test, it's working ok
Can somebody help please?
October 26, 2011 at 2:59 pm
Provided I read that right.. You are trying to get data from a SQL 2008 into a SQL 2000.
If that is correct then you have two possible problems or both. The first is that the MS Distributed Transaction Controller (MSDTC) on the SQL 2008 Both may not be configured to allow external conenctions. you can see these instructions:
http://hspinfo.wordpress.com/2009/03/24/network-access-for-distributed-transaction-manager-msdtc-has-been-disabled/[/url]
The other is firewall issues, but since I haven't had to solve this one I can't really give much advice.
CEWII
October 27, 2011 at 7:18 am
Many thanks, after settings MSDTC on SQL2008 it's working now
October 27, 2011 at 8:59 am
Excellent, I had run accross this very problem but it was 2000 -> 2005. Nothing failed, just nothing went. Nearly caused a rollback of a HUGE deployment in 2008 until I found that by default it was configured differently than windows 2000.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply