How to fix Invalid object name errors on working query?

[dougancil]

I have the following query:

insert into msbtotal.dbo.ne wclients
SELECT distinct tcms_members.db o.memberdata2.* FROM tcms_members.db o.memberdata2 left outer join
msbtotal.dbo.me mberdata on tcms_members.db o.memberdata2.i d =
msbtotal.dbo.me mberdata.id where msbtotal.dbo.me mberdata.id is
null

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

Invalid object name 'msbtotal.dbo.n ewclients'.

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?

[Rabbit]

Have you checked the table? Maybe someone accidentally renamed it or deleted it. Or maybe someone accidentally changed the permissions on the table.

[dougancil]

That query is supposed to create a table called newclients. I did check the table and also that database and reset the permissions, and I'm still getting the error. this stopped working yesterday and the change I made was to make all of the references to the database be msbtotal.dbo.ne wclients. So the process I have are these 4 steps:

1. insert into msbtotal.dbo.ne wclients
SELECT distinct tcms_members.db o.memberdata2.* FROM tcms_members.db o.memberdata2 left outer join
msbtotal.dbo.me mberdata on tcms_members.db o.memberdata2.i d =
msbtotal.dbo.me mberdata.id where msbtotal.dbo.me mberdata.id is
null
GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

2.select distinct * into newclients2
from msbtotal.dbo.ne wclients

3. drop table msbtotal.dbo.ne wclients

4. sp_rename 'newclients2', 'newclients'

[Rabbit]

I don't understand, why do you have an intermediary table? Can't you truncate new clients before inserting into it? Or can't you just do the insert? Is there a reason why you have to insert into an intermediary table before dropping the original and renaming it?

What was the process before you made the changes?

[dougancil]

There doesnt have to be an intermediary table, I just created this job and was going to go back later and clean it up (which I never did ... obviously.) What I changed in the steps was instead of just referring to the table as newclients, I changed them to msbtotal.dbo.ne wclients so in other words

this line:
from msbtotal.dbo.ne wclients
was originally this
from newclients

[Rabbit]

Are you able to do a select from that table outside of the process?

[dougancil]

From the table newclients? That table doesnt exist currently. The table is created with the query.

[Rabbit]

If it doesn't exist right now, it's not going to create it. An insert into will not create a table. A select into will though.

[dougancil]

Rabbit,

it seems that there was originally a newclients table but that had been deleted, hence causing this issue. I've added the table back and have been able to execute the job as needed.