Solved

Invalid length parameter passed to the RIGHT function

Posted on 2014-10-31
5
248 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 65

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 65

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

738 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