Changing Database within a stored procedure

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

    Changing Database within a stored procedure

    I need to create a stored procedure in the master database that can
    access info to dynamically create a view in another database. It
    doesn't seem like it should be very hard, but I can't get it to work.
    Here's an example of what I want to do.

    CREATE PROCEDURE create_view @dbname sysname
    AS
    BEGIN
    DECLARE @query varchar(1000)
    SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'
    EXEC(@query)
    END

    In this case, I get an error with the word "go". Without it, I get a
    "CREATE VIEW must be the first statement in a batch" error. I tried a
    semicolon in place of "GO" but that didn't help either.

    Thanks
    Tags: None
    • Simon Hayes
      #2
      Re: Changing Database within a stored procedure


      "Bruce" <sandell@pacbel l.net> wrote in message
      news:595024a5.0 404122312.e0cf0 0f@posting.goog le.com...[color=blue]
      > I need to create a stored procedure in the master database that can
      > access info to dynamically create a view in another database. It
      > doesn't seem like it should be very hard, but I can't get it to work.
      > Here's an example of what I want to do.
      >
      > CREATE PROCEDURE create_view @dbname sysname
      > AS
      > BEGIN
      > DECLARE @query varchar(1000)
      > SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'
      > EXEC(@query)
      > END
      >
      > In this case, I get an error with the word "go". Without it, I get a
      > "CREATE VIEW must be the first statement in a batch" error. I tried a
      > semicolon in place of "GO" but that didn't help either.
      >
      > Thanks[/color]

      It would probably be easier to do this from a client-side script - it's easy
      to pass the database name to osql.exe, for example. In addition, you may
      want to rethink your approach slightly, as it would be better to implement a
      controlled deployment process for your code, so you can take a view script
      from your source control system and create it in any database you want. See
      this link also:

      The Curse and Blessings of Dynamic SQL
      http://www.sommarskog.se/dynamic_sql.html#Dyn_DB


      But if you really need to do it in TSQL, then this is one way:

      CREATE PROCEDURE create_view @dbname sysname
      AS
      BEGIN
      DECLARE @cmd varchar(1000)
      set @cmd = 'osql -E -d ' + dbname + ' -Q "CREATE VIEW..."'
      exec master..xp_cmds hell @cmd, NO_OUTPUT
      END

      Simon


        Comment

        • Simon Hayes
          #3
          Re: Changing Database within a stored procedure


          "Bruce" <sandell@pacbel l.net> wrote in message
          news:595024a5.0 404122312.e0cf0 0f@posting.goog le.com...[color=blue]
          > I need to create a stored procedure in the master database that can
          > access info to dynamically create a view in another database. It
          > doesn't seem like it should be very hard, but I can't get it to work.
          > Here's an example of what I want to do.
          >
          > CREATE PROCEDURE create_view @dbname sysname
          > AS
          > BEGIN
          > DECLARE @query varchar(1000)
          > SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'
          > EXEC(@query)
          > END
          >
          > In this case, I get an error with the word "go". Without it, I get a
          > "CREATE VIEW must be the first statement in a batch" error. I tried a
          > semicolon in place of "GO" but that didn't help either.
          >
          > Thanks[/color]

          It would probably be easier to do this from a client-side script - it's easy
          to pass the database name to osql.exe, for example. In addition, you may
          want to rethink your approach slightly, as it would be better to implement a
          controlled deployment process for your code, so you can take a view script
          from your source control system and create it in any database you want. See
          this link also:

          The Curse and Blessings of Dynamic SQL
          http://www.sommarskog.se/dynamic_sql.html#Dyn_DB


          But if you really need to do it in TSQL, then this is one way:

          CREATE PROCEDURE create_view @dbname sysname
          AS
          BEGIN
          DECLARE @cmd varchar(1000)
          set @cmd = 'osql -E -d ' + dbname + ' -Q "CREATE VIEW..."'
          exec master..xp_cmds hell @cmd, NO_OUTPUT
          END

          Simon


            Comment

            • Bruce
              #4
              Re: Changing Database within a stored procedure

              Hi Simon,

              Thanks for the help. This stored procedure is installed by customers,
              so I have no idea what databases they have or what databases they will
              want to create this view on. I will give your solution a try.

              Thanks,
              Bruce

                Comment

                • Bruce
                  #5
                  Re: Changing Database within a stored procedure

                  Hi Simon,

                  Thanks for the help. This stored procedure is installed by customers,
                  so I have no idea what databases they have or what databases they will
                  want to create this view on. I will give your solution a try.

                  Thanks,
                  Bruce

                    Comment

                    Previous template Next
                    Working...