Category Archives: MS Dynamics GP Database Management

How to correct errors in the PM00400 keys master table in MS Dynamics GP.

Posted on by
2

As previous posts point out the PM00400 keys master file can be the source of several errors.  MS does not recommend to delete the PM00400 file and recreate it.  They now recommend that you run check-links.  I have found that check-links will not correct these errors.

A previous post outlined a method to do this manually.  However, a client of mine was kind enough to provide me with a script that automates this entire process or recreating the PM00400 table.

I will post the SQL script here as soon as I can. In the meantime if you need it sooner please contact me via email.

**Update**

This script will recreate the PM00400 file.  Make sure to do a full database backup and have everyone log out of Microsoft Great Plains.  If you have any problems downloading the file, please let us know.

PM00400 GP Script

 

 

 

Sampe VBA Excel Macro with loop to update MS Dynamics GP table.

Posted on by
Reply

Here is a full example of the VBA sample code with a loop.  This VBA code loops through an Excel spreadsheet and inserts records in the MS Dynamics GP Payroll Posting Accounts table UPR40500. In this case we only insert accounts for Gross Pay.

The VBA sample code also create a record set named (rs) from the GL00100 table that will provide the GL account index for the full account.  The full GL account is matched to the record in the GL00100 table to get the account index.

Comment lines begin with an apostrophe (‘).

Public Sub GetAccounts()

‘Define ADO connection object and recordset object
Dim cn As ADODB.Connection, rs As ADODB.Recordset

‘set cn (connection) to hold database connection string
‘set rs (recordset) to hold the results for the retrieved record set ‘from SQL
Set cn = New ADODB.Connection
cn.Open “Provider=’SQLOLEDB’;Data Source=’dh-PC’;Initial Catalog=’TWO’;” & _
“Integrated Security=’SSPI’”
Set rs = New ADODB.Recordset

‘strSQL will hold the select SQL statemment to run
‘rs.open opens the record set with strSQL, and connection cn
Dim strSQL As String

‘ declare other variables
Dim strDept As String
Dim strPosition As String
Dim strPayCd As String
Dim strMainAcct As String
Dim strPayType As Integer
Dim strAcctIndx As Integer
Dim strFullAcct As String
Dim rsFullAcct As String

‘Read cells from payroll accounts sheet starting in row 2
Dim i As Integer
i = 2
strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Let strSQL = “select * from GL00100″
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
‘Use a MsgBox to display a messge and field contents for debuging, ‘remove the apostrophe in from the MsgBox statement below to see ‘how it works.

‘MsgBox strSQL

‘Process all the rows in the spreadsheet
Do Until IsEmpty(Cells(i, 1))

‘step through the record set and perform required actions on the records
‘reference the fields using the column name from the SQL table
Do While Not rs.EOF
‘retrieve the GL account index from the first row of the dataset
strFullAcct = strDept & strMainAcct & strPosition
rsFullAcct = Left(rs!ACTNUMBR_1, 3) & Left(rs!ACTNUMBR_2, 4) & Left(rs!ACTNUMBR_3, 2)
If strFullAcct = rsFullAcct Then intID = rs!ACTINDX
‘move to the next record in the recordset
rs.MoveNext
Loop

‘strAcctIndx holds the account index retrieved from the record set
‘set the department, position, pay code, pay type 1 = gross pay
strAcctIndx = intID

‘adjust department and position code strings to proper length
strDept = Right(strDept, 2)
strPosition = Right(strPosition, 2)

‘create the sql string to insert the record into the Payroll posting accounts table
strSQL = “INSERT INTO UPR40500 (DEPRTMNT,JOBTITLE,PAYROLCD,UPRACTYP,ACTINDX) VALUES”
strSQL = strSQL & “(” & strDept & “,” & strPosition & “,’” & strPayCd & “‘,” & strPayType & “,” & strAcctIndx & “)”
‘MsgBox strSQL
cn.Execute strSQL

i = i + 1
strDept = “”
strPosition = “”
strPayCd = “”
strMainAcct = “”
strPayType = 0
strAcctIndx = 0
strFullAcct = “”
rsFullAcct = “”

strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Loop

MsgBox “Import Completed”

‘close the open record set
rs.Close:   Set rs = Nothing
‘close the connection to the database
cn.Close:   Set cn = Nothing
End Sub

Sample VBA Excel Macro script to update MS Dynamics GP MS SQL tables

Posted on by
Reply

Here is a sample VBA script you can use to work with tables in MS Dynamics GP and MS SQL.  The script shows a select statment and and insert statement for MS SQL. Make sure to add the Reference for the MS ActiveX Data Objects 2.8 Library to the project under Tools-Reference in Excel or the script will not compile.

The sample script locates the gl account index from the GL00100 gl account master file then combines that with payroll data to insert the record into the UPR40500 GL Payroll Posting Accounts table.

Public Sub GetAccounts()

‘Define ADO connection object and recordset object
Dim cn As ADODB.Connection, rs As ADODB.Recordset

‘set cn (connection) to hold database connection string
‘set rs (recordset) to hold the results for the retrieved record set from SQL
Set cn = New ADODB.Connection
cn.Open “Provider=’SQLOLEDB’;Data Source=’dh-PC’;Initial Catalog=’TWO’;” & _
“Integrated Security=’SSPI’”
Set rs = New ADODB.Recordset

‘strSQL will hold the select SQL statemment to run
‘rs.open opens the record set with strSQL, and connection cn
Dim strSQL As String
Let strSQL = “select * from GL00100 WHERE(ACTNUMBR_1 = ’000′) AND (ACTNUMBR_2 = ’2940′) AND (ACTNUMBR_3 = ’00′)”
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText

‘step through the record set and perform required actions on the records., in this case ‘there is only one record in the recordset
‘reference the fields using the column name fro the SQL table
Do While Not rs.EOF
‘retrieve the GL account index from the first row of the dataset
intID = rs!ACTINDX
‘show the account index number
MsgBox intID
‘move to the next record in the recordset
rs.MoveNext
Loop

‘close the open record set
rs.Close:   Set rs = Nothing
‘ declare the variables for next operation
Dim strDept As String
Dim strPosition As String
Dim strPayCd As String
Dim strMainAcct As String
Dim strPayType As Integer
Dim strAcctIndx As Integer

‘strAcctIndx holds the account index retrieved from the record set
‘set the department, position, pay code, pay type 1 = gross pay
strAcctIndx = intID
strDept = “ACCT”
strPosition = “ADA”
strPayCd = “HOUR”
strPayType = 1
‘create the sql string to insert the record into the Payroll posting accounts table
strSQL = “INSERT INTO UPR40500 (DEPRTMNT,JOBTITLE,PAYROLCD,UPRACTYP,ACTINDX) VALUES”
strSQL = strSQL & “(‘” & strDept & “‘,’” & strPosition & “‘,’” & strPayCd & “‘,’” & strPayType & “‘,’” & strAcctIndx & “‘)”
MsgBox strSQL
cn.Execute strSQL

‘close the connection to the database
cn.Close:   Set cn = Nothing
End Sub

 

MS Dynamics GP This batch is being edited by another user.

Posted on by
Reply

The long fix is to follow MS Techknowledge Article ID 850209, A batch is held in posting, busy, marked, locked, or edited status in MS Dynamics GP.

If you know the batch has zero transactions and you know the user name or batch name just run a delete statement on SY00800 for the batch or userid.

Of course do an SQL backup of the company database and Dynamics database first.

In a new query run the following in MS SQL Management Studio.

SELECT * FROM DYNAMICS..SY00800
Identify the batch and userid.

Run the following delete statement again against system database.

delete SY00800 where BACHNUMB = ‘XXX’

In MS Dynamics GP in the company go to Batches for the module in quesion and select and delete the batch name.

MS Dynamics GP sa user or other user can’t login.

Posted on by
Reply

Talk about weird.  Several weeks ago we put on the round 2 tax update.  We didn’t bother to upgrade the test databases at the time.  Now, we want to use the test database.  So we copied the good company database into the test database.  Naturally you can’t launch into the test database even though it’s at the same level as the good company database because Dynamics isn’t that smart.  The version control tables in the Dynamics database are still at the different levels for the old test databases.  What do you do?

In the interest of time we went into the good production company.  Go to Tools – Utilities – System – Delete Company.  Delete the test companies.

Go into SQL Studio Manager, right click on the test databases and click delete.

Next we would go into Dynamics Utilities and create a new test database.  Guess what? We can’t log in as sa to Utilities or into the good production company.  We can log into SQL Studio Manager with the sa password and also the ODBCAD32.EXE under SYSWOW64 works for the ODBC connection.

So now we are debugging the error “This login failed. Attempt to log in again or contact your system administrator”.  Here is the complete text of the Techknowledge 919345 from the MS Techknowledge for your enjoyment.

When you try to log on to Microsoft Dynamics GP, you may receive the following error message:
This login failed. Attempt to log in again or contact your system administrator

CAUSE

Cause 1

This problem may occur if the password is changed at another computer. If the password is changed at another computer, the user cannot log on to Microsoft Dynamics GP at the user’s computer. This situation occurs because the way in which the server name is set up in the Data Source (ODBC) differs from the way in which the server name is set up on the computer where the password is encrypted.

See Resolution 1 in the “Resolution” section.

Cause 2

This problem may occur if the user logs on by using the incorrect case for the user ID or for the encrypted password. In this case, the error message is received at the next logon.

See Resolution 2 in the “Resolution” section.

Cause 3

This problem may occur if the following conditions are true:

• You are running Microsoft Dynamics GP 9.0.
• A new user is created.
• The new user cannot log on.

See Resolution 3 in the “Resolution” section.

Cause 4

This problem may occur if the Advanced Password Policies check boxes are selected in the User Setup window when you update from Microsoft SQL Server Desktop Engine (MSDE) 2000 to Microsoft SQL Server 2005 Express.

See Resolution 4 in the “Resolution” section.

Cause 5

This problem may occur if the public group does not have the Execute permission on the smDex_Max_Char stored procedure in the master database.

See Resolution 5 in the “Resolution” section.

Cause 6

This problem may occur if you are using mandatory profiles together with Terminal Server.

See Resolution 6 in the “Resolution” section.

Cause 7

This problem may occur if the following conditions are true:

• You are running Microsoft Small Business Financials.
• Not every new user can log on. Specifically, only the sa user can log on.

See Resolution 7 in the “Resolution” section.

Cause 8

This problem may occur if the SQL login for the user is no longer present.

See Resolution 8 in the “Resolution” section.

RESOLUTION

Resolution 1

To resolve this problem, verify the Data Source (ODBC) that you use for Microsoft Dynamics GP at the computer at which you receive the error message. To verify the Data Source (ODBC), click Start, click Administrative Tools, click Data Sources (ODBC), and then double-click the DSN connection. In the list that appears, you should see one of the following things:

• The name of the computer that is running Microsoft SQL Server
• The TCP/IP address of the computer that is running SQL Server. This address appears in the Servername box.

If you know neither the name of the computer that is running SQL Server nor the TCP/IP address of the computer that is running SQL Server, you can verify the Data Source (ODBC) on a working computer. For more information about an ODBC setup, click the following article number to view the article in the Microsoft Knowledge Base:

870416 How to set up an ODBC Data Source on SQL Server for Microsoft Dynamics GP

Resolution 2

The logon User ID field is now case-sensitive in Microsoft Dynamics GP. To resolve this problem, you must encrypt the user’s password by using the correct case for the user ID. To do this, follow these steps:

1. Log on to Microsoft Dynamics GP as the sa user.
2. Click Tools, point to Setup, point to System, and then click User if you use Microsoft Dynamics GP 9.0, or click Microsoft Dynamics GP, click Tools, point to Setup, point to System and then click User if you use Microsoft Dynamics GP 10.0 or Microsoft Dynamics GP 2010.
3. In the User ID box, click the lookup button, and then click the user who cannot log on.
4. In the Password box and in the Confirm Password box, type a new password.Note If you are using Microsoft SQL Server 2005 or Microsoft SQL Server 2008 and Windows Server 2003 or Windows Server 2008 and are in a Windows Server 2003 or Windows Server 2008 domain, you can click to select the Change Password at Next Login check box in the User Setup window. For this feature to work correctly, the client computers must use SQL Native Client for the DSN connections that they use for Microsoft Dynamics GP.

Resolution 3

To resolve this problem, obtain the latest service pack for Microsoft Dynamics GP. For more information, visit one of the following Microsoft Web sites, depending on whether you are a partner or a customer.

Partners

Customers

Resolution 4

To resolve this problem, follow these steps:

1. Log on as the sa user.
2. On the Tools menu, click Setup, click System, and then click User.
3. Click the user who is experiencing the problem.
4. Click to clear the following check boxes:

• Change Password Next Login
• Enforce Password Policy
• Enforce Password Expiration
5. In the Password box and then in the Confirm Password box, type the user’s password, and then click OK.
6. Have the user log on to see whether the problem still occurs.

For Microsoft Dynamics GP 10.0 or Microsoft Dynamics GP 2010, follow these steps:

1. Log on as the sa user.
2. On the Microsoft Dynamics GP menu, click Tools, point to Setup, point to System, and then click User.
3. Click the user who is experiencing the problem.
4. Click to clear the following check boxes:

• Change Password Next Login
• Enforce Password Policy
• Enforce Password Expiration
5. In the Password box and then in the Confirm Password box, type the user’s password, and then click OK.
6. Have the user log on to see whether the problem still occurs.

Resolution 5

To resolve this problem, follow these steps, depending on the version of SQL Server that you are using.

SQL Server 2005 or SQL Server 2008

1. Click Start, point to Programs, point to Microsoft SQL Server 2005 or to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. Log on as the sa user.
3. Under the server that is running SQL Server and Microsoft Dynamics GP, expand Databases, expand System Databases, expand Master, expand Programmability, and then expand Stored Procedures.
4. Right-click smDex_Max_Char, and then click Properties.
5. On the Permissions tab, click the ADD button under Users or roles.
6. In the “Select Users or Roles” window, click the Browse button.
7. Click to select the Public check box, and then click OK two times.
8. On the Permissions tab, verify that Public appears under Users or roles.
9. Click to select the Execute/Grant check box, and then click OK.

SQL Server 2000

1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
2. Log on as the sa user.
3. Under the server that is running SQL Server and Microsoft Dynamics GP, expand Databases, expand Master, and then expand Stored Procedures.
4. Right-click smDex_Max_Char, and then click Properties.
5. Click the Permissions button.
6. Click to select the EXEC check box next to the Public user, click Apply, and then click OK two times.

Resolution 6

To resolve this problem, take one of the following actions, depending on the version of Microsoft Dynamics GP that you are using.

Microsoft Dynamics GP 2010 or Microsoft Dynamics GP 10.0

Have the system administrator remove the mandatory profile.

Microsoft Dynamics GP 9.0

Obtain hotfix 947362 or the latest update. For more information, visit one of the following Microsoft Web sites, depending on whether you are a partner or a customer.

Partners

Customers

Resolution 7

To resolve this problem, obtain the Microsoft Small Business Financials 9.0 logon hotfix. For more information, visit one of the following Microsoft Web sites, depending on whether you are a partner or a customer.

Partners

Customers

Resolution 8

To resolve this problem, verify that the SQL login exists. If the SQL login does not exist, remove the user ID from the Microsoft Dynamics GP database, and then re-create the SQL login. To do this, follow these steps.

Step 1: Verify that the SQL login exists

To verify that the SQL login exists, follow these steps, depending on the version of SQL Server that you are using.

SQL Server 2008
1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. In the “Connect to Server” window, follow these steps:

a. In the Server name box, type name of the SQL Server.
b. In the Authentication box, click SQL Authentication.
c. In the Login box, type sa.
d. In the Password box, type the password for the sa user, and then click Connect.
3. Click New Query, and then paste the following script into the blank query window: 

SELECT name FROM master.sys.sql_logins

 
4. On the File menu, click Execute.
5. In the list of SQL logins, verify that the SQL login that is experiencing the problem exists.

SQL Server 2005
1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
2. In the “Connect to Server” window, follow these steps:

a. In the Server name box, type name of the SQL Server.
b. In the Authentication box, click SQL Authentication.
c. In the Login box, type sa.
d. In the Password box, type the password for the sa user, and then click Connect.
3. Click New Query, and then paste the following script into the blank query window: 

SELECT name FROM master.sys.sql_logins

 
4. On the File menu, click Execute.
5. In the list of SQL logins, verify that the SQL login that is experiencing the problem exists.

SQL Server 2000
1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
2. In the “Connect to SQL Server” window, follow these steps:

a. In the SQL Server box, type the name of the SQL Server.
b. In the Connect using area, click SQL Server Authentication.
c. In the Login name box, type sa.
d. In the Password box, type the password for the sa user, and then click OK.
3. Click New Query, and then paste the following script into the blank query window: 

SELECT name FROM master..syslogins

 
4. On the File menu, click Execute.
5. In the list of SQL logins, verify that the SQL login that is experiencing the problem exists.

Step 2: If the SQL login does not exist, remove the user ID from the Microsoft Dynamics GP database

To remove the user ID from the Microsoft Dynamics GP database, take one of the following actions, depending on the version of SQL Server that you are using.

SQL Server 2008 or SQL Server 2005

Run the following script against the DYNAMICS database and against all company databases:

DROP USER  '<XXX>'

Note In this script, the placeholder <XXX> represents the actual login ID of the user.

SQL Server 2000

Run the following script against the DYNAMICS database and against all company databases:

sp_dropuser  '<XXX>'

Note In this script, the placeholder <XXX> represents the actual login ID of the user.

Step 3: Re-create the SQL login and the user ID in the Microsoft Dynamics GP database

To re-create the SQL login and the user ID in the Microsoft Dynamics GP database, follow these steps:

1. Log on to Microsoft Dynamics GP as the sa user.
2. Open the User Setup window. To do this, take one of the following actions, depending on the version of Microsoft Dynamics GP that you are using.Microsoft Dynamics GP 2010 or Microsoft Dynamics GP 10.0

On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click User.

Microsoft Dynamics GP 9.0

On the Tools menu, point to Setup, point to System, and then click User.
3. In the User Setup window, follow these steps:

a. In the User ID box, click the Lookup button, and then click the user account.
b. If the SQL login for the user does not exist, you receive the following error message:

This user does not have a corresponding SQL Login. To create a SQL Login, enter a password and choose save.
c. Click OK.
d. Re-create the SQL login for the user. To do this, change the password in the Password box, and then click Save.
e. Close the User Setup window.
4. Open the User Access Setup window. To do this, take one of the following actions, depending on the version of Microsoft Dynamics GP that you are using.Microsoft Dynamics GP 2010 or Microsoft Dynamics GP 10.0

On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click User Access.

Microsoft Dynamics GP 9.0

On the Tools menu, point to Setup, point to System, and then click User Access.

5. Re-create the user ID for the company databases. To do this, select the user, click to clear the Access check box for all the companies that are selected, and then click to select the Access check box again for the companies to which you want the user to have access.
6. Click OK to close the User Access Setup window.