I am experiencing a problem with an EDI process that uses a SQL Batch Service to connect to our DEV environment. However, when we point the EDI service to our TEST server, it errors out.
I have checked every single property on our TEST and DEV servers as well as the properties on the associated tables. They are identical. Nothing has changed in the SQL either.
The EDI is a PUBSUB that generates X12 834s for various vendors. The SQL Batch Service is running a modified code that executes a stored procedure. The stored procedure executes two different sets of code, one to extract the 834 Header data for each vendor, and a second to extract member level details. This is done by passing a parameter variable. When the EDI is pointed at the TEST SQL db, it is able to execute the code for the member level detail (@ParamMode = 11), however it errors for the Header data extract (@ParamMode = 10).
The big question I have is this: Is there a setting within Ensemble that we can check to make sure it is treating each of our servers the same?
Here is the stored procedure code, and below that is the Ensemble error message we are getting:
CREATE
PROCEDURE [dbo].[834Automation_Master] (@GroupList VARCHAR(1000), @ParamMode int )
AS
BEGIN
SET NOCOUNT ON;
DECLARE @date DATETIME = GETDATE()
DECLARE @VendorNum CHAR(15)
SET @VendorNum = (SELECT item from fnSplit(@GroupList, ','))
/***************************
EXTRACT DETAIL DATA
***************************/
IF @ParamMode = 11
BEGIN
exec dbo.FH_834Auto_MemberLevelDetail @VendorNum
SELECT *
FROM tmpX834FinalMemberLevel
ORDER BY elgemp_eenmbr ,elgdep_SeqCntr
END
/***************************
EXTRACT HEADER DATA
***************************/
IF @ParamMode = 10
BEGIN
SELECT *
FROM (SELECT
ltrim(rtrim(sl.SegID)) +
(CASE
WHEN sl.SegID LIKE 'ISA%'
THEN 'Interchange'
WHEN sl.SegID LIKE 'GS%'
THEN 'Group'
ELSE ltrim(rtrim(sl.LoopID)) END + Cast(sl.SegLoopKey AS VARCHAR)) AS 'SegLoop'
,vs.SegLoopChar
,DATEDIFF(ss,DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0), @date) AS 'ISA13' --Interchange control number, uses date diff in seconds
,RIGHT(CAST(CONCAT('000',(CONCAT(RIGHT(CAST(CONCAT('00',DATEPART(yy,@date)) AS VARCHAR),2)
,RIGHT(CAST(CONCAT('00',DATEPART(mm,@date)) AS VARCHAR),2)
,RIGHT(CAST(CONCAT('00',DATEPART(dd,@date)) AS VARCHAR),2)))) AS VARCHAR),6) AS 'ISA09' --Interchange date in YYMMDD format
,RIGHT(CAST(CONCAT('000',(CONCAT(RIGHT(CAST(CONCAT('00',DATEPART(hh,@date)) AS VARCHAR),2)
,RIGHT(CAST(CONCAT('00',DATEPART(mi,@date)) AS VARCHAR),2)))) AS VARCHAR),4) AS 'ISA10' --Interchange time in HHMM format
,RIGHT(CAST(CONCAT('000',(CONCAT(RIGHT(CAST(CONCAT('00',DATEPART(yyyy,@date)) AS VARCHAR),4)
,RIGHT(CAST(CONCAT('00',DATEPART(mm,@date)) AS VARCHAR),2)
,RIGHT(CAST(CONCAT('00',DATEPART(dd,@date)) AS VARCHAR),2)))) AS VARCHAR),8) AS 'GS04' --Group header date in CCYYMMDD format
,RIGHT(CAST(CONCAT('000',(CONCAT(RIGHT(CAST(CONCAT('00',DATEPART(hh,@date)) AS VARCHAR),2)
,RIGHT(CAST(CONCAT('00',DATEPART(mi,@date)) AS VARCHAR),2)
,RIGHT(CAST(CONCAT('00',DATEPART(ss,@date)) AS VARCHAR),2)))) AS VARCHAR),6) AS 'GS05' --Group header time in HHMM format
FROM OD_QLCOPY_DEV_01.dbo.VendorSegmentsX834 vs
JOIN OD_QLCOPY_DEV_01.dbo.SegLoopX834 sl
ON vs.SegLoopKey = sl.SegLoopKey
WHERE vs.VendorNum = @VendorNum) AS sourcetable
PIVOT(MAX(SegLoopChar)
FOR SegLoop IN ([ISA05Interchange1],
[ISA06Interchange2],
[ISA07Interchange3],
[ISA08Interchange4],
[GS02Group5],
[GS03Group6],
[REF027],
[N1021000A8],
[N1031000A9],
[N1041000A10],
[N1021000B11],
[N1031000B12],
[N1041000B13],
[N1011000C14],
[N1021000C15],
[N1031000C16],
[N1041000C17],
[INS04200018],
[INS04200019],
[REF02200020],
[NM1082100A21] )) AS pivottable
;
END
END
The EDI error:
