Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

912 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now