Showing posts with label GPScripts. Show all posts
Showing posts with label GPScripts. Show all posts

Tuesday, July 27, 2010

GPWindow.com – Thousands of GP Tips and Tricks from All MVPs and Experts

For a while now, we have had the custom GP Custom Search engine which searched handpicked Dynamics GP Blogs. Now you have an easy to remember Address for this Search Engine-  www.GPWindow.com.

In addition to the Search Engine, the GP Articles by various MVPs and Experts have been organized by categories, for people who like browsing rather than searching. There are over 6000 articles in various categories. The topics are ranked based on their importance based on how often they are recommended by other GP Experts.

As of today, there are 6169 articles organized into various GP categories like  - 


In this category, right on the top you will see Mark Polino’s extremely famous “50 MORE Tips in 50 Minutes” presentation. Under the main Tips and Tricks category, you can find hundreds of great tips for each module like -


2. REPORTING (531 Sub-Item) which includes MANAGEMENT REPORTER, SmartList Builder, Report Writer and so on.

3.  A category for the red hot SUPPORT DEBUGGING TOOL under TOOLS FOR GP (102 Sub-Items)

4.  OFFICE INTEGRATION (143 Sub-Items)

Some sample posts -

5. DEVELOPMENT (470 Sub-Items)

6. GP USER INFO (190 Sub-Items)

7. GP TECH INFO (249 Sub-Items)

This category would be useful for people who support or implement Dynamics GP. It has information about

8. DOWNLOADS (30 Sub-Items)

Hopefully this section will save everybody a lot of time. Here you will find the latest links for Dynamics GP Release Downloads, FRx DownloadsGP Service Pack Downloads, IM Downloads and Management Reporter Download

My favorite link here is the Dynamics GP Product Downloads and Service Pack Links maintained and kept current by David Musgrave


Here you will find Consultants Sell article by Martin from eOne Solutions, and lots of excellent articles by Dwight Specht like “I did my job” – “Yeah, but we suck” , Managing in a Downturn, Bad Client! Bad, Bad Client!Evaluation of Consulting Staff and many others.

10. GP 3RD PARTY SOLUTIONS / ISVs (188 Sub-Items)

Here you will find some product information including information about Free Dynamics GP Addon’s by Matt Landis and company.

11. SQL SCRIPTS (217 Sub-Items)

Here you will find the Useful SQL Scripts Series by David Musgrave and other scripts by Mariano, Mark, Victoria, M Daoud and Ron Wilson at Real Life Dynamics User Blog. You can find more than 200 scripts under various modules.

12. SECURITY IN GP (121 Sub-Item) 


This site is all Dynamics GP, so you would see a category for Dex.ini Settings (with excellent posts from Leslie Vail, Mariano and Mark ) and one for Reports.dic under SUPPORT (TECHNICAL) (438 Sub-Items) .

N0tes -

  1. There are over 200 categories so I highly recommend using “CTRL + F” to look for a category.
  2. There are over 6000 articles, the articles above are just a small selection from each category. I was also trying to keep a healthy mix of articles from all GP Experts. Please dig into a category to find out more articles. Over the next few months I will try and detail out the most popular articles in each category.

Thank you notes !

Thanks to David Musgrave for noticing and promoting the Custom GP Blog Search, which lead to GPWindow.com, and for his excellent review. Thanks to Vaidy for taking out time to review the website in detail, and staying up late to give his feedback in a busy week. Thanks to Mariano Gomez, the GP expert with the big heart, for passing on good words about GPWindow. Thanks to Mark Polino and M Daoud for their feedback.

Thanks to my good friend Evgeny for his techie magic. Thanks to Rubal, the idea-machine, for suggesting a URL and the directory, she already has ideas about the next version. Thanks to Kuldeep for telling me every Monday morning- “You know, I’ll tell you the truth - the website looked pretty average last week, but now it ROCKS! It looks very useful now!”, and after reading this post telling me “You know I like the site, and I like your post about it, but the Thank you notes are a little cheesy!” He said it was perfect, when I told him, the only name I could take out was his name.

Thanks to Chetna for volunteering to spend time in reviewing the website and checking meticulously if there was any missing data, to Tina for her keen eye pointing out all the things that didn’t work, to Richard and Belinda for their encouragement and humor!

Feedback and Next Steps -

  1. Use the website, Search or Directory, whatever you prefer. If you have trouble finding a download link, try looking it up in the Downloads category, if you are searching for a SQL Script, try using the search. If GPWindow helps, let me know or spread the word.
  2. If you go into a category and notice an old article missing, please shoot me a line. Because of the sheer volume of the articles there could be some articles missing from their appropriate categories. 
  3. Do see any categories missing ? or any categories that are difficult to find ?
  4. Any other feedback your might have – add it as a comment to this blog or shoot an email.

Hopefully GPWindow will help you find the extremely valuable articles created by people in the GP community ! Good Luck!

Tuesday, October 21, 2008

Automated GP Database Backups with SQL Express

With GP’s support for SQL Express, a lot of people are going with this free option instead of the full fledged SQL Server. A few things need to be tweaked here and there – but generally it works well. I’ve covered a security gotcha before, and today I’ll cover one of the most important things that people forget when using SQL Express – Database Backups.

SQL Express does not come with the SQL Server Agent which is mostly used to create DB Backups for GP Databases. You have to automate it yourself. Below are the steps I’ve used at a couple of places to automate GP Backups when using SQL Express.

The high-level architecture of the simple backup system involves using a Windows Scheduled Task, with Stored Procedures for backing up the databases. You can also build an advanced system – more on that below.

1. Create a batch file that calls the SP to backup the databases, like this –

sqlcmd -s"sqlexpress instance name" -E -Q"exec spBackupdatabase"

2. Create a SP, that backups up the dynamics and company databases, like this –

CREATE PROC[dbo].[spBackupDatabase] As

DECLARE @filename nvarchar(2500)

SET @fileName = 'C:\GreatPlainsBackups\'+ Cast(DATEPART(m, GetDate())as nvarchar)

+ '-' + Cast(DATEPART(d, GetDate()) as nvarchar)

+ '-'+ Cast(DATEPART(yy, GetDate()) as nvarchar)+'-Dynamics.Bak'

BACKUP DATABASE [Dynamics] TO DISK = @fileName


Set @fileName = 'C:\GreatPlainsBackups\'+ Cast(DATEPART(m, GetDate())as nvarchar)

+ '-' + Cast(DATEPART(d, GetDate()) as nvarchar)

+ '-'+ Cast(DATEPART(yy, GetDate()) as nvarchar)+'-FXDD.Bak'



3. Create a scheduled Task to launch the batch file at a schedule that suits you





You could do advanced things like below, with some more programming -

a. Backing up all databases on SQL express

b. Removing backups older than 7 days

c. Sending email to configured users

We’ve built a custom application that does that in Dot.Net 2.0, if any of you need that, let me know – and I’ll put it out here for your reference.

Monday, May 5, 2008

Dynamics GP Table Naming Logic

A million thanks to Mark Polino for putting up an amazing presentation on understanding Table Structure in GP. Please visit Mark's blog post which contains a link to the powerpoint. A must see for anybody who writes reports for GP.

Here are the gems from his powerpoint about table naming logic in-

1. ‘00000’ - Master tables contain all the permanent data about your business. Accounts, vendors, customers, items, and so on.

2. ‘10000’ - Work tables contain unposted batches of transactions entered using windows that can be opened using the Transactions button on the toolbar. Temporary transactions that can be changed or deleted until they are posted to an open table.

3. ‘20000’ - Open tables contain posted transactions for the current year, of which some may be unpaid. Information in open tables is moved to history tables when the transactions are paid.

4. ‘30000’ - History tables contain paid transactions, or transactions from a previous year.

5. ‘40000’ and up - Setup tables contain all the default settings and module options you’ve specified in the setup windows for each series.

In addition to that, I think the tables in 50000 series are the temp tables.

Based on this Logic - Review some of the Receivables Management(RM) Tables in GP

RM00101 RM Customer Mater RM00102 Customer Master Address File

RM10101 RM Distribution Work File RM10201 RM Cash Receipts Work File

RM20101 RM Open File RM20101 RM Open File Debit Copy

RM30101 RM History File RM30201 RM Apply History File

RM40101 RM Module Setup File RM40201 RM Period Setup

Now review the Payables Management(PM) Table

PM00100 PM Class Master File PM00200 PM Vendor Master File

PM10000 PM Transaction WORK File PM10100 PM Distribution WORK OPEN

PM20000 PM Transaction OPEN File PM20100 PM Apply To OPEN OPEN Temporary File

PM30200 PM Paid Transaction History File PM30300 PM Apply To History File

PM40100 PM Setup File PM40101 PM Period Setup File

and the General Ledger(GL) Tables

GL00100 Account Master GL00100 Distribution Account Master

GL10000 Transaction Work GL10001 Transaction Amounts Work

GL20000 Year-to-Date Transaction Open

GL30000 Account Transaction History

GL40000 General Ledger Setup GL40100 Quick Journal Setup

GL50010 General Ledger Period Header Temporary GL50011 General Ledger Period Temporary

Monday, March 31, 2008

Searching for Data in a Large SQL Database like Great Plains

Most of the time spent designing a report, or writing some code against great plains is spent in finding the right table. I have written about this before.

Using Mark's Excel Sheets and Resource Descriptors


I also refer to -

SQL script that lets you search for a keyword in all tables of a database.


Today I found this on Mariano Gomez's blog -

SQL Script to find all tables which contain a particular Column -


The following example query attempts to solve the issue by exposing all tables where the account index (ACTINDX) column is found within the Fabrikam database (TWO)

select distinct rtrim(objs.name)
from syscolumns cols
inner join sysobjects objs on (cols.id = objs.id)
inner join sysindexes indx on (cols.id = indx.id)
where (cols.name = 'ACTINDX') and (objs.xtype = 'U') and (indx.rowcnt <> 0)

The results are as shown below:


Tuesday, March 11, 2008

SQL Script to Backup All Databases

Richard pointed out this out to me yesterday. This script would backup all non-system databases on a SQL Server. You can modify it to read the company names from SY01500 and backup just the dynamics and company databases if you like.

From : Simple script to backup all SQL Server databases

You will need to change the @path to the appropriate backup directory and each backup file will take on the name --  "DBnameYYYDDMM.BAK"

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\' 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 
FETCH NEXT FROM db_cursor INTO @name  
CLOSE db_cursor  
DEALLOCATE db_cursor

Monday, October 29, 2007

Migrating Dynamics Databases from one server to another, and upgrading Great Plains.

When you are migrating the Dynamics Databases from your old server to a new one(Congratulations on the new server, BTW!), the general series of steps is as follows(bird's eye view) -

  1. Backup and transfer the Dynamics and company databases
  2. Backup and transfer the sql logins, sql jobs , database maintenance plans. Proof the jobs.
  3. If you are upgrading from SQL 2000 to SQL 2005, you would need to upgrade the compatibility from V8 to V9.

Somewhere along, if you are also upgrading great plains, which is a common case, sometimes you might face this error.


The following SQL statement produced an error:
  use msdb   /*IF THE JOB DOESN'T ALREADY EXIST, CREATE IT...*/ if not exists (select name from sysjobs where name = 'Remove Posted PJOURNALs From All Companies')     begin         /*CREATE THE JOB...*/         exec sp_add_job      ....



"Server XXX does not exist"


"Upgrade of Database XXXX failed. Microsoft Dynamics Utilities will now shut down"

You would also face this error when you do test upgrades, as your machine name is different from the name of the machine the original backups were taken on. The jobs etc, seem to be looking for the old name.

I've found the following script handy in these cases, it changes the SQL Server name back to what you want it to be.

The code below renames SQL server.  When you are doing a test upgrade on a different server, or after you migrate to a new server


Purpose: renames SQL server.

Server: all

Database: DBAservice

Originally Developed By : Yul Wasserman 03/08/02


Declare @pNewName varchar(256)

Set @pNewName ='XXXXXXXX' -- Change this to the name of the new server/test server

Declare @OldName varchar(256)

Declare @NewName varchar(256)

set @OldName=''

select @OldName=isnull(srvname,'') from  master.dbo.sysservers where srvid=0

If @pNewName is NULL


        create table #NName (NName varchar (256))

        insert #NName exec master.dbo.xp_getnetname

        select @NewName=Nname from #Nname

        drop table #Nname


ELSE If @pNewName is not NULL


        select @NewName=ltrim(rtrim(@pNewName))


If @OldName<>@NewName


        IF @OldName <>''


               print 'Attempting to drop server '+@OldName

               Exec master.dbo.sp_dropserver  @OldName


        print 'Attempting to add server '+@NewName

        Exec master.dbo.sp_addserver @NewName,'local'


If isnull(@@Servername,'')<>@NewName


        Print 'Please shut down and restart SQL Server in order to complete renaming.'


Else If isnull(@@Servername,'')=@NewName


        Print 'SQL Server is already named ' +@NewName


Sunday, October 28, 2007

Troubleshooting Long Login Times and Performance Issues in Great Plains

We faced this problem specifically for GP 9.  We tried a host of steps, then created a support ticket, submitted information log and trace info to Microsoft, and what actually fixed it was - moving to the latest service pack.

I'll put the information here - in case somebody finds it useful.

1 Check that you are running the latest service pack for Great Plains AND MSSQL.

I was used to ignoring this, and "Apply the latest windows updates" suggestion. But sometimes, this is the only thing that fixes things.

2 Check if tracing is turned on for all ODBC's

If tracing is enabled - there will be a sql log file created that would record every ODBC call that is made to any application that uses the ODBC drivers. Therefore the application is slowed.

To check the setting, click Start | Settings | Control Panel | 32bit ODBC | Select Tracing tab.

3 Check if a Dexsql log is accidentally on

Open the Dex.ini file that exists in your Great Plains application folder in Windows Explorer. Find and confirm the following statements in the Dex.ini file:




4 Turn off Home Page Display and check login time

Specifically the metrics and outlook section can be slow

5 Turn off security from Reminders and Check login time

Dynamics GP >> Forms >> System >> Reminder

6 Run profiler and review queries fired during startup

7 Are any anti-virus softwares installed on the system?

If the issue replicated only on machines that have the anti-virus

8 Can you reproduce the performance issue on all the computers?

This would identify if this is a machine related problem

9 Specifically, can you reproduce the performance issue when you are sitting directly at the computer that is running Microsoft SQL Server?

This would identify if you have network problems

10 Are there any physical symptoms on the computer that is running SQL Server? For example, is processor usage at 100 percent? Is the processor light on? With How many users do you face this problem ?