执行这个,然后在EXEC ATTACH_DB,把结果贴出来alter proc attach_db
as
begin
declare @dbname varchar(50),@i int,@dbPath varchar(50),@ldfPath varchar(50),@mdfPath varchar(50),@sql varchar(1000),@rq varchar(100)
declare mycs cursor FORWARD_ONLY for select distinct xzqh_bm from xzqh
open mycs
set @i = (select count(*) from (select distinct xzqh_bm from xzqh) as xzqh)
fetch next from mycs into @dbname
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @mdfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '.mdf'
set @ldfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '_log.ldf'
set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
print (@sql)
while @i > 1
begin
fetch next from mycs into @dbname
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @mdfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '.mdf'
set @ldfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '_log.ldf'
set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
print (@sql)
set @i=@i-1
end
close mycs
DEALLOCATE mycs
END
as
begin
declare @dbname varchar(50),@i int,@dbPath varchar(50),@ldfPath varchar(50),@mdfPath varchar(50),@sql varchar(1000),@rq varchar(100)
declare mycs cursor FORWARD_ONLY for select distinct xzqh_bm from xzqh
open mycs
set @i = (select count(*) from (select distinct xzqh_bm from xzqh) as xzqh)
fetch next from mycs into @dbname
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @mdfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '.mdf'
set @ldfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '_log.ldf'
set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
print (@sql)
while @i > 1
begin
fetch next from mycs into @dbname
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @mdfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '.mdf'
set @ldfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '_log.ldf'
set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
print (@sql)
set @i=@i-1
end
close mycs
DEALLOCATE mycs
END
sp_attach_db '371601_200', 'D:\zrzjk\CWJJJG201311\371601_200.mdf' , 'D:\zrzjk\CWJJJG201311\371601_200_log.ldf'
sp_attach_db '371601_311', 'D:\zrzjk\CWJJJG201311\371601_311.mdf' , 'D:\zrzjk\CWJJJG201311\371601_311_log.ldf'
sp_attach_db '371601_400', 'D:\zrzjk\CWJJJG201311\371601_400.mdf' , 'D:\zrzjk\CWJJJG201311\371601_400_log.ldf'
sp_attach_db '371601_500', 'D:\zrzjk\CWJJJG201311\371601_500.mdf' , 'D:\zrzjk\CWJJJG201311\371601_500_log.ldf'
sp_attach_db '371602_111', 'D:\zrzjk\CWJJJG201311\371602_111.mdf' , 'D:\zrzjk\CWJJJG201311\371602_111_log.ldf'
sp_attach_db '371602_200', 'D:\zrzjk\CWJJJG201311\371602_200.mdf' , 'D:\zrzjk\CWJJJG201311\371602_200_log.ldf'
sp_attach_db '371602_311', 'D:\zrzjk\CWJJJG201311\371602_311.mdf' , 'D:\zrzjk\CWJJJG201311\371602_311_log.ldf'
sp_attach_db '371602_400', 'D:\zrzjk\CWJJJG201311\371602_400.mdf' , 'D:\zrzjk\CWJJJG201311\371602_400_log.ldf'
sp_attach_db '371602_500', 'D:\zrzjk\CWJJJG201311\371602_500.mdf' , 'D:\zrzjk\CWJJJG201311\371602_500_log.ldf'
sp_attach_db '371621_111', 'D:\zrzjk\CWJJJG201311\371621_111.mdf' , 'D:\zrzjk\CWJJJG201311\371621_111_log.ldf'
sp_attach_db '371621_200', 'D:\zrzjk\CWJJJG201311\371621_200.mdf' , 'D:\zrzjk\CWJJJG201311\371621_200_log.ldf'
sp_attach_db '371621_311', 'D:\zrzjk\CWJJJG201311\371621_311.mdf' , 'D:\zrzjk\CWJJJG201311\371621_311_log.ldf'
sp_attach_db '371621_400', 'D:\zrzjk\CWJJJG201311\371621_400.mdf' , 'D:\zrzjk\CWJJJG201311\371621_400_log.ldf'
sp_attach_db '371621_500', 'D:\zrzjk\CWJJJG201311\371621_500.mdf' , 'D:\zrzjk\CWJJJG201311\371621_500_log.ldf'
sp_attach_db '371622_111', 'D:\zrzjk\CWJJJG201311\371622_111.mdf' , 'D:\zrzjk\CWJJJG201311\371622_111_log.ldf'
sp_attach_db '371622_200', 'D:\zrzjk\CWJJJG201311\371622_200.mdf' , 'D:\zrzjk\CWJJJG201311\371622_200_log.ldf'
sp_attach_db '371622_311', 'D:\zrzjk\CWJJJG201311\371622_311.mdf' , 'D:\zrzjk\CWJJJG201311\371622_311_log.ldf'
sp_attach_db '371622_400', 'D:\zrzjk\CWJJJG201311\371622_400.mdf' , 'D:\zrzjk\CWJJJG201311\371622_400_log.ldf'
sp_attach_db '371622_500', 'D:\zrzjk\CWJJJG201311\371622_500.mdf' , 'D:\zrzjk\CWJJJG201311\371622_500_log.ldf'
sp_attach_db '371623_111', 'D:\zrzjk\CWJJJG201311\371623_111.mdf' , 'D:\zrzjk\CWJJJG201311\371623_111_log.ldf'
sp_attach_db '371623_200', 'D:\zrzjk\CWJJJG201311\371623_200.mdf' , 'D:\zrzjk\CWJJJG201311\371623_200_log.ldf'
sp_attach_db '371623_311', 'D:\zrzjk\CWJJJG201311\371623_311.mdf' , 'D:\zrzjk\CWJJJG201311\371623_311_log.ldf'
sp_attach_db '371623_400', 'D:\zrzjk\CWJJJG201311\371623_400.mdf' , 'D:\zrzjk\CWJJJG201311\371623_400_log.ldf'
sp_attach_db '371623_500', 'D:\zrzjk\CWJJJG201311\371623_500.mdf' , 'D:\zrzjk\CWJJJG201311\371623_500_log.ldf'
sp_attach_db '371624_111', 'D:\zrzjk\CWJJJG201311\371624_111.mdf' , 'D:\zrzjk\CWJJJG201311\371624_111_log.ldf'
sp_attach_db '371624_200', 'D:\zrzjk\CWJJJG201311\371624_200.mdf' , 'D:\zrzjk\CWJJJG201311\371624_200_log.ldf'
sp_attach_db '371624_311', 'D:\zrzjk\CWJJJG201311\371624_311.mdf' , 'D:\zrzjk\CWJJJG201311\371624_311_log.ldf'
sp_attach_db '371624_400', 'D:\zrzjk\CWJJJG201311\371624_400.mdf' , 'D:\zrzjk\CWJJJG201311\371624_400_log.ldf'
sp_attach_db '371624_500', 'D:\zrzjk\CWJJJG201311\371624_500.mdf' , 'D:\zrzjk\CWJJJG201311\371624_500_log.ldf'
sp_attach_db '371625_111', 'D:\zrzjk\CWJJJG201311\371625_111.mdf' , 'D:\zrzjk\CWJJJG201311\371625_111_log.ldf'
sp_attach_db '371625_200', 'D:\zrzjk\CWJJJG201311\371625_200.mdf' , 'D:\zrzjk\CWJJJG201311\371625_200_log.ldf'
sp_attach_db '371625_311', 'D:\zrzjk\CWJJJG201311\371625_311.mdf' , 'D:\zrzjk\CWJJJG201311\371625_311_log.ldf'
sp_attach_db '371625_400', 'D:\zrzjk\CWJJJG201311\371625_400.mdf' , 'D:\zrzjk\CWJJJG201311\371625_400_log.ldf'
sp_attach_db '371625_500', 'D:\zrzjk\CWJJJG201311\371625_500.mdf' , 'D:\zrzjk\CWJJJG201311\371625_500_log.ldf'
sp_attach_db '371626_111', 'D:\zrzjk\CWJJJG201311\371626_111.mdf' , 'D:\zrzjk\CWJJJG201311\371626_111_log.ldf'
sp_attach_db '371626_200', 'D:\zrzjk\CWJJJG201311\371626_200.mdf' , 'D:\zrzjk\CWJJJG201311\371626_200_log.ldf'
sp_attach_db '371626_311', 'D:\zrzjk\CWJJJG201311\371626_311.mdf' , 'D:\zrzjk\CWJJJG201311\371626_311_log.ldf'
sp_attach_db '371626_400', 'D:\zrzjk\CWJJJG201311\371626_400.mdf' , 'D:\zrzjk\CWJJJG201311\371626_400_log.ldf'
sp_attach_db '371626_500', 'D:\zrzjk\CWJJJG201311\371626_500.mdf' , 'D:\zrzjk\CWJJJG201311\371626_500_log.ldf'
sp_attach_db '371677_111', 'D:\zrzjk\CWJJJG201311\371677_111.mdf' , 'D:\zrzjk\CWJJJG201311\371677_111_log.ldf'
sp_attach_db '371677_200', 'D:\zrzjk\CWJJJG201311\371677_200.mdf' , 'D:\zrzjk\CWJJJG201311\371677_200_log.ldf'
sp_attach_db '371677_311', 'D:\zrzjk\CWJJJG201311\371677_311.mdf' , 'D:\zrzjk\CWJJJG201311\371677_311_log.ldf'
sp_attach_db '371677_400', 'D:\zrzjk\CWJJJG201311\371677_400.mdf' , 'D:\zrzjk\CWJJJG201311\371677_400_log.ldf'
sp_attach_db '371677_500', 'D:\zrzjk\CWJJJG201311\371677_500.mdf' , 'D:\zrzjk\CWJJJG201311\371677_500_log.ldf'
sp_attach_db '371688_111', 'D:\zrzjk\CWJJJG201311\371688_111.mdf' , 'D:\zrzjk\CWJJJG201311\371688_111_log.ldf'
sp_attach_db '371688_200', 'D:\zrzjk\CWJJJG201311\371688_200.mdf' , 'D:\zrzjk\CWJJJG201311\371688_200_log.ldf'
sp_attach_db '371688_311', 'D:\zrzjk\CWJJJG201311\371688_311.mdf' , 'D:\zrzjk\CWJJJG201311\371688_311_log.ldf'
sp_attach_db '371688_400', 'D:\zrzjk\CWJJJG201311\371688_400.mdf' , 'D:\zrzjk\CWJJJG201311\371688_400_log.ldf'
sp_attach_db '371688_500', 'D:\zrzjk\CWJJJG201311\371688_500.mdf' , 'D:\zrzjk\CWJJJG201311\371688_500_log.ldf'
sp_attach_db '371699_111', 'D:\zrzjk\CWJJJG201311\371699_111.mdf' , 'D:\zrzjk\CWJJJG201311\371699_111_log.ldf'
sp_attach_db '371699_200', 'D:\zrzjk\CWJJJG201311\371699_200.mdf' , 'D:\zrzjk\CWJJJG201311\371699_200_log.ldf'
sp_attach_db '371699_311', 'D:\zrzjk\CWJJJG201311\371699_311.mdf' , 'D:\zrzjk\CWJJJG201311\371699_311_log.ldf'
sp_attach_db '371699_400', 'D:\zrzjk\CWJJJG201311\371699_400.mdf' , 'D:\zrzjk\CWJJJG201311\371699_400_log.ldf'
sp_attach_db '371699_500', 'D:\zrzjk\CWJJJG201311\371699_500.mdf' , 'D:\zrzjk\CWJJJG201311\371699_500_log.ldf'
'371601_111', 'D:\zrzjk\CWJJJG201311\371601_111.mdf' , 'D:\zrzjk\CWJJJG201311\371601_111_log.ldf'
sp_attach_db '371601_200', 'D:\zrzjk\CWJJJG201311\371601_200.mdf' , 'D:\zrzjk\CWJJJG201311\371601_200_log.ldf'
sp_attach_db '371601_311', 'D:\zrzjk\CWJJJG201311\371601_311.mdf' , 'D:\zrzjk\CWJJJG201311\371601_311_log.ldf'
sp_attach_db '371601_400', 'D:\zrzjk\CWJJJG201311\371601_400.mdf' , 'D:\zrzjk\CWJJJG201311\371601_400_log.ldf'
sp_attach_db '371601_500', 'D:\zrzjk\CWJJJG201311\371601_500.mdf' , 'D:\zrzjk\CWJJJG201311\371601_500_log.ldf'
sp_attach_db '371602_111', 'D:\zrzjk\CWJJJG201311\371602_111.mdf' , 'D:\zrzjk\CWJJJG201311\371602_111_log.ldf'
sp_attach_db '371602_200', 'D:\zrzjk\CWJJJG201311\371602_200.mdf' , 'D:\zrzjk\CWJJJG201311\371602_200_log.ldf'
建议你先检查:1、路劲是否存在。或者sqlserver对齐有读写权限。
2、直接在SSMS中执行:sp_attach_db '371601_111', 'D:\zrzjk\CWJJJG201311\371601_111.mdf' , 'D:\zrzjk\CWJJJG201311\371601_111_log.ldf' 看看会不会报错
你试试把上面的这行代码:set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '改成:set @sql='exec sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
create proc [dbo].[createDbTable]
as
begin
declare @rc nvarchar(100),@dbname varchar(100),@dblogname varchar(100),@mdfPath varchar(100),@ldfPath varchar(100),@createDbSql varchar(1000)
declare @i int,@j int,@dbPath varchar(100),@kjqj varchar(50),@gsdm varchar(50),@zth varchar(50),@kjnd varchar(50),@rq varchar(50),@pznrSql varchar(1000)
declare @pzmlSql varchar(1000),@fzxlbSql varchar(1000),@fzxzlSql varchar(1000),@kmxxSql varchar(1000),@yebSql varchar(1000),@ztcsSql varchar(1000),@pubkszlSql varchar(1000),@pubzyxxSql varchar(1000)
declare mycs cursor FORWARD_ONLY for select distinct xzqh_bm from xzqh
set @i =( select count(*) from xzqh )
set @j=@i
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @rc='md D:\zrzjk\CWJJJG'+ @rq
set @dbPath='D:\zrzjk\CWJJJG'+ @rq +'\'
exec xp_cmdshell @rc
open mycs
fetch next from mycs into @dbname
set @dblogname=@dbname+'_log'
set @mdfPath=@dbPath + @dbname + '.mdf'
set @ldfPath=@dbPath + @dbname + '_log.ldf'
set @kjqj=@rq
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @createDbSql=
'CREATE DATABASE ' + @dbname + ' ON PRIMARY
( NAME= ''' + @dbname + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + @dblogname + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + @dbname + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pznrSql)
set @pzmlSql='select * into ' + @dbname + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + @dbname + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + @dbname + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + @dbname + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
/*此数据一般不做修改*/
set @fzxzlSql='select * into ' + @dbname + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + @dbname + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + @dbname + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + @dbname + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
while @i>1
begin
fetch next from mycs into @dbname
set @kjqj=@rq
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @dblogname=@dbname+'_log'
set @mdfPath=@dbPath + @dbname + '.mdf'
set @ldfPath=@dbPath + @dbname + '_log.ldf'
set @createDbSql=
'CREATE DATABASE ' + @dbname + ' ON PRIMARY
( NAME= ''' + @dbname + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + @dblogname + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + @dbname + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec(@pznrSql)
set @pzmlSql='select * into ' + @dbname + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + @dbname + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + @dbname + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + @dbname + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
set @fzxzlSql='select * into ' + @dbname + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + @dbname + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + @dbname + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + @dbname + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
set @i=@i-1
end
close mycs
DEALLOCATE mycs
end
( NAME= '371601_111',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_111.mdf',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= '371601_111_log',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_111_log.ldf',SIZE=1024KB,FILEGROWTH=10%)
select * into 371601_111.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth='111'
and kjqj<='201311' and gsdm='00100101'
select * into 371601_111.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth='111'
and kjqj<='201311' and gsdm='00100101'
select * into 371601_111.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh='111'
and kjnd<='2013' and hsdwdm='00100101'
select * into 371601_111.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth='111'
and kjnd<='2013' and gsdm='00100101'
select * into 371601_111.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<='2013' and gsdm='00100101'
select * into 371601_111.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm='00100101'
select * into 371601_111.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<='2013' and gsdm='00100101'
select * into 371601_111.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<='2013' and gsdm='00100101'
select * into 371601_111.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<='2013' and gsdm='00100101'
as
begin
declare @rc nvarchar(100),@dbname varchar(100),@dblogname varchar(100),@mdfPath varchar(100),@ldfPath varchar(100),@createDbSql varchar(1000)
declare @i int,@j int,@dbPath varchar(100),@kjqj varchar(50),@gsdm varchar(50),@zth varchar(50),@kjnd varchar(50),@rq varchar(50),@pznrSql varchar(1000)
declare @pzmlSql varchar(1000),@fzxlbSql varchar(1000),@fzxzlSql varchar(1000),@kmxxSql varchar(1000),@yebSql varchar(1000),@ztcsSql varchar(1000),@pubkszlSql varchar(1000),@pubzyxxSql varchar(1000)
declare mycs cursor FORWARD_ONLY for select distinct xzqh_bm from xzqh
set @i =( select count(*) from xzqh )
set @j=@i
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @rc='md D:\zrzjk\CWJJJG'+ @rq
set @dbPath='D:\zrzjk\CWJJJG'+ @rq +'\'
exec xp_cmdshell @rc
open mycs
fetch next from mycs into @dbname
set @dblogname=@dbname+'_log'
set @mdfPath=@dbPath + @dbname + '.mdf'
set @ldfPath=@dbPath + @dbname + '_log.ldf'
set @kjqj=@rq
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @createDbSql=
'CREATE DATABASE ' + QUOTENAME(@dbname) + ' ON PRIMARY
( NAME= ''' + QUOTENAME(@dbname) + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + QUOTENAME(@dblogname) + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pznrSql)
set @pzmlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
/*此数据一般不做修改*/
set @fzxzlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
while @i>1
begin
fetch next from mycs into @dbname
set @kjqj=@rq
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @dblogname=@dbname+'_log'
set @mdfPath=@dbPath + @dbname + '.mdf'
set @ldfPath=@dbPath + @dbname + '_log.ldf'
set @createDbSql=
'CREATE DATABASE ' + @dbname + ' ON PRIMARY
( NAME= ''' + @dbname + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + @dblogname + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec(@pznrSql)
set @pzmlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
set @fzxzlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
set @i=@i-1
end
close mycs
DEALLOCATE mycs
end
CREATE DATABASE 371601_311 ON PRIMARY
( NAME= '371601_311',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_311.mdf',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= '371601_311_log',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_311_log.ldf',SIZE=1024KB,FILEGROWTH=10%)
select * into [371601_311].dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth='311'
and kjqj<='201311' and gsdm='00100102'
select * into [371601_311].dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth='311'
and kjqj<='201311' and gsdm='00100102'
select * into [371601_311].dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh='311'
and kjnd<='2013' and hsdwdm='00100102'
select * into [371601_311].dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth='311'
and kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm='00100102'
select * into [371601_311].dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<='2013' and gsdm='00100102'
我执行print出来的代码,也报错消息 102,级别 15,状态 1,第 1 行
'371601' 附近有语法错误。
( NAME= '371601_311',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_311.mdf',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= '371601_311_log',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_311_log.ldf',SIZE=1024KB,FILEGROWTH=10%)这部分的NAME也用那个函数包住
不是已经包住了吗?'CREATE DATABASE ' + QUOTENAME(@dbname) + ' ON PRIMARY
( NAME= ''' + QUOTENAME(@dbname) + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + QUOTENAME(@dblogname) + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
print(@createDbSql)
不是已经包住了吗?'CREATE DATABASE ' + QUOTENAME(@dbname) + ' ON PRIMARY
( NAME= ''' + QUOTENAME(@dbname) + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + QUOTENAME(@dblogname) + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
print(@createDbSql)
正常出来的那个371601_111 这个是包住了,其他的都没有包住,为啥呢:
CREATE DATABASE [371601_111] ON PRIMARY
( NAME= '[371601_111]',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_111.mdf',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= '[371601_111_log]',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_111_log.ldf',SIZE=1024KB,FILEGROWTH=10%)
select * into [371601_111].dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth='111'
and kjqj<='201311' and gsdm='00100101'
select * into [371601_111].dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth='111'
and kjqj<='201311' and gsdm='00100101'
select * into [371601_111].dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh='111'
and kjnd<='2013' and hsdwdm='00100101'
select * into [371601_111].dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth='111'
and kjnd<='2013' and gsdm='00100101'
select * into [371601_111].dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<='2013' and gsdm='00100101'
select * into [371601_111].dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm='00100101'
select * into [371601_111].dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<='2013' and gsdm='00100101'
select * into [371601_111].dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<='2013' and gsdm='00100101'
select * into [371601_111].dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<='2013' and gsdm='00100101'
LOG ON
( NAME= '[371601_111_log]',FILENAME = 'D:\zrzjk\CWJJJG201311\371601_111_log.ldf',SIZE=1024KB,FILEGROWTH=10%)问题应该在这里
as
begin
declare @rc nvarchar(100),@dbname varchar(100),@dblogname varchar(100),@mdfPath varchar(100),@ldfPath varchar(100),@createDbSql varchar(1000)
declare @i int,@j int,@dbPath varchar(100),@kjqj varchar(50),@gsdm varchar(50),@zth varchar(50),@kjnd varchar(50),@rq varchar(50),@pznrSql varchar(1000)
declare @pzmlSql varchar(1000),@fzxlbSql varchar(1000),@fzxzlSql varchar(1000),@kmxxSql varchar(1000),@yebSql varchar(1000),@ztcsSql varchar(1000),@pubkszlSql varchar(1000),@pubzyxxSql varchar(1000)
declare mycs cursor FORWARD_ONLY for select distinct xzqh_bm from xzqh
set @i =( select count(*) from xzqh )
set @j=@i
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @rc='md D:\zrzjk\CWJJJG'+ @rq
set @dbPath='D:\zrzjk\CWJJJG'+ @rq +'\'
exec xp_cmdshell @rc
open mycs
fetch next from mycs into @dbname
set @dblogname=QUOTENAME(@dbname+'_log')
set @mdfPath=QUOTENAME(@dbPath + @dbname + '.mdf')
set @ldfPath=QUOTENAME(@dbPath + @dbname + '_log.ldf')
set @kjqj=@rq
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @createDbSql=
'CREATE DATABASE ' + QUOTENAME(@dbname) + ' ON PRIMARY
( NAME= ''' + QUOTENAME(@dbname) + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + QUOTENAME(@dblogname) + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pznrSql)
set @pzmlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
/*此数据一般不做修改*/
set @fzxzlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
while @i>1
begin
fetch next from mycs into @dbname
set @kjqj=@rq
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @dblogname=QUOTENAME(@dbname+'_log')
set @mdfPath=QUOTENAME(@dbPath + @dbname + '.mdf')
set @ldfPath=QUOTENAME(@dbPath + @dbname + '_log.ldf')
set @createDbSql=
'CREATE DATABASE ' + @dbname + ' ON PRIMARY
( NAME= ''' + QUOTENAME(@dbname) + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + QUOTENAME(@dblogname) + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec(@pznrSql)
set @pzmlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
set @fzxzlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
set @i=@i-1
end
close mycs
DEALLOCATE mycs
end
这些也不是我写的,别人写的,现在运行不了了,我都还没搞明白呢
还是不行
CREATE DATABASE 371601_311 ON PRIMARY 这里只有第一个会有中括号
但是第一个又开始出现新的错误了
CREATE DATABASE 371601_311 ON PRIMARY
( NAME= '[371601_311]',FILENAME = '[D:\zrzjk\CWJJJG201311\371601_311.mdf]',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= '[[371601_311_log]]',FILENAME = '[D:\zrzjk\CWJJJG201311\371601_311_log.ldf]',SIZE=1024KB,FILEGROWTH=10%)
select * into [371601_311].dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth='311'
and kjqj<='201311' and gsdm='00100102'
select * into [371601_311].dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth='311'
and kjqj<='201311' and gsdm='00100102'
select * into [371601_311].dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh='311'
and kjnd<='2013' and hsdwdm='00100102'
select * into [371601_311].dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth='311'
and kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm='00100102'
select * into [371601_311].dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<='2013' and gsdm='00100102'
select * into [371601_311].dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<='2013' and gsdm='00100102'
这个提示数据库371601_311不存在
你试试把上面的这行代码:set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '改成:set @sql='exec sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
谢谢,已经解决了