0

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.

Chris
  • 1
  • 1

2 Answers2

1

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.

enter link description here

AntDC
  • 1,807
  • 14
  • 23
0

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
Cookie Monster
  • 475
  • 6
  • 12
  • This will also work but you need to specify the schema as well. – Cookie Monster Jan 22 '16 at 13:56
  • 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. – AntDC Jan 22 '16 at 16:26
  • The if exist wont work if my table is gone for that split second. It will just give me a invalid object name error. – Chris Jan 26 '16 at 14:43
  • @Chris Do you mean the split second between the If exists check and the execution of the statements inside? – Cookie Monster Jan 27 '16 at 12:38
  • @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. – Chris Jan 27 '16 at 19:43
  • @Chris in that case just do what AntDC has suggested below. – Cookie Monster Jan 28 '16 at 10:53