Invalid length parameter passed to the RIGHT function

Hello Guys,

I'm trying get processor information using the following query:

DECLARE @xp_msver TABLE (
    [idx] [int] NULL
    ,[c_name] [varchar](100) NULL
    ,[int_val] [float] NULL
    ,[c_val] [varchar](128) NULL
    )

INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;

WITH [ProcessorInfo]
AS (
    SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
        ,CASE
            WHEN hyperthread_ratio = cpu_count
                THEN cpu_count
            ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            END AS [number_of_cores_per_cpu]
        ,CASE
            WHEN hyperthread_ratio = cpu_count
                THEN cpu_count
            ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            END AS [total_number_of_cores]
        ,[cpu_count] AS [number_of_virtual_cpus]
        ,(
            SELECT [c_val]
           FROM @xp_msver
            WHERE [c_name] = 'Platform'
            ) AS [cpu_category]
    FROM [sys].[dm_os_sys_info]
    )
SELECT [number_of_physical_cpus]
    ,[number_of_cores_per_cpu]
    ,[total_number_of_cores]
    ,[number_of_virtual_cpus]
    ,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]

Open in new window


It's working on some of the servers, but on some servers I'm getting the following error:

Msg 536, Level 16, State 2, Line 11
Invalid length parameter passed to the RIGHT function.

Kindly, look into this matter and reply as soon as possible.

Thank You
Abhinav SinghSoftware EngineerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
...
SELECT [number_of_physical_cpus]
    ,[number_of_cores_per_cpu]
    ,[total_number_of_cores]
    ,[number_of_virtual_cpus]
    ,SUBSTRING(cpu_category, PATINDEX('%[0-9]%', cpu_category), 2) AS [cpu_category]
FROM [ProcessorInfo]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1))
If CHARINDEX('x', [cpu_category] returns a 0, then 0-1= -1, which is invalid for a RIGHT().
0
 
Abhinav SinghSoftware EngineerAuthor Commented:
Hello Jim,

Thanks for replying.
Can you please suggest an alternative for this?
Thanks again.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'm not familiar with this query, but to avoid the error...

    ,CASE 
        WHEN CHARINDEX('x', [cpu_category]) < 1 THEN NULL
        ELSE LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) END AS [cpu_category]

Open in new window

0
 
Abhinav SinghSoftware EngineerAuthor Commented:
Hello Jim,

Can you please be more specific as to where should I put this part part in my query?
I'm not able to figure out.
Sorry of any inconvenience.
0
All Courses

From novice to tech pro — start learning today.