September 25, 2003 at 5:46 am
Hi!
After reading Kalen's Delaney article (SQL Server mag, September), trying to correct my code. Use
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)
instead of:
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)
get an error:
Server: Msg 156, Level 15, State 1, Procedure blocks_history, Line 172
Incorrect syntax near the keyword 'SELECT'.
Whats wrong I am doing?
Thanks.
September 26, 2003 at 12:12 am
Oops! I just need to add brackets:
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))
Edited by - Roust_m on 09/26/2003 12:16:29 AM
September 26, 2003 at 12:28 am
I am completely unaware of these function,
I mean how to use them and why?
Please give me some leads.
He who knows others is learned but the wise one is one who knows himself.
He who knows others is learned but the wise one is one who knows himself.
September 26, 2003 at 3:59 am
It is kind of analog to DBCC INPUTBUFFER
The difference is that this function returns the highest nest level, while DBCC INPUTBUFFER,- the lowest.
It came with sp3. You can find some info in BOL (sp3), by searching fn_get_sql in the index.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply