November 27, 2006 at 6:31 pm
Hi,
I am tryng to get rid of temp tables in my script, but start getting different results as below. Everything in the two versions of the script is identical, but the #b temp table got replaced by @b-2 table variable. What the problem could be?
Thanks.
-- temp table version:
-- Block_Report.sql
SET NOCOUNT ON
declare @cnt int,
@dt_from datetime,
@dt_to datetime
-- óñòàíîâèòå ïàðàìåòðû
select @dt_from = GetDate() - 7,
@dt_to = GetDate()
declare @blk table
(
dt smalldatetime not null,
spid smallint not null,
blocked smallint,
status varchar(100),
login varchar(100),
dbname varchar(100),
program varchar(100),
cmd varchar(100),
open_tran int,
txt nvarchar(256),
sql varchar(2000),
res nvarchar(256),
srt varchar(256)
--primary key (dt, spid)
)
-- Äîáàâëÿåì ïðîöåññû
insert @blk(
spid,
dt,
blocked,
status,
login,
dbname,
program,
cmd,
open_tran,
txt,
sql,
res,
srt)
select h.who_spid,
h.dt,
h.block_spid,
h.who_status,
h.who_login,
case
when h.who_dbid <> 0 then db_name(h.who_dbid)
else null
end,
h.who_program,
h.who_cmd,
h.who_open_tran,
h.who_EventInfo,
convert(varchar(2000), h.who_sql),
h.who_waitresource,
-- master.dbo.fn_get_object_name_by_key(h.who_waitresource),
null as srt
from UTIL..BLOCKS_HISTORY h
where h.dt between @dt_from and @dt_to
-- Äîáàâëÿåì ïðîöåññû, ó êîòîðûõ íåò ðîäèòåëÿ
select dt, block_spid, min(who_spid) as who_spid
into #b
from UTIL..BLOCKS_HISTORY h
where h.dt between @dt_from and @dt_to
and not exists (select * from @blk b where b.spid = h.block_spid and b.dt = convert(smalldatetime, h.dt))
group by dt, block_spid
select * from #b
insert @blk(
spid,
dt,
blocked,
status,
login,
dbname,
program,
cmd,
open_tran,
txt,
sql,
res,
srt)
select h.block_spid,
h.dt,
0,
h.block_status,
h.block_login,
case
when h.block_dbid <> 0 then db_name(h.who_dbid)
else null
end,
h.block_program,
h.block_cmd,
h.block_open_tran,
h.block_EventInfo,
convert(varchar(2000), h.block_sql),
h.block_waitresource,
-- master.dbo.fn_get_object_name_by_key(h.block_waitresource),
replace(str(h.block_spid, 4), ' ', '0') as srt
from UTIL..BLOCKS_HISTORY h
join #b b on b.dt = h.dt and b.who_spid = h.who_spid
-- Ñòðîèì äåðåâî
select @cnt = 0
while(@cnt < 20)
begin
update b1
set srt = b2.srt + ':' + replace(str(b1.spid, 4), ' ', '0')
from @blk b1
join @blk b2 on b2.spid = b1.blocked and b2.srt is not null
where b1.srt is null
if @@rowcount = 0 break
select @cnt = @cnt + 1
end
select dt,
spid,
blocked,
status,
login,
dbname,
program,
cmd,
open_tran,
res as lock_obj,
replace(replace(replace(txt, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as txt,
replace(replace(replace(sql, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as sql,
srt
from @blk
union all
select dt,
null,
null,
'Tree root',
null,
null,
null,
null,
null,
null,
null,
null,
srt
from @blk
where blocked = 0
order by dt, srt, spid
go
drop table #b
=============================================================================
-- table variable version:
-- Block_Report.sql
SET NOCOUNT ON
declare @cnt int,
@dt_from datetime,
@dt_to datetime
-- óñòàíîâèòå ïàðàìåòðû
select @dt_from = GetDate() - 7,
@dt_to = GetDate()
declare @blk table
(
dt smalldatetime not null,
spid smallint not null,
blocked smallint,
status varchar(100),
login varchar(100),
dbname varchar(100),
program varchar(100),
cmd varchar(100),
open_tran int,
txt nvarchar(256),
sql varchar(2000),
res nvarchar(256),
srt varchar(256)
--primary key (dt, spid)
)
declare @b-2 table
(
dt smalldatetime,
block_spid smallint,
who_spid smallint
)
-- Äîáàâëÿåì ïðîöåññû
insert @blk(
spid,
dt,
blocked,
status,
login,
dbname,
program,
cmd,
open_tran,
txt,
sql,
res,
srt)
select h.who_spid,
h.dt,
h.block_spid,
h.who_status,
h.who_login,
case
when h.who_dbid <> 0 then db_name(h.who_dbid)
else null
end,
h.who_program,
h.who_cmd,
h.who_open_tran,
h.who_EventInfo,
convert(varchar(2000), h.who_sql),
h.who_waitresource,
-- master.dbo.fn_get_object_name_by_key(h.who_waitresource),
null as srt
from UTIL..BLOCKS_HISTORY h
where h.dt between @dt_from and @dt_to
-- Äîáàâëÿåì ïðîöåññû, ó êîòîðûõ íåò ðîäèòåëÿ
insert into @b-2
select dt, block_spid, min(who_spid) as who_spid
from UTIL..BLOCKS_HISTORY h
where h.dt between @dt_from and @dt_to
and not exists (select * from @blk b where b.spid = h.block_spid and b.dt = convert(smalldatetime, h.dt))
group by dt, block_spid
select * from @b-2
insert @blk(
spid,
dt,
blocked,
status,
login,
dbname,
program,
cmd,
open_tran,
txt,
sql,
res,
srt)
select h.block_spid,
h.dt,
0,
h.block_status,
h.block_login,
case
when h.block_dbid <> 0 then db_name(h.who_dbid)
else null
end,
h.block_program,
h.block_cmd,
h.block_open_tran,
h.block_EventInfo,
convert(varchar(2000), h.block_sql),
h.block_waitresource,
-- master.dbo.fn_get_object_name_by_key(h.block_waitresource),
replace(str(h.block_spid, 4), ' ', '0') as srt
from UTIL..BLOCKS_HISTORY h
join @b-2 b on b.dt = h.dt and b.who_spid = h.who_spid
-- Ñòðîèì äåðåâî
select @cnt = 0
while(@cnt < 20)
begin
update b1
set srt = b2.srt + ':' + replace(str(b1.spid, 4), ' ', '0')
from @blk b1
join @blk b2 on b2.spid = b1.blocked and b2.srt is not null
where b1.srt is null
if @@rowcount = 0 break
select @cnt = @cnt + 1
end
select dt,
spid,
blocked,
status,
login,
dbname,
program,
cmd,
open_tran,
res as lock_obj,
replace(replace(replace(txt, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as txt,
replace(replace(replace(sql, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as sql,
srt
from @blk
union all
select dt,
null,
null,
'Tree root',
null,
null,
null,
null,
null,
null,
null,
null,
srt
from @blk
where blocked = 0
order by dt, srt, spid
go
=================================================================
-- Results from temp table version:
dt block_spid who_spid
------------------------------------------------------ ---------- --------
2006-11-25 06:00:04.383 53 55
2006-11-21 15:15:12.750 72 55
2006-11-21 15:19:04.887 72 13
2006-11-21 15:20:06.233 72 13
dt spid blocked status ...
------------------------------------------------------ ------ ------- --------- ...
2006-11-21 15:15:00 NULL NULL Tree root ...
2006-11-21 15:15:00 72 0 runnable ...
2006-11-21 15:15:00 55 72 sleeping ...
2006-11-21 15:19:00 NULL NULL Tree root ...
2006-11-21 15:19:00 72 0 runnable ...
2006-11-21 15:19:00 13 72 background ...
2006-11-21 15:20:00 NULL NULL Tree root ...
2006-11-21 15:20:00 72 0 runnable ...
2006-11-21 15:20:00 13 72 background ...
2006-11-24 09:04:00 59 60 sleeping ...
2006-11-24 09:04:00 60 59 sleeping ...
2006-11-25 06:00:00 NULL NULL Tree root ...
2006-11-25 06:00:00 53 0 sleeping ...
2006-11-25 06:00:00 55 53 sleeping ...
=================================================================
-- Results from table variable version:
dt block_spid who_spid
------------------------------------------------------ ---------- --------
2006-11-25 06:00:00 53 55
2006-11-21 15:15:00 72 55
2006-11-21 15:19:00 72 13
2006-11-21 15:20:00 72 13
dt spid blocked status ...
------------------------------------------------------ ------ ------- ---------- ...
2006-11-21 15:15:00 55 72 sleeping ...
2006-11-21 15:19:00 13 72 background ...
2006-11-21 15:20:00 13 72 background ...
2006-11-24 09:04:00 59 60 sleeping ...
2006-11-24 09:04:00 60 59 sleeping ...
2006-11-25 06:00:00 55 53 sleeping ...
November 27, 2006 at 9:50 pm
Just found it! The problem was with the smalldatetime column linking to another table having datetime type.
So if you change this:
declare @b-2 table
(
dt smalldatetime,
block_spid smallint,
who_spid smallint
)
To this:
declare @b-2 table
(
dt datetime,
block_spid smallint,
who_spid smallint
)
Everything works fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply