Stored procedure to create a new database with tables

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Shiller
    #1

    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.

    Tags: None
    • Massimo-Mastino
      #2
      Re: Stored procedure to create a new database with tables

      Before the CREATE TABLE statements you should put this:

      USE Budget
      go



      "Shiller" <shillerc@gmail .comha scritto nel messaggio
      news:1172675843 .097303.293060@ 8g2000cwh.googl egroups.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.
      >

        Comment

        • Shiller
          #3
          Re: Stored procedure to create a new database with tables

          On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmai l.itwrote:
          Before the CREATE TABLE statements you should put this:
          >
          USE Budget
          go
          >
          "Shiller" <shill...@gmail .comha scritto nel messaggionews:1 172675843.09730 3.293060@8g2000 cwh.googlegroup s.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."

            Comment

            • Greg D. Moore \(Strider\)
              #4
              Re: Stored procedure to create a new database with tables

              "Shiller" <shillerc@gmail .comwrote in message
              news:1172678827 .112925.18770@k 78g2000cwa.goog legroups.com...
              On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmai l.itwrote:
              >Before the CREATE TABLE statements you should put this:
              >>
              >USE Budget
              >go
              >>
              >"Shiller" <shill...@gmail .comha scritto nel
              >messaggionews: 1172675843.0973 03.293060@8g200 0cwh.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.- 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.


                Comment

                • AlterEgo
                  #5
                  Re: Stored procedure to create a new database with tables

                  Shiller,

                  Use a three part qualifier in your create table statement:

                  create table MyDB.dbo.MyTabl e ...

                  -- Bill

                  "Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co mwrote in message
                  news:K_hFh.7399 $tD2.4682@newsr ead1.news.pas.e arthlink.net...
                  "Shiller" <shillerc@gmail .comwrote in message
                  news:1172678827 .112925.18770@k 78g2000cwa.goog legroups.com...
                  >On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmai l.itwrote:
                  >>Before the CREATE TABLE statements you should put this:
                  >>>
                  >>USE Budget
                  >>go
                  >>>
                  >>"Shiller" <shill...@gmail .comha scritto nel
                  >>messaggionews :1172675843.097 303.293060@8g20 00cwh.googlegro ups.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.
                  >

                    Comment

                    • Shiller
                      #6
                      Re: Stored procedure to create a new database with tables

                      On Feb 28, 12:10 pm, "AlterEgo" <altereg...@dsl extreme.comwrot e:
                      Shiller,
                      >
                      Use a three part qualifier in your create table statement:
                      >
                      create table MyDB.dbo.MyTabl e ...
                      >
                      -- Bill
                      >
                      "Greg D. Moore (Strider)" <mooregr_delete t...@greenms.co mwrote in messagenews:K_h Fh.7399$tD2.468 2@newsread1.new s.pas.earthlink .net...
                      >
                      >
                      >
                      "Shiller" <shill...@gmail .comwrote in message
                      news:1172678827 .112925.18770@k 78g2000cwa.goog legroups.com...
                      On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmai l.itwrote:
                      >Before the CREATE TABLE statements you should put this:
                      >
                      >USE Budget
                      >go
                      >
                      >"Shiller" <shill...@gmail .comha scritto nel
                      >messaggionews: 1172675843.0973 03.293060@8g200 0cwh.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.- 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.

                        Comment

                        • Erland Sommarskog
                          #7
                          Re: Stored procedure to create a new database with tables

                          Shiller (shillerc@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, esquel@sommarsk og.se

                          Books Online for SQL Server 2005 at
                          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                          Books Online for SQL Server 2000 at
                          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                            Comment

                            Previous template Next
                            Working...