Invalid Object name error message
-
Thursday, January 10, 2013 12:11 PM
Hello Sir/Mam,
I have two database namely First and Second. In First db, it has the tables namely 'Client' and 'Contact' but in Second db, it has only 'Client' table.
I have only one select statement common to execute in both the databases.
Declare IsContactEnabled bit = 0;
Select
case when IsContactEnabled = 0 then Null Else (select Name from Contact where ClientNum = c.Num) As "Contact Name"
from Client c;
when executing this select statement, it produced "Invalid Object Name Contact" error...
how to solve this?
Note : i want only one select statement , it must be executable in both the databases based on bit value it should be produce the result...
Thanks,
Nandhini.M
All Replies
-
Thursday, January 10, 2013 12:18 PM
Check this Declare @IsContactEnabled bit = 0;
Select
case when IsContactEnabled = 0 then Null Else (select Name from FirstDbName..Contact where ClientNum = c.Num) As "Contact Name"
from Client c;
----- I am not sure with ur requirement but the second query ll fetch u the result based on the data present in contact table of first Db.
Please have look on the comment
-
Thursday, January 10, 2013 12:18 PMYou may try with dynamic query. check DB_NAME and add the conditions appropriately.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
-
Thursday, January 10, 2013 12:21 PM
Its not possible. When you run commands, objects must exists. You query is failed at parsing step so zero possibility to get executed on basis on bit value.
You cna do one thing by refering CONTACT table of FirstDB while executing it on both DB, like below, if it suites your requirment.
Select
case when IsContactEnabled = 0 then Null Else (select Name from FirstDB.SchemaNameContact where ClientNum = c.Num) As "Contact Name"
from Client c;
Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. -
Thursday, January 10, 2013 12:25 PM
It doesn't work... it produced the same error...
I can't specify the database name... Because in real time i'm using 5 database.
3 database doesn't have the contact table....
- Edited by Nandhini.M Thursday, January 10, 2013 12:29 PM
-
Thursday, January 10, 2013 12:33 PMdeclare @int int
set @int=1
IF (@int=1)
Select * from FIRSTDB.dbo.Client
Else
Select * from FIRSTDB.dbo.Client UNION
Select * from SECOND.dbo.ClientPlease mark as 'Answer', if the solution solves your problem.
-
Thursday, January 10, 2013 12:34 PMnot working... i executed couple of times....
-
Thursday, January 10, 2013 12:54 PM
-
Thursday, January 10, 2013 12:55 PM
You can use simple if else statement
If IsContactEnabled bit = 0
begin
return null OR select null
end
else if (select 1 from sys.objects where name = 'contact' and type = 'u')
begin
select Name from Contact
you can do inner or out join here with Client table using alias c
where ClientNum = c.Num
end
Mark this post as answer if this resolves your issue.
Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin -
Thursday, January 10, 2013 12:59 PMU can build a dynamic SQL based on existence of that table in the database such that if the table exits execute a dynamic query using the table contact Else a different query which do not cobntain that
Please have look on the comment
-
Thursday, January 10, 2013 1:06 PM
For the above example it will work fine.
But selecting multiple columns from multiple table using join means, two times the code will be repeated...
-
Thursday, January 10, 2013 1:27 PM
Nandhini, As Erland said, if the table does not exists, the query should not use such table reference.
Still, you wanna do so, then you might need to put some effort to use dynamic way as said.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
-
Thursday, January 10, 2013 1:37 PMModerator
One possibility is to have a inline table valued function in both databases called GetContactName, e.g.
create function GetContactName (@ClientNum int) returns table as return select [Name] from dbo.Contact where ClientNum = @ClientNum ;
in the DB that has the Contact table and this function
create function GetContactName (@ClientNum int) returns table as return select cast('' as varchar(20)) as [Name] ;
in the second DB.
Then your main query will become
select C.*, F.Name from Client c CROSS APPLY dbo.GetContactName(c.ClientNum) F
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Marked As Answer by Iric WenModerator Friday, January 18, 2013 9:22 AM
-
Thursday, January 10, 2013 1:44 PM
.png)
