SQL Invalid Object Name

[Guest]

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:

[Dan_Bracuk]

Does it work in Query Analyser?

[Guest]

Yes it does work in Query Analyser.

[Guest]

Hi Dan, can you please help me trouble shoot this. We are trying to launch a new web site from this server and it is impossible without getting ColdFusion to run cfquery.

[Newsgroup_User]

> 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

[Guest]

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.

[srouse72]

Adam,

I'm having this issue as well, and this is the first time I've set up ColdFusion on my own.  I am the database owner and the only one with access to this install of CF.  I wondering if I missed something in my setup.  Where would I check the permissions?

[Guest]

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.

[philh]

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.

[Guest · Accepted solution]

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.