Simple typo in SQL script causes perplexing permission error

By Steve Endow

I recently delivered a new version of a GP customization.  The new version was deployed to a test environment, and right away, the user encountered an odd error.

Cannot find the object 'zDP_BIL10000SD_1', because it does not exist or you do not have permission.

Hmmm.

We confirmed that the stored procedure did exist.  We confirmed that the DYNGRP had EXEC permissions to the procedure.  We then tested with the sa login, and the error did not occur.

So that told us that we likely had a SQL permission issue.  But we checked and double checked the permissions in SQL, and they looked fine.

I then did a test on my development machine and I was able to reproduce the issue as a non-sa user.

Hmmm.

Just to rule out the possibility that the GP customization had an issue, I created a new SQL server login, made it a user in a test company, and added it to DYNGRP.  I then tried to manually run the stored procedure.

exec dbo.zDP_BIL10000SD_1 'GLJOURNAL','GLJOURNAL','3551','3551','6510'

And I got the same error message.

(0 row(s) affected)
Msg 15151, Level 16, State 1, Procedure zDP_BIL10000SD_1, Line 36
Cannot find the object 'zDP_BIL10000SD_1', because it does not exist or you do not have permission.

This seemed so strange.  It didn't make sense.

I then stared at the error message for several minutes and it started to dawn on me.  Do you notice anything unusual about it?

Let's start with the fact that it is actually three different messages.

Message 1:   (0 row(s) affected)

Hmm, that's interesting.  This would seem to indicate that something ran successfully, even though an error is occurring.

Message 2:  Msg 15151, Level 16, State 1, Procedure zDP_BIL10000SD_1, Line 36

Aha!  This message seems to be telling me that there is an actual error INSIDE the stored procedure script.  Well, that's progress.

So if I open the script, what is at line 36?

Line 36 is the GRANT EXEC statement.

Do you see the problem yet?

Why would the stored procedure need to call a GRANT statement itself?  Why would a GRANT statement exist inside the stored procedure CREATE script?

Well, if you look above the grant statement, what's missing?

A GO statement at the end of the stored procedure script.

Ugh.

Somehow, the GO statement was missing from two of the stored procedure scripts.  I suspect that the GRANT was manually added to the scripts, and I didn't notice that the proc scripts didn't end with GO.

So when the proc was created, it included a GRANT statement where it would set permissions on itself.  While obviously not intended, that does technically work.  But it requires elevated rights on SQL, such as those an sa user has.

But when a standard DYNSA user attempts to run the proc, the GRANT statement will fail, because the user does not have permissions to run a GRANT statement.

Just when I think I've seen and made just about every mistake possible, I'm reminded that there are many more out there.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Restoring a Hyper-V VM using Veeam: It's amazingly easy

By Steve Endow

After Tweeting about how I use Veeam for my Hyper-V backups, I promptly received the follow up question that everyone who has managed backups dreads.

Everyone knows that you need backups.  But what many people neglect is testing their backups by routinely performing actual restores.  Myself included.  Sometimes you can only discover a problem with your backup routine or process when you actually try and restore from backup, but testing restores is often that last thing that anyone wants to do.

An example of why I use Veeam for Hyper-V Backups

By Steve Endow

I've been using Hyper-V for years, probably since shortly after it was released.  After I started running several virtual machines, I was faced with a problem.  How do I backup these virtual servers?

I started by occasionally copying the VHDs to a file server.  As I used more VMs and as the VHDs got larger, I had to start compressing the VHDs with 7-Zip.  Which took FOREVER.

So, being clever, I came up with some command line scripts to pause my VMs, compress the VHD to a backup file, then move that backup file to my file server.  I thought I was just so smart.  The process worked, but it still took FOREVER.

And sometimes it wouldn't work.  Sometimes it would fail and I wouldn't know.  Sometimes I had to work late and I had to cancel the job.  It was untenable.

Then I finally had enough and started to look into Hyper-V backup software packages.  I was willing to pay a few hundred dollars to get rid of this administrative mess and headache.

I ended up choosing Veeam for my Hyper-V backup.  Of the two packages I tested, Veeam seemed to have incredible performance.  It seemed clear that the designers and developers had gone to great lengths to make it very fast and very efficient.  I have been using Veeam for a few years now, and tonight I was once again reminded why I appreciate Veeam.

Hyper-V VM with Server 2012 R2 won't boot with "We couldn't complete the updates" message

By Steve Endow

I was just minding my own business, trying to install and configure the GP 2016 Web Client on a fresh Server 2012 R2 VM.

When I connected to the new VM, I saw this message.

"We couldn't complete the updates.  Undoing changes."

The machine just sat there and didn't appear to do anything.  It was stuck.

After finding a few useless suggestions relating to the same problem but for Windows 8, I finally found a reference to this TechNet thread.

https://social.technet.microsoft.com/Forums/windowsserver/en-US/e58c8b30-b91a-4d90-a1b5-8859ffc3b92c/kb2920189-fails-to-install-on-generation-2-vms?forum=winserverhyperv


Unfortunately, the suggestion to turn off Secure Boot on the VM didn't help.  The thread discusses other workarounds, but I didn't know how to install BitLocker on a VM that wouldn't boot.

So then I saw the link to this post:

http://andyparkes.co.uk/blog/index.php/category/hyper-v-2/


And it is there that I learned that using Server Manager, you can install roles or features onto a VM just by pointing to the VHD!!!

MIND BLOWN.

I followed the instructions, and it appears to have resolved the issue.  The VM actually booted up and started applying the updates.

The update message started at 30%, and is now sitting at 100%.  Unfortunately it's now just sitting at 100% and it has been several minutes, so I may have more than one problem to push through.

Fingers crossed.


UPDATE:  I let it sit at 100% for several minutes, but couldn't tell if it was doing anything or stuck again.  So I turned it off, then turned the Secure Boot off again, then restarted.  It then showed the "Undoing changes" again, but after a few minutes, it said "Preparing Windows" and actually finished booting.

Unfortunately, after it finally booted up, this was still showing in Windows Update.  Ugh.  Only 205 more to go.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Payroll Reconciliation and Tax Flag Comparisons

First things first, it is always easier to reconcile payroll as you go.  Bit by bit goes down easier than looking at large ranges of time. What do I mean by reconciling?  Well, after each payroll do the following...




1. Print the check register that shows up automatically in most cases when you post the payroll (the one that shows what you owe as far as taxes)
2. Then print the payroll summary report (Reports-Payroll-Period End), print this from the start date of the current quarter through the payroll check date.  This report should reconcile (wages, taxes, etc) to the cumulative tracking of your payroll check registers.
3. Then print the 941 report for the current quarter.  This should also reconcile (wages, taxes, etc) to the payroll summary report.  And the Schedule B should reconcile by date to your check registers.




You can also follow this same process for state taxes, using the state tax register that prints when you post payroll and comparing it to the state tax summary printed from reports-payroll-period end.  It is important to do these exercises in a cumulative fashion so you inevitably pick up off-cycle adjustments and voids, as well as enable you to compare to your cumulative 941 as you go which makes quarter end a little less climactic :)




When you find an issue, then you can deal with it and already know what payroll or adjustment time period is the source.  However, we don't all live in perfectville and sometimes time gets away from us.  So if you are needing to back track and reconcile multiple payrolls, specifically after you have gone live, one useful double check can be to make sure that individual employee pay codes, benefits, and deductions are set with the same tax flags as the overall setup (comparing Cards-Payroll to Setup-Payroll).  The script below does that specifically for pay codes, but can be easily adapted for benefits or deductions as well.

--Compares UPR00400 employee pay code master to the overall pay code setup

--Identifies where tax flags don't match, or where SUTA states are missing for SUTA taxable codes

--Provides list of affected codes and employees

 

select UPR00400.EMPLOYID as EmployeeID, UPR00400.PAYRCORD as PayCode,

UPR00400.SBJTFUTA as SubjectToFUTA, UPR00400.SBJTSUTA as SubjectToSUTA,

UPR00400.SUTASTAT as SUTAState, UPR00400.SBJTFDTX as SubjectToFed, UPR00400.SBJTSSEC as SubjectToSS, UPR00400.SBJTMCAR as SubjectToMed,

UPR00400.SBJTSTTX as SubjectToState from UPR00400 inner join UPR40600 on UPR00400.PAYRCORD=UPR40600.PAYRCORD

where (UPR00400.SBJTFUTA<>UPR40600.SBJTFUTA) or (UPR00400.SBJTSUTA<>UPR40600.SBJTSUTA)

or (UPR00400.SUTASTAT='' and UPR40600.SBJTSUTA=1) or (UPR00400.SBJTFDTX<>UPR40600.SBJTFDTX)

or (UPR00400.SBJTSSEC<>UPR40600.SBJTSSEC) or (UPR00400.SBJTMCAR<>UPR40600.SBJTMCAR)

or (UPR00400.SBJTSTTX<>UPR40600.SBJTSTTX)


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Dynamics GP message "Do you want to save the unbalanced entry?"

By Steve Endow

This is a trivial issue, but since it took me a few seconds to realize my mistake / accident, I thought I would mention it.

I'm testing a .NET customization to the GL Transaction Entry window that monitors certain GL accounts that are entered in JEs.  While entering a fake JE to test my customization, I chose a random GL account to balance the JE, and then clicked Save.

I received this message:

I was puzzled at first, but then I saw that the second line of the JE did not have a dollar sign in front of the Credit amount, and I had a pretty good guess what I had done.

The account I had randomly chosen, 600-9030-00, just happened to be a Unit account, not a Posting account.

Unit accounts can't be used to balance a GL journal entry, so my test JE was not balanced, as GP indicated.

This is presumably not a common mistake, and is one that is easily diagnosed and resolved, but since I almost never work with unit accounts, I thought I would mention it.

There are quite a few posts on unit accounts, such as these two from Boyer & Associates:

http://www.boyerassoc.com/how-to-use-dynamics-gp-unit-accounts-to-enhance-your-management-reports/

http://www.boyerassoc.com/tips-using-unit-accounts-variable-allocation-accounts-dynamics-gp/


May your journal entries be balanced!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Using INFORMATION_SCHEMA views to quickly and easily find objects in SQL Server

By Steve Endow

Christina Phillips and I have done a SQL presentation at several Dynamics GP conferences, and one of the items I always like to mention is INFORMATION_SCHEMA.  It seems that many people don't know about this fantastic set of views, and are still suffering by using sysobjects.

I think that it is a very underutilized tool for Dynamics GP users and consultants.

INFORMATION_SCHEMA is a set of metadata views that allows you to quickly and easily find objects in SQL Server.  The best part about INFORMATION_SCHEMA is that the syntax is very simple and obvious.  It's vastly easier to use than sysobjects.

Here's an MSDN article that covers INFORMATION_SCHEMA views:

https://msdn.microsoft.com/en-us/library/ms186778.aspx


Let's jump straight into some examples.

What if you want to find every SOP table in the entire database:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'SOP%'


What if you want to find every SOPNUMBE field in the entire database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'SOPNUMBE'


What if you wanted to find every SOPNUMBE field in the SOP tables in the entire database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'SOPNUMBE' AND TABLE_NAME LIKE 'SOP%'


Inversely, what if you wanted to find every SOPNUMBE field that is NOT in a SOP table in the entire database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'SOPNUMBE' AND TABLE_NAME NOT LIKE 'SOP%' ORDER BY TABLE_NAME 



What if you wanted a list of all GP stored procedures related to the SOP1xxxx tables?

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'zDP_SOP1%'


What if you wanted to find any stored procedures that had been altered?

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE CREATED <> LAST_ALTERED 


One thing that I believe INFORMATION_SCHEMA cannot search for is triggers.  For triggers, you will still need to use sysobjects.  Here is a Stack Overflow post with some options for doing that.

I use INFORMATION_SCHEMA regularly to quickly track down tables and fields and find out which tables contain a given field.  It's a huge time saver and if you work with GP queries regularly, is something you should probably start using.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com