Create table overwrites existing table in mssql

Question

In a program we use to update our sites, we use the 'create table' function of MS SQL to create missing tables in databases.

In the thousands of times we have used this, if a table already exists with the same name of the table in the create table command, MS SQL throws an error.

In fact, MS SQL documentation states that only if you include the optional 'overwrite' command, will an existing table be overwritten.

But today, we used this program on a new host we are trying, and the create table command actually did overwrite an existing table with a new table, deleting all the records in the old table.

I am trying to figure out what settings at the server would allow this.

This is the code we are using:

<CFQUERY NAME="recipe" DATASOURCE="recipe" dbtype="ODBC" username="#ruser#" password="#rPASS#">
Create Table recipecats (
RecipecatID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
recipecatdescription NVARCHAR(50) NULL)
</cfquery>

Newsgroup_User · Accepted Answer

Are you sure it's not just creating another table with the same name, but a
different owner?

I've seen weirdness (it's probably only weird to me because I am ignorant
of what's going on) I can READ a table owned by a different user without
qualifying the table name with its owner, and when I go to CREATE a table
of that name, it's actually created as my current user, and thereafter when
querying the unqualified table name, I get results from the new one, not
the old one. If I qualify the table name, I can query both/either. Kind
of like how CF hunts around various scopes when you don't qualify a
variable reference.

Also, your code should probably be testing to see if the table exists
before attempting to create it anyhow. And "just try it, and catching it
if it errors" does not count as "testing if it exists".

--
Adam

Newsgroup_User · Answer

> But today, we used this program on a new host we are trying, and the create
> table command actually did overwrite an existing table with a new table,
> deleting all the records in the old table.

If the DB is being provided by the hosting company, I'd stop trying to work
the problem out yourself and ask THEM why their DB is behaving to spec.

Which version of SQL Server is it?

--
Adam

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded