[dba-SQLServer] Invalid column name dbo.

[Paul Hartland]

John,

I am pretty sure it is this line

ON #MovePKs.dbo.PKMove

Change to

ON #MovePKs.PKMove

On 29 October 2015 at 16:35, John Colby <jwcolby at gmail.com> wrote:

> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Paul Hartland
paul.hartland at googlemail.com