创建数据库，参数传路径名，报错

[gpw]

declare @DbPath nvarchar(50)
declare @Path1 nvarchar(50)
declare @Path2 nvarchar(50)
set @DbPath = 'C:\'
set @Path1 = @DbPath + N'TMP_Data.MDF'
set @Path2 = @DbPath + N'TMP_Log.LDF'

-- Create db

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TMP')
DROP DATABASE [TMP]
GO
CREATE DATABASE [TMP] ON (NAME = N'TMP_Data', FILENAME = @Path1 , SIZE = 721, FILEGROWTH = 10%)
LOG ON (NAME = N'TMP_Log', FILENAME = @Path2 , SIZE = 23, FILEGROWTH = 10%)

服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '@Path1' 附近有语法错误。

如何修改？

[liangCK]

联机帮助提出了解决方法..


USE master;
GO
IF DB_ID (N'Archive') IS NOT NULL
DROP DATABASE Archive;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the CREATE DATABASE statement 
EXECUTE ('CREATE DATABASE Archive 
ON
PRIMARY  
    (NAME = Arch1,
    FILENAME = '''+ @data_path + 'archdat1.mdf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch2,
    FILENAME = '''+ @data_path + 'archdat2.ndf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch3,
    FILENAME = '''+ @data_path + 'archdat3.ndf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
LOG ON 
   (NAME = Archlog1,
    FILENAME = '''+ @data_path + 'archlog1.ldf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
   (NAME = Archlog2,
    FILENAME = '''+ @data_path + 'archlog2.ldf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)'
);
GO

[wgzaaa]

--上面很详细
declare @DbPath nvarchar(50)
declare @Path1 varchar(50)
declare @Path2 varchar(50)
set @DbPath = 'C:\'
set @Path1 = @DbPath + N'TMP_Data.MDF'
set @Path2 = @DbPath + N'TMP_Log.LDF'

-- Create db

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TMP')
DROP DATABASE [TMP]
--GO 分隔了批@Path1传不下来,create 不接受变量，所以要用动态语句获取变量的值　
exec('CREATE DATABASE [TMP] ON (NAME = N''TMP_Data'', FILENAME ='''+@Path1+''' , SIZE = 50, FILEGROWTH = 10%)
LOG ON (NAME = N''TMP_Log'', FILENAME = '''+@Path2+''' , SIZE = 5, FILEGROWTH = 10%) ')

[wgzaaa]

SIZE = 50我缩小了，另停止数据库的使用参看：
http://topic.csdn.net/t/20060123/11/4531414.html

[懒牛科技]

学习了!

[gpw]

-- delete old dbfile
USE master;
GO
IF DB_ID (N'JJ_TMPINOUT') IS NOT NULL
DROP DATABASE [JJ_TMPINOUT];
GO
-- create new dbfile
declare @DbPath nvarchar(200)
declare @MDFFile nvarchar(200)
declare @LDFFile nvarchar(200)
declare @GUID nvarchar(200)
set @GUID = '8818521FF14E45D4ABDD8F4EBE5538A9'
set @DbPath = 'C:\'
set @MDFFile = @DbPath + @GUID + N'_Data.MDF'
set @LDFFile = @DbPath + @GUID + N'_Log.LDF'
EXECUTE ('CREATE DATABASE [JJ_TMPINOUT]
ON
PRIMARY
(NAME = JJ_TMPINOUT_Data,
FILENAME = ''' + @MDFFile + ''',
SIZE = 10MB,
FILEGROWTH = 10%)
LOG ON
(NAME = JJ_TMPINOUT_Log,
FILENAME = ''' + @LDFFile + ''',
SIZE = 10MB,
FILEGROWTH = 10%)'
);
GO

declare @GUID nvarchar(200)
set @GUID = '8818521FF14E45D4ABDD8F4EBE5538A9'

-- insert master table information
select * into [JJ_TMPINOUT].dbo.[XZZXB] from [JJ_JJDb].dbo.[XZZXB] where GUID = @GUID;
-- insert detail tables information
select * into [JJ_TMPINOUT].dbo.[XZZXWJXXB] from [JJ_JJDb].dbo.[XZZXWJXXB] where FGUID = @GUID
select * into [JJ_TMPINOUT].dbo.[FJNRB] from [JJ_JJDb].dbo.[FJNRB] where FGUID IN (Select FJTB From [JJ_JJDb].dbo.[XZZXB] Where GUID = @GUID Union Select FJNR From [JJ_JJDb].dbo.[XZZXWJXXB] Where FGUID = @GUID)
GO
-- separate dbfile
USE master;
ALTER DATABASE [JJ_TMPINOUT]
SET SINGLE_USER;
GO
EXEC sp_detach_db '[JJ_TMPINOUT]', 'False';
GO

正在删除数据库文件 'C:\8818521FF14E45D4ABDD8F4EBE5538A9_Log.LDF'。
正在删除数据库文件 'C:\8818521FF14E45D4ABDD8F4EBE5538A9_Data.MDF'。
CREATE DATABASE 进程正在磁盘 'JJ_TMPINOUT_Data' 上分配 10.00 MB 的空间。
CREATE DATABASE 进程正在磁盘 'JJ_TMPINOUT_Log' 上分配 10.00 MB 的空间。

（所影响的行数为 1 行）


（所影响的行数为 25 行）


（所影响的行数为 26 行）

服务器: 消息 15010，级别 16，状态 1，过程 sp_detach_db，行 25
数据库 '[JJ_TMPINOUT]' 不存在。请用 sp_helpdb 来显示可用的数据库。

不能分离，怎么回事情？能帮忙解决1下？

[hanjs]

CREATE DATABASE [TMP] ON (NAME = N'TMP_Data', FILENAME = @Path1 , SIZE = 721, FILEGROWTH = 10%)
LOG ON (NAME = N'TMP_Log', FILENAME = @Path2 , SIZE = 23, FILEGROWTH = 10%)

需要用动态sql， exec('create db sql')

[hanjs]

5楼，都提示你数据库不存在了？

[gpw]

数据库是创建成功的也能在企业管理器里面看到