472,958 Members | 2,514 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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 12997
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Robin Tucker | last post by:
Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and...
4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
5
by: Thanos | last post by:
Hello, Im a novice at this so bare with me. I have two tables which will be match merged based on 10 conditions (select statements). For example, if a record exists in table A but not in Table...
2
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have...
2
by: Joe Campbell | last post by:
I have a DBA that wrote a stored procedure that does a SELECT from a particluar SQL Server table. Within that stored procedure he links over to grab a column from another database table. I need to...
1
by: masoumeh | last post by:
please help me I use this code TA_Insert_Language as new DataSetName.Insert_LanguageTableAdapter DT_Insert_Language as new DataSetName.Insert_LanguageDataTable dim MaxRow as new DataRow dim...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
9
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table....
5
by: Phil Barber | last post by:
I have a stored procedure that contains several temp tables, I am attempting to assign a tableadapter to the Sp. when it goes to create the persistent fields (in the wizard) I get Select command...
1
by: ajay kumar chada | last post by:
Can anybody help me in finding error in the following SP please, (actullay i need to drop the tables which had been created six months ago CREATE PROCEDURE TGNSX00.TAB_DROP_PROC(OUT OUT_SQLCODE...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.