Unusual update
-
Hi!
I am using fn_get_sql() function to trace the code that causes blocking.
Recently I replaced this code:
UPDATE BLOCKS_HISTORY
SET who_sql = text
FROM BLOCKS_HISTORY, ::fn_get_sql(@who_sql_handle)
WHERE (dt=@dt) and (who_sql_handle=@who_sql_handle)
UPDATE BLOCKS_HISTORY
SET block_sql = text
FROM BLOCKS_HISTORY, ::fn_get_sql(@block_sql_handle)
WHERE (dt=@dt) and (block_sql_handle=@block_sql_handle)with this one:
IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@who_sql_handle))
UPDATE BLOCKS_HISTORY
SET who_sql = 'NOT AVAILABLE'
WHERE (dt=@dt) and (who_sql_handle=@who_sql_handle)
ELSE
UPDATE BLOCKS_HISTORY
SET who_sql = (SELECT SUBSTRING(text, (@who_stmt_start + 2)/2,
CASE @who_stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@who_stmt_end - @who_stmt_start +2)/2
END)
FROM ::fn_get_sql(@who_sql_handle)
WHERE (dt=@dt) and (who_sql_handle=@who_sql_handle))
IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@block_sql_handle))
UPDATE BLOCKS_HISTORY
SET block_sql = 'NOT AVAILABLE'
WHERE (dt=@dt) and (block_sql_handle=@block_sql_handle)
ELSE
UPDATE BLOCKS_HISTORY
SET block_sql = (SELECT SUBSTRING(text, (@block_stmt_start + 2)/2,
CASE @block_stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@block_stmt_end - @block_stmt_start +2)/2
END)
FROM ::fn_get_sql(@block_sql_handle)
WHERE (dt=@dt) and (block_sql_handle=@block_sql_handle))After such change, it seems like this update:
UPDATE BLOCKS_HISTORY
SET block_sql = (SELECT SUBSTRING(text, (@block_stmt_start + 2)/2,
CASE @block_stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@block_stmt_end - @block_stmt_start +2)/2
END)
FROM ::fn_get_sql(@block_sql_handle)
WHERE (dt=@dt) and (block_sql_handle=@block_sql_handle))turns all other rows on fields who_sql & block_sql to NULLs.
In other words, when updating the top row with valid values, it also updates all rows below with NULLs. Sounds crazy, but I cannot find another explanation.
What is wrong with this update?
The full code of my procedure is here:
http://roustam.hotbox.ru/procedure.html
/* would appriciate any suggestions on it */
Thanks.
Edited by - Roust_m on 09/30/2003 05:34:46 AM
Edited by - Roust_m on 09/30/2003 05:35:31 AM
-
This is all, cause I was blind!
I misplaced the braces:
IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@who_sql_handle))
UPDATE BLOCKS_HISTORY
SET who_sql = 'NOT AVAILABLE'
WHERE (dt=@dt) and (who_sql_handle=@who_sql_handle)
ELSE
UPDATE BLOCKS_HISTORY
SET who_sql = (SELECT SUBSTRING(text, (@who_stmt_start + 2)/2,
CASE @who_stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@who_stmt_end - @who_stmt_start +2)/2
END)
FROM ::fn_get_sql(@who_sql_handle))
WHERE (dt=@dt) and (who_sql_handle=@who_sql_handle)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply