58

I get this weird error on SQL Server. And I cannot find solution in older posts.

I have this procedure:

create proc _upJM_SyncAll_test
as
begin
    DECLARE @SQLString nvarchar(max)

set @SQLString = N'
DELETE FROM OPENQUERY([LOCAL_MYSQL],''SELECT acSubject FROM _utjm_setitemprices'') where acSubject not in (select acSubject from _uvJM_SetSubj)
DELETE FROM OPENQUERY([LOCAL_MYSQL],''SELECT acSubject FROM _utjm_setsubj'') where acSubject not in (select acSubject from _uvJM_SetSubj)

update a
set acName2 = b.acName2,
    acName3 = b.acName3,
    acAddress = b.acAddress,
    acPost = b.acPost,
    acPostName = b.acPostName, 
    acCountry = b.acCountry, 
    acVATCodePrefix = b.acVATCodePrefix,
    acCode = b.acCode, 
    anDaysForPayment = b.anDaysForPayment
from OPENQUERY([LOCAL_MYSQL],''SELECT * FROM _utjm_setsubj'') a join _uvJM_SetSubj b on (a.acSubject = b.acSubject)
where 1=1
and (   isnull(a.acName2,'''') <> isnull(b.acName2,'''') OR 
        isnull(a.acName3,'''') <> isnull(b.acName3,'''') OR 
        isnull(a.acAddress,'''') <> isnull(b.acAddress,'''') OR 
        isnull(a.acPost,'''') <> isnull(b.acPost,'''') OR 
        isnull(a.acPostName,'''') <> isnull(b.acPostName,'''') OR 
        isnull(a.acCountry,'''') <> isnull(b.acCountry,'''') OR 
        isnull(a.acVATCodePrefix,'''') <> isnull(b.acVATCodePrefix,'''') OR 
        isnull(a.acCode,'''') <> isnull(b.acCode,'''') OR 
        isnull(a.anDaysForPayment,'''') <> isnull(b.anDaysForPayment,'''')
)

insert into OPENQUERY([LOCAL_MYSQL],''SELECT * FROM _utjm_setsubj'') (acSubject, acName2, acName3, acAddress, acPost, acPostName, acCountry, acVATCodePrefix, acCode, anDaysForPayment)
select b.acSubject, b.acName2, b.acName3, b.acAddress, b.acPost, b.acPostName, b.acCountry, b.acVATCodePrefix, b.acCode, b.anDaysForPayment
from OPENQUERY([LOCAL_MYSQL],''SELECT * FROM _utjm_setsubj'') a right join _uvJM_SetSubj b on (a.acSubject = b.acSubject)
where a.acSubject is null '

EXECUTE sp_executesql @SQLString;
end

When I run procedure in management studio like this:

  exec dbo._upJM_SyncAll_test

everything is OK. I get no error, sync is working just fine.

But when I put execute in trigger like this:

create trigger _utrJM_SetSubj on tHE_SetSubj after insert, update, delete
as
begin
    exec dbo._upJM_SyncAll_test
end

I get this error:

Msg 8501, Level 16, State 3, Procedure _upJM_SyncAll_test, Line 54
MSDTC on server 'server' is unavailable.

Procedure _upJM_SyncAll_test has only 39 lines...

MarredCheese
  • 17,541
  • 8
  • 92
  • 91
Kiki
  • 760
  • 1
  • 7
  • 17
  • Why are you using dynamic SQL in the first place?, it doesn't seem necessary – Lamak Apr 02 '15 at 13:32
  • well it's like that.. linked server [LOCAL_MYSQL] is up and running, but it could happend that is unreachable. In that case if sp_executesql returns error i do something else.. Please note that _upJM_SyncAll_test is just test procedure for this post, _upJM_SyncAll is more complex. – Kiki Apr 02 '15 at 13:43

9 Answers9

146

In my case, the service was stopped. solution: need to turn the MSDTC service on

  1. go to Services. (START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES)
  2. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.
  3. make this service to run Automatically for solving this issue permanently
Iman
  • 17,932
  • 6
  • 80
  • 90
Everson Rafael
  • 2,043
  • 2
  • 20
  • 20
  • 8
    Also, it may be useful to make the service start automatically. Right click the service -> Properties -> Startup Type and select 'Automatic'. Otherwise, you'll need to do this every time your machine reboots (or if the service stops for any reason) – Rob Sep 22 '16 at 04:45
  • This is a far better and simpler solution than the accepted answer – static_void Mar 13 '18 at 06:37
24

Triggers are included in the implicit transaction required for insert, update, and delete statements. Because you are connecting to a linked server within a transaction, SQL Server promotes it to a Distributed Transaction.

You'll need to configure MSDTC, you can either open MMC and load the MSDTC plugin or use the following script to open inbound and outbound transactions.

https://technet.microsoft.com/en-us/library/cc731495.aspx

REG QUERY "HKLM\Software\Microsoft\MSDTC\Security" /v NetworkDtcAccess
REG QUERY "HKLM\Software\Microsoft\MSDTC\Security" /v NetworkDtcAccessTransactions
REG QUERY "HKLM\Software\Microsoft\MSDTC\Security" /v NetworkDtcAccessInbound
REG QUERY "HKLM\Software\Microsoft\MSDTC\Security" /v NetworkDtcAccessOutbound
PAUSE

REG ADD "HKLM\Software\Microsoft\MSDTC\Security" /f /v NetworkDtcAccess /t REG_DWORD /d 1
REG ADD "HKLM\Software\Microsoft\MSDTC\Security" /f /v NetworkDtcAccessTransactions /t REG_DWORD /d 1
REG ADD "HKLM\Software\Microsoft\MSDTC\Security" /f /v NetworkDtcAccessInbound /t REG_DWORD /d 1
REG ADD "HKLM\Software\Microsoft\MSDTC\Security" /f /v NetworkDtcAccessOutbound /t REG_DWORD /d 1
PAUSE

net stop MSDTC
net start MSDTC
PAUSE
DATEx2
  • 3,585
  • 1
  • 24
  • 26
jtimperley
  • 2,494
  • 13
  • 11
  • 1
    After the first pause, you may add the /f option to all the commands: " Force overwriting the existing registry entry without prompt." otherwise this wont work. Other than that, this work perfectly, thanks! – codea Dec 02 '15 at 13:17
  • Thanks for the technet link. I followed the instructions in it and solved my problem. – Rich Jul 10 '17 at 18:37
  • One thing is to start MSDTC, but what does `open inbound and outbound transactions` mean and why is it needed? – Jim Aho Nov 19 '18 at 09:47
22

I ran into the same error, however it wasn't as simple as the Distributed Transaction Coordinator service from not running. I received a driver update automatically through windows that was causing issues with COM+ and not allowing MSDTC to communicate properly even though the MSDTC service was running. In my case, it was an issue with HP hotkey drivers but in researching I found other reports of issues with audio drivers from other manufacturers causing this as well.

To check to see if you have a similar issue, launch Component Services (dcomcnfg.exe), then expand Component Services > Computers > My Computer, from here click on 'COM+ Applications' to see if an error will pop-up with "COM+ unable to talk to Microsoft Distributed Transaction Coordinator" or there will be a red error over the icon for My Computer in the navigation.

The fix for me was to disable the 'HP Hotkey Service" and "HotKeyServiceUWP" services. Once those were disable, MSDTC immediately started working.

Mac
  • 388
  • 2
  • 6
  • 1
    Man, your solution just saved me. Thanks a million! – Oleks Feb 07 '19 at 14:58
  • Thanks a ton! This led my search into the possibility of Lenovo being the culprit. Turns out it was `LenovoSmartStandby` service which needed to be disabled as a temporary workaround. See [this link for more information](https://forums.lenovo.com/t5/ThinkPad-X-Series-Laptops/MSDTC-errors-on-Lenovo-X1/td-p/4199279/page/4) – Frank Fu Feb 28 '19 at 07:30
  • 1
    OH MY GOD!! What were the odds that I had the EXACT SAME issue, thanks a million! – Alex Mar 08 '19 at 22:12
  • I seem to have this problem too on an Acer Swift 5. But no clue which services to stop. – Dabblernl Oct 31 '19 at 20:27
  • 2
    In my case - Lenovo ThinkPad P52 - the reason was the "Dolby DAX API Service". – nzeemin Jan 09 '20 at 14:59
  • 2
    Disabling all HP services worked for me and the error dissapeared. (Tested on Win 10) – mggSoft Feb 10 '20 at 16:28
  • @nzeemin I wish I would have seen your comment an hour ago, thanks! How did you pinpoint the exact service causing issues with MSDT? Just spent an hour on this and couldn't find anything meaningful in the Event Viewer. – JustinMichaels Nov 21 '20 at 21:09
  • @JustinMichaels first a lot of googling showed me it's usially a hardware vendor related service and then just trying suspisuos services one by one, nothing unusual. – nzeemin Nov 22 '20 at 22:15
  • Ran into this same issue as well. I had to reregister the service, using these steps here: https://answers.microsoft.com/en-us/windows/forum/all/error-8004e00f-when-trying-to-access-com/782e5a36-d28f-4b35-a53c-f343e456638d – John Baker Apr 20 '21 at 21:48
  • In my case: HP Print Scan Doctor Service . I have a DELL laptop but an HP printer ... – Bolek Feb 14 '22 at 15:22
3

My issue was resolved by disabling a conflicting service/driver. I disabled all of my HP services (HP App Helper, HP CASL, HP System Info), and now it works fine.

Greg
  • 31
  • 2
1

'Distributed Transaction Coordinator' service was not running, So started service and changed service type to automatic too.

Subhash Madhukar
  • 21
  • 2
0

In my case I had a named instance of Microsoft SQL Server at a remote server and the error output of my application looked like this:

System.Data.SqlClient.SqlException (0x80131904): MSDTC on server 'SERVER\INST_NAME' is unavailable.

Experimenting with the SQL connection string, such as replacing the SERVER\INST_NAME with SERVER,TCP_PORT_NUMBER did not help. I was still getting the same error and I decided to install a default instance (MSSQLSERVER) so that the SQL Server name became SERVER only. This solved the problem as the RPC and MSDTC started resolving the name.

Tsvetan Tihanov
  • 1
0

Powershell solution:

Get-Service -Name MSDTC | Set-Service -StartupType Automatic
Kellen Stuart
  • 7,775
  • 7
  • 59
  • 82
0

Recently this appeared as an issue on my work machine (Win 10, MSSQL 2016 SP2 Express). I do not have linked servers. This appeared from nothing. The MSDTC service is running, MSSQL works fine using the Mgmt Studio. However my .NET 4.7.1 Unit test app refuse to connect with the database with that "MSDTC on server '...' is unavailable" error.

The temporal workaround I found is to add "Enlist=False;" at the end of the connection string to avoid using MSDTC at all for the connection.

The source for this solution is a comment under this topic: https://learn.microsoft.com/en-us/archive/blogs/distributedservices/intermittent-error-msdtc-on-server-servername-is-unavailable

Vanyo Vanev
  • 41
  • 3
0

I was having the same issue but for Azure SQL. (Just posting this here in case someone is having same issue with Azure SQL.) It was because of the application intent being set with the readonly option (ApplicationIntent=ReadOnly) when connecting to the Azure SQL DB with Read Scale Out option enabled in Azure.

For a quick fix, we removed the ApplicationIntent=ReadOnly configuration from the application.

For a better idea about this along with the proper solution please follow the below link:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-173-msdtc-on-server-xxxxx-is-unavailable/ba-p/2376529

Flipz98
  • 11
  • 1