SQL 2005: Errors due to SQL Server Browser not running

With SQL 2000 I was fairly proficient with troubleshooting connection issues, adjusting the client library network settings, and even dealing with dynamic ports and different SQL Server instances. But with SQL 2005, a few features were introduced that change how I troubleshoot connection issues. The features probably help to reduce the number of connection issues by eliminating most client configuration, but when issues do come up, my impression is that they can be more difficult to diagnose with SQL 2005. Fortunately, the resolution is typically simple, but diagnosing the problem can be very frustrating.

Two times in the last several months, I've run into a problem where I simply cannot connect to SQL Server 2005 from another computer. I know the SQL service is running, and I can login to SQL from the server itself, but client workstations and other servers are unable to connect. This has prevented me from logging into Dynamics GP, or it has prevented an eConnect integration from working.

The error message I receive looks something like:

SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [0xFFFFFFFF].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Reading this message might have you think, "Ah, I forgot to enable remote connections!". But then you check the server settings, and you find that Remote Connections are in fact enabled. At this point, the error message becomes misdirection.

You can ping the server, and browse the server shares, so the network is okay. And strangely, you may find that every other workstation in the company can login to GP and connect to the SQL Server without any issues. So you are then possibly tempted to think that it is a workstation issue--again, more misdirection.

The next step is to Google the message, which will provide tons of fruitless newsgroup posts by people who have the exact same problem, but also lacked an answer. Until you stumble onto this excellent post by the Microsoft SQL Server Protocols Team (a protocols team!, who knew!).

It provides an exhaustive list of SQL Server 2005 connection errors, along with actual English descriptions of what those cryptic or kurt messages actually mean. For each error, they also provide some recommended steps for troubleshooting and resolving the error message.

In my case, I was running into lovely error Message #6. And their recommendation: You should enable the SQL Server Browser service on the server.

Sure enough, the SQL Server Browser service was not running. Once I configured and started the service, the login errors instantly disappeared and everything worked fine. Neither the error message, nor the symptoms (other workstations were fine) would have naturally led me to think of the SQL Browser service, and even though I have run into this exact issue before, I simply forgot the role that the Browser service plays with SQL 2005.

In my next post, I'll discuss the SQL Server Browser service, what it does, and why we need it.

White Papers Aplenty

Sometimes it feels like I go through phases where white papers just seem really applicable to issues that come up both within my company and with customers. Having just been through one of these phases, I thought I would share some of my favorites...


Microsoft Dynamics GP and SQL Server 2008
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/mdgpsqlwhitepaper.htm?printpage=false
Customers: https://mbs.microsoft.com/customersource/documentation/whitepapers/mdgpsqlwhitepaper.htm?printpage=false
Interesting whitepaper on the benefits of compression with SQL Server 2008, including benchmarking results on performance.


Choosing a Development Tool for Microsoft Dynamics GP 10
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/gp10_toolswhitepaper.htm?printpage=false
Customers: https://mbs.microsoft.com/customersource/documentation/whitepapers/gp10_toolswhitepaper.htm?printpage=false
This is a great overview of the available development tools, for both developers and application consultants to familiarize themselves with the licensing costs as well as the skills needed to successfully use them, and the capabilities of each tool.


Planning for Security in GP 10
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/msdygp10_securityplanwp.htm?printpage=false
Customers: https://mbs.microsoft.com/customersource/documentation/whitepapers/msdygp10_securityplanwp.htm?printpage=false
This serves as a starting point for security discussions, and it looks at security in a holistic way (not just within the Dynamics client specifically). It reminds me that security is more than roles and tasks.

Deploying Microsoft Dynamics GP 10 in a Multinational Environment
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/deployinggpmultinationalenvironment.htm?printpage=false
Customers: I could not find this on CustomerSource, but please feel free to post on this blog if you cannot access.
This is an older white paper, but I always re-read it before embarking on a multinational implementation. It just helps remind me of the installation considerations and pitfalls of multiple countries, localization requirements, and languages.


Strategies for Multi-Entity Environments in Microsoft Dynamics GP 10
Partner: https://mbs.microsoft.com/partnersource/documentation/whitepapers/strategiesmultientityenvironmentsgp10.htm?printpage=false
Customer: https://mbs.microsoft.com/customersource/documentation/whitepapers/strategiesmultientityenvironmentsgp10.htm?printpage=false
This is another older white paper, but it again reminds me of the considerations of one database -vs- many. I have a worksheet I have developed to assist with the assessment, but this document definitely helped me refine it further.

Achieving Compliance Objectives in Microsoft Dynamics GP 10

Partner: https://mbs.microsoft.com/partnersource/documentation/whitepapers/helpachievecomplianceobjectivesgp.htm?printpage=false

Customer: https://mbs.microsoft.com/customersource/support/documentation/whitepapers/msdygp_whitepaper_achievecomplianceobjectives.htm?printpage=false

This white paper presents a general view of compliance, how to begin the process, and some aspects to review. It is a great starting point for those that are new to compliance requirements, and need some assistance in where to begin.

Please share your favorite white papers, and I will update the post. You can find many more white papers through Deployment>>White Papers on PartnerSource, or through Documentation>>White Papers on CustomerSource :)

Add Fields to Visual Basic does not work in GP 9

I've run into this several times, but I never bothered to figure out the cause or solution.

In GP 9, sometimes when you attempt to add new fields to Visual Basic, the fields will not appear in the VBA editor. The window will appear in the VBA editor, but the fields will not be available in the object drop down list in the code window.

Thankfully David Musgrave solved this long ago in this eggheadcafe.com post.

It seems to be caused by a bad Dynamics.vba file. When I deleted my Dynamics.vba file, the fields added fine.

Nifty Freebies

Sometimes I get questions that make me ask myself, "Why hasn't someone asked me that before?". Of course, in these cases, the resulting answer is never as simple as "there is a setting for that". But I have learned over the years that although something is not an option directly in Dynamics GP that I may be lucky enough to find out that there is a free Professional Services Tool to provide the functionality I need.

For those of you that are not familiar with them, the Professonal Services team is a fabulous group of folks who offer a wide range of services and tools to customers and partners.

For partners, check them out at... https://mbs.microsoft.com/partnersource/resources/support/supportinformation/msdy_winwithexpressconsulting.htm

For customers, you can get similar info at...
https://mbs.microsoft.com/customersource/serviceplans/professionalservices

As you might be able to tell already, I am a big fan of this team. They are great folks, and also crazy smart when it comes to finding creative solutions to issues.

But, back to the freebie tools. Some great examples of these follow, paraphrased/borrowed from the PSTL documentation:

Default Add Item for POP/SOP

• Changes the Add Item option to default as marked in Sales Transaction Entry, Purchase Order Entry, POP Receivings Entry, and Purchasing Invoice Entry. A must have for companies that do not want to allow non inventory items, or want to "opt in" to non-inventory items as opposed to assuming that anything keyed incorrectly is non-inventory.

IV Post To GL

• Changes Inventory batch functionality so it looks at the Posting Setup to determine if the batch should update the General Ledger. I love this one, because it prevents the confusion that inevitably comes from having to select/deselect this option every time.

SOP Default Site per Line

• Affects how a site defaults on sales order line items. Instead of using the default site from the SOP window, it uses the default site assigned to the item under Cards>>Inventory>>Quantities/Sites. I love this one, because it addresses the needs of companies with multiple warehouses the stock different items.

Payables Select Checks Combiner

• Consolidates checks to the same vendor when adding to an existing check batch. This is great tool when users build batches by different ranges, and repeatedly add to the same batch.

And just one more...

Toolkit

• Rebuild the GL00105 without a SQL query tool, turn on/off the dexsql.log, rebuild indexes, and recreate dex procs and tables. This is a great resource for internal IT support to perform many common support tasks.

A reminder from fellow blogger David Musgrave, to not forget about the Support Debugging Tool, another fabulous freebie that partners can use to increase their efficiency in solving issues. Here is a link to David's blog post on it:

http://blogs.msdn.com/developingfordynamicsgp/archive/2009/06/05/support-debugging-tool-build-11-released.aspx

Please feel free to share your favorite Professional Services Toolkit items, free or otherwise. I will update the post with additional suggestions :) Have a great week!

Did you know? Project Macros

How many of you have been caught off guard after installing project accounting on existing Dynamics GP installation, when it begins asking users if they want to add project info for vendors every time they try to enter purchase orders?

Many of you may already realize that GP support has a set of macros, one for Customers and one for Vendors, that will run through and save the default settings for project. A little thing that saves users from having to either populate the records manually, or respond to the message every time.

Feel free to email me directly for more info, christinap@theknastergroup.com.