EONE BLOG

Tech Tuesday: Could not retrieve the next number in the sequence


I ran across something interesting the other day that I can’t say that I’ve heard of happening in about 15 years of experience. While I don’t know that we’ll see this again for another 15 years, the troubleshooting process was interesting, and I thought would be worthwhile to look at.

To set the stage:

  1. The user is running SmartConnect using a GP destination to create Payables Transactions
  2. The map uses “GP Rolling Column” to get the next voucher number from the target GP company
  3. The user claims that if they are logged into GP that we can get the next number successfully but if they log out of GP then SC cannot get the next voucher number (which was confirmed in the screenshare!)
  4. We also verified in GP that Payables Setup did have a valid “next number” for vouchers since GP works fine and the SmartConnect Next Number routine does run fine at times.

The error the map gets is:

Figure 1: Could not retrieve the next number in the sequence.

 

As an aside for those who might wonder about how the “GP Rolling Columns” work, in the eConnect business logic stored procedures that drives the SmartConnect GP destination, there are quite a few “next number” procs that Microsoft has created to do this.

For SmartConnect, development created an E1_SC_GetNextNumber stored procedure that should exist in each GP company that SmartConnect will call initially and then it calls into those eConnect procedures or else a few other E1_SC_GetNextXXXXXX procs that eOne has created to get other document “next numbers” that Microsoft didn’t create routines for.

I’ll also note that SmartConnect also has the ability to call user defined “next number” scripts as detailed in this KB if you need your own due to missing eConnect next number functionality or have a custom destination.

To be fair, the error “Could not retrieve the next number in the sequence.” isn’t all that unusual. There is a KB that talks about a few reasons you would get this error and the resolution to the issue.

But this wouldn’t seem to fit because the claim was that it works if logged into GP.

However, as we know the “next number” logic is all stored procedure based – what difference would logging into GP make?

Since this would seem to be a “SQL Related issue” the first step should be to run a SQL Profile Trace.

In the SQL Profile Trace, make sure that User Errors and Warnings is marked (it isn’t by default) because we need to see the errors if there are any. Besides TextData, useful columns to trace are LoginName, TextData, and ObjectID and ObjectType.

 

Figure 2: SQL Profile Trace results

If the screenshot is hard to see, the interesting things we see in the trace are:

  1. At the top we see the RPC:Starting event to the call of the E1_SC_GetNextNumber
  2. The eOne proc is not encrypted but something it is calling is encrypted. This would be the eConnect proc because it is encrypted as all eConnect procs are encrypted
  3. The first object that is encrypted is ObjectType – 8272 Procedure as expected. The first object id in my case was 1537492606. The 2nd was 1697493176. Since we know that the E1 proc calls the eConnect proc, the first one would have to be taGetPMNextVoucherNumber. Not sure what the 2nd proc is or why it is being called.
  4. We see the login was ‘sa’ so we can assume there isn’t a “rights” issue since sysadmin in SQL. We also didn’t get any SQL errors such as “proc not found” or “permission denied” so it isn’t a routine SQL object error and likely a “business logic” error.


From the SQL Profile trace, we saw that the call that SC is making is simply:

exec TWOR2.dbo.E1_SC_GetNextNumber 1,1,”

 

Running this statement manually in SSMS gives us:

 

Figure 3: Business logic failed somewhere

Reviewing the E1_SC_GetNextNumber script, the relevant portion is here:

 

–Payables

IF @TYPE = 1

BEGIN

IF @DOC_TYPE = 1 OR @DOC_TYPE = 5

BEGIN

DECLARE @O_iNTVCHNUM varchar(21)

SET @O_iNTVCHNUM =

EXECUTE taGetPMNextVoucherNumber

@O_iNTVCHNUM OUTPUT

,0

,@O_iErrorState OUTPUT

 

IF @O_iErrorState <> 0

BEGIN

SELECT ‘err’ as Number

END

ELSE

BEGIN

SELECT @O_iNTVCHNUM as Number

END

END

 

In this case, it appears that the only reason the E1 proc should return the word “err” back is if the taGetPMNextVoucherNumber proc was failing with an error – @O_IErrorState <> 0.

We should find out what that error is.

While we can’t look at the taGetPMNextVoucherNumber proc itself since it is encrypted, we can use SSMS to execute it for us by Right Click on it in SSMS and choose Execute Stored Procedure.

Using our E1_SC_GetNextNumber proc above as a guide to how it is called, we see the only thing we need to pass is the 2nd param to be 0. The other two are out parameters.

After letting SSMS create the script execution for me (and then adding the last query on taErrorCode once I saw there was an eConnect error number returned).

Figure 4: Error 9569 returned from eConnect proc

 

Now THAT is interesting – this implies that the taGetPMNextVoucherNumber is calling the smGetMsgString stored procedure (DYNAMICS db) and failing. This would explain the mysterious second procedure being called in our SQL Profile Trace.

Fortunately, the smGetMsgString stored procedure is actually part of the GP procs and not eConnect so it isn’t encrypted. After opening it in GP and then formatting it a bit to make it readable, we can quickly see our issue. And then also why “it works if they are logged into GP but fails if not”.

 

SELECT @O_iErrorState = 0

 

SELECT @sLanguageID = ACT.Language_ID

FROM dbo.ACTIVITY ACT,

dbo.SY01500 CMP WITH (NOLOCK)

WHERE ACT.USERID = SYSTEM_USER

AND ACT.CMPNYNAM = CMP.CMPNYNAM

AND CMP.INTERID = @I_cDBName

 

IF @sLanguageID IS NULL

SELECT @sLanguageID = 0

 

SELECT @O_vMessage = rtrim(SQL_MSG)

FROM MESSAGES WITH (NOLOCK)

WHERE MSGNUM = @I_iMessageNumber

AND Language_ID = @sLanguageID

 

IF @@ROWCOUNT <> 1

SELECT @O_iErrorState = 20062

ELSE

BEGIN

 

 

After the initial validation (not shown above), the first thing the proc does is check to see if the current user (ie SYSTEM_USER) is logged into the GP ACTIVITY table.

If so, the Language_ID from that login is pulled. But if they aren’t logged in then the @sLanguageID is initialized to 0 (English). Finally a SQL_MSG value is pulled from the DYNAMICS..MESSAGES table using that LanguageID value for a specific message number (that we are unsure of).

Now that we are thinking “language”, we note that this customer IS in Canada using Canada-English GP clients.

Now that we know this, let’s look at the MESSAGE table in SQL.

Figure 5:And that explains it, no records with Language_ID of 0 because I don’t have an English Client

 

In my example above, I’m using a Canada-French language (Language_ID of 28) where the customer was Language_ID of 2 – but either way this procedure worked when logged in because this smGetMsgString found the GP ACTIVITY login record and then used Language_ID of 2 (or 28 in my case).

But if the ‘sa’ user was logged out; the ACTIVITY record wasn’t found and so 0 was used by the proc. This would fail since there are no records with Language_ID of 0 – that failure then rolls back up into SmartConnect.

Once we determined the real issue, we have a couple of easy workarounds – choose one.

  1. Hand modify the smGetMsgString to default the LanguageID to 2 (28 for me) instead of 0.

    IF @sLanguageID IS NULL

SELECT @sLanguageID = 28


This one is risky because if you load a service pack or reload stored procedures, GP will just overwrite your changes and re-break your integration

  1. Copy the MESSAGE records that are already there to the Language_ID of 0

    insert MESSAGES (Language_ID, MSGNUM, SQL_MSG) select 0, MSGNUM, SQL_MSG from MESSAGES


    To me, this is a better approach since GP won’t overwrite or change those records.

After creating the Language_ID = 0 records, retesting the taGetPMNextVoucherNumber in SQL shows it working correctly and then retrying our Payables Map, it now runs successfully.

 

Figure 6:Success!

 

Patrick Roth
eOne Solutions

 

 

 

 

 

Leave a Comment




RECENT POSTS


Tech Tuesday: Useful Javascript Commands
SmartConnect Office Hours Recap for Feb. 15th, 2019
A Customer Story: Extender Saves 140 Hours a Week
SmartConnect Sales Journal Template for Business Central
Join Our Q1-2019 Partner Call! Important Changes Coming!

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
New Releases of Extender and SmartList Builder
2017 SmartConnect Integration Bootcamps

CATEGORIES

ARCHIVES

open all | close all

TAGS

CRM Demo Dynamics Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners release SalesForce SalesForce.com scripting SmartConnect SmartList SmartList Builder SmartList Designer SmartPost SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.