Post reply

Stored procedure to create a database which table definitions are on a T-SQL file

  • August 31, 2009 at 4:51 pm

    #1047224

    So... whats the problem?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 31, 2009 at 9:50 pm

    #1047294

    Opps I forgot to state the issue, the problem is that all the T-SQL code inside the file doesn't get executed properly on the new scope (USE @DBName)

    I'd like to run all the code from the file against the newly created database.

    I hope it's clear now 😀

    ...Alex

  • August 31, 2009 at 10:14 pm

    #1047298

    The use statement that you specified is not going to ever help. The OSQL command executes in its own context, and will go to the default database for whatever user SQL is running under.

    DECLARE @DBName varchar(128)

    SET @DBName = 'DEMO'

    DECLARE @cmd varchar(300)

    IF DB_ID(@DBName) IS NULL

    BEGIN

    SELECT @cmd = 'CREATE DATABASE ' + @DBName

    EXEC ( @cmd )

    SELECT @cmd = 'osql -E -n -S' + @@SERVERNAME + ' -d' + @DBName + ' -i"C:\Temp\MySQLFile.sql" '

    EXEC master..xp_cmdshell @cmd

    END

    This should be real close and somewhat more resilient that the original.

    CEWII

  • September 1, 2009 at 3:17 pm

    #1047864

    Works great! Thanks a bunch! 😀

    ...Alex

  • September 1, 2009 at 3:35 pm

    #1047876

    You are welcome.

    CEWII

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply