Skip to main content
Anonymous
Dec 27, 2006 6:44 PM UTC
Answered

SQL Invalid Object Name

I am pulling my hair out over this one. I am trying to run cfquery from this ColdFusion application using SQL 2000 as my Database. I have tried updating to SQL 2000 service pack 4 and installing the latest com.microsoft.jdbc.sqlserver.SQLServerDriver

SQL 2000 driver. Nothing seems to work. I am able to create DSN's okay. I tried making an ODBC connection and that did not work either. I have tried connecting ColdFusion to another SQL Server 2000 box and it works fine. Here is the error message I am getting.

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'TIER1'.

The error occurred in D:\cfsites\tms-hq\test.cfm: line 1

1 : <cfquery name="GetTiers" datasource="#application.ds#">
2 : SELECT *
3 : FROM TIER1



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

SQL SELECT * FROM TIER1
DATASOURCE TMSMyCFSitePublic
VENDORERRORCODE 208
SQLSTATE 42S02

Resources:
This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer
Jan 1, 2007 2:53 PM UTC
Thanks for your suggestion. I discovered and the account I was using what the problem was. I was using DTS to transfer my db tables were being transfered with the owner as tmsadmin and not dbo. I changed that and everything appears to be working okay.
View reply

9 replies

philh
Inspiring
Dec 30, 2006 9:17 PM UTC
This is typically the situation in which the DSN points at a different database as the default. Try running the query with a fully qualified table name, e.g.

SELECT * FROM
MyDB.dbo.TIER1

Another thing to check is if the account under which the ColdFusion service is running has permissions to the object.
Translate
Anonymous
Correct answer
Jan 1, 2007 2:53 PM UTC
Thanks for your suggestion. I discovered and the account I was using what the problem was. I was using DTS to transfer my db tables were being transfered with the owner as tmsadmin and not dbo. I changed that and everything appears to be working okay.
Translate
Anonymous
Dec 27, 2006 8:25 PM UTC
Also, the username and password that I am using is the db_owner of that particular DB. The only cfquery that I have been able to work is this.

SELECT DB_NAME AS "GEORGE"

The correct DB name is returned.
Translate
Newsgroup_User
Inspiring
Dec 27, 2006 7:58 PM UTC
> Error Executing Database Query.
> [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'TIER1'.

Is the DB user you are using in the datasource config the owner of "TIER1"?
If not, you might have to specify ownerName.TIER1.

As Dan suggested, connect to the DB with QA as *the same user* as per the
datasource, and run the query. What happens then?

--
Adam
Translate
Anonymous
Dec 27, 2006 8:20 PM UTC
Adam, thanks for writing back. Everything works in Query Analyser. No problems there. That was the first thing I checked. I am able to connect to different SQL servers not just the one that I want to. It always gives me this Invalid Object call.
Translate
Dan_Bracuk
Inspiring
Dec 27, 2006 7:24 PM UTC
Does it work in Query Analyser?
Translate
Anonymous
Dec 27, 2006 8:17 PM UTC
Yes it does work in Query Analyser.
Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices