ALTER PROC dbo.proc_MovePost @servername sysname,@dt_begin datetime
AS
DECLARE @dt_end datetime,
@tbname sysname,
@s nvarchar(4000)
SELECT
@dt_end = DATEADD(Month, 1, @dt_begin),
@tbname = QUOTENAME('post_' + CONVERT(char(6), @dt_begin, 112))
set @s = N'
select refindkey,0 as deleted into #movepost from '+ @servername+ N'.DataScrapeDBlocal.dbo.post where dateofpost>=Cast(@dt_begin as NVARCHAR(10))and dateofpost<Cast(@dt_end as NVARCHAR(10))
update a set deleted=1 from #movepost a inner join '+ @tbname+ N' b on a.refindkey =b.refindkey where b.refindkey is not null
insert into '+ @tbname+ N'(refindkey,firstextractiondate,latestextractiondate,threadid,subject,content,dateofpost,itemurl,poster,siteid,forumid,istopicpost)
select a.refindkey,a.firstextractiondate,a.latestextractiondate,a.threadid,a.subject,a.content,a.dateofpost,a.itemurl,a.poster,a.siteid,a.forumid,a.istopicpost
from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null and b.deleted=0
delete from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null
drop table #movepost'
exec sp_executesql @s, N'@servername sysname,@dt_begin datetime, @dt_end datetime',@servername ,@dt_begin ,@dt_end GO
这个存储过程的问题是:
服务器: 消息 170,级别 15,状态 1,行 7
第 7 行: 'a' 附近有语法错误。
AS
DECLARE @dt_end datetime,
@tbname sysname,
@s nvarchar(4000)
SELECT
@dt_end = DATEADD(Month, 1, @dt_begin),
@tbname = QUOTENAME('post_' + CONVERT(char(6), @dt_begin, 112))
set @s = N'
select refindkey,0 as deleted into #movepost from '+ @servername+ N'.DataScrapeDBlocal.dbo.post where dateofpost>=Cast(@dt_begin as NVARCHAR(10))and dateofpost<Cast(@dt_end as NVARCHAR(10))
update a set deleted=1 from #movepost a inner join '+ @tbname+ N' b on a.refindkey =b.refindkey where b.refindkey is not null
insert into '+ @tbname+ N'(refindkey,firstextractiondate,latestextractiondate,threadid,subject,content,dateofpost,itemurl,poster,siteid,forumid,istopicpost)
select a.refindkey,a.firstextractiondate,a.latestextractiondate,a.threadid,a.subject,a.content,a.dateofpost,a.itemurl,a.poster,a.siteid,a.forumid,a.istopicpost
from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null and b.deleted=0
delete from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null
drop table #movepost'
exec sp_executesql @s, N'@servername sysname,@dt_begin datetime, @dt_end datetime',@servername ,@dt_begin ,@dt_end GO
这个存储过程的问题是:
服务器: 消息 170,级别 15,状态 1,行 7
第 7 行: 'a' 附近有语法错误。
解决方案 »
- 这么多'''''''怎么理解?
- 在递归公用表表达式 'sub_table' 的递归部分不允许使用外部联接
- MSSQL2005只有一个bak文件,能否恢复数据库??
- 请教:备份的日志文件为什么这么小??
- @avg_price1 和@avg_price 值为什么不一样
- 全文检索无法检索数字的内容吗?
- [SQL Server 2005]求助Row_Number
- 请问如何通过vb编程实现对sql server数据库的一个表或者是部分表进行备份?
- sqlserver存储过程问题[急]!
- VFP:在表的一字段"编号"中,上一记录为'G0056',如何在下一记录自动生成'G0057'?
- 字符串问题
- Sql server2000打了sp3补丁后,打补丁sp4后依然显示版本信息sp3,为何?
AS
DECLARE @dt_end datetime,
@tbname sysname,
@s nvarchar(4000)
SELECT
@dt_end = DATEADD(Month, 1, @dt_begin),
@tbname = QUOTENAME('post_' + CONVERT(char(6), @dt_begin, 112))
set @s = N'
select refindkey,0 as deleted into #movepost from '+ @servername+ N'.DataScrapeDBlocal.dbo.post where dateofpost>=Cast(@dt_begin as NVARCHAR(10))and dateofpost<Cast(@dt_end as NVARCHAR(10))
update a set deleted=1 from #movepost a inner join '+ @tbname+ N' b on a.refindkey =b.refindkey where b.refindkey is not null
insert into '+ @tbname+ N'(refindkey,firstextractiondate,latestextractiondate,threadid,subject,content,dateofpost,itemurl,poster,siteid,forumid,istopicpost)
select a.refindkey,a.firstextractiondate,a.latestextractiondate,a.threadid,a.subject,a.content,a.dateofpost,a.itemurl,a.poster,a.siteid,a.forumid,a.istopicpost
from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null and b.deleted=0
delete a from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null
drop table #movepost'
exec sp_executesql @s, N'@servername sysname,@dt_begin datetime, @dt_end datetime',@servername ,@dt_begin ,@dt_end GO
试一下
AS
DECLARE @dt_end datetime,
@tbname sysname,
@s nvarchar(4000)
SELECT
@dt_end = DATEADD(Month, 1, @dt_begin),
@tbname = QUOTENAME('post_' + CONVERT(char(6), @dt_begin, 112))
set @s = N'
select refindkey,0 as deleted into #movepost from '+ @servername+ N'.DataScrapeDBlocal.dbo.post where dateofpost>=Cast(@dt_begin as NVARCHAR(10))and dateofpost<Cast(@dt_end as NVARCHAR(10))
update a set deleted=1 from #movepost a inner join '+ @tbname+ N' b on a.refindkey =b.refindkey where b.refindkey is not null
insert into '+ @tbname+ N'(refindkey,firstextractiondate,latestextractiondate,threadid,subject,content,dateofpost,itemurl,poster,siteid,forumid,istopicpost)
select a.refindkey,a.firstextractiondate,a.latestextractiondate,a.threadid,a.subject,a.content,a.dateofpost,a.itemurl,a.poster,a.siteid,a.forumid,a.istopicpost
from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null and b.deleted=0
delete from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null
drop table #movepost'
exec sp_executesql @s, N'@servername sysname,@dt_begin datetime, @dt_end datetime',N'@dt_begin datetime,@dt_end datetime',@dt_begin ,@dt_end GO
exec sp_executesql @s, N'@servername sysname,@dt_begin datetime, @dt_end datetime',N'@dt_begin datetime,@dt_end datetime',@dt_begin ,@dt_end
应该是
exec sp_executesql @s, N'@dt_begin datetime,@dt_end datetime',@dt_begin ,@dt_end
ALTER PROC dbo.proc_MovePost @servername sysname,@dt_begin datetime
AS
DECLARE @dt_end datetime,
@tbname sysname,
@s nvarchar(4000)SELECT
@dt_end = DATEADD(Month, 1, @dt_begin),
@tbname = QUOTENAME('post_' + CONVERT(char(6), @dt_begin, 112))set @s = N'
select refindkey,0 as deleted into #movepost from '+ @servername+ N'.DataScrapeDBlocal.dbo.post where dateofpost>=''' + convert(NVARCHAR(10),@dt_begin,112) + ''' and dateofpost<''' + convert(NVARCHAR(10),@dt_end,112) +
+ ''' update a set deleted=1 from #movepost a inner join '+ @tbname+ N' b on a.refindkey =b.refindkey where b.refindkey is not null
insert into '+ @tbname+ N'(refindkey,firstextractiondate,latestextractiondate,threadid,subject,content,dateofpost,itemurl,poster,siteid,forumid,istopicpost)
select a.refindkey,a.firstextractiondate,a.latestextractiondate,a.threadid,a.subject,a.content,a.dateofpost,a.itemurl,a.poster,a.siteid,a.forumid,a.istopicpost
from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null and b.deleted=0
delete a from '+@servername+N'.DataScrapeDBlocal.dbo.post a inner join #movepost b on a.refindkey=b.refindkey where b.refindkey is not null
drop table #movepost'
exec @s
GO