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:
- The user is running SmartConnect using a GP destination to create Payables Transactions
- The map uses “GP Rolling Column” to get the next voucher number from the target GP company
- 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!)
- 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:
- At the top we see the RPC:Starting event to the call of the E1_SC_GetNextNumber
- 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
- 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.
EDIT – After looking at this again, we COULD find out what those objects are by asking SQL
select * from sysobjects where id in (1537492606,1697493176)
This would have shown me that it was indeed taGetPMNextVoucherNumber and the smGetMsgString procs.
- 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.
-
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
-
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