Solved

Invalid object name error

Posted on 2014-02-07
4
579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Scott Pletcher
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

623 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