INVALID OBJECT NAME

Question

Post reply

INVALID OBJECT NAME

deepeshdhake

SSChasing Mays

Points: 648
More actions
October 1, 2012 at 6:04 pm

#262482

Hello,
I have wrote one store proc. So, in the stored procedure, it is using one table which is in the different database. When I am trying to execute the stored proc it is giving me this error "INVALID OBJECT NAME". I have also given the permission to the stored proc.
Following is the sample stored proc:
create proc proc_name(inputs)
AS
BEGIN
select x.id,y.name
FROM dbo.X_name x inner join dbname.dbo.Y_name y ON x.is=y.id
END
ERROR: Invalid Object Name 'dbname.dbo.Y_name'
NOTE: I got this error when I altered the procedure. Before it was running properly.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

Is the other database on the same server?

What change did you make that stopped it working? Was it adding the reference to dbo.Y_name?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

So looks like that dbname.dbo.Y_name is not accessible from connection you are trying to execute your procedures. Check the name spelling as well.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

OTF SSCertifiable Points: 6054 More actions · Answer 3

The error is almost certainly what it is.

I would just check and confirm the spelling, database and objects you are trying to access.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

deepeshdhake (10/1/2012)
Hello,
I have wrote one store proc. So, in the stored procedure, it is using one table which is in the different database. When I am trying to execute the stored proc it is giving me this error "INVALID OBJECT NAME". I have also given the permission to the stored proc.
Following is the sample stored proc:
create proc proc_name(inputs)
AS
BEGIN
select x.id,y.name
FROM dbo.X_name x inner join dbname.dbo.Y_name y ON x.is=y.id
END
ERROR: Invalid Object Name 'dbname.dbo.Y_name'
NOTE: I got this error when I altered the procedure. Before it was running properly.

Run these two queries and post back the results.

Query 1

select TOP 10 x.id,y.name

FROM dbo.X_name x inner join dbname.dbo.Y_name y ON x.is=y.id

Query 2

select TOP 10 * FROM dbname.dbo.Y_name

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ard5karthick SSCommitted Points: 1603 More actions · Answer 5

HI,

I have created a similar query to yours and it works fine for me. So the problem must be with the syntax. Kindly check it once.

Create Procedure Test(@SCAC Varchar(50))

AS

Begin

Select SCAC, emailAddress

From OC_CARR_BALANCEDUE Balance join OC_User.dbo.portaValues code

on code.codeValue = balance.SCAC and Balance.SCAC = @SCAC

END

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

Hi,

I run the similar query and I didnt get the any error.

create procedure abc as

select x.id,y.name1 from name1 x

inner join Practice.dbo.name1 y ON x.id=y.ID

Command executed successfully

EXEC abc

Result- it returns the matching rows.

Please try to run the query as select * from db_name.dbo.tablename ans check if it is returnug desired result or any error.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/