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)
 Insert: only data that hasn't been inserted prior!
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/07/2013 :  04:52:50  Show Profile  Reply with Quote
try this and let us know if you still get the error


SELECT
PMHost,
PMInstance,
Timestamp,
PMObject,
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
FROM 
(SELECT  PMHost, PMInstance, Timestamp, PMObject,PMValue, PM_COUNTER 
FROM [Warehouse].[sa_itm_user].[K99_GENALARMPUSHALARM]
and timestamp > (select max(timestamp) from logicaldisksample)
) T1
PIVOT(MAX(PMValue) FOR PM_Counter in 
	(
	[Disk Write Bytes/Sec],
	[% Free Space],
	[Free Megabytes],
	[Split IO/Sec],
	[MDL Reads/sec],
	[Threads],
	[Interrupts/sec],
	[Packets Received Non-Unicast/sec],
	[Pin Read Hits %],
	[Transition Faults/sec]
        )
     )P 
	
where PMObject = 'logicaldisk' and
(
 [Disk Write Bytes/Sec] is not null or
 [% Free Space]is not null or
 [Free Megabytes] is not null or
 [Split IO/Sec] is not null or
 [MDL Reads/sec] is not null or
 [Threads] is not null or
 [Interrupts/sec] is not null or
 [Packets Received Non-Unicast/sec] is not null or
 [Pin Read Hits %] is not null or
 [Transition Faults/sec] is not null
) 





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/11/2013 :  10:18:09  Show Profile  Reply with Quote
Hi, sorry but I still get the same error! why is this?

I get:


incorrect syntax near '>'

incorrect syntax near ')'


?? thanks for help guys
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 06/11/2013 :  10:27:27  Show Profile  Reply with Quote
Appearantly, the query seems to be correct, but how if you provide sample data in consumable format (in the form of Insert statments) for the following fields of K99_GENALARMPUSHALARM table

PMHost, PMInstance, Timestamp, PMObject,PMValue, PM_COUNTER

and a couple of records for logicaldisksample table.

This would help us to validate the syntax.

Cheers
MIK
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/12/2013 :  04:28:10  Show Profile  Reply with Quote
Sample data from K99genalarmpushalarm:



pmhost      pminstance  timestamp               pmobject      pmvalue   pm_counter

ahermontest1	"D:"	1130604081815000	LogicalDisk	0	Disk Read Bytes/sec
ahermontest1	"E:"	1130604081815000	LogicalDisk	0	Disk Read Bytes/sec
ahermontest1	"C:"	1130604081815000	LogicalDisk	0	Disk Reads/sec
ahermontest1	"D:"	1130604081815000	LogicalDisk	0	Disk Reads/sec
ahermontest1	"E:"	1130604081815000	LogicalDisk	0	Disk Reads/sec
ahermontest1	"C:"	1130604081815000	LogicalDisk	6707	Disk Write Bytes/sec
ahermontest1	"D:"	1130604081815000	LogicalDisk	3570	Disk Write Bytes/sec
ahermontest1	"E:"	1130604081815000	LogicalDisk	0	Disk Write Bytes/sec


sample data from logicaldisksample:

pmhost      pminstance  timestamp               pmobject      diskwbs    pfs     fmb     siosec

AHERMON401
AHERMON401
AHERMON401
AHERMON401
AHERMON401	"C:"	1130530160148000	LogicalDisk	19445	  57	  23493	    0	  
AHERMON401	"C:"	1130531103817000	LogicalDisk	24979	  57	  23535	    0	 


For the logicaldisk sample data, 'diskwbd', 'pfs', 'fmb' and 'siosec' are diskwritebytessec, percfreespace, freemegabytes, splitiosec
(I just shortened them for formatting purposes, there full names are actually used in the database table just to avoid any confusion.
Also, the columns for which I havent included data contained NULL data.

Does this information help? getting urgent now :(

Many thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  04:36:48  Show Profile  Reply with Quote
try this

SELECT
PMHost,
PMInstance,
Timestamp,
PMObject,
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
FROM 
(SELECT  PMHost, PMInstance, Timestamp, PMObject,PMValue, PM_COUNTER 
FROM [Warehouse].[sa_itm_user].[K99_GENALARMPUSHALARM]
where timestamp > (select max(timestamp) from logicaldisksample)
) T1
PIVOT(MAX(PMValue) FOR PM_Counter in 
	(
	[Disk Write Bytes/Sec],
	[% Free Space],
	[Free Megabytes],
	[Split IO/Sec],
	[MDL Reads/sec],
	[Threads],
	[Interrupts/sec],
	[Packets Received Non-Unicast/sec],
	[Pin Read Hits %],
	[Transition Faults/sec]
        )
     )P 
	
where PMObject = 'logicaldisk' and
(
 [Disk Write Bytes/Sec] is not null or
 [% Free Space]is not null or
 [Free Megabytes] is not null or
 [Split IO/Sec] is not null or
 [MDL Reads/sec] is not null or
 [Threads] is not null or
 [Interrupts/sec] is not null or
 [Packets Received Non-Unicast/sec] is not null or
 [Pin Read Hits %] is not null or
 [Transition Faults/sec] is not null
) 




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/12/2013 :  05:20:35  Show Profile  Reply with Quote
Thanks, but with this query above I now get the error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.



Edited by - mitin on 06/12/2013 05:20:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  05:26:44  Show Profile  Reply with Quote
what about this?


DECLARE @MaxTiemstamp datetime

SELECT @MaxTimestamp= max(timestamp) 
from logicaldisksample


SELECT
PMHost,
PMInstance,
Timestamp,
PMObject,
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
FROM 
(SELECT  PMHost, PMInstance, Timestamp, PMObject,PMValue, PM_COUNTER 
FROM [Warehouse].[sa_itm_user].[K99_GENALARMPUSHALARM]
where timestamp > @MaxTimestamp
) T1
PIVOT(MAX(PMValue) FOR PM_Counter in 
	(
	[Disk Write Bytes/Sec],
	[% Free Space],
	[Free Megabytes],
	[Split IO/Sec],
	[MDL Reads/sec],
	[Threads],
	[Interrupts/sec],
	[Packets Received Non-Unicast/sec],
	[Pin Read Hits %],
	[Transition Faults/sec]
        )
     )P 
	
where PMObject = 'logicaldisk' and
(
 [Disk Write Bytes/Sec] is not null or
 [% Free Space]is not null or
 [Free Megabytes] is not null or
 [Split IO/Sec] is not null or
 [MDL Reads/sec] is not null or
 [Threads] is not null or
 [Interrupts/sec] is not null or
 [Packets Received Non-Unicast/sec] is not null or
 [Pin Read Hits %] is not null or
 [Transition Faults/sec] is not null
) 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/12/2013 :  05:33:11  Show Profile  Reply with Quote
I now get an error saying that the scalar variables must be declared...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  05:40:53  Show Profile  Reply with Quote
quote:
Originally posted by mitin

I now get an error saying that the scalar variables must be declared...


I've declared it
make sure you run the entire query not just the last SELECT part.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/12/2013 :  06:55:59  Show Profile  Reply with Quote
I do realise that you declared it, however I still get the error, and I am using the entire query!! I can't understand this, i have had this problem previously when trying to use variables, it makes me think theres something wrong with my instance of SQL server because its bizzare to get this error when variables are clerly declared.

Really need to get this query working, one annoying error after another

Thanks very much, any more ideas?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/12/2013 :  07:11:12  Show Profile  Reply with Quote
Have you kept any GO batch separator in between the following lines of code?
DECLARE @MaxTiemstamp datetime

SELECT @MaxTimestamp= max(timestamp)
from logicaldisksample

GO
SELECT
PMHost,
PMInstance
.
.
.

And also
1) Try to check for the variable name which displayed along with error message...
2) Check the spell for DECLARE keyword
3) Are you executing above query or dynamic query?
--
Chandu

Edited by - bandi on 06/12/2013 07:15:01
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 06/12/2013 :  07:15:18  Show Profile  Reply with Quote
Check if the following query works on your machine... I just checked and it works on SQL server 2008. If its giving error then copy/paste the complete error message ..

Note: keep it as it is .. I am just trying to figure out if you're using same query as suggested by the folks, or you are modifying it


Declare @K99genalarmpushalarm table (pmhost varchar(30),pminstance varchar(10),[timestamp] varchar(100),pmobject varchar(30),pmvalue int,pm_counter varchar(50))
INSERT INTO @K99genalarmpushalarm VALUES
('ahermontest1','"D:"','1130604081815000','LogicalDisk','0','Disk Read Bytes/sec'),
('ahermontest1','"E:"','1130604081815000','LogicalDisk','0','Disk Read Bytes/sec'),
('ahermontest1','"C:"','1130604081815000','LogicalDisk','0','Disk Reads/sec'),
('ahermontest1','"D:"','1130604081815000','LogicalDisk','0','Disk Reads/sec'),
('ahermontest1','"E:"','1130604081815000','LogicalDisk','0','Disk Reads/sec'),
('ahermontest1','"C:"','1130604081815000','LogicalDisk','6707','Disk Write Bytes/sec'),
('ahermontest1','"D:"','1130604081815000','LogicalDisk','3570','Disk Write Bytes/sec'),
('ahermontest1','"E:"','1130604081815000','LogicalDisk','0','Disk Write Bytes/sec')
select * from @K99genalarmpushalarm
SELECT
PMHost,
PMInstance,
Timestamp,
PMObject,
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
FROM
(SELECT PMHost, PMInstance, Timestamp, PMObject,PMValue, PM_COUNTER
FROM @K99genalarmpushalarm
where [timestamp] > '1130531103817000'
) T1
PIVOT(MAX(PMValue) FOR PM_Counter in
(
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
)
)P

where PMObject = 'logicaldisk' and
(
[Disk Write Bytes/Sec] is not null or
[% Free Space]is not null or
[Free Megabytes] is not null or
[Split IO/Sec] is not null or
[MDL Reads/sec] is not null or
[Threads] is not null or
[Interrupts/sec] is not null or
[Packets Received Non-Unicast/sec] is not null or
[Pin Read Hits %] is not null or
[Transition Faults/sec] is not null
)



Cheers
MIK

Edited by - MIK_2008 on 06/12/2013 07:18:19
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/12/2013 :  07:25:11  Show Profile  Reply with Quote
I got your issue...

DECLARE @MaxTiemstamp datetime -- here variable name should be @MaxTimestamp

SELECT @MaxTimestamp= max(timestamp)
from logicaldisksample



--
Chandu
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/12/2013 :  08:40:46  Show Profile  Reply with Quote
Thanks Bandi, that helped. However I now get the error:

Invalid column name: 'Timestamp'

Something to do with the fact that 'Timestamp' is also a SQL keyword? (sorry not sure of the correct term)

This hasn't been a problem when using timestamp before, I have tables that have that column name and have inserted data into them with no issue previously. When i put quotes around 'timestamp' (thought this might be to do with the issue but don't think it is at all now) in this part of the code:

SELECT @MaxTimestamp= max('Timestamp')
from logicaldisksample

I get the error:

Conversion failed when converting date and/or time from character string.

ow can I now solve this error relating to Timestamp?


Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 06/12/2013 :  09:18:11  Show Profile  Reply with Quote
-->Invalid column name: 'Timestamp'
Make sure the field exists in both tables (K99_GENALARMPUSHALARM and logicaldisksample), and also spells exactly same as you're specifying

-->Conversion failed when converting date and/or time from character string.
if the data for TimeStamp field is same as you provided e.g. 1130604081815000 then its not a valid data for "datetime" datatype. What do you think what would be the date for 1130604081815000?

Cheers
MIK

Edited by - MIK_2008 on 06/12/2013 09:19:38
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  09:28:53  Show Profile  Reply with Quote
quote:
Originally posted by mitin

Thanks Bandi, that helped. However I now get the error:

Invalid column name: 'Timestamp'

Something to do with the fact that 'Timestamp' is also a SQL keyword? (sorry not sure of the correct term)

This hasn't been a problem when using timestamp before, I have tables that have that column name and have inserted data into them with no issue previously. When i put quotes around 'timestamp' (thought this might be to do with the issue but don't think it is at all now) in this part of the code:

SELECT @MaxTimestamp= max('Timestamp')
from logicaldisksample

I get the error:

Conversion failed when converting date and/or time from character string.

ow can I now solve this error relating to Timestamp?






Try

SELECT @MaxTimestamp= max([Timestamp])
from logicaldisksample


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/12/2013 :  10:02:33  Show Profile  Reply with Quote
I just have but get the same error....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  10:04:26  Show Profile  Reply with Quote
do you've that column in the table logicaldisksample?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/12/2013 :  10:23:29  Show Profile  Reply with Quote
ok, I now get the errors:

'Arithmetic overflow error converting expression to data type datetime'

'Conversion failed when converting date and/or time from character string'

FYI in the K99.genalarmpushalarm table the datatype for timestamp is 'char'

whereas in the logicaldisk sampl table it is 'decimal'

is this why? what can be done to resolve this error?

(Thanks so much for bearing with me on this guys, really appreciate this help!!)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  10:49:00  Show Profile  Reply with Quote
then you should tell us how values are stored in the field.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next 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.36 seconds. Powered By: Snitz Forums 2000