Prev: Query and Extract
Next: Invalid object name when I try to create a scaler udf in a stored procedure.
From: Simon Eng Simon on 14 Jun 2010 16:12
Hello all,

I'm getting an error 208 (Invalid Object) when I try to call a stored
procedure using a trigger, but only if the code hits the CATCH block. If I
run the same sp with the same parameters from a query window, the code hits
the CATCH block but it executes fine. Any ideas here? Does the temp table
somehow go out of context or get dumped?

The 208 error occurs in the CATCH block at the "UPDATE
#usp_mp_transactions... " line. Again, if I run this in a query window, I get
an error 2627 which is caught and corrected in the CATCH block and life goes
on.


<code>

/* Rewind the cAccounts Cursor and start posting */
OPEN cAccounts

FETCH NEXT FROM cAccounts INTO @t_account

WHILE (@@fetch_status = 0)

BEGIN -- Begin Loop 2 (L2)

BEGIN TRANSACTION

BEGIN TRY
SET @i = 1
SET @retry = 1

SET @t_post_time = getdate() -- somehow this is the correct
time AS but wrong date?
SET @t_LastUpdated = @t_post_time -- see SET @t_post_time
above.

WHILE @retry=1 AND @j <= @maxretries

BEGIN -- Begin Inside Loop 2-1 (L2-1)

SET @retry=0

INSERT INTO [dbo].[transactions]
([property],[account],[item_no],[post_date],[tran_code],
[notes],[clk],[payer],[charge_property],[shift],[from_acct],
[tax1_code],[tax1_amt],[tax1_basis],[tax1_tax_basis],[tax1_tax_amt],
[tax2_code],[tax2_amt],[tax2_basis],[tax2_tax_basis],[tax2_tax_amt],
[tax3_code],[tax3_amt],[tax3_basis],[tax3_tax_basis],[tax3_tax_amt],
[tax4_code],[tax4_amt],[tax4_basis],[tax4_tax_basis],[tax4_tax_amt],

[post_time],[mark_property],[amount],
[exchange],[tax_type],[orig_date],[LastUpdated],[LastUpdatedBy]
)
SELECT
[property],[account],[item_no],[post_date],[tran_code],
[notes],[clk],[payer],[charge_property],[shift],[from_acct],
[tax1_code],[tax1_amt],[tax1_basis],[tax1_tax_basis],[tax1_tax_amt],
[tax2_code],[tax2_amt],[tax2_basis],[tax2_tax_basis],[tax2_tax_amt],
[tax3_code],[tax3_amt],[tax3_basis],[tax3_tax_basis],[tax3_tax_amt],
[tax4_code],[tax4_amt],[tax4_basis],[tax4_tax_basis],[tax4_tax_amt],

@t_post_time,[mark_property],[amount],
[exchange],[tax_type],[orig_date],@t_LastUpdated,[LastUpdatedBy]
FROM #usp_mp_transactions
WHERE account=(a)t_account and property=(a)t_property

IF @t_debug>0
BEGIN
SET @t_trancount = ( SELECT count(item_no)
FROM
#usp_mp_transactions
WHERE
account=(a)t_account and property=(a)t_property )

PRINT 'DEBUG: usp_post_mealplans
attempting to post ' + CAST(@t_trancount AS VARCHAR(6))
+ ' transaction(s) to
account: ' + @t_account

SET @t_trancount = (SELECT count(item_no)
FROM
#usp_mp_transactions
WHERE
from_acct=(a)t_account and account<>from_acct and property=(a)t_property )

PRINT 'DEBUG: usp_post_mealplans
attempting to post ' + CAST(@t_trancount AS VARCHAR(6))
+ ' autotransferred
transaction(s) for account: ' + @t_account
END

INSERT INTO [dbo].[u_mp_posted]

([property],[account],[post_date],[mealplan],[amount],[attempt])
VALUES (
@t_property,@t_account,@t_post_date,'MP',0,1)

IF @t_debug>0 PRINT 'DEBUG: usp_post_mealplans posted to account: ' +
@t_account;


END -- End Inside Loop 2-1 (L2-1)

END TRY

BEGIN CATCH

IF ERROR_NUMBER() = 1222
BEGIN
-- There's a lock. Give up.
IF @t_debug>0 PRINT 'DEBUG: usp_post_mealplans: Lock Timeout detected.';
IF XACT_STATE() <> 0 ROLLBACK
END
ELSE IF ERROR_NUMBER() IN (1205,2627,3960)
BEGIN
-- We can retry these
IF @t_debug>0
BEGIN
PRINT 'DEBUG:
usp_post_mealplans: ' +
CASE ERROR_NUMBER()
WHEN 1205 THEN
'Deadlock'
WHEN 2627 THEN
'Duplicate Key'
WHEN 3960 THEN
'Conflict'
END + ' on
account ' + @t_account + ' detected.'
END
IF XACT_STATE() <> 0 ROLLBACK
SELECT @retry=1, @j=@j+1
IF @j <= @maxretries
BEGIN
WAITFOR DELAY '00:00:01' -- Give it a sec

-- If there is a duplicate key issue, most likely it is the item_no
IF ERROR_NUMBER()=2627
BEGIN
UPDATE
#usp_mp_transactions SET item_no=1+item_no-(SELECT isnull(min(item_no),0)

FROM #usp_mp_transactions t

WHERE t.account=(a)t_account )

UPDATE
#usp_mp_transactions SET item_no=item_no+(SELECT isnull(max(item_no),0)

FROM transactions t

WHERE t.account=(a)t_account)
END
END
END
ELSE
BEGIN
IF @t_debug>0 PRINT 'DEBUG:
usp_post_mealplans: Other error';
print ERROR_NUMBER()
print ERROR_MESSAGE()
-- Cannot recover, rollback
IF XACT_STATE() <> 0 ROLLBACK

END

END CATCH

IF XACT_STATE() <> 0 COMMIT TRANSACTION


FETCH NEXT FROM cAccounts INTO @t_account

END -- End Loop 2 (L2)

/* Clean up */

CLOSE cAccounts

DEALLOCATE cAccounts


DROP TABLE #usp_mp_transactions

</code>
From: Eric Isaacs on 14 Jun 2010 17:48
Calling a cursor from a trigger is almost always not the best
solution. Using cursors for that matter are usually not the most
ideal solution to most problems. If you're using triggers, they
should handle data in bulk operations, usually based on data in the
INSERTED or DELETED tables.

You didn't provide enough information to explain exactly what you're
trying to do. Temp tables work from triggers, but when you wrap them
in transactions, there are questions about what happens when you
rollback those transactions before you utilize the temp table. In
your catch block, you rollback with IF XACT_STATE() <> 0 ROLLBACK,
before you do other code that references the temp table. Depending on
when the temp table is created, that temp table may be being rolled
back out of existence, possibly.

Seriously reevaluate your design and look at doing these operations in
bulk (possibly with temp tables or better yet, derived tables), rather
than the a cursor and temp table solution you came up with.

-Eric Isaacs
From: Erland Sommarskog on 14 Jun 2010 18:10
Simon Eng (Simon Eng(a)discussions.microsoft.com) writes:
> I'm getting an error 208 (Invalid Object) when I try to call a stored
> procedure using a trigger, but only if the code hits the CATCH block. If
> I run the same sp with the same parameters from a query window, the code
> hits the CATCH block but it executes fine. Any ideas here? Does the temp
> table somehow go out of context or get dumped?

You create the temp table in the beginning of the procedure, I guess?
If you call the procedure from a trigger, you are then in a transaction
defined by the statement that fired the trigger. But in the procedure
you have:

IF XACT_STATE() <> 0 ROLLBACK

And when you are in a trigger, XACT_ABORT is on by default. This means
that any error causes the transaction to be doomed, and you will
rollback. And that includes the statement that fired the trigger -
and it includes the temp table.

I would suggest that you retry scheme is quite advanced, but it may be
difficult to test and verify that it works. You could work around
this by saving @@trancount on input, and if the saved @@trancount value
is > 1, you exit the procedure (because you have burnt your bridges).
But that makes the complex code even more complex.

Also, running a cursor in a trigger is not good for concurrency.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Simon Eng on 16 Jun 2010 09:28
Hello Erland,

Yes, you are correct, the table is created right at the beginning of the SP.
I thought I was isolating the TRY/CATCH within the inner transaction, but
maybe I'm missing something. I'll try your suggestion.

With regard to the use of a CURSOR, you're right and I would prefer a set
based solution, but I also want a way to bundle the transactions on an
account by account basis so that if one account fails to post it doesn't blow
up or delay the overall process. Each transaction set (by account) is
balanced, so missing a set will annoy one guest, but won't throw the system
out of balance or annoy all the guests in the hotel.

As for concurrency, I'm a bit concerned about that also, but since this is
the very first step in the end-of-day process, no other user is supposed to
be accessing the database anyways.

thanks much,
Simon

"Erland Sommarskog" wrote:

> Simon Eng (Simon Eng(a)discussions.microsoft.com) writes:
> > I'm getting an error 208 (Invalid Object) when I try to call a stored
> > procedure using a trigger, but only if the code hits the CATCH block. If
> > I run the same sp with the same parameters from a query window, the code
> > hits the CATCH block but it executes fine. Any ideas here? Does the temp
> > table somehow go out of context or get dumped?
>
> You create the temp table in the beginning of the procedure, I guess?
> If you call the procedure from a trigger, you are then in a transaction
> defined by the statement that fired the trigger. But in the procedure
> you have:
>
> IF XACT_STATE() <> 0 ROLLBACK
>
> And when you are in a trigger, XACT_ABORT is on by default. This means
> that any error causes the transaction to be doomed, and you will
> rollback. And that includes the statement that fired the trigger -
> and it includes the temp table.
>
> I would suggest that you retry scheme is quite advanced, but it may be
> difficult to test and verify that it works. You could work around
> this by saving @@trancount on input, and if the saved @@trancount value
> is > 1, you exit the procedure (because you have burnt your bridges).
> But that makes the complex code even more complex.
>
> Also, running a cursor in a trigger is not good for concurrency.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Eric Isaacs on 16 Jun 2010 16:10
Simon,

Why is this wrapped in a trigger if it's part of the end of day
process? Why not just have it in a sproc that is triggered by a job
once per day?

Another thought would be to handle them in bulk, but if an error
occurs, then step through the accounts one by one.

-Eric Isaacs
 |  Next  |  Last
Pages: 1 2
Prev: Query and Extract
Next: Invalid object name when I try to create a scaler udf in a stored procedure.