468,107 Members | 1,386 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

Stored procedure to create a new database with tables

I want my application to create a new database/tables when run for the
first time. I have created a stored procedure to create the new
database named "budget". When I run the stored procedure, it creates
the budget database but the tables are created in the "master"
database. Please help.

Feb 28 '07 #1
6 12770
Before the CREATE TABLE statements you should put this:

USE Budget
go

"Shiller" <sh******@gmail.comha scritto nel messaggio
news:11**********************@8g2000cwh.googlegrou ps.com...
I want my application to create a new database/tables when run for the
first time. I have created a stored procedure to create the new
database named "budget". When I run the stored procedure, it creates
the budget database but the tables are created in the "master"
database. Please help.

Feb 28 '07 #2
On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmail.itwrote:
Before the CREATE TABLE statements you should put this:

USE Budget
go

"Shiller" <shill...@gmail.comha scritto nel messaggionews:11**********************@8g2000cwh.g ooglegroups.com...
I want my application to create a new database/tables when run for the
first time. I have created a stored procedure to create the new
database named "budget". When I run the stored procedure, it creates
the budget database but the tables are created in the "master"
database. Please help.- Hide quoted text -

- Show quoted text -
I tried using the USE statement, but it's not allowed in stored
procedure: "a USE database statement is not allowed in a procedure,
function or trigger."

Feb 28 '07 #3
"Shiller" <sh******@gmail.comwrote in message
news:11*********************@k78g2000cwa.googlegro ups.com...
On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmail.itwrote:
>Before the CREATE TABLE statements you should put this:

USE Budget
go

"Shiller" <shill...@gmail.comha scritto nel
messaggionews:11**********************@8g2000cwh. googlegroups.com...
I want my application to create a new database/tables when run for the
first time. I have created a stored procedure to create the new
database named "budget". When I run the stored procedure, it creates
the budget database but the tables are created in the "master"
database. Please help.- Hide quoted text -

- Show quoted text -

I tried using the USE statement, but it's not allowed in stored
procedure: "a USE database statement is not allowed in a procedure,
function or trigger."
Correct, USE is not really transact-SQL it's a control statement for query
analyzer, etc.

Off hand I'm not sure of a solution to your problem, but google may. I seem
to recall others having similar problems.

Generally though what you want to do is done by a series of scripts, not a
single stored proc.
Feb 28 '07 #4
Shiller,

Use a three part qualifier in your create table statement:

create table MyDB.dbo.MyTable ...

-- Bill

"Greg D. Moore (Strider)" <mo****************@greenms.comwrote in message
news:K_*****************@newsread1.news.pas.earthl ink.net...
"Shiller" <sh******@gmail.comwrote in message
news:11*********************@k78g2000cwa.googlegro ups.com...
>On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmail.itwrote:
>>Before the CREATE TABLE statements you should put this:

USE Budget
go

"Shiller" <shill...@gmail.comha scritto nel
messaggionews:11**********************@8g2000cwh .googlegroups.com...

I want my application to create a new database/tables when run for the
first time. I have created a stored procedure to create the new
database named "budget". When I run the stored procedure, it creates
the budget database but the tables are created in the "master"
database. Please help.- Hide quoted text -

- Show quoted text -

I tried using the USE statement, but it's not allowed in stored
procedure: "a USE database statement is not allowed in a procedure,
function or trigger."

Correct, USE is not really transact-SQL it's a control statement for query
analyzer, etc.

Off hand I'm not sure of a solution to your problem, but google may. I
seem to recall others having similar problems.

Generally though what you want to do is done by a series of scripts, not a
single stored proc.

Feb 28 '07 #5
On Feb 28, 12:10 pm, "AlterEgo" <altereg...@dslextreme.comwrote:
Shiller,

Use a three part qualifier in your create table statement:

create table MyDB.dbo.MyTable ...

-- Bill

"Greg D. Moore (Strider)" <mooregr_deletet...@greenms.comwrote in messagenews:K_*****************@newsread1.news.pas .earthlink.net...
"Shiller" <shill...@gmail.comwrote in message
news:11*********************@k78g2000cwa.googlegro ups.com...
On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmail.itwrote:
Before the CREATE TABLE statements you should put this:
>USE Budget
go
>"Shiller" <shill...@gmail.comha scritto nel
messaggionews:11**********************@8g2000cwh. googlegroups.com...
I want my application to create a new database/tables when run for the
first time. I have created a stored procedure to create the new
database named "budget". When I run the stored procedure, it creates
the budget database but the tables are created in the "master"
database. Please help.- Hide quoted text -
>- Show quoted text -
I tried using the USE statement, but it's not allowed in stored
procedure: "a USE database statement is not allowed in a procedure,
function or trigger."
Correct, USE is not really transact-SQL it's a control statement for query
analyzer, etc.
Off hand I'm not sure of a solution to your problem, but google may. I
seem to recall others having similar problems.
Generally though what you want to do is done by a series of scripts, not a
single stored proc.- Hide quoted text -

- Show quoted text -
Thank you so much Bill, your technique works.

Feb 28 '07 #6
Shiller (sh******@gmail.com) writes:
I want my application to create a new database/tables when run for the
first time. I have created a stored procedure to create the new
database named "budget". When I run the stored procedure, it creates
the budget database but the tables are created in the "master"
database. Please help.
This is a case where it's better to run statements from the client. After
all, somehow that stored procedure needs to be written somewhere, and I
don't think it's a good idea to write it to the master database.

Alternatively, you can put it a file that you run from OSQL or SQLCMD.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 28 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by fniles | last post: by
5 posts views Thread by Phil Barber | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.