--起始准备
create table error_log(error_number int,error_message varchar(1000),error_procedure varchar(40),spid int,success char(1))
go
create table test2(id int identity(1,1),name char(4))
--1打开 ssms 建立一session1
语句如下
begin transcation
begin try
insert into test2(name) select 'test1'
waitdelay '00:00:10'
select * from test2 where name like 'test%'
insert into error_log(success) select 'Y',@@spid
commit
end try
begin catch
declare @error_log table(error_number int,error_message varchar(1000),error_procedure varchar(40),spid int,success char(1))
insert into @error_log select error_number(),error_message(),error_procedure(),@@spid,'N'
rollback
insert into error_log select * from @error_log
end catch
--1打开 ssms 建立一session2
语句如下
begin transcation
begin try
insert into test2(name) select 'test2'
waitdelay '00:00:10'
select * from test2 where name like 'test%'
insert into error_log(success) select 'Y',@@spid
commit
end try
begin catch
declare @error_log table(error_number int,error_message varchar(1000),error_procedure varchar(40),spid int,success char(1))
insert into @error_log select error_number(),error_message(),error_procedure(),@@spid,'N'
rollback
insert into error_log select * from @error_log
end catch
ps用表变量是为了防止insert into error_log 回滚掉。表变量不支持回滚。同时运行然后select * from error_logselect * from test2求助关于 死锁照成 牺牲死锁 以及死锁重做,try/catch 块的深入运用万分感谢!!!!
2>当死锁发生后,sql server会判断那个事务回滚的成本低,然后就回滚那个成本低的事务.该事务所在的进程就叫牺牲者进程.
3>死锁有个错误号(貌似是3605?),你可以用try/catch捕获这个错误号,然后进入相应的处理步骤.遗憾的是try/catch捕获不到该牺牲者进程是跟哪个进程,哪个事务发生死锁.要获得这些信息,只能在通常发生死锁的时间段启用跟踪.
xxxxx(2弄)(3路)xxx()xxx去掉()内的字符--2005
select * into ## from(select sring='xxxxx(2弄)(3路)xxx()xxx' union all select 'dad(0d)da(da)da()ss')x--cte+nums 解决
with xwj
as
(select id=1
union all
select id+1 from xwj where id<=80
) ,
xwj2
as
(
select sring,id ,substring(sring,id,charindex(')',sring+')',id)-id+1)as sonsring
from ## as a inner join xwj as b on len(a.sring)>=id and substring(')'+sring,id,1)=')'
)
,
xwj3
as
(
select sring,id,px=row_number()over(partition by sring order by id),
substring(sonsring,1,charindex('(',sonsring)) +substring(sonsring,case when charindex(')',sonsring)=0 then 1 else charindex(')',sonsring)end ,len(sonsring)) as sonsring
from xwj2
)
,
xwj4
as
(select sring,px,cast(sonsring as varchar(500)) as sonsring from xwj3 where px=1
union all
select b.sring,b.px,cast(a.sonsring+b.sonsring as varchar(500)) from xwj4 as a inner join xwj3 as b on a.sring=b.sring and b.px=a.px+1
)
select sonsring as sring from xwj4 as a where px=(select max(px) from xwj3 as b where a.sring=b.sring)--xml+cte
select * from ##with
xwj
as
(
select x.sring,px=row_number()over(partition by sring order by (select 1)),
substring(v,1,charindex('(',v)) +substring(v,case when charindex(')',v)=0 then 1 else charindex(')',v)end ,len(v)) as sonsring
from
(select sring,cast('<row>'+replace(sring,')',')</row><row>')+'</row>' as xml) as sonsring
from ## )x
cross apply
(select v=t.c.value('.','sysname')
from x.sonsring.nodes('row') as t(c)
) as b
)
,
xwj2
as
(select sring,px,cast(sonsring as varchar(500)) as sonsring from xwj where px=1
union all
select b.sring,b.px,cast(a.sonsring+b.sonsring as varchar(500)) from xwj2 as a inner join xwj as b on a.sring=b.sring and a.px=b.px-1
)
select sonsring as sring from xwj2 as b where px=(select max(px) from xwj as a where a.sring=b.sring)
ps:
小技巧 如果想根据物理顺序来 排序 2005 可以 用 row_number()over(partition by column_name order by (select 1))
(select 1)
小技巧
避免用 identity 带来的麻烦
整天瞎忙
发现 ssis 比ssrs有意思 嘿嘿
ssis ssrs 皮毛
还得感谢裁掉公司的老大
没他我也不玩着2个
带了个基本原理。