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.

Before the CREATE TABLE statements you should put this:

USE Budget
go



"Shiller" <shillerc (AT) gmail (DOT) com> ha scritto nel messaggio
news:1172675843.097303.293060 (AT) 8g2000cwh (DOT) googlegroups.com...

On Feb 28, 10:44 am, "Massimo-Mastino" <mast... (AT) hotmail (DOT) it> wrote:
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."

"Shiller" <shillerc (AT) gmail (DOT) com> wrote

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.

Shiller,

Use a three part qualifier in your create table statement:

create table MyDB.dbo.MyTable ...

-- Bill

"Greg D. Moore (Strider)" <mooregr_deleteth1s (AT) greenms (DOT) com> wrote

On Feb 28, 12:10 pm, "AlterEgo" <altereg... (AT) dslextreme (DOT) com> wrote:
Thank you so much Bill, your technique works.

Shiller (shillerc (AT) gmail (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) 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