Author |
Topic |
specex
Starting Member
6 Posts |
Posted - 2008-11-28 : 05:14:04
|
Hello expertsI found some sample code on http://www.codeproject.com/KB/IP/PingMonitor.aspx. When i run the SQL statement in SQL 2005 Server and it's works fine but when i run in SQL 2000 Server it shows error.There are two tables from the example, PingLog and HostListThe SQL statement as follows:SELECT AllPing.Host, AllPing.RecordingDate, AllPing.Status FROM PingLog AS AllPingINNER JOIN (SELECT Host, MAX(RecordingDate) AS LastRecordingDate FROM PingLog GROUP BY Host) LastPingON AllPing.Host=LastPing.Host AND AllPing.RecordingDate=LastPing.LastRecordingDateINNER JOIN HostList ON HostList.Host=AllPing.HostWHERE HostList.ShowInMonitor='Y' ORDER BY Host Hope any SQL experts can help me on this, thanks |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-28 : 05:31:42
|
What is the error? E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-28 : 05:36:56
|
I think the error derives from the ORDER BY statement, because the Host column name is found in two tables.Prefix your ORDER BY Host column name with correct alias name, for exampleORDER BY AllPing.Host E 12°55'05.63"N 56°04'39.26" |
|
|
specex
Starting Member
6 Posts |
Posted - 2008-11-28 : 05:40:23
|
Oppps, i forgot to includes it, so sorry...here the error..Msg 209, Level 16, State 1, Line 1Ambiguous column name 'Host'. FYI, when i just only includes the 1st three rows, that is..SELECT AllPing.Host, AllPing.RecordingDate, AllPing.Status FROM PingLog AS AllPingINNER JOIN (SELECT Host, MAX(RecordingDate) AS LastRecordingDate FROM PingLog GROUP BY Host) LastPingON AllPing.Host=LastPing.Host AND AllPing.RecordingDate=LastPing.LastRecordingDate it can shows the data but i like to includes all five rows in order to display the data correctly. TQ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-28 : 05:41:03
|
quote: Originally posted by Peso I think the error derives from the ORDER BY statement, because the Host column name is found in two tables.Prefix your ORDER BY Host column name with correct alias name, for exampleORDER BY AllPing.Host E 12°55'05.63"N 56°04'39.26"
out of curiosity, why it worked in sql 2005 then? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-28 : 05:48:07
|
It assumed that Host colum name from LastPing derived table is an alias.SQL Server 2005 tries with aliases first, and then "real" column names. E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-28 : 05:49:59
|
See this proofDECLARE @Sample TABLE ( i INT, j INT )INSERT @SampleSELECT 1, 1 UNION ALLSELECT 2, 2SELECT s.i, u.jFROM ( SELECT -i AS i, j FROM @Sample ) AS sCROSS JOIN @Sample AS uORDER BY i If you however do a SELECT * you will get the error "ambiguous name". E 12°55'05.63"N 56°04'39.26" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-28 : 05:52:57
|
Oh...i seethanks Peso for clarification |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-28 : 05:56:05
|
You're welcome. E 12°55'05.63"N 56°04'39.26" |
|
|
specex
Starting Member
6 Posts |
Posted - 2008-11-28 : 09:32:16
|
That's do the trick! Thanks Peso..Another same problem is when i try to create these two tables (PingLog and HostList)CREATE TABLE HostList ( ID int IDENTITY(1,1) NOT NULL, Host nvarchar(50) NULL, IsHost char(1) NULL, ShowInMonitor char(1) NULL, DoPing char(1) NULL, PingFreq int NULL, IDparent int NULL, CONSTRAINT PK_HostList PRIMARY KEY CLUSTERED (ID ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE PingLog ( Host varchar(50) NOT NULL, Status varchar(5) NOT NULL, RecordingDate datetime NOT NULL, CONSTRAINT [PK_PingLog] PRIMARY KEY CLUSTERED (Host ASC, Status ASC, RecordingDate ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO the error is Msg 170, Level 15, State 1, Line 10Line 10: Incorrect syntax near '('.Msg 170, Level 15, State 1, Line 7Line 7: Incorrect syntax near '('.But this work on sql 2005 but not 2000. The tables only can be created on sql 2000 when i remove this line...WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] How can i maintains the structure of the original scripts by do some modification? Thanks mate |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-28 : 09:54:59
|
the PAD_INDEX & IGNORE_DUP_KEY features are only available from SQL 2005 onwards |
|
|
specex
Starting Member
6 Posts |
Posted - 2008-11-28 : 10:26:52
|
if removing WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] won't affect the operation of the program then is fine, so thanks 4 all the comments, guys... regards, |
|
|
|