SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting a varchar to int


Converting a varchar to int

Author
Message
spyfly
spyfly
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 48
I am running the query below but I am getting an error "Conversion failed when converting the varchar value "T" to data type int"

Here is the query what am I missing? I also included the functions that I am using

/* APC UPS State UnDP pollers
APCupsAdvStateAbnormalConditions
Status Byte 1 = 'Power Module Failure, '
Status Byte 2 = 'Main Intelligence Module Failure, '
Status Byte 3 = 'Redundant Intelligence Module Failure, '
Status Byte 4 = 'Battery Failure, '
Status Byte 5 = 'Load(kVA) Alarm Threshold Violation, '
Status Byte 6 = 'Redundancy Lost, '
Status Byte 7 = 'Redundancy Below Alarm Threshold, '
Status Byte 8 = 'Bypass notin Range; Either Frequency or Voltage, '
Status Byte 9 = 'Bypass Contactor Stuck in Bypass Condition, '
Status Byte 10 = 'Bypass Contactor Stuck in On-Line Condition, '
Status Byte 11 = 'In Bypass due to an Internal Fault, '
Status Byte 12 = 'In Bypass due to an Overload, '
Status Byte 13 = 'In Maintanence Bypass, '
Status Byte 14 = 'Input Circuit Braker Tripped Open, '
Status Byte 15 = 'System Level Fan Failure, '
Status Byte 16 = 'Redundant Intelligent Module in Control, '
Status Byte 17 = 'IIC Inter-Module Communication Failure, '
Status Byte 18 = 'No Working Power Modules, '
Status Byte 19 = 'Load Shutdown From Bypass; Input Frequency, '
Status Byte 20 = 'Runtime Below Alarm Threshold, '
Status Byte 21 = 'Extended Run Frame Fault, '
Status Byte 22 = 'Output Voltage out of Range, '
Status Byte 23 = 'UPS Not Synchronized, '
Status Byte 24 = 'No Batteries Installed, '
Status Byte 25 = 'Battery Voltage High, '
Status Byte 26 = 'UPS Specific Fault Detected, '
Status Byte 27 = 'Site Wiring Fault, '
Status Byte 28 = 'Backfeed Protection Relay Opened, '
*/
/*
APCupsBasicStateOutputState
Status Byte 1 = 'Abnormal Condition Present, '
Status Byte 2 = 'On Battery, '
Status Byte 3 = 'Low Battery, '
Status Byte 4 = '*online*, '
Status Byte 5 = 'Replace Battery, '
Status Byte 6 = '*Serial Communication Established*, '
Status Byte 7 = 'AVR Boost Active*, '
Status Byte 8 = 'AVR Trim Active*, '
Status Byte 9 = 'Overload, '
Status Byte 10 = 'Runtime Calibration, '
Status Byte 11 = 'Batteries Discharged, '
Status Byte 12 = 'Manual Bypass, '
Status Byte 13 = 'Software Bypass, '
Status Byte 14 = 'In Bypass due to Internal Fault, '
Status Byte 15 = 'In Bypass due to Supply Failure*, '
Status Byte 16 = 'In Bypass due to Fan Failure*, '
Status Byte 17 = 'Sleeping on a Timer, '
Status Byte 18 = 'Sleeping until Utility Power Returns, '
Status Byte 19 = ''*On*, '
Status Byte 20 = 'Rebooting, '
Status Byte 21 = 'Battery Communication Lost*, '
Status Byte 22 = 'Graceful Shutdown Initiated, '
Status Byte 23 = 'Smart Boost or Smart Trim Fault, '
Status Byte 24 = 'Bad Output Voltage*, '
Status Byte 25 = 'Battery Charger Failure*, '
Status Byte 26 = 'High Battery Temperature, '
Status Byte 27 = 'Warning Battery Temperature, '
Status Byte 28 = 'Critical Battery Temperature, '
Status Byte 29 = 'Self Test In Progress, '
Status Byte 30 = 'Low Battery / On Battery, '
Status Byte 31 = 'Graceful Shutdown Issued by Upstream Device, '
Status Byte 32 = 'Graceful Shutdown Issued by Downstream Device, '
Status Byte 33 = 'No Batteries Attached, '
Status Byte 34 = 'Synchronized command is in progress, '
Status Byte 35 = 'Synchronized Sleeping Command is in Progress, '
Status Byte 36 = 'Synchronized Rebooting Command is in Progress, '
Status Byte 37 = 'Inverter DC Imbalance, '
Status Byte 38 = 'Transfer Relay Failure, '
Status Byte 39 = 'Shutdown or Unable to Transfer, '
Status Byte 40 = 'Low Battery Shutdown, '
Status Byte 41 = 'Electronic Unit Fan Failure, '
Status Byte 42 = 'Main Relay Failure, '
Status Byte 43 = 'Bypass Relay Failure, '
Status Byte 44 = 'Temporary Bypass, '
Status Byte 45 = 'High Internal Temperature'
Status Byte 46 = 'Battery Temperature Sensor Fault, '
Status Byte 47 = 'Input Out of Range for Bypass, '
Status Byte 48 = 'DC Bus Overvoltage, '
Status Byte 49 = 'PFC Failure, '
Status Byte 50 = 'Critical Hardware Fault, '
Status Byte 51 = 'Green Mode, '
Status Byte 52 = 'Flag-52 set, '
Status Byte 53 = 'Flag-53 set, '
Status Byte 54 = 'Flag-54 set, '
Status Byte 55 = 'Flag-55 set, '
Status Byte 56 = 'Flag-56 set, '
Status Byte 57 = 'Flag-57 set, '
Status Byte 58 = 'Flag-58 set'
Status Byte 59 = 'Flag-59 set, '
Status Byte 60 = 'Flag-60 set, '
Status Byte 61 = 'Flag-61 set, '
Status Byte 62 = 'Flag-62 set, '
Status Byte 63 = 'Flag-63 set, '
Status Byte 64 = 'Flag-64 set, '
*/
SELECT Nodes.Caption AS Node_Name, Nodes.NodeID AS NodeID,
CustomPollerStatus3.Status as Model,
dbo.MDOT_upsAdvStateAbnormalConditions_FlagParser(CustomPollerStatus.Status) as AbnormalConditions,
dbo.MDOT_APCupsBasicStateOutputState_FlagParser(CustomPollerStatus1.Status) as APCupsBasicStateOutputState,
CustomPollerStatus2.Status as TestDiagnosticsResults
FROM Nodes
JOIN CustomPollerAssignment
ON (CustomPollerAssignment.NodeId = Nodes.NodeId )
JOIN CustomPollers
ON (CustomPollers.CustomPollerId = CustomPollerAssignment.CustomPollerId AND CustomPollers.UniqueName = 'APCupsAdvStateAbnormalConditions')
LEFT JOIN CustomPollerStatus
ON (CustomPollerStatus.CustomPollerAssignmentId = CustomPollerAssignment.CustomPollerAssignmentId)
JOIN CustomPollerAssignment AS CustomPollerAssignment1
ON (CustomPollerAssignment1.NodeId = Nodes.NodeId )
JOIN CustomPollers AS CustomPollers1
ON (CustomPollers1.CustomPollerId = CustomPollerAssignment1.CustomPollerId AND CustomPollers1.UniqueName = 'APCupsBasicStateOutputState')
LEFT JOIN CustomPollerStatus AS CustomPollerStatus1
ON (CustomPollerStatus1.CustomPollerAssignmentId = CustomPollerAssignment1.CustomPollerAssignmentId)
JOIN CustomPollerAssignment AS CustomPollerAssignment2
ON (CustomPollerAssignment2.NodeId = Nodes.NodeId )
JOIN CustomPollers AS CustomPollers2
ON (CustomPollers2.CustomPollerId = CustomPollerAssignment2.CustomPollerId AND CustomPollers2.UniqueName = 'APcupsAdvTestDiagnosticsResults')
LEFT JOIN CustomPollerStatus AS CustomPollerStatus2
ON (CustomPollerStatus2.CustomPollerAssignmentId = CustomPollerAssignment2.CustomPollerAssignmentId)
JOIN CustomPollerAssignment AS CustomPollerAssignment3
ON (CustomPollerAssignment3.NodeId = Nodes.NodeId )
JOIN CustomPollers AS CustomPollers3
ON (CustomPollers3.CustomPollerId = CustomPollerAssignment3.CustomPollerId AND CustomPollers3.UniqueName = 'APCupsBasicIdentModel')
LEFT JOIN CustomPollerStatus AS CustomPollerStatus3
ON (CustomPollerStatus3.CustomPollerAssignmentId = CustomPollerAssignment3.CustomPollerAssignmentId)
WHERE (Nodes.Status = '1')
AND
(
(CustomPollerStatus1.Status <> '0001010000000000000000000000000000000000000000000000000000000000'
AND CustomPollerStatus1.Status <> '0001010000000000001000000000000000000000000000000000000000000000'
AND CustomPollerStatus1.Status <> '0001010000000000001000000000000000000000000000000010000000000000'
AND CustomPollerStatus1.Status <> '0000000000000000000000000000000000000000000000000000000000000000')
OR (CustomPollerStatus2.Status = 'failed(2)'
OR CustomPollerStatus2.Status = 'invalidTest(3)')
)
ORDER by 1 ASC


USE [NetPrefMon]
GO
/****** Object: UserDefinedFunction [dbo].[MDOT_upsAdvStateAbnormalConditions_FlagParser] Script Date: 11/29/2017 8:28:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Spyfly>
-- Create date: <Nov. 11, 2017>
-- Description: <MDOT_upsAdvStateAbnormalConditions_FlagParser>
-- =============================================
ALTER function [dbo].[MDOT_upsAdvStateAbnormalConditions_FlagParser]
(
@val as varchar(33)
)
returns varchar(1000)
as
Begin
DECLARE @counter as int;
DECLARE @flaglist as varchar(33);
DECLARE @flagpos as int;
DECLARE @varlist as varchar(1000);
SET @counter = 0;
IF @val LIKE 'NOT SUPPORTED%'
Return '';
SET @flaglist = @val;
SET @flagpos = 0;
SET @varlist = '';
while @counter < 32
Begin
set @varlist = @varlist +CASE
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 1 THEN 'Power Module Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 2 THEN 'Main Intelligence Module Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 3 THEN 'Redundant Intelligence Module Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 4 THEN 'Battery Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 5 THEN 'Load(kVA) Alarm Threshold Violation, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 6 THEN 'Redundancy Lost, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 7 THEN 'Redundancy Below Alarm Threshold, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 8 THEN 'Bypass notin Range; Either Frequency or Voltage, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 9 THEN 'Bypass Contactor Stuck in Bypass Condition, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 10 THEN 'Bypass Contactor Stuck in On-Line Condition, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 11 THEN 'In Bypass due to an Internal Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 12 THEN 'In Bypass due to an Overload, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 13 THEN 'In Maintanence Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 14 THEN 'Input Circuit Braker Tripped Open, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 15 THEN 'System Level Fan Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 16 THEN 'Redundant Intelligent Module in Control, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 17 THEN 'IIC Inter-Module Communication Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 18 THEN 'No Working Power Modules, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 19 THEN 'Load Shutdown From Bypass; Input Frequency, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 20 THEN 'Runtime Below Alarm Threshold, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 21 THEN 'Extended Run Frame Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 22 THEN 'Output Voltage out of Range, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 23 THEN 'UPS Not Synchronized, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 24 THEN 'No Batteries Installed, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 25 THEN 'Battery Voltage High, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 26 THEN 'UPS Specific Fault Detected, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 27 THEN 'Site Wiring Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 28 THEN 'Backfeed Protection Relay Opened, '
Else ''
End
set @counter = @counter + 1
set @flagpos = @flagpos + 1
End
return substring(@varlist,0,len(@varlist))
End


USE [SolarWindsOrion]
GO
/****** Object: UserDefinedFunction [dbo].[MDOT_APCupsBasicStateOutputState_FlagParser] Script Date: 11/29/2017 8:31:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Spyfly>
-- Create date: <Nov. 11, 2017>
-- Description: <MDOT_APCupsBasicStateOutputState_FlagParser>
-- =============================================
ALTER function [dbo].[MDOT_APCupsBasicStateOutputState_FlagParser]
(
@val as varchar(64)
)
returns varchar(1000)
as
Begin
DECLARE @counter as int;
DECLARE @flaglist as varchar(64);
DECLARE @flagpos as int;
DECLARE @varlist as varchar(1000);
SET @counter = 0;
SET @flaglist = @val;
SET @flagpos = 0;
SET @varlist = '';
while @counter < 64
Begin
set @varlist = @varlist + CASE
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 1 THEN 'Abnormal Condition Present, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 2 THEN 'On Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 3 THEN 'Low Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 4 THEN '' /*online*/
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 5 THEN 'Replace Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 6 THEN '' /*Serial Communication Established*/
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 7 THEN 'AVR Boost Active*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 8 THEN 'AVR Trim Active*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 9 THEN 'Overload, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 10 THEN 'Runtime Calibration, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 11 THEN 'Batteries Discharged, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 12 THEN 'Manual Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 13 THEN 'Software Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 14 THEN 'In Bypass due to Internal Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 15 THEN 'In Bypass due to Supply Failure*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 16 THEN 'In Bypass due to Fan Failure*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 17 THEN 'Sleeping on a Timer, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 18 THEN 'Sleeping until Utility Power Returns, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 19 THEN '' /*On*/
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 20 THEN 'Rebooting, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 21 THEN 'Battery Communication Lost*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 22 THEN 'Graceful Shutdown Initiated, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 23 THEN 'Smart Boost or Smart Trim Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 24 THEN 'Bad Output Voltage*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 25 THEN 'Battery Charger Failure*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 26 THEN 'High Battery Temperature, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 27 THEN 'Warning Battery Temperature, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 28 THEN 'Critical Battery Temperature, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 29 THEN 'Self Test In Progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 30 THEN 'Low Battery / On Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 31 THEN 'Graceful Shutdown Issued by Upstream Device, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 32 THEN 'Graceful Shutdown Issued by Downstream Device, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 33 THEN 'No Batteries Attached, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 34 THEN 'Synchronized command is in progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 35 THEN 'Synchronized Sleeping Command is in Progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 36 THEN 'Synchronized Rebooting Command is in Progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 37 THEN 'Inverter DC Imbalance, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 38 THEN 'Transfer Relay Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 39 THEN 'Shutdown or Unable to Transfer, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 40 THEN 'Low Battery Shutdown, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 41 THEN 'Electronic Unit Fan Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 42 THEN 'Main Relay Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 43 THEN 'Bypass Relay Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 44 THEN 'Temporary Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 45 THEN 'High Internal Temperature'
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 46 THEN 'Battery Temperature Sensor Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 47 THEN 'Input Out of Range for Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 48 THEN 'DC Bus Overvoltage, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 49 THEN 'PFC Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 50 THEN 'Critical Hardware Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 51 THEN 'Green Mode, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 52 THEN 'Flag-52 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 53 THEN 'Flag-53 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 54 THEN 'Flag-54 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 55 THEN 'Flag-55 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 56 THEN 'Flag-56 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 57 THEN 'Flag-57 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 58 THEN 'Flag-58 set'
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 59 THEN 'Flag-59 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 60 THEN 'Flag-60 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 61 THEN 'Flag-61 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 62 THEN 'Flag-62 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 63 THEN 'Flag-63 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 64 THEN 'Flag-64 set, '
Else ''
End
set @counter = @counter + 1
set @flagpos = @flagpos + 1
End
return substring(@varlist,0,len(@varlist))
End

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94272 Visits: 20685
The first thing you are missing is most likely within the data, my guess would be incompatible join columns. The next thing would be properly handling the bit-mask data, much more efficiently done using bitwise logic rather than an iteration. The last thing would be posting a portion of the data, including the part causing the error. Without that, we are only guessing Wink
Cool
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97224 Visits: 20698
Check your data:

SELECT [Status]

FROM CustomPollerStatus

WHERE [Status] LIKE '%T%'

Your @flaglist variable is varchar but you compare it (or rather, a substring of it) to a numeric:
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 31 THEN 'Graceful Shutdown Issued by Upstream Device, '

You could change that to a varchar too:
WHEN SUBSTRING(@flaglist,@flagpos,1) = '1' and @counter = 31 THEN 'Graceful Shutdown Issued by Upstream Device, '



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search