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

Problem with Linked Servers

I am trying to link a sql 2000 server to a sql 2005 Server. I setup the link to the sql 2000 box from the sql 2005 (x64, Enteprrise) box using SSMS. Both boxes have the same Windows domain account that I'm using for auithentication. (I also selected the Impersonate option in SSMS) When I run a simple query on the sql 2005 box referencing the sql 2000 box ( select * from sql2000Box.DBName.dbo.tblName) I get the error message:

OLE DB provider "SQLNCLI" for linked server "sql2000Box" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "sql2000Box" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "sql2000Box".

Help!.

TIA,

Barkingdog

I called Microsoft on this one. They recommended that I enable RPC OUT on the server trying to link to the linked server. When I did I got another error message that appears explicilty in this article:

"You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server"

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I will see if this solution works.

Barkingdog

Problem with Link server from sql 2005 to sql 2005 - Openquery doesnt works

I have created a linked server on which following query works fine.

EXECUTE ('SELECT TOP 10 * FROM dummyOBJECTS') AT [REMOTE]

but the same statement executed with openquery

select * from openquery([remote],'select top 10 * from dummyObjects') returns following error.

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "remote" supplied inconsistent metadata for a column. The column "dummyObjectID" (compile-time ordinal 1) of object "select top 10 * from dummyobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Hi ck!

Could you remove all columns except dummyObjectID and run this again?

If this reproes, could you reply with a sequence of "CREATE TABLE" and "INSERT" statements that will allow me to repro this on my machine?

Problem with installation

Miha,
The following KB link may help
http://support.microsoft.com/defaul...b;en-us;841249. According
to the log (near the end), the connection is being broken when trying to
access the SQL Server. The Windows Firewall that was introduced with Windows
XP SP2 may be causing the problem. The link will provides directions on
configuring the firewall to allow SQL Server 2000 to operate. Hope this
helps. Thanks.
William Hutchison [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Miha" <miha.bernik@.isg.si> wrote in message
news:O$BtUGC9EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'm having problems installing MS SQL 2000 (Developer edition) Server on a
> Windows XP SP2 computer.
> It's a clean Windows XP installation, and it is not the first time the we
> install SQL 2k on a WinXP computer,so it's quite strange becasue quite at
> the end of the installation when I try to configure SQL server
> installation fails.
> I included a log-file that was generating during the installation.
> Any help or tips how to resolve this would be appriciated.
> Thank you all in advance
> Best regards
> Miha
>
>Hi,
I installed SQL 2k over WinXP SP1, and then, I upgraded to SP2...Any
problem...SQL Server is working very well.
You can try to install SQL Server SP3a...may be, this solve your problem.
Richard_SQL
DBA
"William Hutchison [MSFT]" wrote:

> Miha,
> The following KB link may help
> http://support.microsoft.com/defaul...b;en-us;841249. According
> to the log (near the end), the connection is being broken when trying to
> access the SQL Server. The Windows Firewall that was introduced with Windo
ws
> XP SP2 may be causing the problem. The link will provides directions on
> configuring the firewall to allow SQL Server 2000 to operate. Hope this
> helps. Thanks.
> --
> William Hutchison [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Miha" <miha.bernik@.isg.si> wrote in message
> news:O$BtUGC9EHA.1204@.TK2MSFTNGP10.phx.gbl...
>
>sql

Problem with executing Distributed Transactions on Linked Servers

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.
Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default...;en-us;306212. Check it, it
might help you.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>
|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:

> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS

Problem with executing Distributed Transactions on Linked Servers

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212. Check it, it
might help you.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:
> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS

Problem with executing Distributed Transactions on Linked Server

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.
Raja,
That error code you are getting - " 0x8004d01b" translates into - "The
Transaction Manager is not available". I think that seems to be the
problem. What are the OS of the boxes involved?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Problem with executing Distributed Transactions on Linked Server

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.Raja,
That error code you are getting - " 0x8004d01b" translates into - "The
Transaction Manager is not available". I think that seems to be the
problem. What are the OS of the boxes involved?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Problem with Delete function within a Gridview

I've got an issue that when I update a record in the gridview it works fine. When I click the delete link to remove the record from the database, I get the following error, "System.FormatException: Input string was not in a correct format". Part of the Stack Trace refers to "String oldValuesParameterFormatString". This parameter is in my SqlDataSource. It was dynamically created when I originally created the SqlDataSource with VWD 2005 Express Edition. The delete function will work if I remove "OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient",and any reference to "original_" in the DeleteCommand the SqlDataSource. But if I do, the update function doesn't work. Anyway, here's the SqlDataSource: Any help would be greatly appreciated!!!!!

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"

ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"

DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.original_intID AND [street] = @.original_street AND [city] = @.original_city AND [state] = @.original_state AND [zip] = @.original_zip AND [status] = @.original_status AND [pDate] = @.original_pDate AND [sPrice] = @.original_sPrice AND [asPrice] = @.original_asPrice AND [actSalePrice] = @.original_actSalePrice AND [cToDate] = @.original_cToDate AND [rehabBudget] = @.original_rehabBudget AND [tDay] = @.original_tDay AND [eDate] = @.original_eDate AND [loDate] = @.original_loDate AND [rsDate] = @.original_rsDate AND [flooringDate] = @.original_flooringDate AND [estCompDate] = @.original_estCompDate AND [actCompDate] = @.original_actCompDate AND [coe] = @.original_coe AND [lDate] = @.original_lDate AND [credits] = @.original_credits AND [agent] = @.original_agent AND [insComplete] = @.original_insComplete AND [cEscrowDate] = @.original_cEscrowDate AND [bidValue] = @.original_bidValue AND [thomGuideNumber] = @.original_thomGuideNumber AND [locksmith] = @.original_locksmith AND [notes] = @.original_notes AND [hoa] = @.original_hoa"

InsertCommand="INSERT INTO [houses] ([street], [city], [state], [zip], [status], [pDate], [sPrice], [asPrice], [actSalePrice], [cToDate], [rehabBudget], [tDay], [eDate], [loDate], [rsDate], [flooringDate], [estCompDate], [actCompDate], [coe], [lDate], [credits], [agent], [insComplete], [cEscrowDate], [bidValue], [thomGuideNumber], [locksmith], [notes], [hoa]) VALUES (@.street, @.city, @.state, @.zip, @.status, @.pDate, @.sPrice, @.asPrice, @.actSalePrice, @.cToDate, @.rehabBudget, @.tDay, @.eDate, @.loDate, @.rsDate, @.flooringDate, @.estCompDate, @.actCompDate, @.coe, @.lDate, @.credits, @.agent, @.insComplete, @.cEscrowDate, @.bidValue, @.thomGuideNumber, @.locksmith, @.notes, @.hoa)"

OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient"

SelectCommand="SELECT * FROM [houses] WHERE ([intID] = @.intID)"

UpdateCommand="UPDATE [houses] SET [street] = @.street, [city] = @.city, [state] = @.state, [zip] = @.zip, [status] = @.status, [pDate] = @.pDate, [sPrice] = @.sPrice, [asPrice] = @.asPrice, [actSalePrice] = @.actSalePrice, [cToDate] = @.cToDate, [rehabBudget] = @.rehabBudget, [tDay] = @.tDay, [eDate] = @.eDate, [loDate] = @.loDate, [rsDate] = @.rsDate, [flooringDate] = @.flooringDate, [estCompDate] = @.estCompDate, [actCompDate] = @.actCompDate, [coe] = @.coe, [lDate] = @.lDate, [credits] = @.credits, [agent] = @.agent, [insComplete] = @.insComplete, [cEscrowDate] = @.cEscrowDate, [bidValue] = @.bidValue, [thomGuideNumber] = @.thomGuideNumber, [locksmith] = @.locksmith, [notes] = @.notes, [hoa] = @.hoa WHERE [intID] = @.original_intID">

<DeleteParameters>
<asp:ParameterName="original_intID"Type="Int32"/>
<asp:ParameterName="original_street"Type="String"/>
<asp:ParameterName="original_city"Type="String"/>
<asp:ParameterName="original_state"Type="String"/>
<asp:ParameterName="original_zip"Type="String"/>
<asp:ParameterName="original_status"Type="String"/>
<asp:ParameterName="original_pDate"Type="DateTime"/>
<asp:ParameterName="original_sPrice"Type="Decimal"/>
<asp:ParameterName="original_asPrice"Type="Decimal"/>
<asp:ParameterName="original_actSalePrice"Type="Decimal"/>
<asp:ParameterName="original_cToDate"Type="Decimal"/>
<asp:ParameterName="original_rehabBudget"Type="Decimal"/>
<asp:ParameterName="original_tDay"Type="DateTime"/>
<asp:ParameterName="original_eDate"Type="DateTime"/>
<asp:ParameterName="original_loDate"Type="DateTime"/>
<asp:ParameterName="original_rsDate"Type="DateTime"/>
<asp:ParameterName="original_flooringDate"Type="DateTime"/>
<asp:ParameterName="original_estCompDate"Type="DateTime"/>
<asp:ParameterName="original_actCompDate"Type="DateTime"/>
<asp:ParameterName="original_coe"Type="DateTime"/>
<asp:ParameterName="original_lDate"Type="DateTime"/>
<asp:ParameterName="original_credits"Type="String"/>
<asp:ParameterName="original_agent"Type="String"/>
<asp:ParameterName="original_insComplete"Type="String"/>
<asp:ParameterName="original_cEscrowDate"Type="DateTime"/>
<asp:ParameterName="original_bidValue"Type="Decimal"/>
<asp:ParameterName="original_thomGuideNumber"Type="String"/>
<asp:ParameterName="original_locksmith"Type="String"/>
<asp:ParameterName="original_notes"Type="String"/>
<asp:ParameterName="original_hoa"Type="String"/>
</DeleteParameters><UpdateParameters>
<asp:ParameterName="street"Type="String"/>
<asp:ParameterName="city"Type="String"/>
<asp:ParameterName="state"Type="String"/>
<asp:ParameterName="zip"Type="String"/>
<asp:ParameterName="status"Type="String"/>
<asp:ParameterName="pDate"Type="DateTime"/>
<asp:ParameterName="sPrice"Type="Decimal"/>
<asp:ParameterName="asPrice"Type="Decimal"/>
<asp:ParameterName="actSalePrice"Type="Decimal"/>
<asp:ParameterName="cToDate"Type="Decimal"/>
<asp:ParameterName="rehabBudget"Type="Decimal"/>
<asp:ParameterName="tDay"Type="DateTime"/>
<asp:ParameterName="eDate"Type="DateTime"/>
<asp:ParameterName="loDate"Type="DateTime"/>
<asp:ParameterName="rsDate"Type="DateTime"/>
<asp:ParameterName="flooringDate"Type="DateTime"/>
<asp:ParameterName="estCompDate"Type="DateTime"/>
<asp:ParameterName="actCompDate"Type="DateTime"/>
<asp:ParameterName="coe"Type="DateTime"/>
<asp:ParameterName="lDate"Type="DateTime"/>
<asp:ParameterName="credits"Type="String"/>
<asp:ParameterName="agent"Type="String"/>
<asp:ParameterName="insComplete"Type="String"/>
<asp:ParameterName="cEscrowDate"Type="DateTime"/>
<asp:ParameterName="bidValue"Type="Decimal"/>
<asp:ParameterName="thomGuideNumber"Type="String"/>
<asp:ParameterName="locksmith"Type="String"/>
<asp:ParameterName="notes"Type="String"/>
<asp:ParameterName="hoa"Type="String"/>
<asp:ParameterName="original_intID"Type="Int32"/>
<asp:ParameterName="original_street"Type="String"/>
<asp:ParameterName="original_city"Type="String"/>
<asp:ParameterName="original_state"Type="String"/>
<asp:ParameterName="original_zip"Type="String"/>
<asp:ParameterName="original_status"Type="String"/>
<asp:ParameterName="original_pDate"Type="DateTime"/>
<asp:ParameterName="original_sPrice"Type="Decimal"/>
<asp:ParameterName="original_asPrice"Type="Decimal"/>
<asp:ParameterName="original_actSalePrice"Type="Decimal"/>
<asp:ParameterName="original_cToDate"Type="Decimal"/>
<asp:ParameterName="original_rehabBudget"Type="Decimal"/>
<asp:ParameterName="original_tDay"Type="DateTime"/>
<asp:ParameterName="original_eDate"Type="DateTime"/>
<asp:ParameterName="original_loDate"Type="DateTime"/>
<asp:ParameterName="original_rsDate"Type="DateTime"/>
<asp:ParameterName="original_flooringDate"Type="DateTime"/>
<asp:ParameterName="original_estCompDate"Type="DateTime"/>
<asp:ParameterName="original_actCompDate"Type="DateTime"/>
<asp:ParameterName="original_coe"Type="DateTime"/>
<asp:ParameterName="original_lDate"Type="DateTime"/>
<asp:ParameterName="original_credits"Type="String"/>
<asp:ParameterName="original_agent"Type="String"/>
<asp:ParameterName="original_insComplete"Type="String"/>
<asp:ParameterName="original_cEscrowDate"Type="DateTime"/>
<asp:ParameterName="original_bidValue"Type="Decimal"/>
<asp:ParameterName="original_thomGuideNumber"Type="String"/>
<asp:ParameterName="original_locksmith"Type="String"/>
<asp:ParameterName="original_notes"Type="String"/>
<asp:ParameterName="original_hoa"Type="String"/>
</UpdateParameters><SelectParameters>
<asp:QueryStringParameterName="intID"QueryStringField="intID"Type="Int32"/>
</SelectParameters><InsertParameters>
<!-- removed to save space -->
</InsertParameters></asp:SqlDataSource>

If intID is your primary key fo the table, you can do it in a simple way. Please set DataKeyNames="intID" in your gridview and try:

DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.intID"

<DeleteParameters>
<asp:ParameterName="intID"Type="Int32"/>

</DeleteParameters>

|||

limno:

If intID is your primary key fo the table, you can do it in a simple way. Please set DataKeyNames="intID" in your gridview and try:

DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.intID"

<DeleteParameters>
<asp:ParameterName="intID"Type="Int32"/>

</DeleteParameters> 

Hey limno, thanks for the help. Unfortunately, it doesn't work. The record is not deleted. When I click the delete link, the page simply refreshes with the same data fields.

|||

Something strange happened to the reply above. Here it is again...

Hey limno, thanks for the help. Unfortunately, it doesn't work. The record is not deleted. When I click the delete link, the page simply refreshes with the same data fields.

|||

Hi tobias,

Please check in your Page_Load event handler to see if you have reloaded the DataSource by calling DataBind() method again. You need to check if it is a postback by using IsPostBack property.

If DataBind() is called, the operation will have no effect.