invalid object name

Question

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.

Answer 1

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

Answer 2

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