| Author |
Topic |
|
Mopani
Yak Posting Veteran
55 Posts |
 Posted - 2009-07-06 : 14:22:35
|
I am using Visual Web Developer 2008 Express, with SQL server 2008.SELECT FirstName, LastName, BowlerType, ttBowlSkill AS [Bowl Rating], Rank() OVER (ORDER BY ttBowlSkill DESC) AS [Bowl Rank], FROM OneDay This code gives me the desired result, but only after fist giving me a warning that SQL syntax errors were encountered."The OVER SQL construct or statement is not supported."What can I do to rectify this. It results in a problem when I try to built this query in a table adapter and thus will not accept the code. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-06 : 14:24:39
|
1. RANK() function and the other windowed function requires SQL Server 2005 and later.2. The compatibility level for the current database need to be set to 90 or higher.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-07-06 : 14:42:37
|
| I did not quite understand what you meant.I went into SQL Server Management Studio, and checked the properties for the database in question.Under Options, I find "Compatability Level: SQL Server 2008 (100)"Thus it seems as if the compatability is set high enough. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-07-06 : 14:43:47
|
| Did you run the Query from the Query Window"New Query" from SSMS. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-06 : 14:45:33
|
Are you sending this query ad-hoc to the database?If so, which data transport library are you using? ODBC? OLEDB? ADO?If you are not sending this ad-hoc, are you using a view or a stored procedure to return the result?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-07-06 : 14:49:04
|
| it gives me this error, when I build or modify a view that contains the Rank function .. which I have been doing directly in the database explorer of visual studio. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-06 : 14:51:02
|
The question still remains; How do you connect to the database from Visual Studio?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-07-06 : 14:54:28
|
| I do not know how to answer this question or where to check for the answer. |
|
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-07-06 : 14:58:03
|
| Data Source=.\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=Truedoes that help? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-06 : 15:01:03
|
I'd say you use SQL Native Client.Well, there are still some bugs remaining with the Visual Studio.This seems to be one of them. Another was the CASE contstructor. I believe that one is fixed now.You can do a workaround.Write the code as a view and connect to the view from your table adapter.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-07-06 : 15:03:10
|
| ok thank you.That is what I have been doing, but was of the opinion that I was going about it the wrong way.Thanks for your time. |
|
|
|
|
Rank: The OVER SQL construct error