Solved

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

Posted on 2007-03-24
5
200 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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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