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 1 11678
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
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
7 posts
views
Thread by George Hernandez |
last post: by
|
1 post
views
Thread by Bren |
last post: by
|
7 posts
views
Thread by m.ramana |
last post: by
|
17 posts
views
Thread by Mitas Nikos |
last post: by
|
4 posts
views
Thread by Lars |
last post: by
|
1 post
views
Thread by buchalino |
last post: by
| |
3 posts
views
Thread by misscrf |
last post: by
|
2 posts
views
Thread by =?utf-8?B?UMSBdmVscyBNaWhhaWxvdnM=?= |
last post: by
| | | | | | | | | | |