Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    51

    Unhappy Unanswered: Dynamic USE <YourDBNAME>

    I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

    I have a script (or at least I'm trying to have a script) that will create (install) a database for me, then create the tables in the database.

    I'm using a variable for the database name because the same database may be used for different projects on the same server. I only wanted to set the name of the database once.

    I was hoping that after I created the database that I could somehow tell the script to start using that database.

    Use @mydbname is incorrect and if I use the stored proc to execute dynamically, it is not maintained after executing the proc. (Some silly thing about scope).

    Does anyone have any ideas I could use?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Where is your script stored? As a file, or as a stored procedure, or in a table?

    If you are using an interface, like VB, then the simplest solution is to have the interface execute one script to create the database, switch to the new database, and then execute a second script to create the objects.

    If you are running a stored procedure or an sql file, you could package the entire code as dynamic SQL and issue the USE statement in your code. sp_Execute can handle multiple statements, including USE. It is limited in size, I believe, so you would probably have to break your code up by object and inclued the USE statement in each section.

    This question has come up before, so you could browse previous answers, but I have to say I have never seen a multi-database design like this that didn't result in an ongoing mess of version discrepancies, data duplication, and fragile patchworks of DTS packages to keep the whole thing moving. You'll probably tell me that this is not an option, but build scalability into your database design from day one and you will avoid a multitude of problems.

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Dynamic USE <YourDBNAME>

    Originally posted by rmillman
    I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

    Thanks

    I guess, the solution you are looking for is to use dynamic SQL. You will have to use the EXECUTE statement. Paste this code in the query analyzer, and run it:

    USE master
    GO

    DECLARE @DB AS VarChar(50)
    SET @DB = 'YourNewDB'
    EXECUTE('CREATE DATABASE ' + @DB)
    EXECUTE('CREATE TABLE ' + @DB + '.dbo.YourFirstNewTable(YourFirstCol int)')
    -- add your other stuff
    GO

    Is this you are looking for?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Re: Dynamic USE <YourDBNAME>

    Originally posted by rmillman
    I have a script [...] that will create (install) a database for me, then create the tables in the database.
    Just checking: have you tried the -d <dbname> switch in isql ? Just like your sentence above, your batch file would have two parts. First create the database, then swith to it and use it. I would structure the BAT file as follows:

    set DBNAME=mydb
    isql -Q "create database .%DBNAME% ...." -S ... -U ...

    isql -S ... -U ... -d %DBNAME% -i script.sql

    The above executes the create database statement on the command line, so you caan substitute the variable. In the second invocation, the "script.sql" is where you create all your objects. This is pure command-line mode, so it is easily repeatable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •