SQL Server – JenKuntz.ca

SQL Alerts with HTML email body

Today’s #TipTuesday is a bit different from my normal type of post. In the course of monitoring some integrations, I had wanted to change the emails I get when there were issues. What I inherited was email alerts with attachments, without any data that I could use as an Outlook filter to tell me if something was ok or not. I had to open the attachment to see if there were errors. I wanted to find something better.

What I’m writing about today is what I’ve pieced together from various searches and unfortunately, while I got the pieces from various other blogs, I’ve changed so many things and re-worked this so many times, I no longer even have the references of those that helped me get to the point of what I’m sharing today. I wish I could link to them to thank them.

SmartList stuck on My Reports pane

Today’s #TipTuesday post is about troubleshooting an issue with the My Reports home page area/pane and how to remove SmartList favourites from there. The specific scenario here related to favourites that were “stuck” in MyReports after an upgrade to GP 2013 a few years ago. I was reminded of this when I saw this post on the Microsoft Dynamics community forum and someone asked me if I kept the scripts I used to fix this issue.

Mass Update CPY Basic Personal Exemption

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?

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.

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.

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.

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.

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.

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.

1 2

Tag: SQL Server