SQL Server Performance

CASTING binary to char but NOT converting

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by SQL_Guess, Oct 9, 2006.

  1. SQL_Guess New Member

    Hi all,

    I'm trying to find a way to chagen a binary value into a char string with the SAME binary values, in a performant manner.

    In SQL 2000, it seems there was an undocumented XP_VarbinToHex extended stored proc. It seems that would be what I would need to do, except (a) it's an undocumented XP, so I won't really want to use it, (b) it doesn't seem to exist in SQL 2005, anyway.

    What I want to try and do is convert the data from:
    SELECT SQL_Handle FROM SYS.SYSPROCESSES
    into a char string, to enabled concatenation with a char string - something like:
    DECLARE @CharString Varchar(500)
    SET CharString = ''
    SELECT CharString ='The value in SQL_Handle is '+SQL_Handle FROM SYS.SYSPROCESSES

    What I am not trying to do, is a CONVERT or CAST...

    Ideas?

    Panic, Chaos, Disorder ... my work here is done --unknown
  2. FrankKalis Moderator

    Will this help?:


    SELECT text FROM ::fn_get_sql(@sql_handle)



    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. SQL_Guess New Member

    well, yes and no. I've been trying to find a way of doing this without doing a explicit loop (i.e. the while or a cursor).

    I have found a way to do this, but I'm not happy, since it involves using an undocumented function.



    --###################################################### describe script #############################################################################
    PRINT'>>> This script creates the Stored Procedure [dbo].[GetBlockedAndBlockingSQL] <<<'
    PRINT'>>> This execution on server: ['+@@SERVERNAME+'] started at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'
    PRINT''
    PRINT'>>> Create Objects <<<'
    PRINT''
    --################################################## Check and Drop Existing #########################################################################
    SET NOCOUNT ON
    USE Utilities

    -- Drop stored procedure if it already exists
    IF OBJECT_ID('dbo.GetBlockedAndBlockingSQL' ) IS NOT NULL BEGIN
    PRINT '--- Stored Procedure [dbo].[GetBlockedAndBlockingSQL] Exists and will be dropped and re-created ---'
    DROP PROCEDURE dbo.GetBlockedAndBlockingSQL
    END ELSE
    PRINT '--- Stored Procedure [dbo].[GetBlockedAndBlockingSQL] does not exist and will created ---'
    PRINT''
    GO

    --################################################## CREATE PROCEDURE ################################################################################

    CREATE PROCEDURE [dbo].GetBlockedAndBlockingSQL
    AS
    BEGIN--Procedure
    --################################################## COMMENTS ########################################################################################

    --Created By: Regan Galbraith
    --Created On: 2006-10-09
    --Version: 0.1 - 2006-10-09 - initial creation
    --SQL Version: 2005 SP1. Uses TRY/CATCH.

    --Overview:
    --System utility to tracking down problem SQL.
    --ToDo:

    --Sample Usage:
    --DECLARE
    --@RC int
    --
    --exec @rc=[dbo].GetBlockedAndBlockingSQL
    --if @Rc <> 0
    --print 'Failed - @rc = '+CONVERT(VARCHAR,@rc)
    --else
    --print 'Success'

    --################################################## DECLARE & SET VARIABLES #########################################################################
    SET NOCOUNT ON
    DECLARE
    @ErrorDescriptionVARCHAR(512),
    @ErrorINT,
    @RowCountINT,
    @ErrorProcedureSYSNAME,
    @ErrorLineINT,
    @PeriodEndDATETIME ,
    @PeriodStartDATETIME ,
    @FnGetSQLCMDVARCHAR(MAX) ,
    @OutputCountINT

    DECLARE @SysProcesses Table
    (spidsmallintNULL,
    blockedsmallintNULL,
    waittypebinary(2)NULL,
    waittimebigintNULL,
    lastwaittypenchar(256) NULL,
    waitresourcenchar(256) NULL,
    dbidsmallintNULL,
    cpuintNULL,
    physical_iobigintNULL,
    memusageintNULL,
    login_timedatetimeNULL,
    last_batchdatetimeNULL,
    open_transmallintNULL,
    statusnchar(60)NULL,
    hostnamenchar(256)NULL,
    program_namenchar(256)NULL,
    hostprocessnchar(20)NULL,
    cmdnchar(32)NULL,
    loginamenchar(256)NULL,
    sql_handlebinary(20)NULL,
    stmt_startintNULL,
    stmt_endintNULL)

    SET @FnGetSQLCMD = ''--Set '' to allow self-concatenation

    --################################################## Procedure Code Start ############################################################################
    -- Open a TRY block so that any errors with severity > 10 will be caught in a central place (the CATCH block below)
    BEGIN TRY

    --################################################## Pre-Contract Check ##########################################################################
    -- no inputs, so no contract check

    --################################################## Execute Code ################################################################################

    --Get snapshot of current processes
    INSERT INTO @SYSPROCESSES
    (spid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,cpu,physical_io,memusage,login_time,last_batch,open_tran,status
    ,hostname,program_name,hostprocess,cmd,loginame,sql_handle,stmt_start,stmt_end)
    SELECT spid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,cpu,physical_io,memusage,login_time,last_batch,open_tran,status
    ,hostname,program_name,hostprocess,cmd,loginame,sql_handle,stmt_start,stmt_end FROM SYS.SYSPROCESSES (NOLOCK) OPTION (MAXDOP 1)

    --Remove any rows that are not blocked, or causing blocking
    DELETE FROM @SYSPROCESSES WHERE blocked = 0 AND spid NOT IN (SELECT DISTINCT(blocked) FROM @SYSPROCESSES)

    --Generate commands for each of the entries involed in Blocking
    SELECT
    @FnGetSQLCMD=@FnGetSQLCMD+
    'SELECT COALESCE([text],''This May be an ENCRYPTED SP'') FROM sys.dm_exec_sql_text ('
    --::fn_get_sql
    + master.dbo.fn_varbintohexstr(sql_handle)--undocumented FN that gets Binary into a string format without CONVERTING
    +');'
    FROM @SysProcesses ORDER BY Blocked
    --Display list of Blocking chain details
    SELECT'>>> Blocking chain details SYS.SYSPROCESSES Details <<<'
    SELECT DB_NAME(DBID),* FROM @SysProcesses ORDER BY Blocked

    --Execute to get SQL for the SQL_Handles for entries in the blocking Chain
    EXEC (@FnGetSQLCMD)

    --################################################## Post-Contract Check #########################################################################
    --none - it is possible that there are 0 sales over the period

    SET @Error = 0 --Success
    END TRY

    -- Open a Catch block handle any errors with severity > 10 that were caught by the try block above
    BEGIN CATCH
    SELECT@ErrorProcedure= Routine_Schema + '.' + Routine_Name,
    @Error= ERROR_NUMBER(),
    @ErrorDescription= ERROR_MESSAGE(),
    @ErrorLine= ERROR_LINE()
    FROMINFORMATION_SCHEMA.ROUTINES
    WHERERoutine_Type = 'PROCEDURE' and Routine_Name = OBJECT_NAME(@@PROCID)

    RAISERROR('[Procedure:%s Line:%i Error:%i] %s',16,1,@ErrorProcedure,@ErrorLine,@Error,@ErrorDescription)
    END CATCH

    --################################################## Procedure Code End##############################################################################
    RETURN @Error
    END--Procedure
    GO

    --################################################## confirm creation ################################################################################

    IF OBJECT_ID ('.GetBlockedAndBlockingSQL' ) IS NOT NULL
    PRINT '--- Stored Procedure [dbo].[GetBlockedAndBlockingSQL] Created Successfully ---'
    ELSE
    PRINT '### ERROR : Stored Procedure [dbo].[GetBlockedAndBlockingSQL] does not exist ###'
    PRINT''

    --################################################## grant rights ####################################################################################
    GRANT Execute On [dbo].GetBlockedAndBlockingSQL to PUBLIC

    --###################################################### end script ##################################################################################
    PRINT'>>> This execution on server: ['+@@SERVERNAME+'] ended at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

    --Test Usage:
    exec [dbo].GetBlockedAndBlockingSQL


    Panic, Chaos, Disorder ... my work here is done --unknown
  4. SQL_Guess New Member

    Comments, improvement Welcome. Criticism accepted(... after all the practice I've had, I have to [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] )<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page