Here is a cool little SQL Server tip from the April GP Reports Viewer newsletter. Say you have a list of rows and you want to assign sequential numbers to them, so that you always know which one is first, second, etc. Or so that you can display line numbers. This is often a need […]
Read more
Changing part of a string in SQL Server using REPLACE
This latest SQL Server tip comes from the Feb/March 2013 GP Reports Viewer newsletter. Have you ever needed to change part of a string in SQL Server? I have seen this come up in a variety of situations, like needing to change account names in the General Ledger or needing to show something differently when […]
Read more
Concatenating strings in SQL Server
This SQL Server tip comes from the April 2012 edition of the GP Reports Viewer newsletter. A request that we have seen many times when creating SOP invoices for Dynamics GP is to concatenate serial numbers. So if there is an item with serial numbers, instead of showing a list with one serial number per […]
Read more
Finding resources
Almost on a daily basis I find myself looking for things like system requirements, end of support dates, build numbers and downloads for the various products that I work with. Even though search engines have greatly improved over the last few years, I still often struggle to find this information. While I consider myself to […]
Read more
Getting the name of a month from the month number in SQL Server
I was creating a SmartList for a customer recently and was using the GL11110 and GL11111 views in GP for monthly summaries of P&L accounts, but wanted to show the month names instead of period ID’s to make it a bit more user-friendly. I could have linked to the fiscal period setup table to get the period names, but […]
Read more
Results of SQL Server poll
It has been about a month and 119 people voted, so I wanted to share the results of my SQL Server poll. I asked what version of SQL Server my readers were using for their Dynamics GP and here are the results: While I am glad to see that only a few voters (5%) are on […]
Read more
SQL Server poll
I am starting to see articles and blog posts about Microsoft SQL Server 2011 everywhere, so I thought I would poll my readers to ask versions of SQL Server you are using. The poll is below: Once I have some results back, I will post an update. In the meantime, keep in mind that SQL Server […]
Read moreHow to find all SQL tables with a column name
Often I need to find all the tables in Dynamics GP that have a particular column or search for all columns where I only have a partial column name. Over the years I have seen lots of different code to accomplish this, so this is nothing new. But I am asked about this enough that […]
Read moreSearching for strings in SQL Server using LIKE
Many times when searching for data in SQL Server we need to search for strings and use wild cards. One common wild card is %. As an example, I am going to use the Customer Master table (RM00101) in the Dynamics GP sample company, Fabrikam, Inc. If I want to all customers with ‘USA’ in the Class ID, […]
Read more
Why support only part of a solution on SQL Server 2008?
This is something that came up as I was reviewing posts in the FRx newsgroup and talking to customers about GP upgrades. I wanted to rant about it a little and see what others thought. Maybe I am over-reacting? Dynamics GP 10.0 is supported on SQL Server 2008. However, FRx Reporter 6.7 is only compatible […]
Read moreNew page with resources
I have published a new page called Resources that has links to information I am either frequently looking up or I am being asked for routinely. Stuff like Dynamics GP system requirements, printer compatibility lists, how to translate a build number in GP, FRx or SQL Server to a specific service pack, when support for various versions […]
Read moreWhen does my SQL Server support end?
Did you know that mainstream support for SQL 2000 ended in April of 2008? Here is a list of the SQL versions and the relevant release and support end dates: SQL Server Version Release Date End of Mainstream Support End of Extended Support Link SQL 7 Mar 01 1999 Dec 31 2005 Jan 11 2011 […]
Read moreSQL Server – how to get date differences
Often, when writing a report, we need to calculate the difference between two dates or the difference between a date and today. For example – how old is an invoice? How many days overdue is an invoice? Below is some SQL code that will help. First, if you need to dynamically get the current date […]
Read more

May 7, 2013 

