[AccessD] Invalid column name dbo.

[McGillivray, Don]

Hi John,

WAG here, as I have no experience with SQL Server, but in the join clause you refer to the join value from #MovePKs as #MovePKs.dbo.PKMove.  Maybe that should be #MovePKs. PKMove?  That is, lose the .dbo in the middle.


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Thursday, October 29, 2015 9:36 AM
To: Discussion concerning MS SQL Server; Access Developers discussion and problem solving
Subject: [AccessD] Invalid column name dbo.

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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com