提示错误是:Microsoft][ODBC SQL Server Driver][SQL Server] Lock Request time out period exceeded.
解决方案 »
- 存储过程执行
- 求sql 根据某字段中的值(int型)X 查询X条数据
- 主A机与备份B机上都安装有sql server 2000,要实现数据实时备份到B机上,要怎么设置处理?
- UPDATE T_FinalAccounts SET FullBudget = FullBudget + NULL 把我以前的记录都变为NULL了,大哥救命呀?
- 提问! 求语句!
- sql统计
- 高手们来看看这个procedure怎么写?
- 求sql查询
- update sysprocesses
- UNION如何用啊?
- 视图中连接大量的表而且数据量超大的问题 --------在线求救
- sql server 连接的奇怪问题,IP不行,但计算机名可以
IF object_id('f_cid') IS not null DROP function f_ciddeclare @str1 varchar(1000) set @str1 ='CREATE function f_cid(
@id int
)returns @re table( OrganizationCode int,[level] int,Closed tinyint)
as
begin
declare @l int
set @l=0
insert @re select OrganizationCode,@l,Closed from dbE.dbo.MstOrganization where ParentOrg=@id and Closed=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.OrganizationCode,@l ,a.Closed
from dbE.dbo.MstOrganization a,@re b
where a.ParentOrg=b.OrganizationCode and b.[level]=@l-1 and a.Closed=0
end
return
end 'exec (@str1)IF object_id('tempdb..#BB') IS not null DROP TABLE #BB
CREATE table #BB(OrganizationCode int, SonOrg int,Closed tinyint)DECLARE tnames_cursor CURSOR
FOR
SELECT OrganizationCode
FROM dbE.dbo.MstOrganization
where OrganizationCode=@org and Closed=0
ORDER BY OrganizationCode
OPEN tnames_cursor
DECLARE @cur1 INT
FETCH NEXT FROM tnames_cursor INTO @cur1
WHILE(@@fetch_status=0)
BEGIN INSERT #BB
SELECT @cur1,
a.OrganizationCode,
a.Closed
FROM dbE.dbo.MstOrganization a,f_cid(@cur1) b
WHERE a.OrganizationCode=b.OrganizationCode
FETCH NEXT FROM tnames_cursor INTO @cur1
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursorINSERT #BB
SELECT OrganizationCode,OrganizationCode AS SonOrg ,Closed
FROM dbE.dbo.MstOrganization
where OrganizationCode=@org
and
Closed=0IF object_id('tempdb..#tempUpdate') IS not null DROP TABLE #tempUpdateselect #BB.SonOrg
into #tempUpdate
from #BB
where exists(
select 1 from OrganizationTOP_exempt_data
where #BB.SonOrg=OrganizationTOP_exempt_data.OrganizationCode
and
OrganizationTOP_exempt_data.Year=@year
and
OrganizationTOP_exempt_data.Week=@week
and
OrgHierarchyCode=@orgH
)DECLARE orgCd_cursor CURSOR
FOR
SELECT SonOrg FROM #tempUpdate
OPEN orgCd_cursor
DECLARE @cur2 INT
FETCH NEXT FROM orgCd_cursor INTO @cur2
WHILE(@@fetch_status=0)
BEGIN
update OrganizationTOP_exempt_data
set
--FactTime = @ft,
ChangedTime=@chTime,
DeletedDivision=@delDiv, Deleted=0,Reason=@R,
AmendManagerID=@MngID,
AmendTime=@manTime
where OrganizationTOP_exempt_data.OrganizationCode =@cur2
and
OrganizationTOP_exempt_data.Year=@year
and
OrganizationTOP_exempt_data.Week=@week
and
OrgHierarchyCode=@orgHFETCH NEXT FROM orgCd_cursor INTO @cur2
END
CLOSE orgCd_cursor
DEALLOCATE orgCd_cursorinsert into dbo.OrganizationTOP_exempt_data(
OrgHierarchyCode,
OrganizationCode,
Year,
Week,
ChangedTime,
Reason,
DeletedDivision,
Deleted,
RegistManagerID,
RegistTime
)
SELECT
@orgH,
#BB.SonOrg,
@year,
@week,
@chTime,
@R ,
@delDiv,
0,
@MngID,
@manTime
FROM #BB
where not exists(
select 1 from OrganizationTOP_exempt_data
where #BB.SonOrg=OrganizationTOP_exempt_data.OrganizationCode
and
OrganizationTOP_exempt_data.Year=@year
and
OrganizationTOP_exempt_data.Week=@week
and
OrgHierarchyCode=1
)
IF @@ERROR < >0 goto ERRHUNDLER --事务会滚----------------------------
中间也用了很多游标和临时表,形式和上面用到的差不多,就省略了。。
------------------------------提交事务
COMMIT TRANSACTION
RETURN 0--出错处理
ERRHUNDLER: ROLLBACK TRANSACTION
RETURN @@ERROR