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); 你把DATABASE_PRINCIPAL_ID这个改成database_id就可以了,有时候书上有点错误是正常的啊。 @data_path就就是取数据库的绝对路径的。
按12楼的回复把principal去掉成功建立数据库,与版主教授的公式表面上对比无差别,接此例的下一个案例教授查询,命令如下 create database demo_snapshot on(name=demo,filename='d:\snapshot\demo.mdf') as snapshot of demo出现消息 5014,级别 16,状态 3,第 1 行 数据库 'demo' 中不存在 文件'demo'。然后我又把试验demo的部分改过了,按上例建立的内容输入demodb1或者arch1都提示错误,不是找不到数据库就是说其他的,到底我又错那里了??
set @data_path=(select SUBSTRING(physical_name, 1, charindex(N'master.mdf',LOWER(physical_name))-1)
FROM master.sys.master_files
WHERE DATABASE_PRINCIPAL_ID= 1 AND FILE_ID = 1);
execute('create database demo on primary
(name = arch1,filename = '''+@data_path + 'demodb1.mdf'',size = 100MB, maxsize = 200,
filegrowth = 20),
(name = arch2,filename = '''+@data_path + 'demodb2.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20),
(name = arch3,filename = '''+@data_path + 'demodb3.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20)
log on
(name = archlog1, filename = '''+@data_path + 'archlog1.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20),
(name = archlog2, filename = '''+@data_path + 'archlog2.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20)');
红字部分是函数,而不是一个列,可以改成这样试试:
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_PRINCIPAL_ID()= 1 AND FILE_ID = 1);
execute('create database demo on primary
(name = arch1,filename = '''+@data_path + 'demodb1.mdf'',size = 100MB, maxsize = 200,
filegrowth = 20),
(name = arch2,filename = '''+@data_path + 'demodb2.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20),
(name = arch3,filename = '''+@data_path + 'demodb3.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20)
log on
(name = archlog1, filename = '''+@data_path + 'archlog1.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20),
(name = archlog2, filename = '''+@data_path + 'archlog2.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20)');
set @data_path=(select SUBSTRING(physical_name, 1, charindex(N'master.mdf',LOWER(physical_name))-1)
FROM master.sys.master_files
WHERE DATABASE_PRINCIPAL_ID()= 1 AND FILE_ID = 1);
execute('create database demo on primary
(name = arch1,filename = '''+@data_path + 'demodb1.mdf'',size = 100MB, maxsize = 200,
filegrowth = 20),
(name = arch2,filename = '''+@data_path + 'demodb2.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20),
(name = arch3,filename = '''+@data_path + 'demodb3.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20)
log on
(name = archlog1, filename = '''+@data_path + 'archlog1.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20),
(name = archlog2, filename = '''+@data_path + 'archlog2.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20)');
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
消息 1801,级别 16,状态 3,第 1 行
数据库 'demo' 已存在。请选择其他数据库名称。当我删除demo数据库的时候就出现消息 512,级别 16,状态 1,第 2 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
消息 5105,级别 16,状态 2,第 1 行
出现文件激活错误。物理文件名 'demodb1.mdf' 可能不正确。请诊断并更正其他错误,然后重试此操作。
消息 1802,级别 16,状态 1,第 1 行
CREATE DATABASE 失败。无法创建列出的某些文件名。请查看相关错误。烦请再三指教。
理解了这个我容易一点知道自己错在哪里。
SET @data_path = ( SELECT TOP 1
SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE DATABASE_PRINCIPAL_ID() = 1
AND FILE_ID = 1
) ;
EXECUTE('create database demo on primary
(name = arch1,filename = '''+@data_path + 'demodb1.mdf'',size = 100MB, maxsize = 200,
filegrowth = 20),
(name = arch2,filename = '''+@data_path + 'demodb2.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20),
(name = arch3,filename = '''+@data_path + 'demodb3.ndf'',size = 100mb, maxsize = 200,
filegrowth = 20)
log on
(name = archlog1, filename = '''+@data_path + 'archlog1.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20),
(name = archlog2, filename = '''+@data_path + 'archlog2.ldf'',size = 100 mb, maxsize = 200,
filegrowth = 20)') ;
问题1:除非空间出现不足,否则想不到什么理由需要多个日志文件。
问题2:你可以查查:
SELECT SUBSTRING(physical_name, 1, LEN(physical_name))
FROM master.sys.master_files
WHERE DATABASE_PRINCIPAL_ID() = 1
AND FILE_ID = 1
这个结果有多条数据,怎么可以直接付给一个变量呢?赋给一个表还差不多
按照版主
SELECT SUBSTRING(physical_name, 1, LEN(physical_name))
FROM master.sys.master_files
WHERE DATABASE_PRINCIPAL_ID() = 1
AND FILE_ID = 1这个公式出现我所有的数据库名称,但公式作用不太明白。
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);
你把DATABASE_PRINCIPAL_ID这个改成database_id就可以了,有时候书上有点错误是正常的啊。
@data_path就就是取数据库的绝对路径的。
create database demo_snapshot
on(name=demo,filename='d:\snapshot\demo.mdf')
as snapshot of demo出现消息 5014,级别 16,状态 3,第 1 行
数据库 'demo' 中不存在 文件'demo'。然后我又把试验demo的部分改过了,按上例建立的内容输入demodb1或者arch1都提示错误,不是找不到数据库就是说其他的,到底我又错那里了??