Solved

Invalid length parameter passed to the RIGHT function

Posted on 2014-10-31
5
260 Views
Last Modified: 2014-10-31
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
0
Comment
Question by:Abhinav Singh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40416456
>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
 

Author Comment

by:Abhinav Singh
ID: 40416463
Hello Jim,

Thanks for replying.
Can you please suggest an alternative for this?
Thanks again.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40416468
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40416477
...
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
 

Author Comment

by:Abhinav Singh
ID: 40416483
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question