Invalid Object -- TAKING SQL at school PLEASE HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Invalid Object -- TAKING SQL at school PLEASE HELP

Author

Topic

goh2499
Starting Member

3 Posts

Posted - 2004-10-08 : 02:19:05

When running a script to select duplicate records in customers table recieve the following:

Server: Msg 208, Level 16, State 1, Procedure test_select, Line 3
Invalid object name 'dvd_r_us.dbo.customers'.

My code looks like this:

CREATE PROCEDURE test_select AS

SELECT [dvd_r_us.dbo.cust1.*], [dvd_r_us.dbo.cust2.*]

From [dvd_r_us.dbo.customers] as [dvd_r_us.dbo.cust1], [dvd_r_us.dbo.customers] as [dvd_r_us.dbo.cust2]

Where dvd_r_us.dbo.cust1.customer_firstname=dvd_r_us.dbo.cust2.customer_firstname

and dvd_r_us.dbo.cust1.customer_lastname=dvd_r_us.dbo.cust2.customer_lastname

and dvd_r_us.dbo.cust1.customer_shipping_street_address1=dvd_r_us.dbo.cust2.customer_shipping_street_address1

and dvd_r_us.dbo.cust1.customer_shipping_street_address2=dvd_r_us.dbo.cust2.customer_shipping_street_address2

and dvd_r_us.dbo.cust1.customer_shipping_address_city=dvd_r_us.dbo.cust2.customer_shipping_adress_city

and dvd_r_us.dbo.cust1.customer_shipping_address_zip=dvd_r_us.dbo.cust2.customer_shipping_address_zip

and row>1
GO
:end code

I just need it to diplay all duplicate records on the customers (it is indeed customers) table i created....I guess i made it to hard for myself

PLEASE HELP!!!!!!!!!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-10-08 : 02:34:33

have a look at this http://www.sqlteam.com/item.asp?ItemID=3331 it will show you an easier way to find duplicates.

Damian

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-08 : 05:30:33

your notation is incorrect. If you prefer using braces around object names, the systax is [db].[owner].[object] ; not [db.owner.object].
Example : [dvd_r_us].[dbo].[customers] not [dvd_r_us.dbo.customers]

Also, you do NOT use three part naming convention for ALIAS name.

Ex :
[dvd_r_us.dbo.customers] as [dvd_r_us.dbo.cust1] is incorrect.
[dvd_r_us].[dbo].[customers] as cust1 is correct.

Hemanth Gorijala
BI Architect / DBA...

goh2499
Starting Member

3 Posts

Posted - 2004-10-09 : 02:40:38

Thank you I had made it tooo

hard and now it works and is a little shorter the new code is as follows:

CREATE PROCEDURE test_select AS

SELECT customer_firstname, customer_lastname,
shipping_street_address1, shipping_street_address2,
shipping_address_city,shipping_address_zip, count(*)

From [dvd_r_us].[dbo].[customers] as cust1

group by cust1.customer_firstname, cust1.customer_lastname,
cust1.shipping_street_address1, cust1.shipping_street_address2,
cust1.shipping_address_city, cust1.shipping_address_zip
having count(*)>1
order by cust1.customer_lastname desc
GO
:end code

I need the alias to satisfy some of the requirements from the instructor

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-09 : 02:49:41

Few good practices...
1. Use alias in both "select" and "group by" or don't use them in both.
2. Do not use three part naming convension if dealing with the DBO tables in same database. It increases portability and re-usability of code. This convenience far outweighs the performance gain otherwise.
3. Don't use alias if dealing with only one table in the query. Just makes the life a little easier.

hth.

Hemanth Gorijala
BI Architect / DBA...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources