Solved

Invalid object name error

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why Delete * statement wont work with sql server ? 6 43
create an aggregate function 9 36
performance query 4 24
Sql Query 6 68
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

832 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