Solved

Invalid object name error

Posted on 2014-02-07
4
566 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 88
Help in Understanding a SQL Query 7 28
C# SQL BULK INSERT CLASS 5 35
Calculating Business Hours 19 63
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

23 Experts available now in Live!

Get 1:1 Help Now