TCP Chimney Setting and SQL Server Error: TCP Provider: An existing connection was forcibly closed by the remote host

David Meego - Click for blog homepageYou might have heard me discuss the TCP Chimney settings before. If not, don’t worry as this post is here to make sure you know about the setting and make sure it is disabled for all your Microsoft Dynamics GP installs.

My earlier post which mentions the TCP Chimney settings, discusses an issue with a large SmartList query not returning all the expected data. This post aims to discuss the bigger picture without referencing a specific module or function.

 

The Problem

When you login to Microsoft Dynamics GP, a connection to the SQL Server is created which has a SPID (Server Process ID) associated with it. As you work with Dynamics, it uses this connection to continuously communicate to the SQL Server. When Dynamics creates SQL temporary tables, they are created as ##tables and linked to the current SPID. The tables exist until the Dynamics code finishes with the table and closes it, or until the connection for the SPID is broken and SQL “cleans up” and removes the table.

If the connection is lost for any reason, Dynamics will re-establish a new connection with a new SPID so you can continue working. However, any queries currently in process will stop as they were for the old SPID. Also temporary tables created for the now disconnected SPID are not available to the new SPID as they were destroyed by SQL when the old SPID was lost. The fact that Dynamics automatically reconnects could make it appear as though nothing has happened, so the end user might not realise they had been disconnected.

Losing the connection can cause:

  • Errors attempting to access Temporary tables that are no longer available, usually generating an DBMS Error 12 (Syntax Error):

The stored procedure createSQLTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.

The stored procedure createTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.

Note: This issue is most common on windows that use ##temp tables to populate scrolling windows. Two primary examples of windows which behave this way are the RM and PM Inquiry windows (see article in More Information section below).
  

  • Datasets returned to be incomplete, such as SmartList queries (see article in More Information section below). This can be seen in the DEXSQL.LOG as

/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(08S01) Native Err:(10054) stmt(21931584):*/
MicrosoftSQL Server Native Client 10.0TCP Provider: An existing connection was forcibly closed by the remote host.
*/
 

  •  Posting Interruptions, forcing transactions into Batch Recovery

 

Possible Causes

There can be a number of reasons for the SQL Connection to be lost between the SQL Server and a client workstations, including but not limited to:

  • Workstations set to Stand By, Sleep or Hibernate when not used for a period of time.
  • Timeout settings on SQL Server, disconnecting idle sessions.
  • Faulty network infrastructure causing the connection to the server to be dropped. This includes bad Wireless quality.
  • Not installing critical updates, causing a faulty network driver to drop the connection.
  • The Database Property under Options “Auto Close” was set to True allowing the server to close “inactive” connections. This must be set to False.

But there are also a couple of settings which are known to cause disconnections. These are the TCP Chimney setting and the SyncAttackProtect setting. Disabling these settings on both the Server and the client has been known to resolve unexplained disconnections.

 

The Solution

Below are the steps from my previous “SmartList” article with the steps to disable the features and the links to the relevant KB articles: 

 

The following article explains the issues with the TCP Chimney primarily for a Windows Server 2003 machine:

“General Network error,” “Communication link failure,” or “A transport-level error” message when an application connects to SQL Server (KB 942861)

The following registry changes will disable the TCP Chimney feature on a Windows Server 2003 machine:

  1. Launch regedit.exe
  2. Edit DWORD EnableTCPChimney under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set data value to 0
  3. Edit DWORD EnableRSS under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set data value to 0
  4. Edit DWORD EnableTCPA under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set data value to 0
  5. Changes take effect after a reboot. 

 

The following article explains how to disable the TCP Chimney for a Windows Server 2008 machine.

Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008 (KB 951037)

The following command line command will disable the TCP Chimney feature on a Windows Server 2008 or later machine:

netsh int tcp set global chimney=disabled

 

Troubleshooting: Connection Forcibly Closed (TechNet Article)

The following registry changes will disable SynAttackProtect feature:

  1. Launch regedit.exe
  2. Add DWORD value named SynAttackProtect under registry key HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set value to 0
  3. Changes take effect after a reboot

 

More Information

For more information on this and related issues, see the following articles:

 

I hope the helps resolve some of those unexpected and weird errors for you. 

David

Read the article:
TCP Chimney Setting and SQL Server Error: TCP Provider: An existing connection was forcibly closed by the remote host

December 4, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 704

Developers: Microsoft Dynamics GP 2013 Service Pack 2 moves the Vendor and Customer Combiner & Modifier into core code

David Meego - Click for blog homepageNow before you ask…. the answer is No, I have not spent all week playing XBox One (as mentioned on the last post). Yes, we did get one and yes the kids have been using it, but the reason I have not posted on the blog is just that I have been very busy with support cases.

Talking of support cases……..

I had a case today where a developer was having issues when using the Customer Combiner. This functionality was part of PSTL (Professional Services Tools Library) and as of Microsoft Dynamics GP 2013 Service Pack 2 is now part of core code.

Note: During this article I will be referring to the Customer ID, this is the ‘Customer Number’ (CUSTNMBR) field. The same information is applicable to the ‘Vendor ID’ (VENDORID) field.

 

The Problem

The developer was receiving a “Violation of Primary Key Constraint” error when using the Customer Combiner. Using a SQL Profile Trace, they worked out that the problem was being caused when the Combiner’s code issued an update statement on their custom table which caused the error.

UPDATE

SET CUSTNMBR = ‘‘ WHERE CUSTNMBR = ‘

This update statement will work perfectly if you want to move a transaction table record from the old Customer ID to the new combined Customer ID. However, it will fail when applied to a master table record as it will create a duplicate record. This duplicate issue can occur with any table that has the Customer ID field as part of a primary key or another key that does not allow duplicates.

 

The Solution

So you ask, “How can I avoid this issue with my custom tables in my custom code?”. Well, if the Customer ID is used in a custom table which is a transactional table, there is no problem. If the custom table is a master table, then you will need to add some code into your customisation to handle the situation.

Note: The information below is based on how the code incorporated into the DYNAMICS.DIC for GP 2013 SP2 works, the details for the PSTL version of the code is different.

To understand how to add our extra custom code we need to understand how the original code works.


Customer Combiner and Modifier window

From the Customer Combiner and Modifier window, when you click Process, depending on the mode selected (Combiner or Modifier) it will execute a Dexterity global procedure which will in turn call a SQL Stored Procedure. The SQL Stored Procedure will then disable some triggers, call a second stored procedure and re-enable the triggers. The second stored procedure is where the actual work is done. This stored procedure will update all the tables in the core products (from Microsoft) as required and then run a script which will run an additional update statement against all tables in the database which have the column CUSTNMBR in them (except the core products tables already updated).

Now here is the cool bit; just before the stored procedure makes changes and just after it has made changes it calls Pre and Post scripts which can be used by developers to add additional code. This is similar to the Pre and Post scripts when working with eConnect.

The table below shows the scripts involved:

  Customer
Combiner 
Customer
Modifier  
Vendor
Combiner
Vendor
Modifier  
Dexterity Procedure rmCustomerCombiner rmCustomerModifier pmVendorCombiner pmVendorModifier
1st Stored Procedure rmCustomerCombiner rmCustomerModifier pmVendorCombiner pmVendorModifier
2nd Stored Procedure rmCustomerCombinerMaster N/A pmChangeVendorMaster N/A
Pre Stored Procedure rmCustomerCombinerPre rmCustomerModifierPre pmVendorCombinerPre pmVendorModifierPre
Post Stored Procedure rmCustomerCombinerPost rmCustomerModifierPost pmVendorCombinerPost pmVendorModifierPost

Note: The Modifier scripts don’t use a second stored procedure, all the code is in the first stored procedure. Use the CoreParams_SP2.txt file in the SDK (Software Development Kit) or a SP2 source dictionary to obtain the parameter list for the Dexterity global procedures.

There are two solutions to this problem:

  • Either use a Dexterity Trigger to run before the Dexterity Procedures listed above to update your custom tables, so that when the SQL code runs against your tables there will be no records to update.
     
  • Or, if you cannot use Dexterity triggers, you can update the Pre Stored Procedure to make the changes for your tables.

If you need to perform any actions after the updates have occurred, you can use an after Dexterity Trigger or the Post Stored Procedure.

  

The Recommendation

Now that the Combiner and Modifier functionality is part of the core code, it means that ALL Dynamics GP sites will have this feature and so it will be up to developers to ensure that their customisations are compatible. In the past not every site used the PSTL features, but that will change now that the code has become part of the core functionality.

If you can use Dexterity Triggers, this will be simpler as many Dexterity developers can trigger on the same scripts without clashing with each other. Changing the SQL Pre and Post Stored Procedures will need to be a manual process as it might have already been modified already by another developer or system administrator.

Start updating your apps.

Hope you found this useful. 

David

Originally posted here:
Developers: Microsoft Dynamics GP 2013 Service Pack 2 moves the Vendor and Customer Combiner & Modifier into core code

December 2, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 275

XBRL for Germany – Part 2: Create reports with XBRL … – MSDN Blogs

The second step in preparing Management Reporter to create the reports required for filing to the Ministry of Finance in Germany is creating and tagging the lines of your report with XBRL elements.This video steps through 

Originally posted here:
XBRL for Germany – Part 2: Create reports with XBRL … – MSDN Blogs

November 26, 2013 · April Olson · No Comments
Tags: , , , ,  · Posted in: Blogs I Follow Total Views: 164

Microsoft Surface 2 Review – and Tips

I bought a Surface 2 as an impulse purchase last week, knowing fully well that I might regret not buying the more beefier Surface 2 Pro. Lighter than the Pro, and only allowing apps to be installed from the windows store, the RT version of Surface just seemed limited. However, here is how I justified the Surface 2 RT over the Surface 2 Pro –

  • Its lighter (680 gm)
  • Longer Battery Life (around 10 hours)
  • I already have a laptop for the beefier work.
  • Cheaper (AU$ 527 for 32 GB version vs. AU $1016 for the Pro 2 64 GB). In addition, there was a discount on the tablet at the conference I was attending.

In addition, I could do the following easily –

  • Connect to Azure Virtual Machines
  • Use full version of Microsoft Office (actually almost full, as it doesn’t support VBA, but that wasn’t important for me).

One week later, I am not regretting getting the Surface 2 at all. In fact, I have been pleasantly surprised multiple times. It started with the two freebies

  • One year of Skype Unlimited World subscription : Living in Australia, and making a lot of calls to US, I always bought this anyway. I used to pay $45 every quarter for unlimited calls to US – so that was immediately a saving of $180 for me.
  • 2 years of 200 GB free with SkyDrive : This means $200 of free storage for two years. After that I will have to either pay or download and dump it else where.  I currently have a Dropbox subscription, but its $199.20 per year. Skydrive is almost half the cost, so there is a big chance I’ll probably just end up using Skydrive storage.

I almost missed these two – till Sharepoint MVP Sezai Komur pointed them out to me.

Multiple Windows Stacking –

I was meanwhile, already loving the ability to stack multiple windows side by side so easily. So I could have skype open for instant messages and read my email at the same time. If two windows were not enough – Scott Hanselman by changing the resolution, showed how you could stack 3 windows. Its 1080p, so this does work pretty well.

 

Screenshot (1) 

Remote Desktop

This was again extremely easy out of the box, and with the great support of running GP on Azure – I am really looking forward to this. Again Scott Hanselman has a great set of tips on making this experience optimal – Using a Surface 2 (RT/ARM) to get actual work done + Remote Desktop + Visual Studio + Azure.

Business Analyzer App

This app was the reason I bought my first tablet a few years back. It’s been updated quite a few times and I think with the lightweight surface – I’ll be using this even more.

CFOHOMEPAGE

Kickstand 

One of the big issues I had with the first version of the surface was the inability to keep it on my lap and work. Microsoft’s listened and added a Dual-position Kickstand. This really helps keep it stable when you can’t grab a table.

Conclusion

I think with the second refresh of the Surface, Microsoft really has a strong competitor to the iPads and the android tablets.  The iOS app store still has a much greater selection of apps, but for a majority of my work which is in Microsoft Office, Surface is now my go-to device. The native Remote Desktop support helps as well. I’ll still be gifting my grandma an iPad, but for most working people – Surface is going to be my recommendation.

UPDATE : Leon Tribe also has a great review on the Surface 2 – Review: Surface 2 32G (Why I Am Shocked How Good It Is)

Read this article:
Microsoft Surface 2 Review – and Tips

November 26, 2013 · Jivtesh Singh · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 237

Business Analyzer R5 First Run

With Business Analyzer R5 installed it’s time to take a look. Clicking the tile will launch the app which will show a Net Income – Summary view and, as is standard in Microsoft apps, a slice of the next part … Continue reading

Follow this link:
Business Analyzer R5 First Run

November 23, 2013 · Ian Grieve · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 175

Kuntz Consulting Inc. – Canadian Payroll Year End 2013 Tax Updates

Hi, I’m Jen, the owner & chief blogger at Kuntz Consulting Inc. I’m a CGA as well as a Microsoft Certified Professional in the Dynamics GP application. My home base is just Email: info@kuntzconsulting.ca. © Copyright, 2014 

More:
Kuntz Consulting Inc. – Canadian Payroll Year End 2013 Tax Updates

November 22, 2013 · Jen Kuntz · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 119

Xbox One has arrived – In Stores Now

David Meego - Click for blog homepageSix months after the Xbox One was revealed, Launch Day, November 22nd 2014, has arrived.

Welcome to a new era of gaming and entertainment.

For all the latest information check out the Xbox website and Major Nelson’s Blog:

.

Here is your invitation to jump ahead:

Xbox One: Invitation (direct link)

(Please visit the site to view this video)

 

Xbox One Day One Launch Videos:

Xbox One Launch: Greetings from Times Square! (direct link)

(Please visit the site to view this video)

 

Xbox One Launch: Sweet Rides, Armored Trucks and the Undead (direct link)

(Please visit the site to view this video)

 

To understand the bigger picture with one device for your entertainment have a look at this video:

Xbox One All-in-One Demo with Yusuf Mehdi and Marc Whitten (direct link)

(Please visit the site to view this video)

 

For those interested:

The Making Of Xbox One’s Invitation (direct link)

(Please visit the site to view this video)

 

More videos on YouTube:

 

For those of you “pulling a sickie” today, Larry Hryb (aka Major Nelson) has organised a Doctor’s Note for you to use:

 

Enjoy

David

Visit link:
Xbox One has arrived – In Stores Now

November 22, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 257