August 23, 2018 at 11:09 am
Hi All,
I have the Stored procedure below which iterates through a table to decrypt RSA encrypted email addresses.
When run in SSMS it runs perfect however when run in SSIS i get the error message:
Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
any ideas?ALTER PROC Data.DecryptRSA
AS
DECLARE @i INT
SET @i = (
SELECT count(*)
FROM Data.RSAtoDecrypt
WHERE DecryptedEmail IS NULL
)
SELECT @i
WHILE @i >= 0
BEGIN
DECLARE @PrivateKeyXML NVARCHAR(MAX);
BEGIN
--Get the XML Key
SET @PrivateKeyXML = (
SELECT [PrivateKeyXML]
FROM [dbo].[RSAKeys]
WHERE [KeyName] = 'Key1'
);
--Call the C# Fcuntion passign in the Encoded/Encrytped String and the XML Key
SELECT [dbo].[RSADecrypt]((
SELECT Emailaddress
FROM Data.RSAtoDecrypt
WHERE rn = 1
), @PrivateKeyXML) DecryptedValue
INTO #A
END;
--Update Decrypted Value where RN = 1
UPDATE Data.RSAtoDecrypt
SET DecryptedEmail = DecryptedValue
FROM #a
JOIN Data.RSAtoDecrypt ON RN = 1
DROP TABLE #A;
UPDATE Data.RSAtoDecrypt
SET RN = NULL;
--Recalculate RN ignoring decrypted emails
UPDATE Data.RSAtoDecrypt
SET RN = B.RN
FROM Data.RSAtoDecrypt A
JOIN (
SELECT ID
,CookieID
,Emailaddress
,decryptedemail
,ROW_NUMBER() OVER (
PARTITION BY NULL ORDER BY ID
) RN
FROM Data.RSAtoDecrypt
WHERE decryptedemail IS NULL
) B ON a.ID = b.ID
--iterate
SET @i = @i - 1
END
August 24, 2018 at 8:56 am
It would appear you have data you are not expecting..The part of your code that has WHERE rn = 1, must be bringing back more than 1 row. There doesn't seem to be any part of your code that sets RN to 1, and there is an update to set all rows in the table to have RN be NULL, so I'm not clear from your code, exactly how you are choosing the one row you want to decrypt.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 24, 2018 at 12:46 pm
I'm also not sure why you are using a WHILE loop in the first place. You should be able to do this without any loops.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 26, 2018 at 12:00 pm
drew.allen - Friday, August 24, 2018 12:46 PMI'm also not sure why you are using a WHILE loop in the first place. You should be able to do this without any loops.Drew
Thanks guys.
Posted at the end of a very long day and realised i could have just done this as a simple update.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply