Error Generating Complex Return type

[MarkBrindle]

Hi,

I'm playing with the trial of linqConnect. I have imported my DB into Entity Developer V5.8.676. Everything looks great except for one Stored Proc.

This Stored Proc is working in MS LINQ to SQL for the past 3 years (the person who wrote the SP has left).

I'm getting these errors:

• 1 The property Source 'BillerCode' in the type 'MPayBpayBillerFileImportGetBillersForImportResult' has been used already. C:\Users\mark.brindle\Desktop\devant\MPayDataContext.lqml

• 2 The property Source 'PaymentMethod' in the type 'MPayBpayBillerFileImportGetBillersForImportResult' has been used already. C:\Users\mark.brindle\Desktop\devant\MPayDataContext.lqml

• 3 The property Source 'ActivateDate' in the type 'MPayBpayBillerFileImportGetBillersForImportResult' has been used already. C:\Users\mark.brindle\Desktop\devant\MPayDataContext.lqml

I guess I could change the Union to insert into a temp table, then return the tmp table. I would like to just leave the SP alone

Any help? Is the SP incorrect or are there options to resolve the generation?

Regards
Mark Brindle

Code: Select all

USE [mPayments]
GO
/****** Object:  StoredProcedure [dbo].[MPayBpayBillerFileImportGetBillersForImport]    Script Date: 8/09/2015 7:13:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MPayBpayBillerFileImportGetBillersForImport]
AS
  DECLARE @now int
  SELECT
    @now = CAST(CONVERT(varchar, GETDATE(), 112) AS int)

  DECLARE @DebitPayment varchar(10)
  SELECT
    @DebitPayment = '001'

  DECLARE @billers TABLE (
    BillerCode char(10),
    ActivateDate char(8),
    PaymentMethod char(3)
  )

  INSERT INTO @billers
    SELECT
      BillerCode,
      MAX(ActivateDate),
      MAX(PaymentMethod)
    FROM dbo.MPayBpayBillerFileImport
    WHERE CAST(ActivateDate AS int) <= @now
    AND PaymentMethod = @DebitPayment
    GROUP BY BillerCode

  DECLARE @nonDebitBillers TABLE (
    BillerCode char(10),
    ActivateDate char(8),
    PaymentMethod char(3)
  )

  INSERT INTO @nonDebitBillers
    SELECT
      BillerCode,
      MAX(ActivateDate),
      MAX(PaymentMethod)
    FROM dbo.MPayBpayBillerFileImport
    WHERE CAST(ActivateDate AS int) <= @now
    AND PaymentMethod != @DebitPayment
    AND BillerCode NOT IN (SELECT
      r.BillerCode
    FROM dbo.MPayBpayBillerFileImport r
    INNER JOIN @billers b
      ON b.BillerCode = r.BillerCode
      AND b.ActivateDate = r.ActivateDate
      AND r.PaymentMethod = @DebitPayment)
    GROUP BY BillerCode

  SELECT
    *
  FROM dbo.MPayBpayBillerFileImport r
  INNER JOIN @billers b
    ON b.BillerCode = r.BillerCode
    AND b.ActivateDate = r.ActivateDate
    AND r.PaymentMethod = b.PaymentMethod
  UNION
  SELECT
    *
  FROM dbo.MPayBpayBillerFileImport r
  INNER JOIN @nonDebitBillers b
    ON b.BillerCode = r.BillerCode
    AND b.ActivateDate = r.ActivateDate
    AND r.PaymentMethod = b.PaymentMethod
  ORDER BY b.PaymentMethod

[Shalex]

We cannot reproduce the problem in our environment. Please send us:
1) your *.lqml model
2) if possible, also give us the DDL script of the database objects your stored procedure talks to