| Author |
 Topic  |
|
parameshjoy
Starting Member
India
12 Posts |
 Posted - 03/27/2012 : 12:27:13
|
DECLARE @total INT
Insert 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.dbid
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
Getting error
Msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable
Paramesh |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/27/2012 : 12:31:11
|
should be this i guess
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/27/2012 : 12:32:41
|
But i will be scheduling this query for every one hour so the its updates table
The above query you provide will create a new table but i cant update the same table for every one hour
Paramesh |
Edited by - parameshjoy on 03/27/2012 12:33:35 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/27/2012 : 12:34:02
|
you current query doent have an update. it will keep inserting new rows as its written
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/27/2012 : 12:44:56
|
I need the same just inserting new rows into table CPU data
Paramesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/27/2012 : 14:57:22
|
quote:
Originally posted by parameshjoy
I need the same just inserting new rows into table CPU data
Paramesh
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 MVP
http://visakhm.blogspot.com/
|
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/27/2012 : 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)
Singapore
16746 Posts |
Posted - 03/27/2012 : 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
Time is always against us
|
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/27/2012 : 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)
Singapore
16746 Posts |
Posted - 03/27/2012 : 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
Time is always against us
|
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/28/2012 : 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)
Singapore
16746 Posts |
Posted - 03/28/2012 : 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
Time is always against us
|
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/28/2012 : 00:16:52
|
Am referring to the Visakh's query it creates new a table....
Paramesh |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 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
Time is always against us
|
Edited by - khtan on 03/28/2012 02:51:38 |
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/28/2012 : 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 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable
Paramesh |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 03:20:49
|
that is your original query right ? Have you try Visakh's query Posted - 03/27/2012 : 12:31:11 ?
KH
Time is always against us
|
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/28/2012 : 03:28:15
|
Visakh's query will create new table, i dont want to create another table.
Paramesh |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 03:33:34
|
Houston, we have a problem here. We've had a infinite loop.
Beam me up Scotty !
KH
Time is always against us
|
Edited by - khtan on 03/28/2012 03:33:57 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 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
Time is always against us
|
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 03/28/2012 : 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 Shaw
SQL Server MVP |
|
|
|
parameshjoy
Starting Member
India
12 Posts |
Posted - 03/28/2012 : 04:55:45
|
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@total".
Getting this error for query provided
Paramesh |
|
|
|
Topic |
|
An INSERT statement cannot contain a SELECT
