I've seen this behavior as well. Mine was due to server memory, which I'll explain in the guidelines. This was pesky because it was intermittent.
With partial cache, the cache starts off empty then queries until it finds a match. If you have multiple matches, the first one wins. With full cache, if you have multiple matches, I'm not sure which would win. Probably the first in the cache order.
Partial cache has an option for miss cache, which will remember which records don't have matches and won't query them again. This will be a problem if you are inserting into the table that you are doing a lookup on. Also with full cache, if your source contains duplicates, the second won't get a match after the first is inserted which will be a problem if you want to suppress all but the first.
Here are a few guidelines that I try to follow when using lookups:
- The lookup will never swap cache to disk. If it runs out of memory, the task will fail. If this is your issue, get more ram or try partial.
- Use integers whenever possible. If the field is string but can be cast to int, do it. This remove case and white space issues all together
- Trim strings. White space will give you fits.
- If it is a string, upper case both sides before comparing (lookups are case sensitive and will fail if the cases don't match).
- Null never equals Null. If the column is nullable, replace both sides with "UNKNOWN" if string and a irrational number like -999 if it is integer.
- If the column is empty, replace both sides with the text "EMPTY"
These are a pain in the butt, but it's better than getting a call at 3am because a tier 1 customers ETL failed.
The error in the EXEC
part of the INSERT-EXEC
statement is leaving your transaction in a doomed state.
If you PRINT
out XACT_STATE()
in the CATCH
block it is set to -1
.
Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT
succeeds.
ALTER PROCEDURE test -- or create
AS
BEGIN try
DECLARE @retval INT;
DECLARE @t TABLE(x INT CHECK (x = 0))
INSERT INTO @t
VALUES (1)
SET @retval = 0;
SELECT @retval;
RETURN( @retval );
END try
BEGIN catch
PRINT XACT_STATE()
PRINT ERROR_MESSAGE();
SET @retval = -1;
SELECT @retval;
RETURN( @retval );
END catch;
Adding this to the CATCH
block
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
Doesn't help. It gives the error
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC
anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.
DECLARE @RC INT;
EXEC sp_executesql
N'EXEC @RC = test',
N'@RC INT OUTPUT',
@RC = @RC OUTPUT;
INSERT INTO @t
VALUES (@RC)
Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.
DECLARE @RetVal INT = -1
IF OBJECT_ID('PrintMax', 'P') IS NULL
BEGIN
EXEC('create procedure PrintMax as begin print ''hello world'' end;')
SET @RetVal = 0
END
SELECT @RetVal;
RETURN( @RetVal );
Best Answer
I would suggest trying to put the stored procedure logic into a view or table-valued function, which could then be referenced in the lookup.
If that can't be done, then you can use the ugly workaround you already discovered: