[dba-SQLServer] Invalid column name dbo.

[John Colby]

I have a query which is taking hours to complete.  IMHO it shouldn't and so
I am trying to speed things up by preselecting data in temp tables.

I create the table and a unique index on it, then insert the data, about
900K rows.

Notice the two select statements which I created for testing how long it
took to return data.  The first select runs just fine, and finishes
selecting the data in about 13 seconds.

The second select then joins the temp table to a largish table (7+ billion
rows).  It likewise returns the data in about the same time, the same
number of rows as the first select.  The problem is that it presents an
error in the messages tab

Msg 207, Level 16, State 1, Procedure usp_MovesSimmons, Line 72
Invalid column name 'dbo'.

I cannot see where there is a column named .dbo in the second select.

To reiterate - No error message if I uncomment the first statement and run
just that select.  Error if I comment that out and run just the second
select.

WEIRD!

Can anyone see what I may be missing?

Create Table #MovePKs (PKMove int, St char(2), Zip char(5), MSA Char (4),
MSAPlusName varchar(100));
CREATE UNIQUE CLUSTERED INDEX IX_1 on #MovePKs (PKMove)

insert into #MovePKs (PKMove, St, Zip, MSA, MSAPlusName)
SELECT  _DataDB101.dbo.AZData.pk,
_DataDB101.dbo.AZData.st,
_DataDB101.dbo.AZData.zip5,
ZipCodes.dbo.vZipStSimple.MSA,
ZipCodes.dbo.vZipStSimple.MSAPlusName
FROM  _DataDB101.dbo.AZData
INNER JOIN ZipCodes.dbo.vZipStSimple
ON _DataDB101.dbo.AZData.zip5 = ZipCodes.dbo.vZipStSimple.Zip
WHERE     (NOT (_DataDB101.dbo.AZData.HashPerson IS NULL))
AND (_DataDB101.dbo.AZData.AddrValid IN ('V', 'E', 'PO'))
AND (_DataDB101.dbo.AZData.movedate_ >= @FromYrMo)
AND (_DataDB101.dbo.AZData.movedate_ <= @ToYrMo)

--Select St, Zip, MSA, MSAPlusName  from #MovePKs

Select St, Zip, MSA, MSAPlusName
from #MovePKs
Inner Join _DataSimmonsProtocolSimple.dbo.tblDB101SimmonsResultSimple
ON #MovePKs.dbo.PKMove =
_DataSimmonsProtocolSimple.dbo.tblDB101SimmonsResultSimple.PKDb101




-- 
John W. Colby
Colby Consulting