The OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction : SQL SERVER ERROR

Please visit my new Web Site WWW.Codedisplay.com

In many Sql Server forum i found the error "The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction". That's why i am decided to describe this error with a solution that i have resolved yesterday. One simple solution is Sql Server has a service named "Distributed Transaction" which you need to ON to resolve this problem. But one disadvantage of this service is it will take memory space than usual. You have another simple solution which i want to share in the later part of this article.

Full Error:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 19
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Reason:
Specially i found this error when i am trying to run a dynamic query to insert data into another server like below:

DECLARE @tbl VARCHAR(8)
SELECT @tbl=CONVERT(VARCHAR(8),DATEADD(day, (DATEDIFF (day, '19800104', getdate()) / 7) * 7, '19800104'),112)

DECLARE @sql nvarchar(2000);

SET @sql='select 
 account_id,
 sum(case when account_balance >=0  and account_balance <99 then 1 else 0 end) b_0to99,
 sum(case when account_balance >=100  and account_balance <499 then 1 else 0 end) b_100to499,
 sum(case when account_balance >=500  and account_balance <999 then 1 else 0 end) b_500to999,
 sum(case when account_balance >1000  then 1 else 0 end) b_g1000
from
 sdp_dedicated_stage_'+ @tbl +'
group by account_id
order by convert(integer,account_id)'

INSERT INTO [SQLDB\SQL100].[RA_CTL_SUMMARY].[dbo].FM_DA_TREND_ANALYSIS
EXEC SP_EXECUTESQL @sql

DROP TABLE #tmpDA

Solution:
First create a table definition within the scope and insert dynamic sql returned data into this table and then insert data into the remote server or another server table like below:

DECLARE @tbl VARCHAR(8)
SELECT @tbl=CONVERT(VARCHAR(8),DATEADD(day, (DATEDIFF (day, '19800104', getdate()) / 7) * 7, '19800104'),112)

CREATE TABLE #tmpDA(account_id int,b_0to99 bigint,b_100to499 bigint,b_500to999 bigint,b_g1000 bigint)

DECLARE @sql nvarchar(2000);

SET @sql='select 
 account_id,
 sum(case when account_balance >=0  and account_balance <99 then 1 else 0 end) b_0to99,
 sum(case when account_balance >=100  and account_balance <499 then 1 else 0 end) b_100to499,
 sum(case when account_balance >=500  and account_balance <999 then 1 else 0 end) b_500to999,
 sum(case when account_balance >1000  then 1 else 0 end) b_g1000
from
 sdp_dedicated_stage_'+ @tbl +'
group by account_id
order by convert(integer,account_id)'

INSERT #tmpDA
EXEC SP_EXECUTESQL @sql

INSERT INTO [SQLDB\SQL100].[RA_CTL_SUMMARY].[dbo].FM_DA_TREND_ANALYSIS
SELECT *,@tbl FROM #tmpDA

DROP TABLE #tmpDA

If you examine the code you will found that i have created a table definition named #tmpDA then i have inserted dynamic sql returned data into the #tmpDA table, after that i have inserted #tmpDA data into the remote server [SQLDB\SQL100]. The problem has been resolved.