Synchronization fails for store procedures w/ linked servers
ksmith@cfund.org
Posts: 2
Get the following error when trying to synch from a server with linked server (CFCTPIV01) to localhost which also have a linked server with the same name. Using SQL Compare 5.2.0.32. Both SQL Servers are 2005 SP1.
The following error message was returned from the SQL Server:
[7391] The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "CFCTPIV01" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI" for linked server "CFCTPIV01" returned message "The transaction manager has disabled its support for remote/network transactions.".
The following SQL command caused the error:
CREATE PROCEDURE [dbo].[CF_SP_Insert_Supervisory_Approval_Sweep_Data]
@CurrentDate datetime,
@returnvalue int output
AS
DECLARE @PrevMonth datetime, @SweepMonth int, @SweepYear int, @TradeCheck int
SET @TradeCheck = 0
SET @PrevMonth = (SELECT DATEADD(m,-1,@CurrentDate))
SET @SweepMonth = (SELECT MONTH(@PrevMonth))
SET @SweepYear = (SELECT YEAR(@PrevMonth))
IF NOT EXISTS(SELECT TradeDate FROM TradeApproval WHERE MONTH(TradeDate) = @SweepMonth AND YEAR(TradeDate) = @SweepYear)
BEGIN
BEGIN TRANSACTION
INSERT INTO TradeApproval (Company_Id, Company_Name, Territory_Id, Territory_Name, TeamLeadName, TeamLeadEmail,
Ledger_Id,TradeDate, FundCode, InstitutionCode, SchoolFundCode, AccountNo, InstSFCode, TransactionCode,
TransactionDescription, Shares, Price, LedgerValue, Total_CF_Assets, Total_Investable_Assets, SweepDate)
select c.Company_Id, Company_Name, t.Territory_Id, Territory_Name,e.Full_Name, e.Work_Email, Ledger_Id,
TradeDate, FundCode, InstitutionCode, SchoolFundCode, AccountNo, InstSFCode, tc.TransactionCode, tc.Description,
Shares, Price, LedgerValue, f.Total_CF_Assets, f.Total_Investable_Assets, GETDATE() As SweepDate
from Ledger L
INNER JOIN CFCTPIV01.CFCTProdED.dbo.Company c
on 'INST' + L.InstitutionCode = c.Institution_Code
INNER JOIN CFCTPIV01.CFCTProdED.dbo.Financial f
ON f.Company_Id = c.Company_Id
INNER JOIN TransactionCode tc
ON L.TransactionCode = tc.TransactionCode
INNER JoIN CFCTPIV01.CFCTProdED.dbo.Territory t
ON c.Territory_id = t.Territory_Id
INNER JOIN CFCTPIV01.CFCTProdED.dbo.Employee e
ON t.Account_Manager_Id = e.Employee_id
WHERE
c.Investor_Status = 'Investor' AND (MONTH(L.TradeDate) = @SweepMonth AND YEAR(L.TradeDate) = @SweepYear)
AND (tc.ComplianceApproval = 1)
-- Exclude the following Funds Short Term (30), Intermedite Term (0009),
-- Evergreens (8800, 8900) and Unsettled Cash (UNCASH)
AND (L.FundCode <> '30' AND L.FundCode <> '0009' AND L.FundCode <> 'UNSCASH'
AND L.FundCode <> '8800' AND L.FundCode <> '8900')
END
ELSE
BEGIN
PRINT 'The Trades already exist in the sweep table, if this is on purpose then remove the trades for that month before importing.'
SET @TradeCheck = 99
END
@Error
@returnvalue = 0)
BEGIN
IF @TradeCheck = 0
BEGIN
COMMIT
END
ELSE
BEGIN
@returnvalue = @TradeCheck
END
END
ELSE
BEGIN
ROLLBACK
END
@returnvalue
The following messages were returned from the SQL Server:
[5701] Changed database context to 'MgmtReporting'.
[5703] Changed language setting to us_english.
[0] Creating [dbo].[CF_SP_Insert_Supervisory_Approval_Sweep_Data]
The following error message was returned from the SQL Server:
[7391] The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "CFCTPIV01" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI" for linked server "CFCTPIV01" returned message "The transaction manager has disabled its support for remote/network transactions.".
The following SQL command caused the error:
CREATE PROCEDURE [dbo].[CF_SP_Insert_Supervisory_Approval_Sweep_Data]
@CurrentDate datetime,
@returnvalue int output
AS
DECLARE @PrevMonth datetime, @SweepMonth int, @SweepYear int, @TradeCheck int
SET @TradeCheck = 0
SET @PrevMonth = (SELECT DATEADD(m,-1,@CurrentDate))
SET @SweepMonth = (SELECT MONTH(@PrevMonth))
SET @SweepYear = (SELECT YEAR(@PrevMonth))
IF NOT EXISTS(SELECT TradeDate FROM TradeApproval WHERE MONTH(TradeDate) = @SweepMonth AND YEAR(TradeDate) = @SweepYear)
BEGIN
BEGIN TRANSACTION
INSERT INTO TradeApproval (Company_Id, Company_Name, Territory_Id, Territory_Name, TeamLeadName, TeamLeadEmail,
Ledger_Id,TradeDate, FundCode, InstitutionCode, SchoolFundCode, AccountNo, InstSFCode, TransactionCode,
TransactionDescription, Shares, Price, LedgerValue, Total_CF_Assets, Total_Investable_Assets, SweepDate)
select c.Company_Id, Company_Name, t.Territory_Id, Territory_Name,e.Full_Name, e.Work_Email, Ledger_Id,
TradeDate, FundCode, InstitutionCode, SchoolFundCode, AccountNo, InstSFCode, tc.TransactionCode, tc.Description,
Shares, Price, LedgerValue, f.Total_CF_Assets, f.Total_Investable_Assets, GETDATE() As SweepDate
from Ledger L
INNER JOIN CFCTPIV01.CFCTProdED.dbo.Company c
on 'INST' + L.InstitutionCode = c.Institution_Code
INNER JOIN CFCTPIV01.CFCTProdED.dbo.Financial f
ON f.Company_Id = c.Company_Id
INNER JOIN TransactionCode tc
ON L.TransactionCode = tc.TransactionCode
INNER JoIN CFCTPIV01.CFCTProdED.dbo.Territory t
ON c.Territory_id = t.Territory_Id
INNER JOIN CFCTPIV01.CFCTProdED.dbo.Employee e
ON t.Account_Manager_Id = e.Employee_id
WHERE
c.Investor_Status = 'Investor' AND (MONTH(L.TradeDate) = @SweepMonth AND YEAR(L.TradeDate) = @SweepYear)
AND (tc.ComplianceApproval = 1)
-- Exclude the following Funds Short Term (30), Intermedite Term (0009),
-- Evergreens (8800, 8900) and Unsettled Cash (UNCASH)
AND (L.FundCode <> '30' AND L.FundCode <> '0009' AND L.FundCode <> 'UNSCASH'
AND L.FundCode <> '8800' AND L.FundCode <> '8900')
END
ELSE
BEGIN
PRINT 'The Trades already exist in the sweep table, if this is on purpose then remove the trades for that month before importing.'
SET @TradeCheck = 99
END
@Error
@returnvalue = 0)
BEGIN
IF @TradeCheck = 0
BEGIN
COMMIT
END
ELSE
BEGIN
@returnvalue = @TradeCheck
END
END
ELSE
BEGIN
ROLLBACK
END
@returnvalue
The following messages were returned from the SQL Server:
[5701] Changed database context to 'MgmtReporting'.
[5703] Changed language setting to us_english.
[0] Creating [dbo].[CF_SP_Insert_Supervisory_Approval_Sweep_Data]
This discussion has been closed.
Comments
This is a common issue with this piece of software, owing to a difference in the supported transaction isolation levels that are allowed through ODBC drivers that are being used to access data through linked servers. For more information, please see this post:
http://www.red-gate.com/messageboard/vi ... php?t=2953