Post reply

Creating tables within a schema

  • November 17, 2009 at 10:42 am

    #217749

    Hi,

    I'm trying to create a prototype for an application using sql server express edition.

    I want to secure the data by employing schemas, eg one schema called customers will hold the customer related tables and stored procedures, another called product1 will hold all the orders for one product category, product2 holds all the order for an entirely different category.

    I have been using sql server for years but have never tried creating any schemas before and my company still haven't bought me any books 🙁

    I have found out how to create the schemas which I have done but when I try to run:

    CREATE TABLE customers.customer

    (

    csID int NOT NULL IDENTITY (1, 1),

    csFirstName char(35) NULL,

    csSecondName char(35) NULL,

    csThridName char(35) NULL,

    csSurname char(35) NULL,

    csDob smalldatetime NULL,

    ) ON [PRIMARY]

    I get:

    Msg 2760, Level 16, State 1, Line 1

    The specified schema name "customers" either does not exist or you do not have permission to use it.

    this is rather puzzling to me as my login *DOMAIN*\wardb is the dbo, and dbo is the owner of all the schemas...

    What am I doing wrong?

    Thanks!!!

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • November 17, 2009 at 10:52 am

    #1080398

    Don't know. Can you post the DDL you used to create the schemas?

  • November 17, 2009 at 10:57 am

    #1080403

    Unfortunately not... I just created them using the graphical tools in management studio.

    Interestingly I have just succesfully created the table using the graphical tools. I hit F4 to get the properties up and selected the schema there.

    now the REALLY weird this is that if I click the generate script button, and copy it into the query window, that doesn't run...

    I can at least get on with what I'm doing now but something seems fundamentally wrong if the gui tools work but the scripts they generate dont...

    I'm confused 🙁

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • November 17, 2009 at 12:21 pm

    #1080456

    Please execute this in your query window:

    SELECT @@SERVERNAME, DB_NAME();

    SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'customers';

    Does this show the right server/database and does your schema appear in the output?

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • November 18, 2009 at 2:52 am

    #1080749

    Hi Flo,

    results:

    -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

    PCS08404\SQLEXPRESS Bob

    (1 row(s) affected)

    CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER DEFAULT_CHARACTER_SET_CATALOG DEFAULT_CHARACTER_SET_SCHEMA DEFAULT_CHARACTER_SET_NAME

    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

    Bob customers dbo NULL NULL iso_1

    I tried again to create a table this morning using:

    create table customers.test

    (

    id int

    )

    and it worked. very strange...

    Thanks

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply