Professional Web Applications Themes

Create table overwrites existing table in mssql - Coldfusion Database Access

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 doentation 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 ...

  1. #1

    Default Create table overwrites existing table in mssql

    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 doentation 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>

    bmyers Guest

  2. #2

    Default Re: Create table overwrites existing table in mssql

    > But today, we used this program on a new host we are trying, and the create 

    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
    Adam Guest

  3. #3

    Default Re: Create table overwrites existing table in mssql

    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
    Adam Guest

Similar Threads

  1. table alias mssql problem
    By Bugsville in forum Coldfusion Database Access
    Replies: 9
    Last Post: March 8th, 06:46 PM
  2. Replies: 1
    Last Post: August 18th, 09:02 PM
  3. Replies: 2
    Last Post: August 12th, 07:55 AM
  4. How to create a table with dynamic table name
    By cathy wang in forum Informix
    Replies: 3
    Last Post: July 17th, 12:46 PM
  5. How to place a new table around an existing one?
    By benleeke webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 15th, 12:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139