473,573 Members | 2,862 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with linking servers

Hi.

I have two sql servers and have ran exec sp_addlinkedser ver '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\dat a\msmprim_Data. MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\log s\msmprim_Log.L DF' , 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.sysl ogins where loginname =
@sqlLoginName)

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

select @logindb = N'master'

if @loginlang is null or (not exists (select * from master.dbo.sysl anguages
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_grantdbacces s @sqlLoginName, @sqlLoginName

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

exec sp_addrolemembe r N'db_datareader ', @sqlLoginName

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

exec sp_addrolemembe r N'db_datawriter ', @sqlLoginName

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

exec sp_addrolemembe r 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.sysl ogins where loginname =
@sqlLoginName)

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

select @logindb = N'master'

if @loginlang is null or (not exists (select * from master.dbo.sysl anguages
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_grantdbacces s @sqlLoginName, @sqlLoginName

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

exec sp_addrolemembe r N'db_datareader ', @sqlLoginName

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

exec sp_addrolemembe r N'db_datawriter ', @sqlLoginName

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

exec sp_addrolemembe r N'db_owner', @sqlLoginName

GO
Jul 20 '05 #1
1 11774
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_Aud it
@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_Audi t
else
save tran ams_Insert_Audi t

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_Audi t
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_Audi t

return 0


Regards

Steve
"Steve Thorpe" <st***********@ nospam.hotmail. com> wrote in message
news:bk******** **@titan.btinte rnet.com...
Hi.

I have two sql servers and have ran exec sp_addlinkedser ver '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\dat a\msmprim_Data. MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\log s\msmprim_Log.L DF' , 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.sysl ogins where loginname =
@sqlLoginName)

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

select @logindb = N'master'

if @loginlang is null or (not exists (select * from master.dbo.sysl anguages 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_grantdbacces s @sqlLoginName, @sqlLoginName

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

exec sp_addrolemembe r N'db_datareader ', @sqlLoginName

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

exec sp_addrolemembe r N'db_datawriter ', @sqlLoginName

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

exec sp_addrolemembe r 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.sysl ogins where loginname =
@sqlLoginName)

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

select @logindb = N'master'

if @loginlang is null or (not exists (select * from master.dbo.sysl anguages 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_grantdbacces s @sqlLoginName, @sqlLoginName

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

exec sp_addrolemembe r N'db_datareader ', @sqlLoginName

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

exec sp_addrolemembe r N'db_datawriter ', @sqlLoginName

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

exec sp_addrolemembe r N'db_owner', @sqlLoginName

GO

Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2184
by: George Hernandez | last post by:
I have a site on a set of Linux Servers where my site is PHP enabled and I would like to prevent people from externally linking to content on my site and replace it with a warning image. I've researched the following to put in an .htaccess file on teh root of my server, but none of the methods or changes or suggestions I've found on the web...
1
1528
by: Bren | last post by:
I'm hoping somebody can help me with the following problem. I'm hoping it's not insurmountable. Here's a basic structure: class EventTarget { public: EventTarget(); virtual ~EventTarget();
7
8381
by: m.ramana | last post by:
Two SQL Servers 1) "Test" Payroll DB Transaction Table pr_SubmitTransaction in Payroll DB ******************************************** 2) "Production" Payroll DB Transaction Table Pr_SubmitTransaction in Payroll DB
17
6976
by: Mitas Nikos | last post by:
I am trying to use the library conio2 and though I have managed with a few relatively simple examples when i try to compile a program which conio's function clrscr is not in main() i get an error from the linker. undefined reference to `clrscr' ld returned 1 exit status I include the conio2.h on top of the file where clrscr() fn is. ...
4
2048
by: Lars | last post by:
Hi, I have problems building a simple handcoded C-extension (hello.c) with Cygwin and gcc3.4.4. I hope somebody has encountered the same problem before, and has some advice. The extension works just fine with Linux: gcc -c hello.c -I/usr/local/include/python2.4/ ld -shared hello.o -o hello.so -L /usr/local/lib/ -lpython2.4 -lc
1
2234
by: buchalino | last post by:
Hi Guys, Please can someone help me, I am having a linking problem . I am writing a socket program, the problem is just the linking . I am using VC++ In the process of the problem, I installed the MSDN service pack, but not sure if it has a specific directory to be installed in.
17
2943
by: Nitro | last post by:
I'm having problems getting my DB to Link to different SQL servers. I got it to work fine on my own machine, but when distributed it at the client, and to another office, the Link didn't work. It either timed out, or gave errors. I created a LinkTables Method (below) that would allow me to distribute the DB to the client running on another...
3
12718
by: misscrf | last post by:
In a database, I am creating a new db. From there, I am setting up the tables, so that I can eventually create a front end (usually access, but I may attempt to be brave and lose the shell.) Anyway, I want to use a table, read-only for a lookup. It exists in another database on our system. Is there a way for me to link it into this...
2
7066
by: =?utf-8?B?UMSBdmVscyBNaWhhaWxvdnM=?= | last post by:
Hello, I have a problem. I've linked MySql server to MsSql, in MySql I have a table with Latvian data(character set is ucs2, ucs2_general_ci) and the problem is that when I use openquery to read data from MySQL server, some characters are not translated correctly! I receive question symbols instead of Latvian special characters. Maybe...
0
7679
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7992
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7756
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8048
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5571
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5281
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3722
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2183
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1027
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.