?
Solved

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

Posted on 2007-03-24
5
Medium Priority
?
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

762 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