Decoding hexa error code to windows error message in SQL Server

Problem Statement:
Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be an extra effort to google out for most of us with the error code (mostly a hexa value) and identify the corresponding windows error message.

Solution:
A simple solution would be to use NET HELPMSG with the errocode. But if the error code is a hexa value, then we need to identify the corresponding Decimal value to use as a parameter to NET HELPMSG.

Here is a script to identify actual error message from hex error code. This is important to be aware that this approach will only work for Win32 error codes from Microsoft Windows. If the net helpmsg command does not return a result, the cause is likely the error has not originated from Windows, or no longer a valid Win32 process.

DECLARE @hex VARCHAR(64) = '0x80070643'
Set @hex = Right(@hex,4)

DECLARE @rez BIGINT;
SELECT @rez = ISNULL(@rez,0) * 16 + 
CHARINDEX(substring(@hex,n.number+1,1),'0123456789ABCDEF') - 1
FROM MASTER..spt_values n WHERE n.TYPE='P' AND n.number<len(@hex)

/*
	--To use NET HELPMSG, need to enable xp_cmdshell as below:

	EXEC sp_configure 'show advanced options',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'xp_cmdshell',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'show advanced options',0
	RECONFIGURE WITH OVERRIDE
*/

Declare @Table Table(WindowsErrorMessage nVarchar(MAX))
Declare @s nvarchar(500) = 'NET HELPMSG ' + Cast(@rez as varchar(MAX))
Insert into @Table 
exec xp_cmdshell @s

Select @hex HexaVal,@rez DecimalVal,* From @Table 
where WindowsErrorMessage is not null
Advertisements

How to list file names in a folder in SQL Server

Here is a very small script to list the file names in a folder using SQL Server.

The below snippet has been useful for us to automate the restore process from a defined path(for large number of backup files).


/**********************************************************************/
	DECLARE @COMMANDSHELL TABLE ( LINE VARCHAR(512)) 
	DECLARE  @CMD VARCHAR(512) ,@BACKUPFILEPATH NVARCHAR(256)
	SET @BACKUPFILEPATH = 'D:\DATABASES\BACKUPS\' 
	SET @CMD = 'DIR /B ' + @BACKUPFILEPATH +  ' /TC' 
				
	INSERT INTO @COMMANDSHELL EXEC MASTER..XP_CMDSHELL   @CMD 

	--Data Clean up 
	DELETE FROM   @COMMANDSHELL WHERE  LINE IS NULL

	SELECT* FROM @COMMANDSHELL A
/**********************************************************************/

Prerequisites: You may need to enable the xp_cmdshell using sp_configure as below:


--To Enable the advanced Options
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE WITH OVERRIDE

--To Enable XP_CmdShell 
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE WITH OVERRIDE

--To Disable the advanced Options(by default)
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0
RECONFIGURE WITH OVERRIDE