SmartConnect makes use of stored procedures in each company database to retrieve default document numbers from GP. If these stored procedures are not present, you will receive errors saying, “Could not retrieve the next number in the sequence.” The stored procedures get created when you run the system maintenance in SmartConnect. Normally the solution to the issue is to run the SmartConnect system maintenance by going to the “Setup” tab, in SmartConnect and pressing the “System Maintenance” button.  However, there are cases where this does not resolve the issue, and typically the problem is that the system maintenance is failing before it creates the procedures in each company database. Here are the things to check if running the system maintenance is not creating the stored procedures correctly.

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. 

3.  The third reason that the E1_SC_Get_Next_Number proc can fail is that you have run out of document numbers. So as an example, you are attempting to get the next document number for Receivables Invoices, and you get an error that it’s unable to get the next document number. Open the transaction entry window for whatever module you are attempting to import into and attempt to manually enter the same transaction type and see if you can manually enter the number. If the next document number is set to something like “SALES999999” you have run out of document numbers. You would need to go to the setup window for that module and change the next document number to allow it to increase. If the current next document number is “SALES999999”, you would want to enter a leading 0 in the number and set it to “SALES0999999” to provide additional numbers for the specific document type you are attempting to import. 

 

4.  If you have companies in the SY01500 table that do not have a corresponding SQL database, the SmartConnect system maintenance will fail, but still display “Maintenance Complete” at the end of it. In order to resolve this, you need to run the Microsoft script named “ClearCompanies.SQL” which you can download from this link.  This script will remove any reference to companies in the SY01500 that no longer exist in SQL. After removing missing companies from the SY01500, the system maintenance will complete successfully.

 

5.  The last thing that can cause the procedures to not run correctly is if the DYNGRP does not have permissions to execute the stored procedures. To resolve this, you want to run the Microsoft script named “GRANT.SQL” against each company database where you are encountering the issue. The script is normally located in the C:Program Files (x86)Microsoft DynamicsGP 20XXSQLUtil folder. You would want to run this against each company database where you are unable to retrieve the next document number.

The above steps will typically solve any issues that cause SmartConnect to be unable to get the next available document numbers.