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.

Wednesday, October 15, 2008

"desSPRkmhBBCreh" makes me Smile!

I just read an incredible piece of history from David's Blog -  "What is Column desSPRkmhBBCreh?", I am still smiling thinking about the ingenuity and cheekiness of the developers.

Apparently, the developers figured out that it takes lesser time to select a bogus column, than it takes to query to system tables, when you have to check if a table exists.

So they had to put in any bogus column - and instead of choosing something like clmInvalid or INVALIDCOLUMN - they chose "desSPRkmhBBCreh", which is after their names. The code reviewer would have had an interesting day :)

You should read it at the source

Guys, you made my day!

Thursday, October 9, 2008

Giving access to Alternate/Modified forms and reports in GP 10

As this has changed a lot from earlier versions, this still puts people off. It definitely is a more organized way, in case you have different report access permissions for different people. More than anything, if you maintain the Alternate Report ID's well, anybody going in to provide support knows where to go immediately.

Here are the steps -

To set access to alternate/modified forms and reports:

1. Open the Alternate/Modified Forms and Reports window.
(Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Alternate/Modified Forms and Reports)


2. Enter an ID and description.


3. Select a product from the Product list.

(If you’re using integrating products with Microsoft Dynamics GP, they are displayed in the list in addition to Microsoft Dynamics GP.)

4. In the Type list, select the type of item you want to set access for.


5. In the Alternate/Modified Forms and Report list, a tree view is displayed if there are any alternate/modified forms and reports for the product and type that you selected. The tree view is organized by series.

o Expand a series to display a list of the forms or reports that are available as alternate/modified forms or reports.

o By default, the original forms or reports are selected already. If you want users to be able to access the alternate version of that form or report, select the alternate form or report.


6. Repeat steps 3 through 5 to set access to alternate/modified forms and reports for all products.

7. Choose Save.

Removing Reminders which pop up after logging in to Dynamics GP

I get asked this question at least once every month - "How can I remove the reminders which pop up ? They kind of slow things down"

Most people figure out the common stuff themselves, but have trouble getting rid of the Customer Improvement Program part. I too had trouble getting rid of it, it would pop back up even after I had deleted it. Below are solutions to disable reminders/tasks lists Prompts after login

Option1: Delete the Reminders and Tasks

Microsoft Dynamics GP >> Reminders >> Change Reminder Preferences

Remove Custom Reminders and uncheck pre defined Reminders

Microsoft Dynamics GP >> Reminders >> Microsoft Tasks List

Open the Tasks and Delete or Mark as Complete

Microsoft Default Task (CEIP) is not deleted by deleting in normal way.

To delete it, Open the Task on clicking on it. Click on Name Link.

Select “No, I don’t want to Participate”.

Then delete the Task. You will not be prompted for this Task when you login again.

Option 2 : Remove Reminders Prompt by adding it to the “To-Do” web part on Home Page.

Click on Customize This page link on Home Page.

Mark “To Do”

“To Do” will be Displayed as Web Part on home page

Next time on login, you will not be prompted for Reminder or Tasks List Window

Thursday, October 2, 2008

Exporting Currency from SmartList to Excel

We received a simple request for a smart user - who was having trouble when Excel sheets were exported from SmartList. It exported the currency along with the values, and it would take him time to clean it up to do totals.

There is an old setting in in dex.ini ExplorerFormatCurrency, which controls if currency amounts are exported as text or numbers.

Making ExplorerFormatCurrency=FALSE ensures that foreign currency is exported as easy to use numbers.

I am not sure if this is still supported, but it is definitely pretty useful.

Yesterday, I found that if you use ExplorerFormatCurrency=FALSE, Analytical Accounting SmartList's export 0 amounts to excel. It shows data in the SmartList, but exports all zeroes to Excel. Turning off the ExplorerFormatCurrency fixes this problem.

Rebuilding the tables.sys and procs.sys file using a dex.ini setting

Andy writes about the steps required to recreate the tables.sys and procs.sys files in GP Client Folder. It requires adding the duExtractSQLResources=TRUE line in the dex.ini file. Its one of the many tips which you get only from Microsoft Support. Often some of these are hidden in KB, but we just have trouble finding them.

Mark had covered dex.ini settings about an year back, which is a very useful read as well.

Mariano has an extensive document with dex.ini settings out as well.