Tech Tuesday: Unable to retrieve the next number in the sequence
1. The first thing to check is that you have a SQL sysadmin setup in the GP connector. When you run the system maintenance, it needs to create stored procedures in each company database, and the only way that will happen is if you have a sysadmin setup in the GP connector setup. To check this, open SmartConnect, go to the “Setup” tab and press the “Setup” button. In the SmartConnect Setup window highlight the “SmartConnect Dynamics GP Connector” and press the “Connector Setup” button at the button of the window.
In the window that opens, make sure the “Use Credentials” checkbox is checked, and that you have a SQL sysadmin setup in the SQL user window. We typically recommend using the SQL “sa” user, however you can put any SQL user in there that has sysadmin rights in SQL. Even if they don’t want to leave the “sa” user in there, you need to put a sysadmin in there while you run the system maintenance. After the maintenance is run, and all the procedures are created, you can put any user in there that has the DYNGRP role in each company database, and it will still be able to get the next document numbers.
If you leave the “Use Credentials” checkbox unchecked, then SmartConnect will attempt to use the Windows credentials of the user running the map to connect to SQL. This would require that you add your Windows accounts to SQL and make them members of the DYNGRP for all databases. Obviously this would present a security risk, so we recommend always having the “Use Credentials” checkbox checked with a SQL login that is a member of the DYNGRP for all the company databases.
2. The second thing that can cause the issue is the system maintenance failing before it completes. One reason can be excessive amounts of records in the SmartConnect logging tables. As part of the system maintenance, it deletes log tables older than 30 number of days. Normally what will happen is that you will see a message that says, “Old Event Log data removed…” and then it never progresses past that and displays “Maintenance Complete”. To resolve this issue, you can run a SQL script to delete the event logs older than 30 data. Download the script named “Delete Logs Older than 30 days.sql“, and run it against the SmartConnect database in SQL management studio. After running the script, then attempt to run the system maintenance again. If you see the system maintenance saying “Updating Microsoft Dynamics GP Database…” then it has moved past the “delete logs” portion of the script.
The above steps will typically solve any issues that cause SmartConnect to be unable to get the next available document numbers.
Integrate & Automate without Any Code.
SmartList Data has Never Been Faster.
The Easiest Way to Report on GP Data.
Leave a Comment