Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Invalid length parameter passed to the RIGHT function

Posted on 2014-10-31
5
Medium Priority
?
309 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

704 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