You can pass tablename through a variable in SSIS. Just needs to create a variable for that. You may need a metdata table which tracks details of tablename change etc.

Hello,

We have a table (example: tableA) with approximately 14 million records and many stored procedures uses this table for joins during retrieve on UI. 

The data in this table will be populated through an SSIS package, once in every day. Actually, the SSIS package will populate another table (example: tableA_ETL) and then renames the table to the original name (example: from tableA_ETL to tableA and tableA to tableA_ETL) so that it would not affect the end users during data populate.

Now the issue is that we are getting 'Invalid object name dbo.tableA' error while user try to retrieve on UI. We observed that this is happening when user does a retrieve operation through UI during the table rename (example from tableA_ETL to tableA).

Could anyone please suggest any solution for this?

Thanks

Rakesh

• Moved by Kalman TothModerator 3 hours 9 minutes ago Not database design

Hi R,

I think you have a very small time when you have renamed TableA to something else and you haven't yet renamed TableA_ETL to TableA. I suggest you do the following

BEGIN TRANSACTION

SELECT TOP 0 * FROM TableA WITH (TABLOCKX)

/* rename both of your tables as you do already */

DBCC FREEPROCCACHE 

COMMIT TRANSACTION

This way, queries that want to read TableA will wait for a few milliseconds while the renaming executes.

Hope that helps,

Richard.

What is use of FREEPROCCACHE ? is it really required. You should have also included in your answer the grave afteraffects of running this command. This would clear cache store for proc and proc might start taking longer time to execute.

The issue is simple here IMO its just the object change is not happening correctly or users are trying to access when change is happening

Can it just be?

BEGIN TRANSACTION
/* rename both of your tables as you do already */
COMMIT TRANSACTION

What is the purpose of: SELECT TOP 0 * FROM TableA WITH (TABLOCKX) ?

Hi Rakesh,

You Can handle this at UI(application) side itself.

Step1: Put your sql code that tries to contact the table (TableA) in for loop and Error handling block.

Step2:If your SQL Code Catches error description as ''Invalid object name dbo.tableA' ,then put a piece of code to wait for couple of seconds and then retry.

Step3.Repeat step 2 until you get rid of error.

Step4.If success come out of loop.

Pros: Database need not be touched with DBCC commands.

Cons: User has to wait during Table rename, But application will not throw error.

Let me know if you have more questions.

Balakrishna