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 level. Show all posts
Showing posts with label level. 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
Wednesday, March 7, 2012
Remote Deployment
What's the easiest way to deploy a project to a remote server? Keep in mind there won't be any machine level access to the server housing the sql server.
Your question is bit short.
If you are talking about deployment of Analysis Services project you have several ways.
1. Change your BI Dev project properties to tell it to deploy to remote machine.
2. Use Deployment utility.
3. deploy your project first to the local Analysis Server and then use Synchronization or Backup/Restore to move data and metadata to a remote server.
I am sure you can be quite creative and find other ways to deploy your solution.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)