I have a table that gets deleted and re created in milliseconds(cant just insert and delete). Of course this occurs sometimes when another stored procedure is running and trying to call that table. How would I avoid this? I have tried 'waitfor' xx seconds and different types of loops to wait until the table is back but I still get the error saying the table does not exist or (invalid object name 'xxxx') Thanks for any help.
2 Answers
Delete and recreate the table within a transaction.
When ever you read / write from / to it make sure you transaction isolation level is READ COMMITTED.
That way, the table should always be there as your read / writes won't happen until the transaction for deleting and creating the table is commited.
I think that's right, so I hope that helps.
Comments
You need to check if the table exists before you try to access it. You can do something like:
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'Schema'
AND TABLE_NAME = 'Table'))
BEGIN
-- Do stuff with the table
END
Another option is to handle the schema errors by using TRY/CATCH together with dynamic SQL like this:
BEGIN TRY
DECLARE @sql nvarchar(100)
SET @sql = 'SELECT * FROM NonExistentTable'
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
SELECT'Do stuff here'
END CATCH
6 Comments
Cookie Monster
This will also work but you need to specify the schema as well.
AntDC
But if you you are "already in that schema" you don't. Fair point though as it is not clear if the OP is or isn't.
Chris
The if exist wont work if my table is gone for that split second. It will just give me a invalid object name error.
Cookie Monster
@Chris Do you mean the split second between the If exists check and the execution of the statements inside?
Chris
@CookieMonster Correct. Sometimes I do the exist and the table is truly there. Then my code below to get data from that table wont work because then that split second happens. Then we get the error. Is there a way to avoid getting that error and just rollback or delay a second so I can get my table? Hope this makes sense.
|
TRUNCATEthe table?