Error converting data type varchar to bigint.

Hello,

I try to format the following query

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
    FROM sys.dm_os_performance_counters
    WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
    AND counter_name = 'Target Pages';

;WITH src AS
(
    SELECT
        database_id, db_buffer_pages = COUNT_BIG(*)
        FROM sys.dm_os_buffer_descriptors
        --WHERE database_id BETWEEN 5 AND 32766
        GROUP BY database_id
)
SELECT
    [db_name] = CASE [database_id] WHEN 32767
        THEN 'Resource DB'
        ELSE DB_NAME([database_id]) END +'|'+
    db_buffer_pages +'|'+
     db_buffer_pages / 128 +'|'+
     CONVERT(DECIMAL(6,3),
        db_buffer_pages * 100.0 / @total_buffer) FROM src;

Open in new window


The error Error converting data type varchar to bigint. returned. How can I resolve it?
Thanks
bibi92Asked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
+'|'+   is a string.
 CONVERT(DECIMAL(6,3), anything) is a decimal.

You can't concatenante a string with a non-string, so in order to pull this off you have to convert the decimal value to a string, something like this...

+'|'+
     CAST(CONVERT(DECIMAL(6,3),
        db_buffer_pages * 100.0 / @total_buffer) as varchar(100)) FROM src
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
What is the name of field you want to convert?

If it is Field1, try:

database_id, db_buffer_pagesX = CONVERT(COUNT_BIG, Field1)

 Also I notice you have:

WHERE RTRIM([object_name]) LIKE '%Buffer Manager' ...
   
Which is the same as

WHERE [object_name] LIKE '%Buffer Manager%' ...

but much slower. Loose RTRIM()

Mike
0
 
bibi92Author Commented:
Hello,

I need to add  +'|'+ between each field and I think this generated the error. I have to convert  CONVERT(DECIMAL(6,3),
        db_buffer_pages * 100.0 / @total_buffer)
Thanks
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just for kicks and giggles, let's identify the exact line that throws the error...
Execute the function, and when it errors again double-click on that error message, and note where the cursor jumps.  That is the line that caused the error.
Post that line into this question.
0
 
bibi92Author Commented:
Hello,

The line that caused the error :
+'|'+
     CONVERT(DECIMAL(6,3),
        db_buffer_pages * 100.0 / @total_buffer) FROM src

Thanks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
bibi92, do you still need help with this question?
0
 
bibi92Author Commented:
thanks regards
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade, good luck with your project.  -Jim
0
 
bibi92Author Commented:
Thanks a lot for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.