Beginning Balances not Showing in Management Reporter

We ran into an interesting issue where the numbers in Management Reporter did not match what was in Dynamics GP. We did the standard first step of re-creating the data mart and integration, but the numbers were still off.

Upon further investigation, the beginning balances were missing from the report. We looked in GP, and the trial balance looked right, but the MR numbers were still off.

Then we noticed that the Source Document for the beginning balances was not the standard "BBF"; it was "BF". It appeared that something had happened to GP during the year end close, so they created a journal entry to replace it then altered it in SQL to change the transaction date.

We had to update the Source Document in SQL and gave it an Original Transaction Source of "CLOSE". After re-creating the data mart, all was well in the world.

Time Log Excel Spreadsheet

As a consultant, I need to keep track of my day to make sure our company bills our customers for the appropriate hours I spend. If I'm working on one customer all day, it is pretty easy to enter my time into our billing system at the end of the day. On the other hand, when I'm working on several customers throughout the day and being interrupted by other customers and other consultants via meetings, e-mails, and phone calls, I need a spreadsheet where I can track every minute of the day - and is easy to see what I worked on for each external or internal customer when I'm entering my time into the billing system.

A colleague of mine had created the basic format, but it was very static. Always trying to improve and streamline, I changed it into a dynamic interface. Plus, I really wanted it to be easy to see all the work I did throughout the day for one particular customer and project. 

I have been trying to ween myself off VBA in Excel whenever possible, so this spreadsheet contains no VBA macros - it is all Excel formulas and conditional formatting. The sheet is protected to keep data entry in the designated places, but there is no password if you want to poke around and see how it works.

Download the file here

Shoot me a comment if you find the spreadsheet useful.

eConnect error - Name cannot begin with the 'x' character.

I just ran into a rather interesting error I'd never seen before in Integration Manager. This integration uses the eConnect destination adapter going into Purchase Orders.

The error we received was as follows:

 eConnect error - Name cannot begin with the '6' character, hexidecimal value 0x36. Line 1, position 12559.

Integration Manager / eConnect creates the XML code automatically, so how would we even troubleshoot this?

I found a reference to an article on PartnerSource for saving the XML files IM/eConnect creates to a folder for troubleshooting, but the link was down and I couldn't find reference to it anywhere else on the internet (of course). See this page for a little more about the link (scroll down to the "Creating XML Data Files") section.

Looking at the data file and the error a few more times, I had a hunch there may be something wrong in the parsing of the import data so I looked through the text file for a '6' with a special character nearby. I found a 6 with the "less than" sign. Apparently Integration Manager 10 / eConnect couldn't handle this very well. I had the customer remove the "less than" sign, and all came in just fine.

Here is a screenshot from the import file (most of it is obscured for privacy reasons):

ASI_Explorer_Get_Field_List Script Terminated

I ran into this rather sketchy error on a customer system when trying to open the Sales Transaction SmartList:

Unhandled script exception:
Index 258 of array '[Not Found]' is out of range in script
'ASI_Explorer_Get_Field_List'. Script terminated

Generally an index out of range error means the array variable is dimentioned smaller than what the script is trying to pull out of it. For instance, the code is trying to access location 10 of an array with only 8 spots.

I went into the Tools >> Setup >> System >> SmartList Options window in GP and noticed that all the fields for the Sales Transaction SmartList were selected. I figured maybe the saved settings had gotten corrupted somehow and just needed to be re-saved. I clicked OK, and it gave me the error saying that I selected too many columns, and that I needed to reduce the number of display columns to less than 190.

I have not idea how they were all selected in the first place (or maybe the settings were somehow corrupt), but I just hit the Default button to reset the selection to just the default fields and saved. No more error.

Automatic Screen Capture

Description:
A utility to create automatic screenshots. Multiple monitors supported.
I created this program to help me go back through my day to see what all I worked on.
This is freeware. No reverse-engineering or anything like that. Use at your own risk.

One known issue - it does not handle the larger Windows 8 DPI settings very well. That will be the next thing I tackle whenever I get back around to working on the program.

WARNING:  
This is not intended to capture someone's screen without their knowledge.
I will not change it to hide the system tray icon nor the process in Task Manager.

How To:

Settings Screen

Prefix	File name prefix
Suffix	File name suffix, including timestamp; currently this is not editable
Folder	Folder in which to save the screenshots
Compression	Percent to which to compress the image files
	A lower number creates smaller files; a higher number is higher quality
	20 percent is the lowest suggested value that is still readable
Capture	Amount of time between screenshots
Stop	Amount of time between reminders if the auto capture is stopped
Snooze	Snooze the capture and stop warnings
Set	Press to save and apply the settings
Start	Start or stop the auto capture
Minimize	Minimize the window to the system tray
Exit	Close the capture program
System Tray
Double-click	Create a screenshot right now
Right-click | Settings	Open the settings window
Right-click | Start	Start or stop the auto capture
Right-click | Capture	Create a screenshow right now
Right-click | Snooze	Snooze for duration of selected time
Right-click | Exit	Close the program

Notes:

If you stop the auto capture but do not fully exit the program, the settings window will pop up
after the interval specified in the Stop Reminder Interval. This is designed so you do not forget
to turn on the auto capture if you needed to stop it for a period of time but don't use snooze.


Version Info:

1.8
-------
Fixed a bug when manually entering a save folder
Added a capture button on the Settings window
Added statistics of Captures vs Missed timed screenshots
Added About window

1.7
-------
Added Snooze functionality to the system tray
Updated system tray icons to show the current status of the auto-capture; yellow if snoozed, red of stopped
Various code streamlining

1.6
-------
Added Snooze functionality; use from the settings window; this will pause captures and stop warnings
The Set botton will turn yellow when settings have changed but are not Set
The Snooze button will turn yellow while snoozing
Added warnings for un-Set changes before starting capture or exiting the program
Setting window format update

1.5
-------
Added the Stop Reminder Interval
Added Start/Stop to the system tray
Added Settings to the system tray
Changed the system tray double-click to be a manual screenshot
Updated the message box when closing the program from the settings screen

1.0
-------
Original version


Future enhancements
-------
Program icon
System Tray icon
Monitor selection

The download file is the executable itself - not an installation program.

Download from SkyDrive

GP User Activity with Status and IP

If you've ever needed to clear out old GP users, you know it can sometimes be difficult to figure out which ones are still connected and which ones are not. Yes, the ones that logged in two days ago may seem the obvious choices to kick out, but that's not always the case. We've seen users leave GP open for days on end to not lose their seat and their SQL session is hot, and we've seen users open GP in the morning but bombed out an hour later but didn't need it anymore so they didn't bother to log in to clear their seat.

Either way, this script below combines several GP and SQL system tables and views to give you a broad view of the status of your GP users. It will show whether users in User Activity are in fact still connected to GP, how long they've been logged into GP, when their last SQL activity occurred and how long ago it was, their computer's IP address, if they are working on batches, and what table they are currently working in.

I'm sure there is still some room for improvement, and I will be adding to it from time to time, so check back later for any updates.

USE DYNAMICS
SELECT 
    Activity.USERID UserID, 
    Users.USERNAME Username, 
    Activity.CMPNYNAM Company,
    CASE
        WHEN AbandonedSessions.SQLSESID IS NULL
            THEN 'Connected'
        ELSE 'Removable'
    END Status,
    CONVERT(varchar(20),ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) LoggedInAt,
    ISNULL(CONVERT(varchar(20),ConnectedSessions.last_batch),'') LastGPUsage,
    CASE WHEN GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))-1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 's '
    END    TimeSinceLogin,
    CASE 
        WHEN ConnectedSessions.last_batch IS NULL 
            THEN ''
        WHEN GETDATE()-ConnectedSessions.last_batch < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-ConnectedSessions.last_batch)- 1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-ConnectedSessions.last_batch)) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-ConnectedSessions.last_batch)) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-ConnectedSessions.last_batch)) + 's '
    END GPIdleTime,
    ISNULL(lock.table_path_name,'') OpenTable,
    ISNULL(batch.BatchCount,0) BatchesInProgress,
    Activity.SQLSESID SessionID,
    ISNULL(CONVERT(varchar(10),ConnectedSessions.SPID),'') SPID,
    ISNULL(ConnectedSessions.client_net_address,'') IPAddress
FROM 
    DYNAMICS..ACTIVITY Activity
    INNER JOIN DYNAMICS..SY01400 Users 
    ON Activity.USERID = Users.USERID
LEFT JOIN
    (
        SELECT *
        FROM DYNAMICS..Activity 
        WHERE SQLSESID NOT IN (
            SELECT SQLSESID
            FROM DYNAMICS..Activity A
            INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
            INNER JOIN master..sysprocesses P
            on S.sqlsvr_spid = P.spid
            AND A.USERID = P.loginame
        )
        AND NOT EXISTS
            (SELECT spid, c.CMPNYNAM DB, loginame,*
            FROM master..sysprocesses p
            inner join master..sysdatabases d
                on p.dbid = d.dbid
            inner join DYNAMICS..SY01500 c
                on d.name = RTRIM(c.INTERID)
            where c.CMPNYNAM = Activity.CMPNYNAM
                and p.loginame = Activity.USERID
                and p.program_name = ''
            )
    ) AbandonedSessions
    ON Activity.SQLSESID = AbandonedSessions.SQLSESID
    AND Activity.USERID = AbandonedSessions.USERID
LEFT JOIN
    (
        SELECT 
            A.SQLSESID, 
            P.spid, 
            P.last_batch, 
            ec.client_net_address, 
            CASE 
                WHEN P.login_time < A.LOGINDAT + A.LOGINTIM 
                    THEN p.login_time 
                ELSE A.LOGINDAT + A.LOGINTIM 
            END last_login
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
            ON S.sqlsvr_spid = P.spid
        AND A.USERID = P.loginame
        INNER JOIN sys.dm_exec_connections ec
            ON S.sqlsvr_spid = ec.session_id
    ) ConnectedSessions
    ON Activity.SQLSESID = ConnectedSessions.SQLSESID
LEFT JOIN
    (
        SELECT session_id, max(table_path_name) table_path_name
        FROM tempdb..DEX_LOCK
        GROUP BY session_id
    ) lock
    ON Activity.SQLSESID = lock.session_id
LEFT JOIN
    (
        SELECT
            USERID,
            CMPNYNAM,
            COUNT(*) BatchCount
        FROM
            Dynamics..SY00800
        GROUP BY
            USERID,
            CMPNYNAM
    ) batch
    ON Activity.USERID = batch.USERID
    and Activity.CMPNYNAM = batch.CMPNYNAM
ORDER BY 
    Activity.LOGINDAT,
    Activity.LOGINTIM

Check Disk Space script

Part of normal maintenance of a Dynamics GP SQL server is to make sure there is enough disk space for your data as it grows. Similarly, you need to make sure your data files aren't growing more than they should.

This script draws from several system tables to show the recovery model, the last backup of the data or log file, the size of the file, the growth of the file the next time it runs out of room, the drive on which the file is stored, and the free space of the drive.

I included the Recovery Model and LastSQLBackup columns to show the situation where FULL recovery is used, but no Transaction Log backups are taking place. This situation causes the Transaction Log to grow unnecessarily until it fills the hard drive. If you do not back up the Transaction Log, having FULL recovery is both useless and dangerous to your data at the same time; dangerous because it can bottom out your hard drive while transactions are being posted, and useless because the log file hasn't been backed up to be able to use in a restore which is the whole point of having it in the first place.

-- Shows database file space usage and free disk space
USE [master]

IF EXISTS (SELECT * FROM sys.tables WHERE name='#cbs_drivespace')
    DROP TABLE #cbs_drivespace

IF EXISTS (SELECT * FROM sys.tables WHERE name='##cbs_db_sizes')
    DROP TABLE ##cbs_db_sizes

CREATE TABLE #cbs_drivespace
(
    Drive varchar(10), 
    MB_Free numeric(19,2)
)

CREATE TABLE ##cbs_db_sizes
(
    dbname varchar(100),
    filetype varchar(4),
    size_in_gb numeric(19,2),
    growth_in_percent numeric(19,2),
    growth_in_gb numeric(19,2)
)

DECLARE @SQL varchar(4000)
declare @DatabaseId varchar(255)

INSERT INTO #cbs_drivespace(Drive, MB_Free)
EXEC master.dbo.xp_fixeddrives

declare DatabaseLoop cursor for 
    select name from master..sysdatabases where name in 
        (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
        or name in ('master','msdb','tempdb')

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId
while (@@fetch_status <> -1)
    begin

    /*Inner Loop for each Database*/
    SET @SQL =
        'INSERT INTO ##cbs_db_sizes '
        + 'SELECT ''' + @DatabaseId + ''' AS dbname, '
        + 'CASE WHEN RIGHT(RTRIM(filename),3) = ''mdf'' THEN ''Data'' ELSE ''Log'' END AS filetype, '
        + 'CONVERT(numeric(19,2),size * 8 / 1024. / 1024) AS size_in_gb, '
        + 'CONVERT(numeric(19,2),CASE WHEN status & 0x100000 > 0 THEN growth ELSE (growth * 100.) / (size * 1.) END) AS growth_in_percent, '
        + 'CONVERT(numeric(19,2),CASE WHEN status & 0x100000 = 0 THEN growth * 8 / 1024. / 1024 ELSE size * (growth / 100.) * 8 / 1024 / 1024 END) AS growth_in_gb '
        + 'FROM ' + @DatabaseId + '..sysfiles'
    EXEC (@SQL)
    fetch next from DatabaseLoop into @DatabaseId 
    end

close DatabaseLoop
deallocate DatabaseLoop

SELECT
    db.name as DBName,
    db.dbid as DBID,
    DatabasePropertyEX(db.name, 'Recovery') as RecoveryModel,
    s.filetype as FileType,
    CASE 
        WHEN s.filetype = 'Data' 
            THEN ISNULL(CONVERT(varchar(15),bu.LastBU,101),'Never')
        WHEN s.filetype = 'Log' AND DatabasePropertyEX(db.name, 'Recovery') = 'SIMPLE'
            THEN 'N/A'
        ELSE
            ISNULL(CONVERT(varchar(15),bu.LastBU,101),'Never')
    END LastSQLBackup,
    s.size_in_gb as SizeInGB,
    s.growth_in_gb as GrowthInGB,
    s.growth_in_percent as GrowthInPercent,
    ds.Drive as DriveLetter,
    CONVERT(numeric(19,2),ds.MB_Free / 1024) DriveGBFree
FROM
    sysdatabases db
INNER JOIN
    ##cbs_db_sizes s
    ON db.name = s.dbname
INNER JOIN
    #cbs_drivespace ds
    ON LEFT(db.filename,1) = ds.Drive
LEFT JOIN
    (
        SELECT
            a.Name as dbname,
            COALESCE(Convert(varchar(100), MAX(b.backup_finish_date), 101),'Backup Not Taken') as LastBU,
            b.type as BUType,
            CASE 
                WHEN b.type = 'D' THEN 'Data'
                WHEN b.type = 'L' THEN 'Log'
            END as BUFileMatch
        FROM 
            sysdatabases a 
        LEFT OUTER JOIN 
            msdb.dbo.backupset b
            ON b.database_name = a.name
        WHERE
            b.type IN ('D','L')
        GROUP BY 
            a.Name,
            b.type
    ) bu
    ON s.dbname = bu.dbname
    AND s.filetype = bu.BUFileMatch
ORDER BY
    DBName,FileType

DROP TABLE #cbs_drivespace
DROP TABLE ##cbs_db_sizes