SQL invalid object name

[jci714]

I used SQL administrator to copy a database. Registered it successfully with
XP, and CF Administrator. I can run query analyzer with the new database.
When I change the DSN name in a .cfm it reognizes the DSN but all tables get
a "invalid object name" error.
All the permissions for the Database and Tables seem to be the same.
I can not find any other permissions to check. I had the same problem a long
time ago, but can't remember the fix.
Help

[mxstu]

Sounds like you're using sql server.

- What kind of connection are you using: JDBC / ODBC?
- If you run the run the following in a cfquery and dump the results, what is
the database name?
SELECT DB_NAME() AS CurrentDatabaseName

[jci714]

ODBC

[mxstu]

Maybe you haven't set the default database in the DSN, so you're in the
"master" database. If you run the run the following in a cfquery and dump the
results, what is the database name shown?

SELECT DB_NAME() AS CurrentDatabaseName

Btw - Why ODBC and not JDBC?

[jci714]

SELECT DB_NAME() AS CurrentDatabaseName
Returns Master

[mxstu]

That's you're problem. Just open up the DSN and set the "default" to the correct database name (ie. not "master")

[jci714]

I changed the default to the databasename, and it still shows master. I will shut everythi9ng down and see if it takes effect when I restart. I think that is the step I was forgetting. Thanks

[jci714]

That took care of it. I have made note of it until next time.
Are there any advantages to using JDBC ?
Thanks.

[jci714]

That was the problem.
Thanks for the quick replies.
What is the advantage to using JDBC?

[Scott*e]

using jdbc directly takes out the lag of the jdbc to odbc conversion.

I use MSSQL2000 wih jdbc without problem.