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
Archive | SQL Server RSS feed for this section
Changing part of a string in SQL Server using REPLACE
March 11, 2013 
3 Comments
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
May 21, 2012 6 Comments
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
May 12, 2012 5 Comments
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
May 23, 2011 17 Comments
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
February 8, 2011 0 Comments
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
January 14, 2011 5 Comments
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
April 23, 2010 5 Comments
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
March 2, 2009 7 Comments
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?
February 3, 2009 30 Comments
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
December 22, 2008 2 Comments
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?
October 29, 2008 10 Comments
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
October 27, 2008 6 Comments
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 moreMy Book
-
Using credit cards to pay vendors in Dynamics GP
-
SQL view for Payables GL distributions in Dynamics GP
-
Year end close in Dynamics GP
-
SQL view for all posted GL transactions in Dynamics GP
-
SQL view with all SOP line items in Dynamics GP
-
Granting access to a new SmartList in Dynamics GP
-
SQL view to show AP apply information in GP
-
SQL view for Payables payment apply detail in GP
-
Sales Transaction Entry GL distributions in Dynamics GP
-
SQL view for sales by item by month
-
SQL view for sales quantities by item by site by month in Dynamics GP
-
SQL view for SOP sales by customer by month
-
Assign sequential numbers in SQL Server
-
SQL view for current Receivables aging detail in Dynamics GP
-
SQL view for current Payables aging detail in Dynamics GP
-
Changing part of a string in SQL Server using REPLACE
-
SQL view for sales quantities by item by site by year
-
SQL view for Fixed Allocation Accounts in Dynamics GP
-
Happy New Year – 2013
-
How to add a sparkline to a report in SSRS
-
Victoria Yudin: Luis, not sure what to tell you - my out-of-the-bo...
-
Luis: Victoria, Thank you for your help. I tried the m...
-
-
Patrick: With the database I inherited I don't know how or ...
-
-
-
-
-
zgar: Great, thanks so much. I got the SOP info on what...
-
