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)
