Identifying Orphaned SQL Server Users

Scenario 1:  Your client has a production Dynamics GP server, and decides that they want to have a separate test server for Dynamics GP.  You install SQL Server and Dynamics GP on the test server, and then you restore copies of the Dynamics database and company databases, and viola, your test server is ready.

Scenario 2:  Your client calls and frantically tells you that their Dynamics GP SQL server has died.  Fortunately, and to your surprise, they actually had good database backups from the prior night.  You replace the failed hard drives, rebuild the server, install SQL, and restore the Dynamics and company databases.

In both of these scenarios, what is a common administrative hassle that you will likely eventually run into?

Well, when you restore a database from SQL Server A onto SQL Server B, the database users will not have corresponding SQL Server logins.

For Scenario 1, there is a very handy "Capture Logins" script provided in KB 878449 that allows you to transfer the SQL logins to a new server, saving alot of time and hassle deleting users and recreating users.

For Scenario 2, if you or the client don't regularly run the Capture Logins script (I haven't met anyone who does, although it probably isn't a bad idea), then you will have a fresh SQL Server with no logins, and databases with a bunch of orphaned users.

This isn't earth shattering news or even a big deal, just a bit of a hassle.

But while studying for the painful SQL 2008 exam, I came across a system stored procedure that identifies such orphaned users.

sp_change_users_login @Action='Report';

And contrary to what I recently wrote about the SQL 2008 exam content being largely useless to the GP crowd, I actually used this script today!  A client's SQL server had died and been rebuilt, and we were having to reconstruct a very complex, horribly designed custom integration that required numerous SQL Server logins.  While trying to figure out which logins were missing on the rebuilt server, I remembered this command and gave it a try.

Sure enough, it produced a nice clean list of orphaned users, and I saw the one that I was looking for.

Sure, you could compare the SQL logins with the database users in each DB, but this command is very simple and very easy.  And if you have security conscious clients, they might want to run this to remove any orphaned users so that they have clean databases.

And there you have it.  The first semi-usable nugget that I've gleaned from studying for the SQL exam!

The Software Testing Disconnect

So you've taken some exams and become "certified"? So how much better prepared are you to handle those complex client requirements that invariably come up?

I've been in conference rooms with teams of very smart people, and all the certifications on the planet wouldn't have helped us with certain basic, fundamental requirements that were both challenging, and which pushed well past the limits of certain areas of Dynamics GP.

While I am a big proponent of certification, the limitations of the exam process are still frustrating to me.

I bring this up because I'm studying for my third exam to update my GP certifications.  GP 2010 Install & Config felt pretty relevant.  There are a lot of valuable real world topics and knowledge that aren't included in the exams, but there's only so much you can cover in 75 questions based on the instructor led training materials.

The GP 2010 Financials exam felt, well, a lot like a GP 10 Financials exam.  I wasn't able to tell if the exam had even been updated, and it felt surprisingly easy.  It did cover all of the basics, but it seemed a little too basic. 

And now, the reason why I am writing about this topic:  The SQL Server 2008 Implementation and Maintenance (70-432) exam.  This exam, like it's SQL 2005 equivalent, is a doozie.  For the average GP consultant, I think this exam is out in left field.  There are certainly sections that are very relevant to the GP world (configuration, backups, maintenance plans, security, etc.), but then there is a lot of content that is so irrelevant to the GP crowd that it's painful to waste brain capacity memorizing it just for the test.

The emphasis on enterprise features is predictable--those tend to be the fancier, cooler sounding, and newer features that allow Microsoft to compete against the big competitors.  But an 8 member SQL cluster just isn't a common occurrence for GP customers.  And honestly, if one of my clients had a server cluster for GP, I would want a dedicated SQL Server expert by my side to handle those complexities.  I don't want my family physician performing my brain surgery.

Here are a few examples of questions that make my eyes roll:

What is the command to extend the expiration period for a merge replication subscription?

What @sync_type value should you use for new peer-to-peer replication nodes?


When would you use an On change: prevent policy?  When would you use On Demand?


What is the exact syntax of the BACKUP DATABASE T-SQL command?

When do you want to configure the cost threshold for parallelism, and what is the syntax for the T-SQL to do so?

What is the exact syntax of the sp_add_notifications command and parameter values? 


If you know the answer to any of these, well, move to the front of the line--you can be my DBA any day.  Most of these are not applicable to GP, and for those that are, well, that is what Books On Line is for.  Memorizing the syntax of obscure or rarely used T-SQL commands that can be performed in SQL Management Studio isn't really the value that I bring to my clients.

For the rest of us that focus more on GP, and the SQL Server features that are relevant to GP, many of these questions seem like a pedantic waste of time.

I understand that there is no longer a "SQL for GP" exam, and I can understand the desire to point everyone to a single, uniform SQL exam track, but there used to be a SQL exam for GP consultants, and I think it did a great job of covering the topics that were relevant to GP folks without battering us with features that either aren't used, or can't be used, in GP environments.

Studying for the SQL 2005 exam, I did learn about some SQL Server features that I never knew existed, but since that last exam, there have been exactly zero times that I have needed to know about those features.  Kind of interesting, but completely irrelevant.

This is an age old question that has been deliberated for years, but having to study again for the SQL exam brought it up again in a very specific way.

For those other poor souls out there also having to take the SQL 2008 exam, I feel your pain...

Jealous Analytical Accounting

While doing some testing with Purchase Orders that have Analytical Accounting codes, this message popped up when I tried to save an existing PO.

Uhhh, seems like AA is a little insecure.  "even once"?  Is that like "you don't call or write anymore"?  Someone apparently needs some attention...

Removing Dynamics GP companies that no longer exist

This is a common problem and easy to fix, but I had a very hard time finding the KB article and corresponding SQL script on PartnerSource, so I'm posting this for my own reference so that I don't have to dig around using the often unreliable horrible PartnerSource search.

I had a call today with a client that has over 120 GP company databases.  Because there are so many databases, and so much data, they don't maintain all of the databases in their multiple test environments.

In the test environment we were working in, there were 22 company databases setup, but naturally GP still thinks there are 120, and displays them all in the company listing during login.  Normally that wouldn't be an issue, but we were working with a 3rd party product that scanned all databases, and if a database wasn't present, an error would occur.

So this scenario is essentially the same as if a GP company database had been deleted through SQL Server Management Studio, instead of through GP.  The records for the company database exist in the GP tables, but the corresponding master..sysdatabases records and physical databases "no longer" exist.

The KB article is titled "Deleting a company in Microsoft Dynamics GP", and is supposedly KB Article 855361.  At least that's what it says on the version I found--there might be more than one version, as the client had the same script, but with a different KB number in his notes.

After more searching, I found this article on Customer Source, and this apparently identical article on PartnerSource that appears to reference the same script.  But I can't find a KB Article number.  Here is a File Exchange link to download the SQL script referenced in these two articles.

Despite my repeated attempts to search for 855361, KB855361, and even "Deleting a company", I couldn't seem to find the article through the PartnerSource search.  Fortunately a Google search turned up a link to the article, and I was able to access it directly via the URL linked above.


UPDATE: It looks like all of the links above no longer work.  Here is a link to download a zip file containing the KB article and SQL script.  Please note that both of these files are very old--from 2009-2011, so they may not be applicable to GP versions newer than GP 2010, and you will want to be careful to review and test them prior to using them in a real environment.

https://1drv.ms/u/s!Au567Fd0af9Tn22I-s9ZCibHbjv7

The Basics: Deductions in Arrears

New functionality often comes out, and I find it is ignored, or users think its "too complicated" to spend time or money on configuring. So I thought I would break down one of my favorites in recent years- Deductions in Arrears (DIA). This was part of Payroll Extensions in GP 10.0, and it makes life so much easier for payroll folks who have to deal with the impact of part time or seasonal employees when they do not earn enough to cover their deductions.

DIA will track when a deduction is not taken completely due to a shortage of wages. It will then attempt to collect the deduction until the obligation is met in subsequent payrolls. You can manage the arrears, and make manual adjustments (for example, if a portion fo the arrears is forgiven).

First, to view the DIA options, you must grant access to the alternate HRM Solution Series windows for Payrolls using Microsoft Dynamics GP>>Setup>>System>>Alternate/Modified Forms and Reports. These steps are covered in detail in the Payroll Extensions user guide.

In the spirit of keeping it simple, here are the fields available in Deduction Setup for DIA (Microsoft Dynamics GP>>Setup>>Payroll>>Deductions):

• Allow Arrears: Mark if arrears transactions should be tracked for this deduction. For example, DIA might be used for health insurance deductions but not for voluntary United Way contributions.
• Mandatory Deductions: These work in tandem with Transactions>>Payroll>>Mandatory Arrears, to create arrears transactions for an employee who was not paid in a payroll. For example, a part time employee has no hours to be paid but still needs to pay their health insurance premium. You can use mandatory arrears to create the arrears transaction for the employee.
• Collect When Possible: Mark this if you want the arrears to be collected even if the deduction is not included in the payroll build. For example, if an employee has an arrears transaction for health insurance, should it be collected during a bonus payroll which does not include a health insurance deduction?

Although all of these options are available in deduction setup, they can be modified per employee as well, Cards>>Payroll>>Deduction.

So, during a payroll, arrears transactions will automatically be created for deductions that are marked to allow arrears and cannot be fully collected from the employee during the pay run. You can then view the arrears transactions using Cards>>Payroll>>Arrear Transactions. By employee, you can view the number of arrears pending including the original, collected, and remaining amounts. There is also an adjusted column, which tracks any manual increases or decreases to the arrears amount. You can use the Trx # expansion to view detailed transaction information for the selected arrears, including amounts collected and dates.

Those are the basics. In most cases, it takes less than 15 minutes to configure DIA and it saves a tremendous amount of time and manual tracking. Plus, it increases the odds that correct amounts are collected from employees as quickly as possible!

Errors from Dynamics GP Local Temp Tables

This week I received an e-mail from a client regarding an error they received when posting SOP Invoice batches.

A hundred batches a day post fine, but recently, two batches have failed with the following errors:

An open operation on table 'IV_Cost_Variance_TEMP' failed because the path does not exist.

An open operation on table 'IV_Cost_Variance_TEMP' has an incorrect record length.

My first thought was that maybe there is an issue with a third party module or GP version where GP is somehow trying to write data to a temp table that has a slightly different schema.  But this is a vanilla GP install with the proper service pack.

So today I Googled the error, and I found some puzzling blog articles that mentioned C-Tree files.  When I saw dates of 2004, I thought okay, those are just old.  But then I saw more from 2009 and 2010, still mentioning C-Tree files.

In a comment posted in 2010 to one of his 2009 blog posts, David Musgrave directs someone with a similar error to these two Microsoft blog posts:

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/10/an-open-operation-on-table-temp-table-errors.aspx

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/04/21/unexplained-temp-table-errors.aspx

As David explains in the first post, "Not all temporary tables used by Microsoft Dynamics GP are SQL tables created in the tempdb system database.  Many temporary tables used for quick processing in windows use local ctree temporary tables."

Say what???  This may be common knowledge for a lot of folks, but I naively thought that GP had been fully transitioned over to SQL Server.  Apparently there are still some vestigial pieces of code that rely on local file system temp tables.  I would have thought that the MCP at Microsoft would have squashed those rogue programs, but apparently they are still riding their Light Cycles around in the GP code.  Naturally, there are probably some performance benefits to being able to perform temp table operations locally, but I am hoping that there are plans to wean GP off of the C-Tree implementation of those tables using the file system.

As the second article by Allan Cahill explains, they found that a local anti-virus application was taking just enough time to scan the temp files that it was locking them, preventing GP from deleting them.  Excluding those files from the scan seemed to resolve the issue.

I have forwarded this information to my client, and I don't yet know whether this is the cause of my client's errors, but I found this pretty interesting.

So keep those Temp folders nice and clean!  (feels like advice for a Windows 3.1 user...)

Speed Certification Exams!

Things have been very busy lately, so I apologize for my absence (in case there was actually someone out there that noticed).

My in-laws arrived from China in July, and my wife delivered our second daughter, Sarah, on August 9th.  Let's just say that those things, on top of an ever growing backlog of work have kept me more than busy lately.

So as many of you know, Microsoft has made some changes to the partner program and partner certification requirements, and they have also released the Dynamics GP 2010 certification exams.  So this month I have to take at least 3 exams to get current on the latest versions:  GP 2010 Install & Config, GP 2010 Financials, and SQL 2008.

This morning while driving to the exam center, I happened to be on a call with my blog-mate Christina, and she mentioned that when she takes certification exams that she has taken several times before, she tries to take them as fast as she can.  Apparently this is how a long time GP guru spices up her workday?

So, finding that an interesting challenge, I thought I would give it a try.  Unlike Christina, who can ace the exams in her sleep without studying, I actually studied a few hours for the Install & Config exam since there is a fair amount of content covering features that I rarely use.  Since I was pretty confident I wouldn't fail the exam, I figured that a race against the clock might be an interesting challenge.

I sat down at the exam computer at 9:46am and tried to get past the annoying 97 pages of legal babble they make you click through before starting the actual exam.  I then jumped right in, discarding the "distractors" and finding the valid answers.  Most of the questions I knew instantly, but a few made me stop and think to try and figure out the correct answer, which made me self conscious, as I could see the clock on the wall ticking away.

The questions and answers whizzed by, and in no time, I was on question 75.  Whatever that question was (something in the "configuring reporting" section), I remember that it was one where I had to stop and try and figure it out.  So close, yet thwarted by a final question.  As soon as I got through it, glanced at the review screen to finish up one question I had apparently missed (I had only checked 1 of the 2 required answers), I pushed through the "I'm Done!!!" screens and glanced up at the clock:  10:12am.

So that was 75 questions in 26 minutes, or an average of 20.8 seconds per question.  Looking back at it, I probably could have done it faster without affecting my score much, but as it was my first Speed Exam session, I was a bit nervous that going too fast would result in stupid mistakes.

I ended up with a 93%, so I passed with flying colors (or as someone I know would say:  "You studied way too much, or you took way too long, since you only need XX% to pass.").  I do wonder which 5 questions I got wrong though, something that always annoys me with the certification exams--they provide a "score" for each section, but even if I reviewed the section materials afterward, since I don't remember the specific questions (they are all a complete blur when you do them quickly), it's difficult to know which one I answered incorrectly.

Anyway, if any of you are taking your Dynamics GP Certification Exams, let me know how you did.  And if you are a long time certified Great Plains / Dynamics GP consultant and have taken the exams many times before, give the Speed Certification game a try and let us know your time and your score!