MSDTC with client OS (Windows 7, 8, 10)

When trying to setup distributed transactions (MSDTC) there are essentially 2 things you need to do. 

First, you need to enable connections by running Component Services MMC

image

Second you need to allow access through the firewall

image

But if you are doing this on a “client OS” like Windows 7, 8, or 10, trying to get your dev machine to talk to your SQL Server on the network for example, you might run into additional problems.

If you run the DTCPing ( https://www.microsoft.com/en-us/download/details.aspx?id=2868 ) MSDTC trouble shooting tool and you get “Access is denied”, like this:

Invoking RPC method on (compname)
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
–>RPC pinging exception
–>5(Access is denied.)
RPC test failed

To fix this issue follow these steps (https://blogs.msdn.microsoft.com/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/)

  1. Click Start, click Run, type Regedit, and then click OK.
  2. Locate and then click the following registry key:  HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT
  3. On the Edit menu, point to New, and then click Key.
  4. Note If the RPC registry key already exists, go to step 5.
  5. Type RPC, and then press ENTER. Click RPC.
  6. On the Edit menu, point to New, and then click DWORD Value.
  7. Type RestrictRemoteClients, and then press ENTER.
  8. Click RestrictRemoteClients.
  9. On the Edit menu, click Modify.
  10. In the Value data box, type 0, and then click OK.
  11. Note To enable the RestrictRemoteClients setting, type1.
  12. Close Registry Editor and restart the computer.
Advertisements

Reporting blocked processes in SQL

Recently I’ve been working on an issue where a query was being blocked.  In order to get the specifics on this it’s possible to use the Blocked progress report in the SQL Profiler.

To do this you need to first enable advanced options by running:

SP_CONFIGURE’show advanced options’,1 ;
GO

Then run:

RECONFIGURE;
GO

After turning that one, you need to set the blocked process threshold.  This is the number of seconds between checks to see if there is blocking going on. 

SP_CONFIGURE’blocked process threshold’,10 ;
GO

This will set it for 10 seconds.  You could change it to something lower if you want.  When you are done testing you should change it back to 0 (disabled).

After setting the threshold you need to reconfigure again:

RECONFIGURE;
GO

Then pick the Blocked process report from SQL Profiler:

image

The possible values of the Mode value are:

0=NULL
1=Sch-S
2=Sch-M
3=S
4=U
5=X
6=IS
7=IU
8=IX
9=SIU
10=SIX
11=UIX
12=BU
13=RangeS-S
14=RangeS-U
15=RangeI-N
16=RangeI-S
17=RangeI-U
18=RangeI-X
19=RangeX-S
20=RangeX-U
21=RangeX-X

Procedure or function expects parameter which was not supplied.

If you are googling for this error:

“Procedure or function (name) expects parameter (@parameter) which was not supplied.”

and you are SURE that you are providing the parameter to your stored procedure call, remember that for this to work properly with ADO.NET you need to set the IDdCommand or SqlCommand CommandType to be CommandType.StoredProcedure.

image