Category Archives: MS Tools and Reports

MS Dynamics GP printing problem – cannot print multiple copies

Posted on by
Reply

You have just upgraded from a previouse version of MS Windows and now have Windows 7 32 bit 0r 64 bit operating system and can’t print multiple copies in the windows print dialog box.  Also the Quick Print setup in SOP does not work and will not print multiple copies.

This is a windows driver issue.

First, disable the Mopier setting in the printer properties.  Right click on the printer, go to Printer Properties, Device Settings, scroll down until you see Mopier, click disable and Apply and OK.

For an HP printer this should solve the problem.

If you continue to have problems, unplug the printer from the computer, totally uninstall the printer and driver from windows. Restart the computer, then plug the printer in and reinstall. Also make sure you have downloaded to most recent software and drivers for windows 7.

Crystal Reports help and service packs.

Posted on by
Reply

Crystal Reports is a great program for adding reports for MS Dynamics GP.  MS is trying to push everyone to SRS.  For those of you still using Crystal and not following the heard over the cliff here is a link to the page for downloading Crystal service packs.

http://resources.businessobjects.com/support/additional_downloads/service_packs/crystal_reports_en.asp

 

MS Dynamics GP sample ADO script to pass a date parameter to MS SQL stored procedure.

Posted on by
Reply

Here is sample ADO code to connect and pass a date to an MS SQL stored procedure from Excel and MS Dynamics Integration Manager.

Excel Macro code example:

Sub PerDiem()

‘ Create a connection object.
Set cn = CreateObject(“ADODB.Connection”)
cn.Open “Provider=’SQLOLEDB’;Data Source=’YourSQLServerName’;Initial Catalog=’YourSqlDBName’;” & _ “Integrated Security=’SSPI’”
‘ declare other variables
Dim strDate As String
Dim advarChar As Integer
Dim adParamInput As Integer
Dim i As Integer

‘use this method to read cells from the active excel sheet
‘this reads cell in row 1 column 1 to get the date, example 5/07/2011
‘in this example using character data for the date key in the slashes
i = 1
strDate = ActiveWorkbook.Sheets(“YourExcelSheetName”).Cells(i, 1).Value

‘build the ADO command object, set the parameters, execute the command object
‘in this case the date is passed to the stored procedure
‘make sure to include the @ in the stored procedure parameter name
Set Cmd_obj = CreateObject(“ADODB.Command”)
Cmd_obj.ActiveConnection = cn
Cmd_obj.CommandText = “YourSqlStoredProcName”
Cmd_obj.CommandType = 4
advarChar = 129
adParamInput = 1
Cmd_obj.Parameters.Append Cmd_obj.CreateParameter(“@YourSqlStoredProcExpectedParameterName”, advarChar, adParamInput, 10, strDate)
Cmd_obj.Execute

MsgBox “Stored Proc Call Completed”
‘close the open record set
cn.Close: Set cn = Nothing
End Sub

 

VBA script attached to MS Dynamcis Integration Manager example:

This example integration uses the econnect adpater to connect to project acccounting.
The script is attached to the Before Integration script button.
The code prompts for the date in character format, example 5/07/2011.
As you can see the ADO command object syntax is almost identical.

Dim strDate, text
Dim advarChar
Dim adParamInput
Dim objConnection, Cmd_obj
strDate = InputBox (“Enter Week Ending Date “)
Set objConnection = Nothing
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.ConnectionString = “Provider=MSDASQL;DSN=YourODBCDSNnameforSQLDB;UID=sa;PWD=Yoursapassword;”
objConnection.Open
Set Cmd_obj = CreateObject(“ADODB.Command”)
Cmd_obj.ActiveConnection = objConnection
Cmd_obj.CommandText = “YourSQLStoredProcName”
Cmd_obj.CommandType = 4
advarChar = 129
adParamInput = 1
Cmd_obj.Parameters.Append Cmd_obj.CreateParameter(“@YourSQLStorProcExpectedParameter”, advarChar, adParamInput, 10, strDate)
Cmd_obj.Execute

Set Cmd_obj = Nothing
Set objConnection = Nothing

MS Dynamics GP how to move buttons on the tool bar, GP button all the way to the right of the screen.

Posted on by
Reply

Right click in the blue area of the toolbar and click on Lock Toolbars to unlock it.  The check box should be off.

Now position the pointer to the left of the button you want to move on the vertical line.  You should have the four arrow cursor now, click and grab the line.  You can now drag it right or down, etc.

Position the buttons as you desire.

When finished right click in the blue area again and click Lock Toolbars.

MS Dynamics GP cannot login as sa – problem solved.

Posted on by
Reply

Well I’m still working on this problem and I will update this once we get some resolution.

Thanks to the MS Dynamics blog at http://community.dynamics.com/product/gp/f/32/p/37302/66019.aspx they have a good idea to go into SQL Studio Manager, click the plus sign by Security, click the plus sign by Logins.  Right click on Dynsa and go to Properties, blank out both passwords and OK to save.

When you launch GP again you will get a message that “Blank passwords are not allowed.  Do you want to change your password now?”  Set the password and log into GP.  You can now set the passwords as required for other users.

By the way you can launch GP Utilities and login as dynsa.  However the only thing you can do in Utilities as dynsa is Update Modified Forms and Reports.  You can’t create a new company, etc. This is not ideal but I guess it’s better than nothing.

sa cannot login at the server problem resolution:

This was a huge problem.  Don’t bother simply testing the ODBC connection setup under SYSWOW64.  Go into the SYSWOW64 directory under Windows.  Run the ODBC32.EXE, delete the current ODBC connection.  Evidently the ODBC connection can become corrupted.  Simply testing it will send you off on a multiple day wild goose chase.

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.

MS Dynamics GP close encounters of the weird kind round 2 tax update

Posted on by
Reply

If you are on version 9, 10, or 2010 of MS Dynamics GP and running payroll you must load the round 2 tax update.

However be prepared for a lot of weird problems.

First, make sure to backup your reports.dic and it is a good idea to export those reports to a package file.  After installing the round 2 tax update it is possible you won’t have to upgrade modified forms and reports using utilities.  However on some machines the reports dictionary will become corrupted.  You will need to delete the reports.dic and import the reports from you package file.  If you get “smart heap” memory errors followed by “MS Dynamics GP needs to close sorry for the inconveience” the first thing to do is delete the reports dictionary and reimport.

Why would a tax update wipe out all user stored procedures?  Good old MS, just wipe stuff out that doesn’t belong to them.  But that’s what happens.  In MS SQL database you had best back up your stored procedures.  You should also backup any customizations.

Also, be aware that if you put on the tax update you might also have to load the Integration Manager service packs and eConnect service packs.

MS Dynamics GP can’t reprint posting journals, selections are blank in reprint journals.

Posted on by
Reply

You’ve posted a SOP batch and something locks up and you don’t get the posting reports.  You’ve posted a batch of payables and the printer crashes and you lose the posting reports.  You want to reprint the posting journals in MS Dynamics GP.  You go to Reports – Sales – Posting Journals, you do the lookup for the Audit Trial code to reprint and there’s nothing there.  Oops! You find out you can’t reprint any posting journals from any module.

You check your setup options for each module and see that you are keeping transaction and distribution history.

What’s up?

To reprint posting journals in MS Dynamics GP you must be keeping history, and you must turn on each functional report or origin as it is called under Tools – Setup – Posting – Audit Trail Codes.

You can Mark All for each series or click individual origins for each series.  It’s probably best to analyze what posting journals you really need and check individual boxes.

MS Dynamics GP Integration Manager sample script to create a unique customer number

Posted on by
Reply

When importing customers and orders from a text file how do you create a unique customer number?

Here is a sample script that can be used to create a unique customer number.  The script takes the first six characters from the last name, the first four characters from the first name, and the last four or the credit card number.  Blanks in the last name and first name are excluded and the script moves on to the next section.

‘Build Customer ID

sLast = “”

sFirst = “”

sSuffix = “”

sLastName = SourceFields(“Customers2.Name_On_Card”)

sCharacter = Left(sLastName,1)

sResult = “”

sCount = 1

‘Build Customer ID, adding last name

Do While sCount < 7

sResult = Left(sLastName, sCount)

sCount = sCount + 1

sCharacter = Left(sLastName, sCount)

sCharacter = Right(sCharacter, 1)

If sCharacter = ” ” then sCount = sCount + 99

Loop

sLast = UCase(sResult)

sCount = 1

sCharacter = Left(sLastName,1)

Do While sCharacter <> ” ”

sCharacter = Left(sLastName, sCount)

sCharacter = Right(sCharacter, 1)

sCount = sCount + 1

Loop

sCount = sCount + 3

sResult = Left(sLastName, sCount)

sResult = Right(sResult, 4)

sCharacter = Left(sResult, 1)

If sCharacter = ” ” then

sResult = Right(sResult, 3)

Else

End If

sEnd = 1

Do While sEnd < 5

sCharacter = Left(sResult, sEnd)

sCharacter = Right(sCharacter, 1)

If sCharacter = ” ” then

sResult = Left(sResult, sEnd)

sEnd = sEnd + 99

End If

sEnd = sEnd + 1

Loop

sFirst = UCase(sResult)

‘sFirstName = SourceFields(“Customers2.First_Name”)

‘sCharacter = Left(sFirstName, 1)

‘sResult = “”

‘sCount = 1

‘Build Customer ID, adding Firstname

‘Do While sCount < 5

‘ sResult = Left(sFirstName,sCount)

‘ sCount = sCount + 1

‘ sCharacter = Left(sFirstName, sCount)

‘ sCharacter = Right(sCharacter, 1)

‘ If sCharacter = ” ” then sCount = sCount + 99

‘Loop

‘sFirst = UCase(sResult)

sNumber = SourceFields(“Customers2.Card_Number”)

sResult = “”

Do While Len(sNumber)>0

‘Isolate the leftmost character of the sNumberstring

sCharacter = Left(sNumber,1)

‘Test to determine whether that character is numeric

‘If it is, then add it to the sResult string

If IsNumeric(sCharacter) then

sResult = sResult & sCharacter

End If

‘Remove the leftmost character from sNumber and repeat until

‘the entire sNumber string has been handled.

sNumber = Right(sNumber, Len(sNumber)-1)

Loop

sSuffix = Right(sResult, 4)

CurrentField = UCase(sLast & sFirst & sSuffix)