Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

Invalid object name error

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
Chanel187
Asked:
Chanel187
  • 2
1 Solution
 
Surendra NathCommented:
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
 
Chanel187Author Commented:
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
 
Surendra NathCommented:
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
 
Scott PletcherSenior DBACommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now