EDI SQL error when changing SSMS servers

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:

 

  • 0
  • 0
  • 17
  • 0
  • 0