Archive | SQL Server RSS feed for this section
examine files

SQL stored procedure to search an entire database

I don’t often re-post things from other blogs, but this is just too good not to share. David Musgrave recently published an update to his spSearchOnAllDB stored procedure on his blog, Developing for Dynamics GP. Simply run the script to create the stored procedure and you can search all the tables in a database for a […]

Continue reading
winner

Assign sequential numbers in SQL Server

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 […]

Continue reading
cook

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 […]

Continue reading
red white puzzle

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 […]

Continue reading
binoculars

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 […]

Continue reading
turn the gears

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 […]

Continue reading
SQL Server Poll Results

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 […]

Continue reading
SQL Server Poll Results

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 […]

Continue reading

How 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 […]

Continue reading

Searching 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, […]

Continue reading
question mark

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 […]

Continue reading

New 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 […]

Continue reading

When 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 […]

Continue reading

SQL 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 […]

Continue reading

What version of SQL Server am I running?

Here is a summary list of the major releases and service packs for Microsoft SQL Server: SQL Server RTM SP 1 SP 2 SP 3 / 3a SP 4 2000 8.00.194 8.00.384 8.00.532 8.00.760 8.00.2039 2005 9.00.1399 9.00.2047 9.00.3042 9.00.4035 9.00.5000 2008 10.00.1600.22 10.00.2531 10.00.4000 10.00.5500 2008 R2 10.50.1600.1 10.50.2500 10.50.4000 2012 11.00.2100.60 11.00.3000  11.00.5058 […]

Continue reading
Follow

Get every new post delivered to your Inbox.

Join 1,558 other followers