Working query now giving me invalid object errors

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)

Working query now giving me invalid object errors

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-08 : 10:53:17

I have the following query:

insert into msbtotal.dbo.newclients
SELECT distinct tcms_members.dbo.memberdata2.* FROM tcms_members.dbo.memberdata2 left outer join
msbtotal.dbo.memberdata on tcms_members.dbo.memberdata2.id =
msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
null

and when I try to run this, I get the following error:

Invalid object name 'msbtotal.dbo.newclients'.

I've removed the msbtotal.dbo from this and then will get the error:

Invalid object name "newclients" ..... I had this as a scheduled job in SQL Server Agent jobs and now it's not working. Can anyone suggest a reason as to why it's not working?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-08 : 12:09:22

Something has changed...
Maybe the Schema?

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-08 : 12:30:43

I've looked at both the schema and the permissions and they don't seem to have changed. In my steps in my job, I did change references to the table to mbstotal.dbo but I can't even get that first query to run correctly.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 13:12:08

it seems that there is no table with the name of newclients under the database named: msbtotal. make sure that the table exists under the specified database

Cheers
MIK

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-08 : 13:24:28

And double-check the owner. This is SQL 2000?

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-08 : 14:17:59

This is sql 2000. So I'm told that insert into won't work here but SELECT INTO will. I don't know what that would look like as I've tried it a few ways.

select into msbtotal.dbo.newclients
SELECT distinct tcms_members.dbo.memberdata2.* FROM tcms_members.dbo.memberdata2 left outer join
msbtotal.dbo.memberdata on tcms_members.dbo.memberdata2.id =
msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
null

also

SELECT distinct tcms_members.dbo.memberdata2.* FROM tcms_members.dbo.memberdata2 left outer join into msbtotal.dbo.newclients
msbtotal.dbo.memberdata on tcms_members.dbo.memberdata2.id =
msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
null

What's the proper way to use SELECT INTO for this query?

Thank you

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2011-02-08 : 17:18:32

On you're first query. Please check if the table "newclients" is in the specified database.

Maybe you are refering to this select into syntax:



SELECT   col1, col2, col3, col4
INTO     NewTableName  
FROM     tblEmployee

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources