An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

simpledeveloper

Hi, I am getting the following error when I am trying to run a SQL Query with larger Data, any help how can I run the select statement.

SELECT
CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A'
WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C'
WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R'
WHEN PTT.POSTransactionTypeCode = 'I' THEN 'C'
END AS TransactionType,
CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A'
WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A'
WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A'
WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I'
END AS ProviderStatus,
-- right format. uncomment when the PIN numbers are fixed.
'00000' + C.ProviderNbr + SPACE(1) AS ContractNbr,
-- right format. uncomment when sending the provider numbers that were added wrongly.
--'000000' + C.ContractNbr AS ContractNbr,
'01' AS Other,
'000000' AS PIN,
UPPER(LEFT(C.ProviderName, 28)) AS ContractName,
'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
UPPER(LEFT(A.StreetAddress1, 24)) AS Address1,
UPPER(LEFT(A.StreetAddress2, 24)) AS Address2,
UPPER(LEFT(A.City, 17)) AS City,
UPPER(S.PK_Geographic_Location_Code) AS State,
LEFT(A.Zip1, 9) AS ZipCd,
SPACE(12) AS ErrorCode2,
SPACE(8) AS Filler
FROM
POSPINTransaction AS E INNER JOIN
Provider AS C ON E.FKProviderId = C.PKProviderId INNER JOIN
ProviderDate AS PD ON dbo.Udf_GetPKProviderDate(C.PKProviderId) = PD.PKContractDate INNER JOIN
ProviderAddress AS PA ON PA.FKProviderId=C.PKProviderId INNER JOIN
[Address] AS A ON A.PKAddressId = PA.FKAddressId INNER JOIN
--ParentTypeLKP AS PT ON A.FKParentType = PT.PKParentType AND PT.ParentTypeCd = 'usrContract' INNER JOIN
[State] AS S ON A.FK_State = S.PKStateId INNER JOIN
POSTransactionTypeLKP PTT ON E.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE
(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R'))) OR
(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I')) AND (PD.EffectiveDateTo IS NULL) OR
(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (PD.EffectiveDateTo <= GETDATE())
UNION
SELECT
CAS

Lost User

System.OutOfMemoryException just means that the code is using too much space. The only way to fix it is to reduce the amount of data that you are trying to process in one go.

ZurdoDev

You need to cut back how many records and or fields you are returning.

Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.