Problem with linking servers

[Steve Thorpe]

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

[Steve Thorpe]

I 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" <st***********@nospam.hotmail.com> wrote in message
news:bk**********@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