SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 An INSERT statement cannot contain a SELECT
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/28/2012 :  04:57:59  Show Profile  Reply with Quote
quote:
Originally posted by parameshjoy

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@total".

Getting this error for query provided

Paramesh



you need to declare it. You must have missed that line. Double check your query


KH
Time is always against us


Edited by - khtan on 03/28/2012 04:58:47
Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/28/2012 :  05:10:08  Show Profile  Reply with Quote
DECLARE @total INT


SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)

join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid


Insert into dbo.CPUdata ([database], [system cpu], [database cpu], [%])
SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'

FROM sys.sysprocesses sp (NOLOCK)

JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

--WHERE sp.status = 'runnable'

GROUP BY sb.name

ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc


In the first line of the query i have already Declared it...

I have executed the query provided by Visakh's...

Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/28/2012 :  05:12:47  Show Profile  Reply with Quote
Good for you. All is working right ?


KH
Time is always against us

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/28/2012 :  05:33:13  Show Profile  Reply with Quote
Yes everything is working fine as i required.

Thanks for your help and sorry for confusion

Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/28/2012 :  05:57:53  Show Profile  Reply with Quote
you are welcome


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/28/2012 :  11:51:49  Show Profile  Reply with Quote
Hmm...didnt realise it went so far ...
Is it so difficult to copy paste the given query and test it in SSMS window?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000