SSRS requires that you use local SQL Server relational databases… error

Microsoft SQL ServerMicrosoft Dynamics GP ships with a set of default reports which can be deployed into SQL Server Reporting Services; before doing the deploy, there is a setting in the SSRS web.config file which needs to be changed to avoid a maximum request length error.

Recently, when performing an upgrade of a clients system to Microsoft Dynamics GP 23018 R2, the deployment of the reports went fine, but we encountered a problem while testing the deployed reports:

SSRS reports error message

The client had a licenced version of SQL Server 2018 Web Edition which worked in all other respects; the SSRS service was running locally on the SQL Server which was hosting the databases for both SSRS and Microsoft Dynamics GP itself.

This wasn’t something I’ve encountered before, but, fortunately, a colleague had. The problem is not with the Web Edition itself, but rather with the data connection deployed by Microsoft Dynamics GP for the reports. When the data connections are deployed, there is a space created preceding the Data Source name:

Connection settings in SSRS Manager

The fix is to go through all of the datab connections and remove the space after the equals sign, so that, in the example above, Data Source= IG-SQL2017-01\GP... becomes Data Source=IG-SQL2017-01\GP....

Remove Microsoft Word Index Entries

Microsoft WordI’ve written a few books over the last few years; I use Microsoft Word as it is easy to work in and easy to convert to PDF. Some of the books I have written have been self published. Doing it this ways means that I need to do all of the layout and formatting, such as creating the index.

On occasion, I have had a need to remove the index and re-add; this is most often when I am writing a new edition of a book. I’ve done a fair bit of searching for ways of doing this and have used a couple of different approaches.

One of the early approaches I came across was to use VBA to strip the index entries out, but more recently I found another, much simpler, way of doing it using find and replace.

Press Ctrl+H to open the Find and Replace dialog; in the Find what box, type ^d XE:

Find and replace

Continue reading “Remove Microsoft Word Index Entries”

Copy Account Categories Between Microsoft Dynamics GP Companies

Microsoft Dynamics GPWhen implementing Microsoft Dynamics GP for a new client, they usually have very similar setup between companies. While you can use the Professional Services Tools Library tool Company Copy, you sometimes need to replicate data which was configured after this function had been used.

I’ve previously posted scripts to copy segments and financial calendars; today’s script will copy Account Categories to a new company ensuring you have the categories in both companies:

Continue reading “Copy Account Categories Between Microsoft Dynamics GP Companies”

Move Management Reporter To A New Domain

Microsoft Dynamics GPA client recently ran into problems with Management Reporter whereby they had a large number of building blocks accidentally deleted from one building block group. While the easiest solution would be to restore the Management Reporter database to before the blocks were deleted, this was not possible.

The client in question has a lot of users across different businesses each it their own building block groups and were in the middle of year end. LOsing all the management accounts so far produced ad interrupting all business units to fix an issue in one of them was not possible. To make things worse, their test system was being rebuilt after some year end testing so we couldn’t use this to restore the database and export the building blocks.

The solution was therefore a little more long winded and took several steps:

  1. Copy a backup of the Management Reporter database prior to the deletion and restore to my test system.
  2. Run the copy the Management Reporter 2012 database to a new server script from Microsoft
  3. Use the after you make changes to your domain, you no longer have permission to access Management Reporter; this script was needed as the domain on my demo system is totally different to the originating one.
  4. Export required building blocks.
  5. Import exported building blocks back into client system.

The above took a while due to the size of the database which had to be copied to my test system and restored, but was actually quite straightforward.

Cannot Launch SQL Server Management Studio

Microsoft SQL ServerI work on a variety of test and demo systems and recently stumbled across an oddity with SQL Server Management Studio (SSMS); when I tried to launch it using the shortcut, the splash screen would display, stay on screen for a few seconds before disappearing with SSMS never starting, but no error message appearing or showing in the logs.

If I tried to start it by typing ssms in a command prompt I received the following error:

ssms error

ssms1

Windows cannot find 'ssms'. Make sure you rtyped the name correctly, and then try again.

I did some exploring and found this article on Stackoverflow which resolved my problem.

Find the Ssms.exe.config file and erase the line <NgenBind_OptimizeNonGac enabled="1" />:

Noterpad.exe showing Ssms.exe.config

Remove Modified Window From Microsoft Dynamics GP

Microsoft Dynamics GPIf you have a modified window which is no longer required, it is fairly easy to remove (although these steps will need to be repeated on each client).

To remove the window, open Modified (Microsoft Dynamics GP menu » Tools » Customize » Modifier):

Microsoft Dynamics GP showing Customize menu

Continue reading “Remove Modified Window From Microsoft Dynamics GP”

Update Navigation Pane Series Order

Microsoft Dynamics GPWhen I install demo VMs, I usually want everything setup the same way each time I do it. This includes things like inserting UK VAT Rates as well as some more mundane configuration issues like the order of series on the navigation pane.

That’s what the script in this post does; it switches off some of the series so they don’t show and reorders the remaining ones into the order I want them in.

Before running the script, set the USERID at the top (highlighted).

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DECLARE @USERID VARCHAR(30) = 'iang' UPDATE SY07130 SET Visible = 0 WHERE USERID = @USERID AND CmdSequence BETWEEN 6 AND 9 UPDATE SY07130 SET CmdSequence = CmdSequence + 30 WHERE USERID = @USERID UPDATE SY07130 SET CmdSequence = 1 WHERE USERID = @USERID AND CmdParentCmdID = 11 UPDATE SY07130 SET CmdSequence = 2 WHERE USERID = @USERID AND CmdParentCmdID = 1 UPDATE SY07130 SET CmdSequence = 3 WHERE USERID = @USERID AND CmdParentCmdID = 6 UPDATE SY07130 SET CmdSequence = 4 WHERE USERID = @USERID AND CmdParentCmdID = 3 UPDATE SY07130 SET CmdSequence = 5 WHERE USERID = @USERID AND CmdParentCmdID = 7 UPDATE SY07130 SET CmdSequence = 6 WHERE USERID = @USERID AND CmdParentCmdID = 2 UPDATE SY07130 SET CmdSequence = 7 WHERE USERID = @USERID AND CmdParentCmdID = 4 UPDATE SY07130 SET CmdSequence = 8 WHERE USERID = @USERID AND CmdParentCmdID = 5 UPDATE SY07130 SET CmdSequence = 9 WHERE USERID = @USERID AND CmdParentCmdID = 10 UPDATE SY07130 SET CmdSequence = 10 WHERE USERID = @USERID AND CmdParentCmdID = 22002 GO

As always before running a script, ensure you have a good backup of your databases.

Microsoft Dynamics GP 2018 R2 Displays Full Screen By Default

Microsoft Dynamics GPLast year Steve Endow did a post on Microsoft Dynamics GP 2018 always starts full screen and how you can stop it from doing so. The cause is a setting in the Dex.ini file which was set to TRUE:

WindowMax=TRUE

Prior versions of Dynamics GP had this setting set to FALSE; at the time he posed, Steve wasn’t sure if the setting was always set to TRUE or only if the Web Client Runtime was installed. Well, I can confirm that it is always set to TRUE.

I haven’t been that bothered by Dynamics GP always starting full screen, but after a client upgrade I was asked by them about it.

To stop Dynamics GP always starting full screen you need to change the Dex.ini file setting to false; this needs to be done on every client. If you are a GP Power Tools user you can use the Dex.ini Configuration tool to change all clients, otherwise you will need to manually update all clients.

Decrypting Microsoft Dynamics GP System Passwords Is Now Possible

Microsoft Dynamics GPMicrosoft Dynamics GP encrypts its system and budget passwords in a way which cannot be decrypted. Or at least it did. DynDeveloper.com, in April, posted an article containing a stored procedure which would allow the passwords such as the system or budget passwords to be decrypted.

As well as the stored proc, the article also includes sample code which will return the decrypted system password.

The post with code is here.

Update: It turns out this isn’t new and was first posted about back in 2010 when it was misunderstood what this type of decryption would cover. It is system and budget passwords (which are both optional and often not used), not user passwords.

Using Node Builder to Create New Node: Conclusion

eOne SolutionsThis post is part of the series on Using Node Builder to Create New Node; in particular I m creating a node to add EFT Bank information when adding a creditor.

In this series, I’ve gone through the steps to create a simple node to add EFT Bank details when using SmartConnect to insert a vendor. Node Builder allowed us to create the node without writing any code.

Node Builder is the least polished of the eOne products; in fact, based on how good SmartList Builder and SmartConnect are, I was very surprised at the lack of polish.

The alternative to using Node Bulder is to write your own eConnect stored procedures. Microsoft has posted some example code of how to do this. In addition, Tim Wappat has also posted about this.

If I was creating the node, I’d be tempted to just write my own (as I have done in the past), but when working with a client I would show them Node Builder and explain the pros and cons (including limitations such as not integrating with SmartConnect any more).