User connection to test or wrong company in Dynamics GP zDP_SY30000F_1

So you find that SQL monitor or profiling shows that every minute a user is access a company that you don’t expect to see accessing? How can this happen? This puzzled us for a little while today, we had a user accessing the test company, when we checked with the user, this was not the company there were currently logged into within GP.

Profiling showed the procedure being executed against the database is

exec DYNAMICS.dbo.zDP_SY30000F_1

The source of this is the user message functionality added in GP2013R2, see this previous post Check for User Messages(1) Dynamics GP Process Monitor. In that post I explain that GP polls a table every minute to see if there are any messages to show the user.

Below is a screen shot of SQL profiler filtered for that database and user. The single user’s name fills the blurred cols. The odd thing is that this user is not even logged into the company that owns that database in GP.

SQLProfilerWrongDB

Check for default database settings

My gut told me to head for DSN settings, we checked and indeed there lay the issue. This is occurring because the DSN for the connection that GP is using has a default database set to that of the database this activity is seen on.

However trying to check for the existing default database by going into the ODBC Data Source Administrator, found in the control panel, to check the database default is not enough. The displayed database there does not reflect the currently set default database.

image

Instead to find the default, start an Excel query from Microsoft Query, selecting the DSN used by GP, then clicking the more details button to see the database details. The default database should be master but in the scenario outlined it will be the database where all the activity is seen. Change it to master and the issue should cease.

image

Will it cause harm?

Although normally this should cause no harm as the query is actually targeted at the DYNAMICS database and is just passing through the selected company database, it could potentially be a problem. For example if the selected database is a test company and that test company database gets restored during operational hours. It can also confuse IT administrators that don’t understand the eccentricity of GP.