Solved

Invalid object name error

Posted on 2014-02-07
4
569 Views
Last Modified: 2014-02-10
Hi,

I have written a script to up date multiple rows on different tables within a database.

One running the script I receive an [Invalid object name 'dbo.CT_Room'], but the table exists.

How can I fix this?

Thanks

Declare     @CelcatRoomName   varchar(75)
Declare     @KXRoomName varchar(75)
Declare     @TrendRoomName    varchar(75)
Declare     @RoomUID    varchar(75)
Declare     @CelcatDB1  varchar(75)
Declare     @CelcatDB2  varchar(75)

-- Set the values for datebases, room names and roomUID below

Set @CelcatDB1 = 'Celcat7_UAT_1213'
Set @CelcatDB2 = 'Celcat7_UAT_1314'
set @CelcatRoomName = 'SAFB-119 Seminar Room'
set @KXRoomName = 'SAF Rm 119'
set @TrendRoomName = 'SAF Rm 119'
Set @RoomUID = 'JIM 123'

-- End of setup

Begin tran

-- Change Celcat UID Database1
exec ('use '+ @CelcatDB1)
UPDATE [dbo].[CT_Room]
SET Unique_Name = @RoomUID
WHERE Name = @CelcatRoomName 

-- Change CK-Management UID datbase1    
exec ('use '+ @CelcatDB1)
UPDATE [dbo].[CK_Room] 
SET Sm_Id = @RoomUID
WHERE Room_id in ( select Room_id 
      from [dbo].[CT_Room]  where Name = @CelcatRoomName)

-- Change Celcat UID Database2       
exec ('use '+ @CelcatDB2)
UPDATE [dbo].[CT_Room]
SET Unique_Name = @RoomUID
WHERE Name = @CelcatRoomName 

-- Change CK-Management UID datbase2
exec ('use '+ @CelcatDB2)
UPDATE [dbo].[CK_Room] 
SET Sm_Id = @RoomUID
WHERE Room_id in ( select Room_id 
      from [dbo].[CT_Room]  where Name = @CelcatRoomName)

-- Change KX UID   
UPDATE [KxGerm].[dbo].[SyllabusLocations] 
SET LocationHostKey = @RoomUID
WHERE conferenceroomid in ( select conferenceroomid 
      from [KxGerm].[dbo].[ConferenceRooms]  where Name = @KxRoomName)

--Change Trend UID
UPDATE [EstatesRooms].[dbo].[Rooms]
SET Room_UID = @RoomUID
   WHERE Room_Name = @TrendRoomName
   
Commit tran

Open in new window

0
Comment
Question by:Chanel187
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39841444
You are thinking

exec ('use '+ @CelcatDB1)

will change the whole database context to the database which is given in that variable @celcatdb1....
but that assumption is wrong, the database context will be changed only to that exec statement and later for the update statement it will be changed back to the database where it is getting executed.

if you want to change the database context for the update statement as well, you have to use the dynamic sql as below or a linked server

dynamic sql

declare @SQL NVARCHAR(max)

SET @SQL = 'use '+ @CelcatDB1
set @SQL = '
UPDATE [dbo].[CT_Room]
SET Unique_Name =' +  @RoomUID
+ ' WHERE Name = ' +  @CelcatRoomName
'
exec (@SQL)
0
 

Author Comment

by:Chanel187
ID: 39841475
Hi Surendra


is this what you mean?

Declare     @CelcatRoomName   varchar(75)
Declare     @KXRoomName varchar(75)
Declare     @TrendRoomName    varchar(75)
Declare     @RoomUID    varchar(75)
Declare     @CelcatDB1  varchar(75)
Declare     @CelcatDB2  varchar(75)
declare @SQL NVARCHAR(max)



-- Set the values for datebases, room names and roomUID below

Set @CelcatDB1 = 'Celcat7_UAT_1213'
Set @CelcatDB2 = 'Celcat7_UAT_1314'
set @CelcatRoomName = 'SAFB-119 Seminar Room'
set @KXRoomName = 'SAF Rm 119'
set @TrendRoomName = 'SAF Rm 119'
Set @RoomUID = 'JIM 123'

-- End of setup

Begin tran

-- Change Celcat UID Database1
SET @SQL = 'use '+ @CelcatDB1
set @SQL = '
UPDATE [dbo].[CT_Room]
SET Unique_Name =' +  @RoomUID
+ ' WHERE Name = ' +  @CelcatRoomName

exec (@SQL) 

-- Change CK-Management UID datbase1    
exec ('use '+ @CelcatDB1)
UPDATE [dbo].[CK_Room] 
SET Sm_Id = @RoomUID
WHERE Room_id in ( select Room_id 
      from [dbo].[CT_Room]  where Name = @CelcatRoomName)

-- Change Celcat UID Database2       
exec ('use '+ @CelcatDB2)
UPDATE [dbo].[CT_Room]
SET Unique_Name = @RoomUID
WHERE Name = @CelcatRoomName 

-- Change CK-Management UID datbase2
exec ('use '+ @CelcatDB2)
UPDATE [dbo].[CK_Room] 
SET Sm_Id = @RoomUID
WHERE Room_id in ( select Room_id 
      from [dbo].[CT_Room]  where Name = @CelcatRoomName)

-- Change KX UID   
UPDATE [KxGerm].[dbo].[SyllabusLocations] 
SET LocationHostKey = @RoomUID
WHERE conferenceroomid in ( select conferenceroomid 
      from [KxGerm].[dbo].[ConferenceRooms]  where Name = @KxRoomName)

--Change Trend UID
UPDATE [EstatesRooms].[dbo].[Rooms]
SET Room_UID = @RoomUID
   WHERE Room_Name = @TrendRoomName
   
Commit tran

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39841489
thats correct.

but you have to do that for all the updates where you want to change the database to database2.
not just for the first one...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39842541
If the proc needs to run against different dbs, I suggest putting it in the master db.  You can then run the proc in the context of any db, and all table references in the proc will be for that db.

If you want more info on this, just let me know.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now