Using SanScript for Retrieving System Database Name in Microsoft Dynamics GP

To get the System DB name when integrating with Dynamics, use the following sanscript code:

---------------------------------------------------------------------------------------------
local integer companyid;
local integer dictionary;
local integer file_series;
local integer logical_file;

companyid = DYNAMICS; {constant with value 0 }
dictionary = DYNAMICS;
file_series = TABLESSERIES_SYSTEM; { constant with value 7 }
logical_file = 0;

set sDatabase to SQL_GetDbName(companyid, dictionary, file_series, logical_file, fSuccess).
---------------------------------------------------------------------------------------------

This routine will then return the string name of the System database. This is all done by accessing the SY_Pathnames table and retrieving the column Database Name.

While it probably is much more proper to do this, it probably isn't worth the effort to get the system series DB from code because installation hardcodes the DB to "DYNAMICS" in all current cases. But it is possible in the future this could change so this isn't a bad idea.

VBA Script for connecting to Microsoft Dynamics GP Database

The following VBA script example can be used for the Description_AfterGotFocus event in the Microsoft Dynamics GP Account Maintenance window. This script will connect to the Microsoft Dynamics GP sample TWO database and log on as system administrator with a password. After the connection is made, the script creates a recordset of the data that is stored in the GL00105 account index master table. The script will then return the account index value to the User-Defined1 field in the Account Maintenance window. The script returns this value when you enter a new account or use the Account Lookup button.

To use the example script, follow these steps:

1. Open the Account Maintenance window in Microsoft Dynamics GP.
2. On the Tools menu, click Customize, and then click Add Current Window to Visual Basic.
3. On the Tools menu, click Customize, click Add Fields to Visual Basic, and then click the Account Number field, the Description field, and the User-Defined 1 field.
4. On the Tools menu, click Customize, and then click Visual Basic Editor.
5. In Visual Basic Editor, expand Great Plains Objects, and then double-click AccountMaintenance to open an Account Maintenance code window.
6. Copy the following code, and then paste it into the Account Maintenance code window.

'-------------------------------------------------------------------------------------------------
Private Sub Description_AfterGotFocus()
Dim objRec
Dim objConn
Dim cmdString

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=MSDASQL;DSN=GreatPlains;Initial Catalog=TWO;User Id=sa;Password=password"
objConn.Open


cmdString = "Select ACTINDX from GL00105 where (ACTNUMST='" + Account + "')"

Set objRec = objConn.Execute(cmdString)

If objRec.EOF = True Then
AccountMaintenance.UserDefined1 = ""
Else
AccountMaintenance.UserDefined1 = objRec!ACTINDX
End If
objConn.Close
End Sub
'-------------------------------------------------------------------------------------------------

You can also use the RetrieveGlobalsX.dll file as per for Microsoft Dynamics GP versions like 9 for 9.0, 8 for 8.0, to retrieve the same information that this script example retrieves.

Script for Delete Stranded User Activity in Microsoft SQL Server

In this article, we will going to use a script to delete the activity table records for a specific stranded user in Dynamics GP. This will enable us not to have all the users log out of GP in order to execute these scripts. This is typically handy when we have many users signed into the system from various regions and its a tough job to have all the users sign off the system for this maintenance to be done. Please make sure that you must take a back up of DYNAMICS database before you execute this script.

DECLARE @USERID CHAR(15)

DECLARE @CMPNYNAM CHAR(30)

SET @USERID = 'testuser'

SET @CMPNYNAM = 'Fabrikam, Inc.'

/*Deleting the user record from the DEX_LOCK table*/

DELETE FROM tempdb.dbo.DEX_LOCK

WHERE session_id = ( SELECT SQLSESID

FROM DYNAMICS.dbo.ACTIVITY

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)

)

/*Deleting the user record from the DEX_SESSION table*/

DELETE FROM tempdb.dbo.DEX_SESSION

WHERE session_id = ( SELECT SQLSESID

FROM DYNAMICS.dbo.ACTIVITY

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)

)

/*Deleting the user record from the SY00801 table*/

DELETE FROM DYNAMICS.dbo.SY00801

WHERE USERID = @USERID

AND CMPANYID = ( SELECT CMPANYID

FROM DYNAMICS.dbo.SY01500

WHERE CMPNYNAM = RTRIM(@CMPNYNAM)

)

/*Deleting the user record from the SY00800 table*/

DELETE FROM DYNAMICS.dbo.SY00800

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)

/*Deleting the user record from the ACTIVITY table*/

DELETE FROM DYNAMICS.dbo.ACTIVITY

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)