CASTING binary to char but NOT converting

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

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

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