CASTING binary to char but NOT converting | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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
Will this help?:
SELECT text FROM ::fn_get_sql(@sql_handle) —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
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: [‘[email protected]@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
[email protected] 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
@[email protected]+
‘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
[email protected]= 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: [‘[email protected]@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
]]>