Solved

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

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

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 142

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 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