How to See the Current Status of a Dynamics GP Upgrade or Service Pack

UPDATE: See updated version here.

I've referenced before that we have a customer with over 80 GP companies; they are the mother of many script inventions for me as I have no desire to manually do anything for 80 databases. Well, now they are at 90.

Needless to say, one of the many challenges with such a large number of companies is keeping track of where the database processing is during an upgrade or service pack. It would also be nice to know how long the thing is going to take.

Here is a handy little script I created to help keep track of this:

Edit 8/20/2014: Fixed a couple letter cases to work properly for Binary SQL sorting
Edit 10/15/2014: See updated script here

DECLARE @NewVersion int
DECLARE @NewBuild int

SET @NewVersion = 11    -- Change this to the major version number you are upgrading TO
SET @NewBuild = 1752    -- Change this to the build number you are upgrading TO

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END

SELECT * 
INTO #Completed_List
FROM (
    SELECT
        Completed_Company = RTRIM(db_name),
        Started_At = (SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0),
        Ended_At = CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN GETDATE() ELSE MAX(stop_time) END,
        Run_Time = 
            CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN
                GETDATE()-(SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            ELSE
                MAX(stop_time)-(SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            END,
        Upgrading = CASE WHEN MAX(db_status) <> 0 THEN 1 ELSE 0 END
    FROM
        DB_Upgrade DBU
    WHERE
        EXISTS(
            select *
            from DB_Upgrade where
            db_verMajor = @NewVersion
            and db_verBuild = @NewBuild 
            AND PRODID =  0
            AND db_name <> 'DYNAMICS'
            and db_name = DBU.db_name
        )
    GROUP BY db_name
) a

SELECT 
    Currently_Upgrading = ISNULL((select TOP 1 db_name from DB_Upgrade where ((db_verMajor = @NewVersion AND db_verBuild = @NewBuild) AND (db_verOldMajor <> db_verMajor OR db_verOldBuild <> db_verBuild)) and PRODID = 0 and db_name <> 'DYNAMICS' order by start_time desc),
                                (SELECT TOP 1 db_name FROM DB_Upgrade WHERE db_status <> 0)),
    Not_Upgraded = (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS'),
    Upgraded = (select COUNT(*) from DB_Upgrade WHERE (db_verMajor = @NewVersion AND db_verBuild = @NewBuild AND db_verOldMajor = db_verMajor AND db_verOldBuild = db_verBuild)  and PRODID = 0 and db_name <> 'DYNAMICS'),
    Average_Time_Per_DB = 
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 's '
    ,
    Elapsed_Time =
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 's '
    ,
    Estimated_Remaining_Time = 
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 's '
                                
SELECT
        Completed_Company = CASE WHEN Upgrading = 1 THEN Completed_Company + ' - Upgrading' ELSE Completed_Company END,
        Started_At,
        Ended_At,
        Run_Time = 
            CONVERT(varchar(4),DATEPART(HOUR,(Run_Time))) + 'h ' +
            CONVERT(varchar(4),DATEPART(MINUTE,(Run_Time))) + 'm ' +
            CONVERT(varchar(4),DATEPART(SECOND,(Run_Time))) + 's '
FROM
    #Completed_List
ORDER BY 
    Upgrading DESC, 
    Ended_At DESC

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END

CRM 2011 - Windows Azure Installation Error

I went to install CRM 2011 for a customer last Friday on a brand new server only to get stuck at the Pre-Req screen with a "fatal error during installation" result for the Windows Azure platform AppFabric SDK 1.0.

I did a repair on .NET 4.0 and even did a full uninstall and re-install only to get the same result. I downloaded the WindowsAzureAppFabricSDK-x64.msi file directly from Microsoft's page (April Update) only to get a similar error. I looked in the Event Viewer and saw the following error:

Error 1026, .NET Runtime
Application: RelayConfigurationInstaller.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception
Exception Info: exception code e0434352, exception address 000007FEFCF2AA7D

It turns out it is a known issue that the AppFabric team is working on. See this thread from the MSDN forums.

To get past this error, you can either:

1) Download the previous build from here:
https://mercuryit.blob.core.windows.net/$root/WindowsAzureAppFabricSDK-x64.msi
You can also find this link in the thread in the MSDN forums.
Whenever they come up with a fix it is suggested to download and upgrade to the newer build.

2) Download v 2.0 of the AppFabric from here:
http://www.microsoft.com/download/en/details.aspx?id=17691
I have not personally tried this one, but see this thread for success stories.

Upgrading GP v9 Alt/Mod Reports to v10 or 2010

The upgrade from version 9 to version 10 asks, in so many words, if you want to transfer and upgrade the GP security from the old, not-so-great way of doing things to the new and much improved version. "No" is the appropriate answer there, but the drawback is that the settings for which modified reports are used are not pulled over, either. This can be a huge headache if there are a lot of modified reports as every user could have a completely different setting in each company; on top of that not all of the modified reports may even be used. A lot of times this is not by design - one user needed a report to be customized and thus is the only one with permission to the custom report, or someone was playing with the idea of changing a report, pulled it over in Report Writer to do so, but never actually made the change. Voila! You now have a long list of modified reports that aren't necessarily modified, and the permissions to the ones that actually are modified are not consistent.

I should created something to help with this a long time ago when we actually had a bunch of v9 upgrades left to do, but I recently ran into a situation where it was worth creating just for one customer.

Two things to note about this script: 1) it will only work with Modified Reports, and 2) you have to make a decision about how you want to pull in these permissions. You can have it just set up a single Alt/Mod Form/Report ID to where if anyone had permission in version 9 that everyone now has permission in version 10/2010 or you can break it up into how many times that report is given permission. For example, if ten users have permissions to report 1, five users have permissions to report 2, and eight users have permissions to report 3, you might create two Alt/Mod Form/Report IDs: one for six to ten users and another for one to five users. Run the script multiple times in the order of most min/max average users to least min/max average users, as in the previous example.

Even if you have to do some manual tweaking afterwards, you are much farther ahead than if you did not run it at all. Depending on your particular case, just running the query at the beginning comment section might be enough to help you manually create the settings.

If you do not like the result of any run of the script, you can simply delete the new Alt/Mod Form/Report ID from inside Dynamics GP.

/*
Restore the v9 DYNAMICS database to a new database called DYNAMICSv9
Populate the SY09400 table in the v10 DYNAMICS database to see the names of the reports
  Inside Dynamics GP, go to Maintenance >> Clear Data
  Click Display >> Physical Data
  Change Series to System
  Select Security Resource Descriptions, click Insert, OK and Yes
  Even though you chose Clear Data, it is actually adding it in
  Everyone else has to be out of GP to do the Clear Data or it will not run

SELECT
 CMPANYID,DSPLNAME,RESID, count(DISTINCT V9.USERID) C
FROM 
 DYNAMICSv9..SY02000 V9
LEFT JOIN DYNAMICS..SY09400 Descr
 ON V9.resid = Descr.SECURITYID
 AND V9.restype = Descr.SECRESTYPE
 AND V9.dictid = Descr.DICTID
WHERE 
 V9.DICTID = 0
 AND RESTYPE = 23
GROUP BY
 CMPANYID,DSPLNAME,RESID
ORDER BY
 CMPANYID,RESID


If you run this script and do not like the results, simply delete the created
  Alt/Modified Forms/Reports ID from inside Dynamics GP or manually update it

*/

-- SETTING UP VARIABLES

DECLARE @ALTMODFRMREP_ID CHAR(25)
DECLARE @ALTMODFRMREP_DESCRIPTION CHAR(50)
DECLARE @CREATEDATE DATETIME
DECLARE @USERID CHAR(15)
DECLARE @MAXAVERAGE INT
DECLARE @MINAVERAGE INT


-- CHANGE THESE VARIABLES AS NEEDED
SET @ALTMODFRMREP_ID =            'DEFAULTUSERYC'                -- ID: THIS MUST BE ALL CAPS (15 CHARS)
SET @ALTMODFRMREP_DESCRIPTION =    'Default YourCompany User'    -- DESCRIPTION: DOES NOT HAVE TO BE CAPS (50 CHARS)
SET @CREATEDATE =                '6/9/2011'                    -- USE TODAY'S DATE
SET @USERID =                    'sa'                        -- CAN LEAVE 'sa'

                                                            -- RUN THE SQL QUERY FOUND IN THE COMMENTS ABOVE TO SEE THE 
                                                            --     AVERAGES TO USE IN THE NEXT TWO VARIABLES
                                                            --   THESE WILL DEPEND ON HOW MANY COMPANIES YOU HAVE
                                                            --   OR HOW MANY SETS OF ALT/MOD SETTINGS THERE ARE.
                                                            --   SORRY - NO SCRIPT TO HELP YOU MAKE THAT DECISION
SET @MINAVERAGE =                1                            -- MIN NUMBER OF AVERAGE OCCURRENCES OF USERS USING THE REPORT IN V9
SET @MAXAVERAGE =                1000                            -- MAX NUMBER OF AVERAGE OCCURRENCES OF USERS USING THE REPORT IN V9


-- CREATING THE ALT/MOD FORM/REPORT ID
INSERT INTO SY09200 (SECMODALTID,SECMODALTDESC,CRUSRID,CREATDDT,MDFUSRID,MODIFDT)
VALUES (@ALTMODFRMREP_ID,@ALTMODFRMREP_DESCRIPTION,@USERID,@CREATEDATE,@USERID,@CREATEDATE)

-- COPYING THE SMARTLISTS, ETC. FROM DEFAULTUSER
INSERT INTO SY10800 (SECMODALTID,DICTID,SECURITYID,SECRESTYPE,ALTDICID,Modified)
    SELECT @ALTMODFRMREP_ID,DICTID,SECURITYID,SECRESTYPE,ALTDICID,Modified
    FROM SY10800 WHERE SECMODALTID = 'DEFAULTUSER'

-- UPDATING THE REPORTS ALREADY MARKED AS MODIFIED OR ALTERNATE AS THE V9 MODIFIED OR ALTERNATE REPORTS
UPDATE SY10800
    SET ALTDICID = V9.ALTDICID,
        Modified = 1
    FROM SY10800 V10 INNER JOIN 
        (SELECT DICTID,RESID,RESTYPE,ALTDICID
        FROM DYNAMICSv9..SY02000 V9                            -- v9 database
        WHERE DICTID = 0 AND RESTYPE = 23
            AND EXISTS (
            SELECT DICTID,SECURITYID,SECRESTYPE FROM SY10800 WHERE SECMODALTID = @ALTMODFRMREP_ID
                AND SY10800.SECRESTYPE = V9.RESTYPE AND SY10800.SECURITYID = V9.RESID AND SY10800.DICTID = V9.DICTID
        )
        GROUP BY DICTID,RESID,RESTYPE,ALTDICID
        HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE
        ) V9 ON V10.DICTID = V9.DICTID AND V10.SECRESTYPE = V9.RESTYPE AND V10.SECURITYID = V9.RESID
    WHERE V10.SECMODALTID = @ALTMODFRMREP_ID

-- ADDING THE REPORTS FROM V9
INSERT INTO SY10800 (SECMODALTID,DICTID,SECURITYID,SECRESTYPE,ALTDICID,Modified)
    SELECT @ALTMODFRMREP_ID,DICTID,RESID,RESTYPE,ALTDICID,1
    FROM DYNAMICSv9..SY02000 V9                                -- v9 database
    WHERE DICTID = 0 AND RESTYPE = 23
        AND NOT EXISTS (
        SELECT DICTID,SECURITYID,SECRESTYPE FROM SY10800 WHERE SECMODALTID = @ALTMODFRMREP_ID
            AND SY10800.SECRESTYPE = V9.RESTYPE AND SY10800.SECURITYID = V9.RESID AND SY10800.DICTID = V9.DICTID
    )
    GROUP BY DICTID,RESID,RESTYPE,ALTDICID
    HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE

-- SETTING UP THE USERS TO USE THE REPORTS - THESE PARTS ARE OPTIONAL
--   UPDATING EXISTING PERMISSIONS
UPDATE SY10550
    SET SECMODALTID = @ALTMODFRMREP_ID
    FROM DYNAMICSv9..SY02000 V9                                -- v9 database
    INNER JOIN SY10550 V10 ON V9.USERID = V10.USERID AND V9.CMPANYID = V10.CMPANYID
    WHERE EXISTS (
        SELECT DICTID,RESID,RESTYPE
            FROM DYNAMICSv9..SY02000 V9b                    -- v9 database
            WHERE DICTID = 0 AND RESTYPE = 23
                AND V9.DICTID = V9b.DICTID
                AND V9.RESTYPE = V9b.RESTYPE
                AND V9.RESID = V9b.RESID
            GROUP BY DICTID,RESID,RESTYPE
            HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE 
        )
    AND EXISTS (
        SELECT USERID,CMPANYID
        FROM SY10550 V10
        WHERE V9.USERID = V10.USERID
            AND V9.CMPANYID = V10.CMPANYID
        )
        
--   ADDING NEW ONES IF APPLICABLE
INSERT INTO SY10550 (USERID,CMPANYID,SECMODALTID)
    SELECT USERID,CMPANYID,@ALTMODFRMREP_ID
    FROM DYNAMICSv9..SY02000 V9                                -- v9 database
    WHERE EXISTS (
        SELECT DICTID,RESID,RESTYPE
            FROM DYNAMICSv9..SY02000 V9b                    -- v9 database
            WHERE DICTID = 0 AND RESTYPE = 23
                AND V9.DICTID = V9b.DICTID
                AND V9.RESTYPE = V9b.RESTYPE
                AND V9.RESID = V9b.RESID
            GROUP BY DICTID,RESID,RESTYPE
            HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE 
        )
    AND NOT EXISTS (
        SELECT USERID,CMPANYID
        FROM SY10550 V10
        WHERE V9.USERID = V10.USERID
            AND V9.CMPANYID = V10.CMPANYID
        )
    GROUP BY USERID,CMPANYID



/*
-- SEVERAL LOOKUPS TO HELP FIND THE DATA - ALL OF THESE ARE IN THE DYNAMICS DATABASE
select * from DYNAMICSv9..SY02000 -- THIS IS IN THE V9 DYNAMICS DATABASE
select * from sy10800
SELECT * FROM SY09200
SELECT * FROM SY10550
SELECT INTERID,CMPNYNAM,CMPANYID FROM SY01500
*/

'sa', DYNSA, and POWERUSER in Dynamics GP

Updated 4/2/2015

I sometimes get questions about whether the 'sa' user is required for Microsoft Dynamics GP, what an alternative for 'sa' is, and what the POWERUSER Security Role really does. Well, I'm not a Dynamics GP developer at Microsoft, but here is what I know from what I've seen. Keep in mind this is not a How-To for setting up GP security.

Before going into the differences of each type of user or role, let me explain a little about GP Security. 

During the initial installation and each subsequent company database creation, a database role called DYNGRP is created in each database and assigned permissions to most objects in each database (Tables, Views, and Stored Procedures). This one database role is the basis for all SQL database access for GP users - with the exception of the 'sa' user, but more on that later.

When you create a new user in GP, the following key actions take place, not necessarily in this order and not limited to this list:

• A SQL login is created
• A GP user is created in DYNAMICS..SY01400
• The user is assigned to the DYNGRP database role in the DYNAMICS database
• The user is assigned to the DYNGRP database role in each respective company given to it in the User Access window (after the user is created)
• The user's password is encrypted and saved in SQL

Because all GP users are part of the DYNGRP role, any GP user could log into SQL through the back-end and gain access to pretty much all the data in the GP databases assigned to them - if it weren't for that last line item in the list. Because the passwords are encrypted, users are limited to accessing SQL data through GP.

Now on to the 'sa' and DYNSA users, internal GP Security, the POWERUSER Security Role, and Alternate/Modified Forms and Reports settings.

sa

This is the big cheese of both SQL and GP. There is no greater user than this and there is no GP data it cannot touch. "There is the GP system password," the semi-experienced might say. Well the 'sa' user does not need to be logged in to GP to get to the SQL tables protected by the GP system password.

The ‘sa’ user is also the only GP user who has direct access to the databases from SQL and is the only SQL user that is created outside GP that can log into GP. A special static ‘sa’ user is built into GP, and it cannot be deleted nor have its password changed from inside GP. The 'sa' user is the only GP user who can perform all GP maintenance and administrative tasks inside GP* as well as all maintenance and administrative tasks in SQL. By default, 'sa' has the POWERUSER Role in all companies.

DYNSA

All GP SQL databases are owned by the DYNSA user. This is what allows the DYNSA user to perform most administrative and maintenance tasks without requiring the ‘sa’ user. 

EDIT: This also allows DYNSA to back up company databases inside GP (but cannot restore them - only 'sa' can restore databases). GP adds DYNSA to the SecrityAdmin and dbCreator system roles on installation, so this user can administer GP users but still does not allow creating new GP companies despite the dbCreator role.

System Password

There are certain system-wide administrative tasks that are protected by GP's System Password, not to be confused with the 'sa' password. The System Password protects areas such as GP Security, Multicurrency Access, and other tasks not related to any particular company. You can log in as 'sa' or DYNSA, but if you do not have the System Password you cannot perform those tasks inside GP. Keep in mind that users do not have to be 'sa' or DYNSA to perform some of those tasks as long as they have the System Password.

Internal GP Security

Dynamics GP's security is what keeps regular GP users from having access to all objects in all GP databases given through the DYNGRP SQL database group.

What I describe here relates to Dynamics GP version 10 and above. Version 9 and below security was completely different.

GP Security Operations

Operations are the individual windows and reports to which security access can be given. For example, the Payables Transaction Entry window and Financial Detail Trial Balance Report are Operations.

Operations are defined by the available windows ("forms," in GP terminology) and reports in a given product dictionary. This is not something you will set up as a GP administrator.

GP Security Tasks

Tasks are small groups of Operations that relate to each other. For example, the ADMIN_PURCH_001 task gives access to several windows and reports relating to setting up Purchasing.

GP Security Roles

Roles are groups of Tasks that define a job role. For example, the AP Clerk role allows a user to enter Payables Transactions, maintain Vendors, look up GL entries, and print Purchasing reports.

There are plenty of pre-defined Tasks and Roles available out of the box. I suggest that instead of editing the defaults you create new Tasks and Roles based on the default ones. This will help you or your Microsoft Partner identify which ones are non-standard if you run into any issues related to security.

When giving access to users, you will be specifying which Role(s) they will have and for which company. A user can have multiple Roles and can have different Roles in each GP company.

POWERUSER Role

Unlike the AP Clerk Role where its access is explicitly defined, the POWERUSER Role implicitely has access to everying the DYNGRP database role has access to in a specified GP company database. The 'sa' and DYNSA users are automatically part of the POWERUSER Role. Giving a regular user all other Security Roles is not the equivalent of the POWERUSER Role.

Alternate/Modified Forms and Reports Settings

In Dynamics GP version 9 and below, setting someone up to use a modified report was very granular and did not allow for very easy administration. In version 10 and later, it is like setting up a group of on/off switches: for each alternate or modified option available on the workstation you are using to set this up, you will specify whether to use the default or the alternate/modified alternative. Yes, since you can technically be using different report dictionaries and different modules per workstation, the Alternate/Modified Forms and Reports settings are workstation-specific; most of the time you'll want to utilize the same modules and a shared location for modified reports, but that's not always the case. Alternate/Modified Forms and Report settings can be specified per user per company.

*Adding Additional System Security

As noted by Devo (thanks for that find!), additional security can be added to users to perform certain administrative tasks, such as adding users and companies. See pages 37-40 of the GP Security Planning guide found here.

EDIT: To give users access to be able to create new users or update passwords, assign the SQL login to the SecurityAdmin server role. To give users access to be able to add new companies in GP Utilities, assign the SQL login to the sysadmin server role.

Conclusion

I hope this helps answer at least a few questions regarding how Microsoft Dynamics GP and Microsoft SQL Server work together to provide security for Dynamics GP as well as clarifies how security is implemented inside GP.

If you found this helpful, please leave a comment.

SQL Reports for GP 10 / 2010 Fun

There are three things to know when setting up the Dynamics GP SQL Reports:

1) You need to have your registration keys entered

The first indication that something is going wrong shows up on the screen where you "select the module(s) to deploy reports for" - and I don't mean because there is a preposition at the end of the sentence. There should be a list of modules here other than "Charts and KPIs." That indication aside, when you get through the wizard and look at your SQL Report Manager site, you will find that nothing more than a couple Data Sources and Report Models were actually created.

Register your Dynamics GP inside GP at Tools >> Setup >> System >> Registration

2) You need to use a not-so-obvious URL in the Reporting Tools Setup window

You will get a friendly "The Report Server URL entered is not valid." warning when you enter the standard Report Server URL that you've used everywhere else you've been asked. No, GP requires something a little different.

For Reporting Services 2005, use
http://ServerName:PortNumber/reportserver/reportservice.asmx

For Reporting Services 2008 (Native Mode), use:
http://ServerName:PortNumber/reportserver/reportservice2005.asmx

For Reporting Services 2008 (SharePoint Integrated Mode), use:
http://ServerName:PortNumber/reportserver/reportservice2006.asmx

UPDATE: It appears that GP 2010 R2 does not need this special link. http://ServerName:PortNumber/reportserver is all that is required.

You may instead get an error regarding "set maxRequestLength='20690' in the web.config file". Edit the web.config found in the ReportServer folder in the SQL Reporting Services program folder; it would be similar to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.

Add the following line to the web.config file just above the <securityPolicy> section. If there already is an httpRuntime line, change it to match this statement:

<httpRuntime executionTimeout="9000" maxRequestLength="20960"/>

3) You need to point to a default SQL instance

With the third issue, and you'll have to correct me if this has changed, but I remember running into issues when I had a named SQL instance for Dynamics GP. There really wasn't much that could be done to get the reports deployed. Sorry, no solution here for that one.

SQL Transaction Log Viewer - Poor Man's Version

I came up with a way to somewhat see what is in the SQL Transaction Log in conjunction with the SQL Profiler. So, in addition to seeing the actual SQL statements in the SQL Profiler, you also get an idea, cryptic as it may be, of what those statements are doing to the database. You will need to stop the transaction log backups while you are using this since the transactions, once backed up, will not be returned in our select statement. Note that the Transaction Log only keeps track of changes to the database, which is why we have to run a SQL Profiler trace as well.

The Content0 column is the main payload. There are a couple WHERE statements there to help filter out some of the noise and to look at a particular SQL session (a user's connection) and/or timeframe.

The first script is the my_HexToChar function which translates some of the transaction log information to near-plain-english. You will need to run this script in each database for which you want to look at the transaction log information.

The second and third scripts are for looking at the transaction log information. The first script looks only at the transaction log. The second looks at both the transaction log and the SQL Profiler trace output.

In order to use the third script, you will need to start and run a SQL Profiler trace. Open SQL Profiler, create a new Trace, choose the TSQL_Replay template, check the box for Save to table, select a database other than the one for which you are tracing (I might suggest creating a new DB called ProfilerDB for this purpose), and specify table myReplay. Run the trace and leave it running while you run your queries or programs.

EDIT:
To filter the date range of the returned records, use the commented-out WHERE statement filter for the [Begin Time] column in script 2 or 3.

You can use the fourth script to clear out the trace log and reset the starting point of the transaction log query. If you are wondering what CHECKPOINT is doing to your log/data (it's not clearing out your transaction log), here is a great article from Paul Randal.

SQL Profiler Settings

Script 1: my_HexToChar function

CREATE FUNCTION my_HexToChar (@in VARBINARY(4000))
RETURNS varchar(4000)
AS
  BEGIN
      DECLARE @result varchar(4000)
      DECLARE @i int
      SET @result = ''
      SET @i = 1
      WHILE @i < Len(@in)
        BEGIN
            IF Substring(@in, @i, 1) BETWEEN 0x20 and 0x7A
              -- limiting it to certain visible characters 
              SET @result = @result + cast(Substring(@in, @i, 1) as char(1))
            SET @i = @i + 1
        END
      RETURN @result
  END
GO 

Script 2: view Transaction Log only

SELECT 
    [Begin Time],
    [Transaction Name],
    AllocUnitName,
    [Transaction ID],
    SPID,
    Operation,
    dbo.my_HexToChar([RowLog Contents 0]) Contents0, -- gives an idea of what was changed
    dbo.my_HexToChar([RowLog Contents 1]) Contents1,
    dbo.my_HexToChar([RowLog Contents 2]) Contents2,
    dbo.my_HexToChar([RowLog Contents 3]) Contents3,
    dbo.my_HexToChar([RowLog Contents 4]) Contents4,
    dbo.my_HexToChar([Log Record]) AllContents,
    *
FROM
    fn_dblog(null, null) a
WHERE
    [Transaction ID] in (
        SELECT [Transaction ID]
        FROM fn_dblog(null,null)
        where Operation = 'LOP_BEGIN_XACT'
        and [Transaction Name] not IN ('AutoCreateQPStats','SplitPage','SpaceAlloc','UpdateQPStats')

--        and SPID = 51    -- uncomment this line to limit it to a particular SQL session - look in Activity Monitor for SPIDS

--        and cast([Begin Time] as DATETIME) BETWEEN '07/30/2010 11:30' and '07/30/2010 2:50pm'   -- uncomment this line to limit by date/time
    )
order by a.[Transaction ID],a.[Current LSN]

Script 3: view Transaction Log and SQL Profiler info

SELECT
    *
FROM
(
    SELECT 
        b.[Begin Time],
        [Transaction Name],
        AllocUnitName,
        a.[Transaction ID],
        b.SPID,
        Operation,
        dbo.my_HexToChar([RowLog Contents 0]) Contents0, -- gives an idea of what was changed
        dbo.my_HexToChar([RowLog Contents 1]) Contents1,
        dbo.my_HexToChar([RowLog Contents 2]) Contents2,
        dbo.my_HexToChar([RowLog Contents 3]) Contents3,
        dbo.my_HexToChar([RowLog Contents 4]) Contents4,
        dbo.my_HexToChar([Log Record]) AllContents,
        2 EventType,
        [Current LSN] SortOrder
    FROM
        fn_dblog(null, null) a
    INNER JOIN
        (SELECT max([Begin Time]) [Begin Time],[Transaction ID],max(SPID) SPID FROM fn_dblog(null, null) GROUP BY [Transaction ID] ) b
    ON a.[Transaction ID] = b.[Transaction ID]
    WHERE
        a.[Transaction ID] in (
            SELECT [Transaction ID]
            FROM fn_dblog(null,null)
            where Operation = 'LOP_BEGIN_XACT'
            and [Transaction Name] not IN ('AutoCreateQPStats','SplitPage','SpaceAlloc','UpdateQPStats')
        )
    UNION ALL
    SELECT
        StartTime,
        '',
        '',
        convert(varchar(20),ClientProcessID),
        SPID,
        '',
        TextData,
        ApplicationName,
        '',
        '',
        '',
        '',
        1 EventType,
        convert(varchar(20),EventSequence) SortOrder
    FROM    
        ProfilerDB..myReplay
    WHERE
        DatabaseName = DB_NAME()
) Logs
WHERE
    1=1
    -- and SPID = 60    -- uncomment this line to limit it to a particular SQL session - look in Activity Monitor for it
    -- and cast([Begin Time] as DATETIME) BETWEEN '03/13/2011 18:24:05' and '03/13/2011 18:26:39'   -- uncomment this line to limit by date/time
order by [Begin Time],[Transaction ID],SortOrder

Script 4: clear the Profiler Log and reset the Transaction Log query start point

DELETE FROM ProfilerDB..myReplay
CHECKPOINT

Dynamics GP Security Reports for SQL Reporting Services

User Roles User Tasks User Operations

It is not very easy to find out the current status of everyone’s security in GP. Sure, there are a some standard reports inside GP, but they are not the most user-friendly way to quickly track down who has what permissions to GP’s windows and reports.
That is exactly why we created GP Security Reports for SQL Reporting Services.
There are currently 9 reports in this package to give you at-a-glance, drill-down views of your Dynamics GP security:

1. Role Tasks
2. Task Operations
3. Company Roles – Per User
4. Company Roles – All Users
5. User Roles – Per Company
6. User Roles – All Companies
7. User Tasks – Per Company
8. User Tasks – All Companies
9. User Operations – Per Company

If you would like to find how to purchase these security reports, e-mail info@combussol.com or call 561-392-8135.

GP Security Upgrades

We still have customers upgrading to Dynamics GP 10 or 2010 from version 9 and, even scarier, from version 8. One of the biggest changes starting in version 10 is the security model. The absolute management nightmare of previous versions was changed to something resembling an Active Directory-ish "resource - task group - role group - user" model. This was Microsoft's best addition to GP 10.

When upgrading to version 10 or 2010 from version 9, GP Utilities asks if it should upgrade security. No. NO! Resist the temptation to take the fast track! You will only bring over the junk you are running away from. Start fresh, clean, and clutter-free.

Starting fresh with security also gives the added value of revisiting security requirements. More than a few of our customers did not want to mess with the over-complicated security model of old and just left everyone with access to everything. Not the Sarbanes-Oxley approach, but they had accounting to attend to. When they did take the time to implement security their users would start out with the default permissions assigned by their User Class, and that would be the last time their security ever matched the defaults. A report "I need to run for So-And-So" here, a window "I have to enter in transactions while So-And-So is on vacation" there - all undocumented, of course, and no one would have the guts to reset them back to the default User Class because that would be a phone call from an unhappy user who "can't do anything" just waiting to happen.

Version 10/2010's Security Roles have given nearly all of our customers a reason to implement real security. It's a whole lot easier to figure that their AP Clerk gets the AP Clerk role and that additional permissions are given through the additional role created just for those additions. As far as whether or not to edit the default roles, my suggestion is to leave the default roles alone and either create new ones based on the defaults or create new ones with just the additional permissions. Someone's on vacation? You can give their role to the their underpaid grunt and take it back upon return. One checkbox.

One thing to keep in mind is that you cannot deny access - only allow access. Maybe you have several people performing the same tasks except for one of them who shouldn't see a handful of those windows or reports. You would create one role with all the common tasks and another with the exceptions.

My unique contribution to this subject is this security worksheet to help get started with version 10's security before the upgrade. This is a .zip file with two Excel spreadsheets. One is mainly designed to show the default Tasks contained within each default Role, and the other is a worksheet to assign Users to Roles. Both are derived from a set of SQL Reports we created in-house to give a better visualization than the canned GP security reports. My next post explains more about those reports.

Download the security worksheets here

Couting the Number of Weeks in a Month

I tasked myself with creating a personal budget spreadsheet in Excel. I know there are plenty of them out there, but I could not find one that calculates how many times a weekly or bi-weekly budget item occurs in a given month based on a particular start date. Like some of you I get paid every other week, which means that some months I get paid twice and others I get paid three times (woohoo!). As much fun as it is to figure out how many months I get three paychecks, I don't want to have to manually figure out the number of occurrences for each weekly and bi-weekly line item for each month. That's why computers were invented.

Not finding the exact formula out on the internet to give me what I was looking for (though some were helpful) and still being somewhat of a n00b in Excel, I realized I would have to figure it out using formulas I've never used before but assumed were out there. And I DID NOT want to resort to a VBA user-defined function (UDF); I have been following Chandoo's Excel blog - an unbelievably amazing place to "become awesome in Excel" - and therein have discovered the power of Excel without the need for VBA.

Specifications

• Return the number of weekly or bi-weekly occurrences in a given month
• Based on a Start Date
• Do not use VBA / UDF
• Do not use any other cells to hold portions of the calculation
• Do not use circular references
• Do not have an aneurysm trying to figure this one out

Not wanting to use additional cells to aid with the calculations, I knew the formula would have to involve arrays or something similar. Thankfully, I found out that Excel can do arrays. I also found out how to use the ROW and INDIRECT functions together to create the data to fill said arrays.

Variables

For this example, I copied this formula from cell AW7 of my spreadsheet. It gives me the number of occurrences of my budget line item for January (AW$4).

H is the column that contains the Budget Type:

• W = Weekly - every week based on the Start Date
• BW = Bi-Weekly - every other week based on the Start Date
• SM = Semi-Monthly - twice per month, ignoring the Start Date
• M=Monthly - once per month, ignoring the Start Date

I is the column that contains the Start Date

FiscalYear is the single cell named range that has holds the budget year

4 is the row that contains the month number, one column for each month in columns AW to BH. We will eventually be populating the same number of cells directly below AW4 - BH4 for each line item for which we are creating a budget.

Formula

Here is the formula I came up with:

{=IF(ISBLANK($H7),0,(IF($H7="M",1,(IF($H7="SM",2,SUMPRODUCT(IF(MONTH(ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0))))=AW$4,1,0)*IF(MOD(ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0)))-$I7,IF($H7="BW",14,7))=0,1,0)))))))}

Easy enough, right?

Breakdown

{

Right out of the gate you'll notice the curly braces: this denotes an Array Formula. This lets you do calculations on an array of information in memory without having actual cells for each value on your spreadsheet. When you type an Array Formula you need to press Ctrl-Shift-Enter (CSE) when you are done instead of just hitting Enter; you do not actually type the curly brace - Excel will do this by itself. More on how/why we use the Array Formula later.

=IF(ISBLANK($H7),0,

If our Budget Type cell is blank, return a 0 (zero) - we are not going on a budget for this line item

(IF($H7="M",1,

If our Budget Type is M, return a 1 (one), for once per month.

(IF($H7="SM",2,

If our Budget Type is SM, return a 2 (two), for twice per month.

SUMPRODUCT(

Here, we start our "else" section where the Budget Type is not blank, "M", or "SM"; this leaves "W" and "BW". SUMPRODUCT will give us a sum of all the rows of values in our arrays. We will use two arrays, and it will make more sense when you see what they are.

IF(

Starting with our first array, we are evaluating whether or not the month number returned for each value of the array is part of the month number we are specifying in AW$4.

MONTH(

Get the month number from the date serial number.

ROW(

We need to create an array of numbers to represent a date range. I saw the power of ROW(INDIRECT(...)) here and picked apart Ron Rosenfeld's "very nice" formula (towards the bottom) to see how it worked.

INDIRECT(

The INDIRECT function allows you to specify a range using a non-static value. For example, INDIRECT("1:2") gives us rows 1 and 2. INDIRECT(A1&":"&A2), where A1=5 and A2=10, gives us rows 5 through 10. If you specify a cell containing a date, the serial number for that date is used, and can be converted back to a date as needed.

$I7&":"&

This date range will begin with the Start Date ($I7)...

DATE(FiscalYear,AW$4+1,0)

...and will end on the last day of the specified month (AW$4). The DATE function is in the form DATE(year,month,day). If you notice, our day is 0 (zero); when you use a day of 0, it will give you the last day of the previous month. Hence, we add one (+1) to our month variable AW$4 to get the last day of that month. Don't worry, when we get to month 12, it knows to change month 12+1, day 0 to 12/31.

)))=AW$4,1,0)

We close our INDIRECT, ROW, and MONTH functions and ask if our month is the same as our selected month (AW$4). If it is, return a 1; otherwise, return 0.

*

Multiply our first array, row per row, with our second array. Our first array specifies if the date falls within the selected month (AW$4); our second will let us know if it also falls on a weekly or bi-weekly date, depending on the specified Budget Type ($H7), based on the Start Date ($I7). If both arrays return 1 (1*1=1) we know our budget line includes that date. Conversely, if either or both are 0 (1*0=0 and 0*0=0) our budget line does not include that date.

IF(

We begin our second array. As already mentioned we are finding out which dates fall every 7 or 14 days following the Start Date, depending on whether they are Weekly or Bi-Weekly, respectively.

MOD(

We can easily figure out our 7 or 14 day stepping by whether the remainder of dividing our dates by 7 or 14 is 0 (zero). The MOD function spits out the remainder of two numbers.

ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0)))

This should look familiar. It is exactly the same in the first array so I won't go into another explanation.

-$I7,

We subtract out the start date from each row in the array so we can do our MOD function. For example, if our date range in serial number format is 40,000 through 40,365, subtracting 40,000 gives us 0 through 365. Now we can divide by 7 or 14 and get a proper remainder.

IF($H7="BW",14,7)

Here we find out whether we are dividing by 7 or 14. If our Budget Type ($H7) is "BW", divide by 14; otherwise, divide by 7.

)=0,1,0)))

If our MOD is 0 (zero), meaning that it is evenly divisibly by 7 or 14, return 1; otherwise, return 0.

)

Close out our SUMPRODUCT function.

)))}

Close out our Budget Type IF statements and Array Formula. Don't forget: Excel adds the curly brace when you press CSE - you do not.

Where to go from here

There is plenty of room for improvement in this formula. For instance, the Start Date only works correctly if it is within January; adding a condition looking for a negative number would take care of that. An End Date would also be great. We could add "Bi-Monthly", "Annually" and "Semi-Annually" to the Budget Type list, and base all the Budget Types on the Start Date.

I hope this post not only gets you started with a formula ready to go but also gives you the knowledge to take it a few steps further. Let me know if you found it useful.

Copy Live to Test Company

Note: see updated version here

One of those "busy work" tasks for a Dynamics GP consultant is when we need to create a test company and copy a live database into the test company. Well, I'm not about to automate the creating of the company in GP Utilities, but I can certainly help with the part of backing up and restoring the live database into the test company and running the Microsoft CreateTestCompany.sql script afterwards.
This tried and tested script will perform the following:

• (Optional) Back up the live company database
• (Optional) Back up the test company database
• Restore the live company backup (or, alternately, any specified backup file) over the test company database
• Run Microsoft's CreateTestCompany.sql script against the test company database
• (Optional) Change the posting output options to Screen instead of Printer or File

I find it extremely useful when I need to restore it several times to get a procedure down right.

DECLARE @SourceDatabase varchar(10),
        @TESTDatabase varchar(10),
        @DatabaseFolder varchar(1000),
        @BackupFolder varchar(1000),
        @BackupFilename varchar(100),
        @UseRestoreFile tinyint,
        @RestoreFile varchar(100),
        @TESTBackupFilename varchar(100),
        @BackupTESTFirst tinyint,
        @LogicalFilenameDB varchar(100),
        @LogicalFilenameLog varchar(100),
        @SetPrintToScreen tinyint,
        @SQL varchar(max)
-----------------------------------------------------------------------
-- Set up all the information here for the live and test database names
-----------------------------------------------------------------------

SET        @SourceDatabase =    'TWO'
USE                             [TWO]    -- enter the Source Database between the braces
SET        @TESTDatabase =      'TEST'
SET        @BackupFolder =      'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' --end with a backslash (\)
--                               Folder where you want to save the backup file
SET        @UseRestoreFile =     0    --    0 = Create a backup and restore it
--                                    --    1 = Use the below filename to restore from instead of creating new backup
--                                    Use this when you just want to reload a backup you already created
SET        @RestoreFile =       'TWO_20110111.bak'
SET        @BackupTESTFirst =    1     --    0 = No; 1 = Yes
--                                    Backup the TEST database before restoring
--                                    You should do this the first time you restore per day
SET        @SetPrintToScreen =   1    --    0 = No; 1 = Yes
--                                    Change the posting output to the screen instead of the printer
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
SELECT @LogicalFilenameDB =      (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 1
SELECT @LogicalFilenameLog =     (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 0
SELECT @DatabaseFolder =         left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 1
print @LogicalFilenameDB
print @LogicalFilenameLog
print @DatabaseFolder

SET    @SQL = ''

IF @UseRestoreFile = 0
    BEGIN
        SET    @BackupFilename = @BackupFolder + @SourceDatabase +
            '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
            '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
            '_for_' + @TESTDatabase + '.bak'
        END
    ELSE
        BEGIN
            SET @BackupFilename = @BackupFolder + @RestoreFile
        END

print '.'
print 'Backup Filename: ' + @BackupFilename
print '.'

SET @TESTBackupFilename = @BackupFolder + @TESTDatabase +
    '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
    '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
    '_pre_LIVE_restore.bak'

-- check to see if the TEST database is in use first
IF EXISTS(SELECT loginame=rtrim(loginame),hostname,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
        WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
        = @TESTDatabase)
    BEGIN
        PRINT 'The database is in use'
        SELECT loginame=rtrim(loginame),hostname,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
            WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
            = @TESTDatabase
    END
ELSE
    BEGIN
        IF @UseRestoreFile = 0
        BEGIN

        -- Back up the LIVE database
            SET @SQL = '' +
                'PRINT ''Backing up Source database (' + @SourceDatabase + ')''; ' +
                'BACKUP DATABASE [' + @SourceDatabase + '] TO DISK ' +
                '= N''' + @BackupFilename + ''' ' +
                'WITH NOFORMAT, NOINIT, NAME ' +
                '= N''' + @SourceDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
            EXEC (@SQL)
        END

        -- Back up the TEST database
        IF @BackupTESTFirst = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Backing up Destination database (' + @TESTDatabase + ')''; ' +
                    'BACKUP DATABASE [' + @TESTDatabase + '] TO DISK ' +
                    '= N''' + @TESTBackupFilename + ''' ' +
                    'WITH NOFORMAT, NOINIT, NAME ' +
                    '= N''' + @TESTDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
                EXEC (@SQL)
            END

        -- Restore to the TEST database
        SET @SQL = '' +
            'PRINT ''Restoring to Destination database (' + @TESTDatabase + ')''; ' +
            'RESTORE DATABASE [' + @TESTDatabase + '] FROM DISK ' +
            '= N''' + @BackupFilename + ''' ' +
            'WITH FILE = 1, ' +
            'MOVE N''' + @LogicalFilenameDB + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Dat.mdf'', ' +
            'MOVE N''' + @LogicalFilenameLog + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Log.ldf'', ' +
            'NOUNLOAD, REPLACE, STATS = 10'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Setting Recovery Model to Simple''; ' +
            'ALTER DATABASE [' + @TESTDatabase + '] SET RECOVERY SIMPLE WITH NO_WAIT'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Shrinking Log File'' ' +
            'USE [' + @TESTDatabase + '] ' +
            'DBCC SHRINKFILE (N''' + @LogicalFilenameLog + ''' , 0, TRUNCATEONLY)'
        EXEC (@SQL)

        IF @SetPrintToScreen = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Setting output to print to screen instead of printer'' ' +
                    'USE [' + @TESTDatabase + '] ' +
                    'UPDATE SY02200 SET PRTOSCNT = 1, PRTOPRNT = 0'
                EXEC (@SQL)
            END

        -- Run the CreateTestCompany script from MS
        SET @SQL = '' +
            'PRINT ''Running CreateTestCompany script on ' + @TESTDatabase + '''; ' +
            'USE [' + @TESTDatabase + '] ' +
            'if not exists(select 1 from tempdb.dbo.sysobjects where name = ''##updatedTables'') ' +
            ' create table [##updatedTables] ([tableName] char(100)) ' +
            'truncate table ##updatedTables ' +
            'declare @cStatement varchar(255) ' +
            'declare G_cursor CURSOR for ' +
            'select ' +
            'case ' +
            'when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'') ' +
            ' then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) ' +
            'else ' +
            '''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' ' +
            'end ' +
            'from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c ' +
            'where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'', ''COMPANYCODE_I'') ' +
            'and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = ''BASE TABLE''; ' +
            'set nocount on; ' +
            'OPEN G_cursor; ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'WHILE (@@FETCH_STATUS <> -1) ' +
            'begin ' +
            'insert ##updatedTables select ' +
            'substring(@cStatement,8,patindex(''%set%'',@cStatement)-9) ' +
            'Exec (@cStatement) ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'end ' +
            'DEALLOCATE G_cursor ' +
            'select [tableName] as ''Tables that were Updated'' from ##updatedTables '
        EXEC (@SQL)
            PRINT 'Don''t forget to run GP SQL Maintenance on the ' + @TESTDatabase + ' database.'
    END

Add-In Initialization Error

Got this error today at one of our customers when opening Dynamics GP 10:

Microsoft.Dynamics.GP.BusinessIntelligence.DeployDataConnections.dll:
Could not load file or assembly 'Application.Dynamics, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=... or one of its
dependencies. Insufficient system resources exist to complete the requested
service. (Exception from HRESULT: 0x800705AA) InnerException:
Insufficient system resources exist to complete the requested service.
(Exception from HRESULT: 0x800705AA)

Windows Server 2008 32-bit, Remote Desktop server

I checked the obvious, given "insufficient system resources":

Available RAM: Gigs free
Available Hard Disk: hundreds of Gigs free
Um... CPU usage..? less than 5% usage

Before trying to repair the installation of Dynamics GP I figured I would reboot. I've ignored that underestimatedly valid option too many times lately to blow it off once again. Fortunately, it paid off and they were able to get back in.

If anyone runs into this and solves it with something other than a reboot, a little feedback would be nice. I'm not discounting the fact that I may run into it again sometime soon at this customer.

Match SQL Access to GP Access

We recently reformatted an 80-company Dynamics GP installation using Corporate Renassaince Group's Reformatter. The Reformatter migrates to a second SQL server, data is bulk copied over, and the accounts are re-formatted in the process. Works great. But...

Two things you have to do yourself: set up companies in GP Utilities on the new server for each company in your installation, and set up SQL logins and give them access to each database in SQL. I used the KB878449_Capture_Logins.sql script and reset the user passwords, but I still had to manually add them to the same SQL databases they had on the existing server. Imagine doing that for 10 users times 80 companies!

Well, that's why I created the script below, but it has other applications as well. Have you ever created a test company, copied the live data over, went into GP's User Access window, and checked the Access box for that new company only to get "The user could not be added to one or more databases"? This script is for you. It will also remove users from databases to which they should not have access.

Other errors this script may fix (depending on the root cause) include "A get/change operation on table 'syUserDefaults' failed accessing SQL data.", "The server principal (login) is not able to access the database (database) under the current security context.", and "A get/change first operation on table 'coProcess' failed accessing SQL data."

/*
This script will set access to SQL databases where each GP user has access
  within the User Access window inside GP.
You can run this after setting up a test company, or any time you get the error:
  The user could not be added to one or more databases.
*/
declare @DatabaseId    varchar(5),
        @UserId        varchar(30),
        @NotFound      tinyint,
        @SQL           varchar(max)
set     @SQL = ''
declare UserLoop cursor for
    select USERID from DYNAMICS..SY01400
        where USERID NOT IN ('sa','DYNSA')
open UserLoop
fetch next from UserLoop into @UserId
while (@@fetch_status <> -1)
    begin
        SET @SQL = @SQL + 'USE [DYNAMICS]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    BEGIN
        CREATE USER [' + @UserId + '] FOR LOGIN [' + @UserId + ']
        EXEC sp_addrolemember N''DYNGRP'', N''' + @UserId + '''
    END
'
    fetch next from UserLoop into @UserId
    end
close UserLoop
deallocate UserLoop
declare DatabaseLoop cursor for
    select RTRIM(c.INTERID),RTRIM(u.USERID),(CASE WHEN a.USERID IS NULL THEN 1 ELSE 0 END) NotFound
    from DYNAMICS..SY01500 c cross join DYNAMICS..SY01400 u left join DYNAMICS..SY60100 a ON c.CMPANYID = a.CMPANYID AND u.USERID = a.USERID
    WHERE INTERID IN
        (select name from master..sysdatabases)
    AND u.USERID NOT IN ('sa','DYNSA')
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId,@UserId,@NotFound
while (@@fetch_status <> -1)
    begin
    /*Inner Loop for each Database*/
        IF @NotFound = 0 -- it is found; add it if it does not exist
            SET @SQL = @SQL +    'USE [' + @DatabaseId + ']
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    BEGIN
        CREATE USER [' + @UserId + '] FOR LOGIN [' + @UserId + ']
        EXEC sp_addrolemember N''DYNGRP'', N''' + @UserId + '''
    END
'
        ELSE
            SET @SQL = @SQL +    'USE [' + @DatabaseId + ']
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    DROP USER [' + @UserId + ']
'
    fetch next from DatabaseLoop into @DatabaseId, @UserId, @NotFound
    end
close DatabaseLoop
deallocate DatabaseLoop
exec (@SQL)