Solved

Cannot create SQL server express 2005 table using the query window.

Posted on 2007-03-24
5
196 Views
Last Modified: 2010-03-19
I have SQL Server Express 2005 and I am trying to create a table by clicking on the "New query" button and executing the following query:

CREATE TABLE TestTable
(
    ID            INT NOT NULL,
    ItemCatRefID  INT NULL,
    ItemRefID     INT NULL,
    PageNumber    INT NULL,
    DisplayOrder  INT NULL,
)

The result was:
Command(s) completed successfully

However, the table is nowhere to be seen. I refreshed and opened and closed the database but still no good. When I execute the query again it says the table still exists. In the end I was able to manually create the table.

Does anyone know what I am doing wrong?
0
Comment
Question by:mike99c
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18785951
what user did you connect with?
what database are you connected to when you run the script?
I assume in the master database, please check with this query:
select db_name()

and you are not looking in the correct database.
also, you should specify the user when creating the table....

CREATE TABLE dbo.TestTable
(
    ID            INT NOT NULL,
    ItemCatRefID  INT NULL,
    ItemRefID     INT NULL,
    PageNumber    INT NULL,
    DisplayOrder  INT NULL,
)
0
 

Author Comment

by:mike99c
ID: 18785981
I am logged in using the same user that is also the owner of the database.

When I ran your script it showed I was connected to the master database.

I ran the create query with the dbo. prefix and I still get the same problem. The table is nowhere to be seen but the database still thinks it is there as I cannot run the query again with the same database name.

There are only 2 databases in the Enterprise manager and I cannot fnd it there either.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18785994
>When I ran your script it showed I was connected to the master database.
so, did you look in the master database? you have to refresh the table listing in the object browser!
0
 
LVL 9

Expert Comment

by:dbeneit
ID: 18785998
please can you post the entire create script?
in the sql you have a synax error in "," before ")"
0
 

Author Comment

by:mike99c
ID: 18786012
Thanks angellll, your were right it was in the master database. I am new to SQL server so was not aware of this.

I now right clicked on the database I wanted to create the table in and selected "new query". I then refreshed the tables and it appeared there. I will award you the full points.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question