Change Filename creating database

I wish to change filename parameter depending on the root path of SQL Server :

C:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL where X>=1

I can find this root path by the following querie :

select SUBSTRING (physical_name,1,len(physical_name)-11) from sys.database_files where type_desc like 'LOG')

But i can't include the result in the create database command to be independant of the new root path in a the case of a deployment :

CREATE DATABASE [ACS] ON PRIMARY
(
NAME = N'ACS',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'ACS_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf',
SIZE = 3840KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%
)

Somebody can help me please ?

You will have to compose your SQlString first, then executing it with the help of sp_executesql or EXEC.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Following your clear but short instruction, i write the next queries that don't work, have a correction ?

DECLARE @.prmname nvarchar(500)
DECLARE @.prmname2 nvarchar(500)
DECLARE @.prmpath nvarchar(500)
DECLARE @.prmpath2 nvarchar(500)
DECLARE @.SQLString nvarchar(500)
DECLARE @.ParmDefinition nvarchar(500);

/* Build the SQL string */
SET @.SQLString =
N'CREATE DATABASE [ACS] ON PRIMARY
(
NAME = @.name,
FILENAME = @.path,
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = @.name2,
FILENAME = @.path2,
SIZE = 3840KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%
)
COLLATE Latin1_General_BIN2';
SET @.ParmDefinition = N'@.path varchar(500),@.path2 varchar(500), @.name varchar(500), , @.name2 varchar(500)';

/* Execute the string with the parameter value. */
SET @.prmname = N'ACS';
SET @.prmpath = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf';
SET @.prmname2 = N'ACS_log';
SET @.prmpath2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf';

EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.name= @.prmname,@.path=@.prmpath, @.name2= @.prmname2,@.path2=@.prmpath2;

|||

DECLARE @.prmname nvarchar(500)

DECLARE @.prmname2 nvarchar(500)

DECLARE @.prmpath nvarchar(500)

DECLARE @.prmpath2 nvarchar(500)

DECLARE @.SQLString nvarchar(500)

DECLARE @.ParmDefinition nvarchar(500);

/* Execute the string with the parameter value. */

SET @.prmname = N'ACS';

SET @.prmpath = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf';

SET @.prmname2 = N'ACS_log';

SET @.prmpath2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf';

/* Build the SQL string */

SET @.SQLString =

N'CREATE DATABASE [ACS] ON PRIMARY

(

NAME = ' + @.prmname + ',

FILENAME = ' + @.prmpath + ',

SIZE = 5120KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024KB

)

LOG ON

(

NAME = ' + @.prmname2 + ',

FILENAME = ' + @.prmpath2 + ',

SIZE = 3840KB,

MAXSIZE = 2048GB,

FILEGROWTH = 10%

)

COLLATE Latin1_General_BIN2'

EXECUTE sp_executesql @.SQLString

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

It's not working.

Incorrect Syntax for 'C' for DECLARE @.prmpath2 nvarchar(500)