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
> )
Showing posts with label perform. Show all posts
Showing posts with label perform. Show all posts
Friday, March 23, 2012
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
> )
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
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
Monday, March 12, 2012
Remote restore question
Hello everyone,
Quick question. We perform db backups at 10pm on a certain database
that is about 12 gig. Then the transaction log is backed up every
hour. We'd like to restore this database to another off site server,
then apply the logs to keep it in sync w/ production. I've tested this
and it seems to work fine. My only question is regarding the full
backups that are performed every night. Due to bandwidth limitations,
we can't get the full db backup to the remote server in a reasonable
amount of time (the logs can make it over within one hour no problem).
Can we just keep restoring the logs on the remote server without the
nightly db backups being restored? I guess I'm asking if the full db
backup does anything to the log (empties it or the like) that would
make the dbs out of sync?
Thanks in advance.That would be fine. This is one of the reasons why a full backup doesn't
truncate the transaction log. But I suggest you have operational
instructions readily available on how to do this manually, in case the log
restores becomes out of sync, for any reason...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||You can restore logs in sequence on a SQL 7.0 or 2000 and ignore intervening
full backups. This is intentional so that if a full backup goes bad, you
can go to an older full backup and restore more logs to get your database up
to the last committed transaction.
BTW, this is called log-shipping. SQL enterprise edition includes it as
built-in, but many people write their own routines to handle situations and
configurations not directly supported but the built-in tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||Excellent. Thanks to both of you.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<enVq$YGDEHA.1588@.tk2msftn
gp13.phx.gbl>...
> You can restore logs in sequence on a SQL 7.0 or 2000 and ignore interveni
ng
> full backups. This is intentional so that if a full backup goes bad, you
> can go to an older full backup and restore more logs to get your database
up
> to the last committed transaction.
> BTW, this is called log-shipping. SQL enterprise edition includes it as
> built-in, but many people write their own routines to handle situations an
d
> configurations not directly supported but the built-in tools.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
Quick question. We perform db backups at 10pm on a certain database
that is about 12 gig. Then the transaction log is backed up every
hour. We'd like to restore this database to another off site server,
then apply the logs to keep it in sync w/ production. I've tested this
and it seems to work fine. My only question is regarding the full
backups that are performed every night. Due to bandwidth limitations,
we can't get the full db backup to the remote server in a reasonable
amount of time (the logs can make it over within one hour no problem).
Can we just keep restoring the logs on the remote server without the
nightly db backups being restored? I guess I'm asking if the full db
backup does anything to the log (empties it or the like) that would
make the dbs out of sync?
Thanks in advance.That would be fine. This is one of the reasons why a full backup doesn't
truncate the transaction log. But I suggest you have operational
instructions readily available on how to do this manually, in case the log
restores becomes out of sync, for any reason...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||You can restore logs in sequence on a SQL 7.0 or 2000 and ignore intervening
full backups. This is intentional so that if a full backup goes bad, you
can go to an older full backup and restore more logs to get your database up
to the last committed transaction.
BTW, this is called log-shipping. SQL enterprise edition includes it as
built-in, but many people write their own routines to handle situations and
configurations not directly supported but the built-in tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||Excellent. Thanks to both of you.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<enVq$YGDEHA.1588@.tk2msftn
gp13.phx.gbl>...
> You can restore logs in sequence on a SQL 7.0 or 2000 and ignore interveni
ng
> full backups. This is intentional so that if a full backup goes bad, you
> can go to an older full backup and restore more logs to get your database
up
> to the last committed transaction.
> BTW, this is called log-shipping. SQL enterprise edition includes it as
> built-in, but many people write their own routines to handle situations an
d
> configurations not directly supported but the built-in tools.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
Friday, March 9, 2012
Remote Installation
Is it possible to perform a remote installation of SQL Server 2000?Jeff wrote:
> Is it possible to perform a remote installation of SQL Server 2000?
Yes, it is.
As taken from the FAQ:
The Computer Name screen of the SQL Server setup provides the option of
selecting the local computer, a remote computer, or when installing
clustering, a virtual server. However, note that all prerequisites must
first be installed on the remote computer before beginning the installation.
For more information on remote setup, see the "Remote Setup Information"
topic in SQL Server Books Online.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
> Is it possible to perform a remote installation of SQL Server 2000?
Yes, it is.
As taken from the FAQ:
The Computer Name screen of the SQL Server setup provides the option of
selecting the local computer, a remote computer, or when installing
clustering, a virtual server. However, note that all prerequisites must
first be installed on the remote computer before beginning the installation.
For more information on remote setup, see the "Remote Setup Information"
topic in SQL Server Books Online.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Remote Installation
Is it possible to perform a remote installation of SQL Server 2000?Jeff wrote:
> Is it possible to perform a remote installation of SQL Server 2000?
Yes, it is.
As taken from the FAQ:
The Computer Name screen of the SQL Server setup provides the option of
selecting the local computer, a remote computer, or when installing
clustering, a virtual server. However, note that all prerequisites must
first be installed on the remote computer before beginning the installation.
For more information on remote setup, see the "Remote Setup Information"
topic in SQL Server Books Online.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
> Is it possible to perform a remote installation of SQL Server 2000?
Yes, it is.
As taken from the FAQ:
The Computer Name screen of the SQL Server setup provides the option of
selecting the local computer, a remote computer, or when installing
clustering, a virtual server. However, note that all prerequisites must
first be installed on the remote computer before beginning the installation.
For more information on remote setup, see the "Remote Setup Information"
topic in SQL Server Books Online.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)