Author |
Topic |
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-27 : 12:27:13
|
DECLARE @total INTInsert into dbo.CPUdata ([database], [system cpu], [database cpu], [%]) SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbidSELECT 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.nameORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc Getting error Msg 199, Level 15, State 1, Line 0An INSERT statement cannot contain a SELECT statement that assigns values to a variableParamesh |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-27 : 12:31:11
|
should be this i guessDECLARE @total INTSELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbidInsert 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.nameORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-27 : 12:32:41
|
But i will be scheduling this query for every one hour so the its updates tableThe above query you provide will create a new table but i cant update the same table for every one hourParamesh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-27 : 12:34:02
|
you current query doent have an update. it will keep inserting new rows as its written------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-27 : 12:44:56
|
I need the same just inserting new rows into table CPU dataParamesh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-27 : 14:57:22
|
quote: Originally posted by parameshjoy I need the same just inserting new rows into table CPU dataParamesh
then what was the purpose of below statement??The above query you provide will create a new table but i cant update the same table for every one hour------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-27 : 22:33:27
|
Sorry instead of inserting rows i mentioned as update....My intention is to create a cpu process trend report by caputring the out of this query to a table and at the end of the month create a report on this and show to management.Hence the query will give the output and i need to capture in one table till month end.Kindly help me.Paramesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-27 : 22:52:43
|
quote: Originally posted by parameshjoy Sorry instead of inserting rows i mentioned as update....My intention is to create a cpu process trend report by caputring the out of this query to a table and at the end of the month create a report on this and show to management.Hence the query will give the output and i need to capture in one table till month end.Kindly help me.Paramesh
?then the query Visakh posted should do the work. Isn't it ? KH[spoiler]Time is always against us[/spoiler] |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-27 : 23:36:05
|
Hi Khtan,The query provided by Visakh will create a new table and then insert, i will be running this query every one hour so that the table gets insert with output of every one hours...I need to run the query every oen hour...Paramesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-27 : 23:54:42
|
quote: Originally posted by parameshjoy Hi Khtan,The query provided by Visakh will create a new table and then insert, i will be running this query every one hour so that the table gets insert with output of every one hours...I need to run the query every oen hour...Paramesh
the query did not create a new table. It only insert records to it KH[spoiler]Time is always against us[/spoiler] |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-28 : 00:11:07
|
It creates a new table and even output got inserted to that table.Now i need to schedule this query for every one hour and the output will append in the same table every one hour for this i need to alter this query.. Need help on this.Paramesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-28 : 00:13:26
|
quote: Originally posted by parameshjoy It creates a new table and even output got inserted to that table.Now i need to schedule this query for every one hour and the output will append in the same table every one hour for this i need to alter this query.. Need help on this.Paramesh
are you referring to Visakh's query ? His query does not create a new table. KH[spoiler]Time is always against us[/spoiler] |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-28 : 00:16:52
|
Am referring to the Visakh's query it creates new a table....Paramesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-28 : 02:50:10
|
quote: Originally posted by parameshjoy Am referring to the Visakh's query it creates new a table....Paramesh
Can you highlight which part of his query that creates new a table ?Are you referring to his post on 03/27/2012 : 12:31:11 ? KH[spoiler]Time is always against us[/spoiler] |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-28 : 03:17:36
|
i have cpudata table already exists in my database, now from the query i posted 03/27/2012 : 12:27:13 need to update the table cpudata with the output of the query, and this query will run every one hour, now am getting the error Getting error Msg 199, Level 15, State 1, Line 0An INSERT statement cannot contain a SELECT statement that assigns values to a variableParamesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-28 : 03:20:49
|
that is your original query right ? Have you try Visakh's query Posted - 03/27/2012 : 12:31:11 ? KH[spoiler]Time is always against us[/spoiler] |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-28 : 03:28:15
|
Visakh's query will create new table, i dont want to create another table.Paramesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-28 : 03:33:34
|
Houston, we have a problem here. We've had a infinite loop.Beam me up Scotty ! KH[spoiler]Time is always against us[/spoiler] |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-28 : 03:40:45
|
quote: Originally posted by parameshjoy Visakh's query will create new table, i dont want to create another table.Paramesh
OK. Let's start all over again.Obviuosly, you didn't try Visakh's query at all. How do you know it will create a new table if you haven't try it ?Which statement in his query will create a new table ? KH[spoiler]Time is always against us[/spoiler] |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-28 : 04:25:19
|
quote: Originally posted by parameshjoy Visakh's query will create new table, i dont want to create another table.Paramesh
It does NOT. There is no CREATE TABLE in his script, there is no INTO clause, the query he wrote for you will insert rows into an existing table, nothing more.If you'd tested it, you'd see that.--Gail ShawSQL Server MVP |
|
|
parameshjoy
Starting Member
12 Posts |
Posted - 2012-03-28 : 04:55:45
|
Msg 137, Level 15, State 2, Line 2Must declare the scalar variable "@total".Getting this error for query providedParamesh |
|
|
Next Page
|