>Microsoft SQL Server DSN Configuration

January 31, 2011

>As I wrapped up fellow MVP Victoria Yudin‘s book just a week aback and prepared to write a review, I was reminded of the importance of properly configuring your Microsoft Dynamics GP DSN connection — the artifact that allows the client software to read and write data to your company databases and the system database — something the Microsoft Dynamics GP installation gracefully setup automatically nowadays for you. So, if this is the case, why is it that so many people still have issues with DSN connections and the Microsoft Dynamics GP client not “seeing” the server, I wondered.

So, I figured in this article, I would go back to basics to walk through some of the common issues and demistify the Microsoft SQL Server DSN configuration options that the setup gracefully takes care of.

1. First up, if you are on Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2, you should be using the Microsoft SQL Server Native Client driver. At this point, if you have any legacy ODBC drivers for systems that have been upgraded from SQL Server 7.0 or SQL Server 2000, these should not be used to connect to SQL Server 2005 or SQL Server 2008, because a) you are not taking full advantage of the Native Client driver’s performance, and b) simply because the old driver is not designed to be used with the new.


Getting started


Clearly name your connection so you can identify which version of GP or server you are targetting. If you have multiple physical environments, for example, test and production, clearly name the driver to distinguish which environment you are targeting. Also, note that you can setup a driver that points to an instance of SQL Server by using the MACHINE\INSTANCE_NAME convention. Starting with version 10.0, the ODBC name must be exactly the same across all workstations where Microsoft Dynamics GP is installed.

2. Microsoft Dynamics GP only supports SQL Server authentication. As much as you complain or rant about the system not supporting Integrated Windows Authentication, you cannot set the authentication method to anything other than SQL Server authentication, in which case you will need a login ID and password only to test your connection. You can certainly avoid going through the other steps of the setup if you choose to uncheck the Connect to SQL Server to obtain default settings for the additional configuration options checkmark.



Setting up the Authentication method

Also note the checkmark option’s prompt. The settings are read from SQL Server to obtain the default ODBC connection settings. This might sound redundant, but you will understand what this means next.

Some frequently asked questions as well are:

a) Why can’t I use my Microsoft Dynamics GP account to authenticate my ODBC connection?

Because Microsoft Dynamics GP encrypts passwords on SQL Server. Since the password is encrypted on Microsoft SQL Server you would have to enter the sequence of characters that are a part of the encrypted password to authenticate and this is simply not feasible as well, you don’t know the encrypted password to begin with. For more information see Why does Microsoft Dynamics GP encrypt passwords over at Developing for Dynamics GP.

b) Does the password I enter here get stored with the Connection?

Categorically No! The user Id and password information entered here is only used for verification of the SQL Server default settings and testing of the connection itself. They are never stored with the setup.

3. With newer versions of Microsoft Dynamics GP, there’s no need to set the default database to Microsoft Dynamics GP system database, DYNAMICS. However, since I am an old timer and still have my own quirks, I do it as a standard practice. The default database is the master database when no other database is specified in the connection.

Choosing default database
What is still standard though is to disable the Use ANSI quoted identifiers and Use ANSI nulls, paddings and warnings in your connection settings. Now, keep in mind from my previous observation, that these connection settings are defaulted from Microsoft SQL Server settings at first. Why I emphasize this? Because I always get asked, Why are these checkmarks on? When in doubt, ask your Microsoft SQL Server administrator to show you the SQL Server properties for Connections.
Microsoft SQL Server Properties window – Connections tab
Also, you will want to note that at this point you can define a Mirroring server if you are running a mirrored database environment for your Microsoft Dynamics GP databases. For more information on Mirrroring, see KB article 926490 – Description of the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP (access to CustomerSource or PartnerSource is required to view this article).

These are some frequently asked questions:

a) Why can’t I enable ANSI quoted identifiers and ANSI nulls, warnings, and pads?

The answer lays with Microsoft Dexterity. Dexterity (through the Runtime Engine) does not support quoted identifiers for character strings or hetorogeneous transactions — the function of ANSI nulls, warnings, and pads is to maintain consistency of transactions and queries across distributed platforms. Since Microsoft Dynamics GP is a client/server based application and the system database, DYNAMICS was designed to live on the same SQL Server with the company databases, there was really no need to maintain this compatibility. After all, no heterogeneous query would ever be issued to begin with. 
4. Some more settings that are key to keep in mind reside with this wizard page. Among them Perform translation for character data, which receives most of the attention among Microsoft Dynamics GP consultants and database adminstrators alike. This setting was designed to do, as it name suggests, translations of characters between the client code pages and the server code page.

Defining connection settings
In earlier versions of MDAC, i.e., MDAC 2.1 or later version of the SQL Server ODBC driver (version 3.70.0623 or later) or the OLEDB provider (version 7.01.0623 or later), under some circumstances you could experience translation of character data from the client code page to the server code page, even when Autotranslation is disabled for the connection. Autotranslation is not the only mechanism that can result in code page conversion. The SQL Server 7.0 ODBC driver and OLEDB provider introduced a behavior when connecting to MSDE 1.0, SQL Server 7.0, or later versions of either. All SQL statements sent as a language event are converted to Unicode on the client before being sent to the server. The end effect of this is similar to an Autotranslation of all data flowing from the client to the server through a language event, regardless of the current Autotranslation setting for the connection. This will not introduce any difficulties except when trying to store non-translated character data from a code page other than SQL Server’s code page.

So why do we talk about code pages? Because a) Microsoft Dynamics GP does not support Unicode characters. If you are a database developer it means, there is no such thing as NCHAR, NVARCHAR, or NTEXT table columns defined throughout the system or company databases, that would otherwise allow for Unicode character storage. Since this has been the case since version 1 of Microsoft Dynamics GP, it’s easy to see how a different client code page (as in, a different language being ran at the Windows operating system level) could cause issues if this option were to be enabled). I have ran my own tests with a Russian locale (code page 1251) and 1250 code page on SQL Server and when this option is enabled, I get jumbled data in my GP tables when submitting Russian characters to be stored on the server. That’s because, while there are subtle differences between code page 1251 and code page 1250, the latter only supports Eastern European languages that are based on the latin character set.

5. Once you have completed the settings, it’s now time to review the summary for all the options you have chosen. 

Reviewing connection configuration
6. Finally, you can test and you should now be good to go.

Testing connection
I hope this review of ODBC settings for the SQL Server Native driver have served as a good first step in understanding the configuration. With so many settings it’s easy to see why any subtle change would cause issues across the board..

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Advertisements

>The always busy Mark Polino

January 27, 2011

>

Every once in a while, I run into someone who inevitably ask me “what do I need to do to become a Microsoft MVP?“. At some point in the past, I tried to answer this question with at least some of the things that I do in the community, just as an example of the numerous ways you can contribute. Frankly, there’s no one way to become a Microsoft MVP and furthermore this is the best response I can give to this question: You have to be an MVP without the title, before even receiving the Award.

So, what does this have to do with Mark Polino and why am I talking about MVPs you may be asking. Well, it turns out that Mark is probably one of the busiest MVPs I have ever known… the guy simply does not sleep! Even though, like me, he would argue that 4 hours constitute a good night rest.

Today in particular, I wanted to highlight yet another contribution from Mark to the community – among all the others that you will see in the next few months. As it turned out, Mark had been working on a top secret project — ok, maybe I am watching way too much TV. The result of those efforts is now MyGPSearch.com. This new search engine is built on the Google search plarform and compiles information from all existing blogs in the community. You may ask, and how is that new or different? What’s new and different is, MyGPSearch is also able to search for a query across the former newsgroups and the Community website. This allows you to find a topic even in discussions conducted between individuals in the community.

Mark makes it clear though that in adding any new resource he will carefully evaluate whether you are trying to advertise your company or simply sell something. If that’s the case, your site may not make the cut.

As if this wasn’t enough, MyGPSearch is also available for the Android platform (QR code below), so be sure to get the app if you really need to have a handy resource, literally, while out at a client.

Don’t have an Android phone? Make sure to check out MyGPSearch.com mobile site. With so many choices, please don’t tell me you can’t find what you need.

The next time you ask, “What do I need to become an MVP?”, just look it up in MyGPSearch.com. I have added a permanent link to MyGPSearch.com on the right frame of this blog.

Ok, now my head hurts!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/


>Finding out the version of Office Web Components being used with your Microsoft Dynamics GP home page

January 25, 2011

>Seriously, I wasn’t planning on doing any more articles on the home page – at least not for now. But you have to admit this one is compelling. To find the version of the Microsoft Office Web Components running on your system,

1. Right-click on any metric graph currently being displayed on your home page.

Office Web Components options

Choose the About… option to continue.

2. You will now see the version number of the Office Web Components installed and being used with Microsoft Dynamics GP.

Office Web Components version

If you have any questions on a specific version, please see my article Microsoft Dynamics GP 2010 and Office Web Components for more information.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/


>The Microsoft Dynamics Convergence Blog site

January 25, 2011

>

More on Microsoft Dynamics Convergence 2011 Atlanta… if you are excited about the possibilities at Convergence and you are looking for some good information on what to do before, during, and after the conference, then the Microsoft Dynamics Convergence Blog is for you. Headed up by Microsoft’s Alexa Hoekstra, the Convergence blog is a very good resource with helpful tips and articles, whether you are a battle-tested Convergence goer or a newbie to the event.

Haven’t registered yet? What are you waiting on?

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/


>Changing the metrics properties on the Microsoft Dynamics GP home page – Part 2

January 24, 2011

>Part 2 of 2 – Changing the metrics properties on the Microsoft Dynamics GP home page.


In Part 1 of the series I showed a couple cool things that can be accomplished on the Microsoft Dynamics GP home page with metrics and changing graph properties and so on. Today, I will explore two more features that I think will make you fall in love — ok, let’s not get carried away — with the Microsoft Office Web Components, and in particular the Chart Control: the Toolbar and Command and Options selections.

Toolbar

The Toolbar option enables a graphical toolbar above the metrics being displayed.

Office Web Components – Toolbar option
Enabled Toolbar option

Now, with the toolbar enabled, you have access to some of the elements discussed in the previous article and more. You can change the graph layout and some of the Data properties as shown in the previous article.

Command and Options

Of all the options presented, the Command and Options is one of my favorites as it allows you to visually enhance your graph and even change the type of graph, similar to the Data options. Take a look at the following visual enhancements produced by changing some of the Border/Fill properties.

Command and Options – Border/Fill option

Finally, if you have worked with Microsoft Excel producing graphs in the past, then you now probably have a lot of experience in creating and modifying graphs, so I won’t delve into this. Nonetheless, know that they are a number of options that you can play with to bring some life to your charts and even copy/paste these from the home page onto some fancy report that you must deliver, so why not bring some life to those graphs?

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/


>Microsoft Dynamics GP 2010 Implementation book review

January 24, 2011

>Just around the last week of Christmas I got myself a brand new copy of my friend, Victoria Yudin‘s Microsoft Dynamics GP Implementation book. No, I did not get a free copy and actually refused one in support of my fellow MVP’s work. I spent the last few weeks reading through each chapter — I still use a highlighter — and thought I would provide some insight on the content and the author.

On the Content

As a seasoned implementation veteran, I have to admit there are many elements of this book that appeared pretty straight forward, but relevant if you are a new comer to the Microsoft Dynamics GP world. Victoria does a good job of walking the reader through the importance of project management, assembling a good team, executing the implementation, and delivering post implementation support as an integral part of the project. I have say, that I have gone through the PMP certification and indoctrinated myself with the Sure Step methodology and, unless you do project management for a living, this book rounds out very well all the considerations needed to deliver a solid project.

Now, if you are a hands-on person on the project, Victoria also makes sure you have some insight into the system requirements, infrastructure, installing SQL Server, and setting up the system.

I would have liked to see more of the “in the trenches” experiences that Victoria has been through in the book, but I can see how the book would have quickly become anecdotal and perhaps hard to follow given the topic, since all implementations are different in nature. Nonetheless, items such as, the importance of the collaboration and interaction between the configuration team and the customer’s IT team seem not have made it to the book. Also, some of the systems configuration requirements seem to have come from the standard Microsoft recommendations and don’t necessarily reflect the complexities of some environments and other considerations needed in such environments. Now, on the good side, at least the standard system recommendations are in the book since I have seen many installation teams that have not a clue these requirements even exist.

If you are a seasoned Microsoft Dynamics GP professional, there are still a few things you can learn – for example, are you still insisting in parallel testing? Hear what Victoria has to say. If you are new comer to the Microsoft Dynamics GP implementation world and/or have delivered a couple implementations so far, then this book is for you.

If you are an individual or an organization evaluating Microsoft Dynamics GP and want to get a pretty good idea on the elements that go into making your implementation successful along with considerations on environment and team, this book is for you too! The books language is simple and easy to digest, yet solid in its delivery of each topic covered.

On the Author

Having known Victoria for the past 3 years or so, it is easy to see why she would have been inclined to write a Microsoft Dynamics GP implementation book. Victoria brings over 20 years of experience delivering high quality Microsoft Dynamics GP implementations and she has the distinguished honor of being a Microsoft Dynamics GP MVP for the past 8 years. If you are truly looking for a Microsoft Dynamics GP implementation book from a well rounded individual you can trust, this book is it! Keep in mind that outside Microsoft’s manuals and material, there is little available on the subject at your local library.

Victoria has fielded and answered more than 5,000 questions from the community over the past 8 years, runs a blog that constantly keeps you updated on her reporting prowesses, and manages to still keep a cool head in tough situations. No easy job considering the nature of enterprise resource planning implementations.

Go and get your Microsoft Dynamics GP 2010 Implementation book now.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/


Changing the metrics properties on the Microsoft Dynamics GP home page – Part 1

January 23, 2011

Part 1 of 2 – Changing the metrics properties on the Microsoft Dynamics GP home page.


In this series I wanted to surface a little known secret about the standard Microsoft Dynamics GP Home Page metrics: you can change the graph properties…no coding required! In fact, there are many graph properties that can be changed.

Gross Profit for the Past 12 Months sample metric

Microsoft Dynamics GP makes use of the Office Chart Control, a component of the Microsoft Office Web Components to display the metrics that you have become accustomed to see on the home page — see my article Microsoft Dynamics GP 2010 and Office Web Components for more information on the different versions supported.

When you right-click on the metric of your choice a menu becomes available that will allow you to choose from a number of options, as shown below.

Today we will be looking at the Data properties.
Data

Command and Options window – Data Source tab

The Data option presents the Commands and Options window with 3 tabs: Data Source, Data Sheet, Type… you have guessed correctly! You can make changes to what’s being displayed on the metric by creating a new data sheet or reading data from a query. However, this information will be lost as soon as you refresh the home page.

Perhaps, the option that’s most important at this point is the Data Sheet option. The Data Sheet option shows the numbers crunched by GP for each category and series of the metric being displayed.



Command and Options window – Data Sheet tab

 The good thing about the Data Sheet tab is, you can actually use the numbers displayed to reconcile against your subsidiary module, since that’s the source of the data for all metrics.

Finally, the Type tab allows you to change the type of graph being displayed. Most metrics are set to display bar graphs by default, but you can make a subtle change to the type of graph to enhance your reading of the data being presented.

Commands and Options window – Type tab

The series continues tomorrow exploring some of the other properties available to you as a user.
Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/