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_DBdatamsmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:ACS_DBlogsmsmprim_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