Last week, I was asked if I knew of a quick way to update the Basic Personal Exemption amount in Dynamics GP Canadian Payroll. It’s not the first time I’ve been asked as it’s relatively common for an “oops” on the CPY Year End Close routine to either forget to update the personal exemption amounts or that somehow the wrong amounts are in there. So, today’s #TipTuesday is a short one: what’s the quickest (& safest) way to update this if it was wrong on multiple people?
Continue reading “Mass Update CPY Basic Personal Exemption”Tag: SQL Server
What’s included in those SQL rpt roles?
Today’s #TipTuesday is a security tip to help you identify what exact permissions the SQL fixed database roles have, that start with rpt*. If you’re granting access to some of your windows users or groups, you want to know what they can get into, right?
The purpose behind the rpt* roles is generally for SSRS (SQL Reporting Services) reports that ship with Dynamics GP since users running those reports are outside of GP, not tying into GP permissions. There are plenty of other posts out there about GP & SSRS, so I am not going to get into that here. Instead I am going to show you a SQL query I’ve used to see what permissions are granted to what role, login, user or group in SQL.
Continue reading “What’s included in those SQL rpt roles?”Fun with XML, the end
(At least I hope it’s the end, LOL!). Here are the various parts to my mini-series so far, which is the story behind all of this (part 1), the technical deep dive into the SQL query (part 2), a bit about namespaces in the FOR XML (part 3) and finally a bit about validating an XML file against an XSD schema (part 4).
So, what is this post about? This is taking a step back to describe the process itself. The part 1 story tells you what I’m doing, and ultimately I am all about the process. It’s one thing to create some code in order to create an XML file, but ultimately the best solution for an ongoing, repeatable process is to think about the end to end steps from the point of view of the user. This is about the “solution” that I created, which may give you some ideas on either the thought process behind it, or ideas on how to mimic the same thing yourself for a process of your own on something else.
Continue reading “Fun with XML, the end”Fun with XML, part 4
The last of the “technical” parts of this mini-series is about how to validate an XML file with a parser, against an XSD schema. This would complete the list of things I learned in this journey to create a T5 XML file, that I didn’t know before I started.
If you’re interested in the previous parts, part 1 is the story behind all of this, part 2 is a deep dive into the SQL code and syntax for the query, and part 3 is about getting the namespaces correct on the XML file.
Continue reading “Fun with XML, part 4”Fun with XML, part 3
The fun continues! Today’s post is about the namespaces. I was unable to figure this out for the longest time and ended up giving up, manually pasting in the values I needed for submission. It was only in prepping this post that I came up with a way to get the namespace attributes in the right spot.
If you are interested in the entire journey, check out part 1 (the story behind this) and part 2 (deep dive into the technical details of creating the XML in SQL).
It seems from searching for this in various forums that other people had the same issue I did: using WITH NAMESPACES seemed to then put the namespaces in every element parent tag, where I only want it on the Submission tag. Here is how I worked around it and I can only assume it’s a “workaround” not a recommended way of doing it. It works so I’m going with it! I hope this helps someone else out there.
Fun with XML, part 2
Today’s post is a continuation of the mini-series I started on Feb 28th. You can read the first part here if you’d like, which contains the story behind this. In this post I’ll be getting into the technical details on what I learned.
This is about the guts of the XML itself, in SQL, where I ended up writing this procedure. First, before I get into the details, I wanted to reference this Stack Overflow post that was the single most useful post and example for the work I did. The person who posted the example, Ryan, also laid out a very simple temp table structure so I literally copied that code and pasted it into my test SQL server and played with it to understand it.
Continue reading “Fun with XML, part 2”Fun with XML, part 1
This is going to be a short series of posts (short series, not short posts!) about my journey with creating an XML file for T5 filing with the Canada Revenue Agency (CRA for short). So many things happened in the last couple of weeks on this, that there were plenty of lessons learned to share and stories to tell! The story overall was one of a lot of frustration at times but in the end, a lot of pride in figuring things out with a deadline looming and ultimately, successfully creating an XML file to submit on time for our T5’s.
Continue reading “Fun with XML, part 1”Clear Companies script & custom tables
This is a funny story… or funny now that I’ve figured out the problem I had earlier today. This story begins with a DEV/TEST environment for Dynamics GP where I was setting up some scripts to automate the process of restoring from PROD to DEV. I posted a blog article about this earlier this year in fact, but I didn’t get into the Clear Companies script.
Continue reading “Clear Companies script & custom tables”GP Utilities user permissions
Today’s #TipTuesday post is around GP Utilities and what permissions are required. If you are going through any service pack install or upgrade, you might have a lot of workstations that need to be updated! In our case, we just upgraded our Dynamics GP and wanted all hands on deck to help with installs, including some IT team members who didn’t have any GP access. Here’s how you do it and what permissions are required.
Start/Stop SQL by Batch File
Today’s #TipTuesday post is for anyone who might be running more than one local instances of SQL Server. I often have at least a couple of instances on my laptop, as historically I would want to have the ability to mimic some client installs, as close as I can. While I am no longer a consultant, I will continue to do this so that I can have an instance the somewhat resembles what version of SQL and Dynamics GP we use at work + the latest version of GP with all the latest things installed. For blogging alone, I want to have the latest and greatest to play with and learn what’s new.