Mysterious db and tables

[Honcho]

This is really strange behavior. When I create a new SQL 2000 db using
Enterprise Manager and then register the data source using the CF Admin page,
everything works as expected and the new db gets an OK status indicating that
CF can connect to it in the normal way.

When I connect to the db via RDS using either HomeSite + or Dreamweaver MX, I
actually connect to a mysterious db that DOES NOT appear in the list of
databases on my development server when using Enterprise Manager. The name of
the db is the correct one I want to work with, but the tables are all wrong.
This is a repeatable problem. Every new database I create shows me one list of
tables using Enterprise Manager and a different list of tables using HomeSite
or Dreamweaver. All of my old databases work properly, it's only new ones that
are behaving this way. The tables in the mysterious db has two tables that
appear to be standard CF variables with a table named dbo.CDATA and dbo.CGLOBAL
and there are about 20 values in these tables all timestamped 2004-07-31. The
other tables are all appear to be system type tables such as dbo.spt_X where X
is items such as datatype_info, fallback_db, etc.

Anyone have any idea how to restore normal functionality to this system?

[BKBK]

Suppose you chose to have client management and suppose that, in your
Coldfusion Administrator, you checked the "Create Client Database Tables"
box. Then Coldfusion will automatically create the tables, CDATA and CGLOBAL,
by running an in-built SQL script that runs:

<cfquery datasource="yourDatasource">
CREATE TABLE [dbo].[CDATA] (
[cfid] [char] (20) NULL ,
[app] [char] (64) NULL ,
[data] [text] NULL
)

CREATE TABLE [dbo].[CGLOBAL] (
[cfid] [char] (20) NULL ,
[data] [text] NULL ,
[lvisit] [datetime] NULL
)
</cfquery>

[Honcho]

Good suggestion as a possibility, however, the Client Variables db already exists and is the default storage mechanism for Client Sessions. Thanks for idea, it just doesn't seem to be the proble.

[mxstu]

Is it possible cf is in the "master" database? Try running this command to get the current database name

SELECT DB_NAME() AS CurrentDatabaseName

[BKBK]

Mxstu said:

>... Is it possible cf is in the "master" database?

Indeed. I've just seen that the tables, "dbo.spt_X where X is items such
as datatype_info, fallback_db, etc." come from the system database in
MS SQL called "Master".

Honcho said:

>... The name of the db is the correct one I want to work with, but the tables
>... are all wrong

Combinig the two hints, one could conclude that MS SQL automatically added
those system("master") tables to your database when you created it. Can you
verify whether your database contains those tables? If so, what happens when
you delete them?

[mxstu]

>If so, caution...caution... what happens when you delete them?
Now that's just evil ... ;-)

I'm thinking he's simply logged into the master database and that he's not
really in the database he thinks he is in at all. Maybe the default database
name is not set in HomeSite/Dreamweaver. Although since I don't use either
product, I'm not even sure they have a "default database" setting ;-)

[Honcho]

Good idea! I checked the master db and it did contain the two CF tables and the
data from 2004-07-31. I deleted the two tables without any apparent problems.

This allows me to now restate the problem: I can create a new db in the usual
fashion using Enterprise Manager, make tables, perform queries and all the
other standard things - as long as I use Enterprise Manager. When I access the
new db using HomeSite +, or a CF page, I am apparently connecting to the Master
db rather than the newly created db. I can properly connect and interact with
old databases with HomeSite + or ColdFusion pages without any difficulity, the
problem is just with any new db I create.

[BKBK]

Indeed getting stranger and stranger. Let's avoid complication and first verify
that

- you heed Mxstu and are not "simply logged into the master database and that
he's not
really in the database he thinks he is in at all";

- you didn't inadvertently give your new database the name "master";

- in Dreamweaver, in the Window menu, select databases and, if required, log
in
with your Coldfusion Administrator credentials. You should get the list of all
the databases
that Coldfusion can connect to. Is the name of your new database in the list?
And the "Master"
database? Assuming your new database is in the list, if you click on the "+"
to expand the
list of tables, do you see all the tables you expect?

[Honcho]

I found the problem. It really was something stupid.

In registering a new data source in CF Admin, I was specifying the Data Source
Name on the first page, but failing to name the database on the second page. In
these circumstances, CF evidently connects to master db and generates a status
OK message.

It's been a VERY LONG time since I've set up a new database and I overlooked
one of the basic steps and CF failed indicate anything was wrong.

Thanks for the suggestions, it got me pointed in the right direction.