asked on

CF doesn't like my SQL table

I get the following error on one of my templates. History is a table in the Contacts datasource and Contacts is a table in the Contacts datasource. The area in question is the invalid object name. My CF datasouces are just fine in the CF admin.
-----------------------------------------

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'History'.

The error occurred in C:\Inetpub\wwwroot\CCL\Contacts\Summary.cfm: line 6

4 : <cfset TwoWeeksOld = DateAdd('WW', -2, Now())>
5 :
6 : <cfquery name="PastDue" datasource="Contacts">
7 : SELECT (select max(History.HistoryID) from History where History.RefID = Contacts.ID), Contacts.ID, Contacts.FirstName, Contacts.MiddleName,
8 : Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments,

--------------------------------------------------------------------------------

SQL SELECT (select max(History.HistoryID) from History where History.RefID = Contacts.ID), Contacts.ID, Contacts.FirstName, Contacts.MiddleName, Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments, History.RefID, History.LastContact, History.NextContact FROM Contacts LEFT OUTER JOIN History ON Contacts.ID = History.RefID WHERE HistoryID = (select max(History.HistoryID) from History where History.RefID = Contacts.ID) OR History.HistoryID IS NULL GROUP BY History.HistoryID, Contacts.ID, Contacts.FirstName, Contacts.MiddleName, Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments, History.RefID, History.LastContact, History.NextContact HAVING HISTORY.LastContact < DateAdd(Week,-2,GetDate()) ORDER BY Company ASC
DATASOURCE Contacts
VENDORERRORCODE 208
SQLSTATE 42S02

Brian Coughter

ASKER

Also, I had backed up these MS SQL Server databases and then restored them after reinstalling SQL Server and CF.

James Rodgers

this is your sql

SELECT (select max(History.HistoryID)
from History
where History.RefID = Contacts.ID), Contacts.ID, Contacts.FirstName, Contacts.MiddleName, Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments, History.RefID, History.LastContact, History.NextContact
FROM Contacts LEFT OUTER JOIN History ON Contacts.ID = History.RefID
WHERE HistoryID = (select max(History.HistoryID) from History where History.RefID = Contacts.ID) OR History.HistoryID IS NULL
GROUP BY History.HistoryID, Contacts.ID, Contacts.FirstName, Contacts.MiddleName, Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments, History.RefID, History.LastContact, History.NextContact
HAVING HISTORY.LastContact < DateAdd(Week,-2,GetDate()) ORDER BY Company ASC

i think it should be

SELECT History.HistoryID,Contacts.ID, Contacts.FirstName, Contacts.MiddleName, Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments, History.RefID, History.LastContact, History.NextContact
FROM Contacts LEFT OUTER JOIN History ON Contacts.ID = History.RefID
WHERE HistoryID = (select max(History.HistoryID) from History where History.RefID = Contacts.ID) OR History.HistoryID IS NULL
GROUP BY History.HistoryID, Contacts.ID, Contacts.FirstName, Contacts.MiddleName, Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments, History.RefID, History.LastContact, History.NextContact
HAVING HISTORY.LastContact < DateAdd(Week,-2,GetDate()) ORDER BY Company ASC

Brian Coughter

ASKER

I get the same error only this time the Invalid Object is Contacts.

Brian Coughter

ASKER

This exact SQL query worked fine before I reinstalled everything.

James Rodgers

have you tried breaking down the sql to make sure that each statement is working?

Brian Coughter

ASKER

I ran this whole query in SQL Enterprise Manager and it returned exactly what it was supposed to return. It works fine there.

James Rodgers

>>I ran this whole query in SQL Enterprise Manager and it returned exactly what it was supposed to return. It works fine there.

thats weird if it works it works....

Brian Coughter

ASKER

The other thing I did was try to add this as a stored procedure in SQL but when I comment out the query and add the following line, I get an error:

LINE:
<cfstoredproc datasource="Contacts" procedure="HistorySummary"></cfstoredproc>

ERROR:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'HistorySummary'.

The error occurred in C:\Inetpub\wwwroot\CCL\Contacts\Summary.cfm: line 20

18 : </cfquery>
19 : --->
20 : <cfstoredproc datasource="Contacts" procedure="HistorySummary"></cfstoredproc>
21 :
22 : <html>

--------------------------------------------------------------------------------

SQL {call HistorySummary}
DATASOURCE Contacts
VENDORERRORCODE 2812
SQLSTATE 42000

ASKER CERTIFIED SOLUTION

James Rodgers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Coughter

ASKER

Okay, I fixed it. I had to redo the ODBC Socket and remove/recreate the datasource in ColdFusion.

Thanks Jester!

James Rodgers

np... glad i could help

thanks for the points