Remote Transaction - ReleaseSchemaLock Failure

Any help or suggestion will be appreciated.
Attempting to perform a distributed transaction and the following is
returned:
Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
interface, but returned0x8004d00e for ReleaseSchemaLock .].
[OLE/DB provider returned message: Unspecified error]
The servers are (from spt_server_info):
Server1:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
Server2:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
The linked server is defined (using SQLOLEDB):
sp_helpserver 'hbd-mrg-qrm-t'
name
network_name
status
id collation_name
connect_timeout query_timeout
------
--
------
-- --
hbd-mrg-qrm-t
hbd-mrg-qrm-t
rpc,rpc out,data access,use remote collation
4 NULL
0 0
The code to initiate the transaction is:
begin distributed transaction
set XACT_ABORT ON
exec @.nError = ArchiveAppCopy
if @.nError = 0
The first DML statement, insert is failing within the procedure below:
CREATE Procedure dbo.ArchiveAppCopy as
declare @.nError int
set nocount on
begin tran
Set identity_insert AlertReminder on
if @.@.error <> 0 goto Commit_Label
alter table AlertReminder disable trigger all
if @.@.error <> 0 goto Commit_Label
Insert into AlertReminder (
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID)
Select
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID
>From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where
AppNumber in (select appnumber from TransferApps)
and not exists (select appnumber from AlertReminder where
AlertReminder.AlertReminderID = tblSource.AlertReminderID
)
An update:
Further testing has determined that any schema changes on the local
server will fail the MSDTC transaction.
The schema changes performed here were to disable/enable triggers and
set identity_inserts on/off before the mass copy.
Without any alternatives, I plan to move all the schema changes to
appropriate stored procecedures and call them outside of the
distributed transaction.
Does anyone know why the OLE/DB provider was returned 'Unknown' in the
below message? Other trace messages returned back 'SQLOLEDB', I wonder
if the server link is being broken with the ReleaseSchemaLock and that
failed the DTC.
Jochen.Markert@.fnf.com wrote:
> Any help or suggestion will be appreciated.
> Attempting to perform a distributed transaction and the following is
> returned:
> Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
> Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction.
> OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
> returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
> interface, but returned0x8004d00e for ReleaseSchemaLock .].
> [OLE/DB provider returned message: Unspecified error]
> The servers are (from spt_server_info):
> Server1:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> Server2:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> The linked server is defined (using SQLOLEDB):
> sp_helpserver 'hbd-mrg-qrm-t'
> name
> network_name
> status
> id collation_name
> connect_timeout query_timeout
> ------
> --
> ----
> --
> ------
> -- --
> hbd-mrg-qrm-t
> hbd-mrg-qrm-t
> rpc,rpc out,data access,use remote collation
> 4 NULL
> 0 0
>
> The code to initiate the transaction is:
> begin distributed transaction
> set XACT_ABORT ON
> exec @.nError = ArchiveAppCopy
> if @.nError = 0
>
> The first DML statement, insert is failing within the procedure below:
> CREATE Procedure dbo.ArchiveAppCopy as
> declare @.nError int
> set nocount on
> begin tran
> Set identity_insert AlertReminder on
> if @.@.error <> 0 goto Commit_Label
> alter table AlertReminder disable trigger all
> if @.@.error <> 0 goto Commit_Label
> Insert into AlertReminder (
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID)
> Select
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID
> AppNumber in (select appnumber from TransferApps)
> and not exists (select appnumber from AlertReminder where
> AlertReminder.AlertReminderID = tblSource.AlertReminderID
> )

Remote Transaction - ReleaseSchemaLock Failure

Any help or suggestion will be appreciated.
Attempting to perform a distributed transaction and the following is
returned:
Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
interface, but returned0x8004d00e for ReleaseSchemaLock .].
[OLE/DB provider returned message: Unspecified error]
The servers are (from spt_server_info):
Server1:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
Server2:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
The linked server is defined (using SQLOLEDB):
sp_helpserver 'hbd-mrg-qrm-t'
name
network_name
status
id collation_name
connect_timeout query_timeout
----
----
--
----
--
--
----
----
-- --
hbd-mrg-qrm-t
hbd-mrg-qrm-t
rpc,rpc out,data access,use remote collation
4 NULL
0 0
The code to initiate the transaction is:
begin distributed transaction
set XACT_ABORT ON
exec @.nError = ArchiveAppCopy
if @.nError = 0
The first DML statement, insert is failing within the procedure below:
CREATE Procedure dbo.ArchiveAppCopy as
declare @.nError int
set nocount on
begin tran
Set identity_insert AlertReminder on
if @.@.error <> 0 goto Commit_Label
alter table AlertReminder disable trigger all
if @.@.error <> 0 goto Commit_Label
Insert into AlertReminder (
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID)
Select
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID[vbcol=seagreen]
>From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where[
/vbcol]
AppNumber in (select appnumber from TransferApps)
and not exists (select appnumber from AlertReminder where
AlertReminder.AlertReminderID = tblSource.AlertReminderID
)An update:
Further testing has determined that any schema changes on the local
server will fail the MSDTC transaction.
The schema changes performed here were to disable/enable triggers and
set identity_inserts on/off before the mass copy.
Without any alternatives, I plan to move all the schema changes to
appropriate stored procecedures and call them outside of the
distributed transaction.
Does anyone know why the OLE/DB provider was returned 'Unknown' in the
below message? Other trace messages returned back 'SQLOLEDB', I wonder
if the server link is being broken with the ReleaseSchemaLock and that
failed the DTC.
Jochen.Markert@.fnf.com wrote:
> Any help or suggestion will be appreciated.
> Attempting to perform a distributed transaction and the following is
> returned:
> Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
> Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction.
> OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
> returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
> interface, but returned0x8004d00e for ReleaseSchemaLock .].
> [OLE/DB provider returned message: Unspecified error]
> The servers are (from spt_server_info):
> Server1:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> Server2:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> The linked server is defined (using SQLOLEDB):
> sp_helpserver 'hbd-mrg-qrm-t'
> name
> network_name
> status
> id collation_name
> connect_timeout query_timeout
> ----
---
> --
> ----
--
> --
> ----
---
> -- --
> hbd-mrg-qrm-t
> hbd-mrg-qrm-t
> rpc,rpc out,data access,use remote collation
> 4 NULL
> 0 0
>
> The code to initiate the transaction is:
> begin distributed transaction
> set XACT_ABORT ON
> exec @.nError = ArchiveAppCopy
> if @.nError = 0
>
> The first DML statement, insert is failing within the procedure below:
> CREATE Procedure dbo.ArchiveAppCopy as
> declare @.nError int
> set nocount on
> begin tran
> Set identity_insert AlertReminder on
> if @.@.error <> 0 goto Commit_Label
> alter table AlertReminder disable trigger all
> if @.@.error <> 0 goto Commit_Label
> Insert into AlertReminder (
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID)
> Select
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID
> AppNumber in (select appnumber from TransferApps)
> and not exists (select appnumber from AlertReminder where
> AlertReminder.AlertReminderID = tblSource.AlertReminderID
> )

Remote Transaction - ReleaseSchemaLock Failure

Any help or suggestion will be appreciated.
Attempting to perform a distributed transaction and the following is
returned:
Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
interface, but returned0x8004d00e for ReleaseSchemaLock .].
[OLE/DB provider returned message: Unspecified error]
The servers are (from spt_server_info):
Server1:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
Server2:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
The linked server is defined (using SQLOLEDB):
sp_helpserver 'hbd-mrg-qrm-t'
name
network_name
status
id collation_name
connect_timeout query_timeout
------
--
----
--
------
-- --
hbd-mrg-qrm-t
hbd-mrg-qrm-t
rpc,rpc out,data access,use remote collation
4 NULL
0 0
The code to initiate the transaction is:
begin distributed transaction
set XACT_ABORT ON
exec @.nError = ArchiveAppCopy
if @.nError = 0
The first DML statement, insert is failing within the procedure below:
CREATE Procedure dbo.ArchiveAppCopy as
declare @.nError int
set nocount on
begin tran
Set identity_insert AlertReminder on
if @.@.error <> 0 goto Commit_Label
alter table AlertReminder disable trigger all
if @.@.error <> 0 goto Commit_Label
Insert into AlertReminder (
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID)
Select
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID
>From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where
AppNumber in (select appnumber from TransferApps)
and not exists (select appnumber from AlertReminder where
AlertReminder.AlertReminderID = tblSource.AlertReminderID
)An update:
Further testing has determined that any schema changes on the local
server will fail the MSDTC transaction.
The schema changes performed here were to disable/enable triggers and
set identity_inserts on/off before the mass copy.
Without any alternatives, I plan to move all the schema changes to
appropriate stored procecedures and call them outside of the
distributed transaction.
Does anyone know why the OLE/DB provider was returned 'Unknown' in the
below message? Other trace messages returned back 'SQLOLEDB', I wonder
if the server link is being broken with the ReleaseSchemaLock and that
failed the DTC.
Jochen.Markert@.fnf.com wrote:
> Any help or suggestion will be appreciated.
> Attempting to perform a distributed transaction and the following is
> returned:
> Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
> Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction.
> OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
> returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
> interface, but returned0x8004d00e for ReleaseSchemaLock .].
> [OLE/DB provider returned message: Unspecified error]
> The servers are (from spt_server_info):
> Server1:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> Server2:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> The linked server is defined (using SQLOLEDB):
> sp_helpserver 'hbd-mrg-qrm-t'
> name
> network_name
> status
> id collation_name
> connect_timeout query_timeout
> ------
> --
> ----
> --
> ------
> -- --
> hbd-mrg-qrm-t
> hbd-mrg-qrm-t
> rpc,rpc out,data access,use remote collation
> 4 NULL
> 0 0
>
> The code to initiate the transaction is:
> begin distributed transaction
> set XACT_ABORT ON
> exec @.nError = ArchiveAppCopy
> if @.nError = 0
>
> The first DML statement, insert is failing within the procedure below:
> CREATE Procedure dbo.ArchiveAppCopy as
> declare @.nError int
> set nocount on
> begin tran
> Set identity_insert AlertReminder on
> if @.@.error <> 0 goto Commit_Label
> alter table AlertReminder disable trigger all
> if @.@.error <> 0 goto Commit_Label
> Insert into AlertReminder (
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID)
> Select
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID
> >From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where
> AppNumber in (select appnumber from TransferApps)
> and not exists (select appnumber from AlertReminder where
> AlertReminder.AlertReminderID = tblSource.AlertReminderID
> )sql

Remote connections error SQL Server 2005

I am getting "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) " when I access my application
But sql server is found to be running
what may be the reason
can any one guide me on thisEverything you could possibly want to know on connectivity issues:

http://blogs.msdn.com/sql_protocols.../22/506607.aspx

Brett
Perfect Computing, Inc

Remote connection refused when creating ODBC connection to SQL Server 2005

When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.

How can I allow the server to allow this.

Any help appreciated

regards

I would recommend looking at the following post, hopefully it already contains the answer to your issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=297514&SiteID=1

If the information here is not enough to fix your problem, let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine