Linked Server , Connection Broken Error - struggling for 4 hours now !

Hi guys,
I've been struggling with this for over 4 hours now but can't seem
the resoltuion anywhere.
I have a stored procedure that inserts into a table via a linked server
and I get the following error
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
My proc is something like this
drop procedure venkatlink
go
create procedure venkatlink
AS
DECLARE @.sql nvarchar(1000)
DECLARE @.Server NVARCHAR(100)
DECLARE @.ProjectID NVARCHAR(50)
BEGIN TRANSACTION
SAVE TRANSACTION tt
CREATE TABLE #test ( studyid int )
SET @.Server = 'srv1.DM.cp.'
SET @.ProjectID = 'BRX101'
SET @.SQL = 'INSERT INTO #test Select studyid from ' + @.Server +
'study where projectid = ' + '''' + @.ProjectID + ''''
exec (@.sql)
select * from #test
COMMIT TRANSACTION
go
-- Now if I get rid of the SAVE transaction and add SET XACT_ABORT ON ,
things are fine, however with the Savepoint ( which I need to rollback
selectively ), things just do not work.
MSDTC is working on both source and target servers.
WHat is also confusing is that all linked servers are created using
SQLOLEDB provider but it complains about ODBC. WHat's all this about?
Please help.
ThanksYour query would get promoted to distributed tran implicitly anyway. Also,
oledb provider for sql does not really support nested transaction,
xact_abort on ensures the entire tran is either committed or rolled back.
There is little you can do here since you're doing dml across data sources.
-oj
"drdeadpan" <vkat01-nospam@.yahoo.com> wrote in message
news:1143619565.075521.136120@.z34g2000cwc.googlegroups.com...
> Hi guys,
> I've been struggling with this for over 4 hours now but can't seem
> the resoltuion anywhere.
> I have a stored procedure that inserts into a table via a linked server
> and I get the following error
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> My proc is something like this
> drop procedure venkatlink
> go
> create procedure venkatlink
> AS
> DECLARE @.sql nvarchar(1000)
> DECLARE @.Server NVARCHAR(100)
> DECLARE @.ProjectID NVARCHAR(50)
> BEGIN TRANSACTION
> SAVE TRANSACTION tt
> CREATE TABLE #test ( studyid int )
> SET @.Server = 'srv1.DM.cp.'
> SET @.ProjectID = 'BRX101'
> SET @.SQL = 'INSERT INTO #test Select studyid from ' + @.Server +
> 'study where projectid = ' + '''' + @.ProjectID + ''''
> exec (@.sql)
> select * from #test
> COMMIT TRANSACTION
> go
>
> -- Now if I get rid of the SAVE transaction and add SET XACT_ABORT ON ,
> things are fine, however with the Savepoint ( which I need to rollback
> selectively ), things just do not work.
> MSDTC is working on both source and target servers.
> WHat is also confusing is that all linked servers are created using
> SQLOLEDB provider but it complains about ODBC. WHat's all this about?
> Please help.
> Thanks
>