Post reply

CREATE DATABASE with a @Variable problem

  • May 10, 2006 at 7:59 am

    #114632

    Hello,

    I'm trying to change a DCM install script to use the same location as the master.mdf for the install location.

    I have:

    DECLARE @DefaultLocation1 as varchar(100)

    DECLARE @DefaultLocation2 as varchar(100)

    SELECT @DefaultLocation1 = LEFT(RTRIM(filename), LEN(RTRIM(filename))-11) FROM master.dbo.sysfiles WHERE name = 'master'  ----I've verified this will return "F:\SQLDATA\MSSQL\Data" which is what I want-----

    SET @DefaultLocation2 = @DefaultLocation1 + '\DCMSolution_Data.MDF'

    SET @DefaultLocation1 = @DefaultLocation1 + '\DCMSolution_Data.LDF'

    CREATE DATABASE [DCMSolution]  ON (NAME = N'DCMSolution_Data', FILENAME = @DefaultLocation2, SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'DCMSolution_Log', FILENAME = @DefaultLocation1, SIZE = 1, FILEGROWTH = 10%)

     COLLATE SQL_Latin1_General_CP1_CI_AS

    I'm getting an "Incorrect syntax near '@DefaultLocation2". I've tried this several different ways and can't seem to get it to work.

    Any and all help is very appreciated!

    Thank you!

  • May 10, 2006 at 8:41 am

    #637015

    This is untested, but try this:

    EXEC ('CREATE DATABASE [DCMSolution]  ON (NAME = N''DCMSolution_Data'', FILENAME = ' + @DefaultLocation2 + ', SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N''DCMSolution_Log'', FILENAME = ' + @DefaultLocation1 + ', SIZE = 1, FILEGROWTH = 10%)

     COLLATE SQL_Latin1_General_CP1_CI_AS')

    I don't believe you can use variables directly like that, so you have to use EXEC to build the string to execute first.

  • May 10, 2006 at 10:04 am

    #637042

    Thank you very much! Almost right on the money... thr only thing you were missing was:

    SET @DefaultLocation2 = CHAR(39) + @DefaultLocation1 + '\DCMSolution_Data.MDF' + CHAR(39)

    SET @DefaultLocation1 = CHAR(39) + @DefaultLocation1 + '\DCMSolution_Data.LDF' + CHAR(39)

    Thanks again for your help!

  • May 10, 2006 at 11:03 am

    #637059

    Glad I could help you out! 

Viewing 4 posts - 1 through 3 (of 3 total)

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