Problem with linking servers

Hi.

I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQL
Server' to link one to the other and also vise versa.

Each server has two users permissioned.

My problem is when ever I try to do something that does a remote write I get
the follow error message
Microsoft OLE DB Provider for SQL Server error '80040e14'

[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Also

when I try and manually run a stored procedure, I get:

Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.

(1 row(s) affected)

(1 row(s) affected)

(50 row(s) affected)

Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
65
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
nested transaction was required because the XACT_ABORT option was set to
OFF.
[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Can anyone suggest how I resolve this.

FYI

Database Creation Script: (both database are the same scripts but db names
are changed)

CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' , SIZE
= 2000, FILEGROWTH = 10%)

COLLATE Latin1_General_CI_AS

GO

exec sp_dboption N'msmprim', N'autoclose', N'false'

GO

exec sp_dboption N'msmprim', N'bulkcopy', N'false'

GO

exec sp_dboption N'msmprim', N'trunc. log', N'false'

GO

exec sp_dboption N'msmprim', N'torn page detection', N'true'

GO

exec sp_dboption N'msmprim', N'read only', N'false'

GO

exec sp_dboption N'msmprim', N'dbo use', N'false'

GO

exec sp_dboption N'msmprim', N'single', N'false'

GO

exec sp_dboption N'msmprim', N'autoshrink', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI null default', N'false'

GO

exec sp_dboption N'msmprim', N'recursive triggers', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI nulls', N'false'

GO

exec sp_dboption N'msmprim', N'concat null yields null', N'false'

GO

exec sp_dboption N'msmprim', N'cursor close on commit', N'false'

GO

exec sp_dboption N'msmprim', N'default to local cursor', N'false'

GO

exec sp_dboption N'msmprim', N'quoted identifier', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI warnings', N'false'

GO

exec sp_dboption N'msmprim', N'auto create statistics', N'true'

GO

exec sp_dboption N'msmprim', N'auto update statistics', N'true'

GO

User Creation Script:

/****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/

use [msmprim]

GO

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

GO

use [msmprim]

GO

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

GOI have got a little further, by adding 'SET XACT_ABORT ON' I have mangaged
to get the remote write to work.

Can someone tell me what the implication of setting 'SET XACT_ABORT ON' is
and what effect will it have on my SPs. Will they still roll back as
before?

Sample SP

procedure [msm].ams_Insert_Audit
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strSCRIPT varchar(128),
@.strLOGINID varchar(32),
@.strVFEID varchar(16),
@.strBILLORGID varchar(16),
@.strRETAILERID varchar(16),
@.strLOCATIONID varchar(16),
@.strPOSTID varchar(16),
@.strXACTIONID varchar(16),
@.strCODE varchar(64),
@.strDATA varchar(1024)
as
declare @.strStep varchar(32)
declare @.trancount int

set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
begin tran ams_Insert_Audit
else
save tran ams_Insert_Audit

SET XACT_ABORT ON

set @.strStep = 'Writing MSM Audit'

insert into
HAN.msmprim.msm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

set @.strStep = 'Writing PSM Audit'

insert into
ACSPSM.psmprim.psm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

if (@.@.error <> 0)
begin
rollback tran ams_Insert_Audit
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : Error Occured'
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran ams_Insert_Audit

return 0

Regards

Steve

"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkeqcl$h14$1@.titan.btinternet.com...
> Hi.
> I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM',
N'SQL
> Server' to link one to the other and also vise versa.
> Each server has two users permissioned.
> My problem is when ever I try to do something that does a remote write I
get
> the follow error message
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
> Also
> when I try and manually run a stored procedure, I get:
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (50 row(s) affected)
> Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
> 65
> Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
> nested transaction was required because the XACT_ABORT option was set to
> OFF.
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
>
> Can anyone suggest how I resolve this.
>
>
> FYI
> Database Creation Script: (both database are the same scripts but db names
> are changed)
> CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
> N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
> (NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' ,
SIZE
> = 2000, FILEGROWTH = 10%)
> COLLATE Latin1_General_CI_AS
> GO
> exec sp_dboption N'msmprim', N'autoclose', N'false'
> GO
> exec sp_dboption N'msmprim', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'msmprim', N'trunc. log', N'false'
> GO
> exec sp_dboption N'msmprim', N'torn page detection', N'true'
> GO
> exec sp_dboption N'msmprim', N'read only', N'false'
> GO
> exec sp_dboption N'msmprim', N'dbo use', N'false'
> GO
> exec sp_dboption N'msmprim', N'single', N'false'
> GO
> exec sp_dboption N'msmprim', N'autoshrink', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'msmprim', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'msmprim', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'msmprim', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'msmprim', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'msmprim', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'msmprim', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'msmprim', N'auto update statistics', N'true'
> GO
>
>
> User Creation Script:
> /****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO