Creating tables within a schema

Question

Post reply

Creating tables within a schema

BenWard

SSCertifiable

Points: 5903
More actions
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
----------------------------------------

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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

BenWard SSCertifiable Points: 5903 More actions · Answer 2

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
----------------------------------------

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

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

BenWard SSCertifiable Points: 5903 More actions · Answer 4

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
----------------------------------------