ROW_NUMBER()

Hi,

I use SQL 2005 and Visual Studio 2005.

I tried to use the ROW_NUMBER() function but I always get an error message saying ( The Over SQL construct or statement is not supported.

SELECT row_number() over (order by fullname) as ROWNUMBER, CustomerID, FullName, Address, PhoneH, PhoneMob, Area, DayNumber
FROM Customers

Thanks.

Try this sample:

Code Snippet

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY FirstName ),
EmployeeID,
FirstName,
LastName
FROM Employees

RowNumber EmployeeID FirstName LastName
-- -- - --
1 2 Andrew Fuller
2 9 Anne Dodsworth
3 3 Janet Leverling
4 8 Laura Callahan
5 4 Margaret Peacock
6 6 Michael Suyama
7 1 Nancy Davolio
8 7 Robert King
9 5 Steven Buchanan

If you do NOT get the same output, please verify your SQL Server version (using @.@.Version).

|||

Hi Mohamed,

Be sure that you are connecting to a 2005 instance and check that the compatibility of the database you are connecting to is 90. See sp_dbcmptlevel in BOL for more info.

AMB

|||

You probably connected the SQL Server 2000 from the Management Studio. Over clause is only accepted by SQL Server 2005.

Execute the following query..

Select @.@.VERSION

It should return as Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) .....etc.

Note:

Database Compellability level won’t affect the OVER clause. So you can utilize the OVER clause in any of the Compellability Level (60, 65, 70, 80, or 90), but it should be SQL Server 2005 or above.

|||

Manivannan.D.Sekaran wrote:

You probably connected the SQL Server 2000 from the Management Studio. Over clause is only accepted by SQL Server 2005.

Execute the following query..

Select @.@.VERSION

It should return as Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) .....etc.

Note:

Database Compellability level won’t affect the OVER clause. So you can utilize the OVER clause in any of the Compellability Level (60, 65, 70, 80, or 90), but it should be SQL Server 2005 or above.

Thanks for your kind reply,

I used (select @.@.version) and got this result.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I still have the same error message.

Thanks.

|||

What environment/tool are you using when the error message occurs?

Are you in a query window in SQL Server Management Studio (File->New->Query with current connection)?

Are you using Query Designer?

Are you using something in Visual Studio?

If you're not issuing the command from a query window in SQL Server Management Studio, then please try that.

|||

Yes. OVER Clause is not working with Query Designer (on Management Studio or Visual Studio).

But it is not a error. You can ignore and continue your rest of work.... On the execution time you will get the proper result

|||Yet another reason to dislike those parts of the tools. I would suggest (if you are a professional programmer, which is likely if you are in these forums that you start writing queries only in the text editor. It will greatly improve your querying skills (and you can use all of the power of SQL Server without crazy tool errors!|||

Manivannan.D.Sekaran wrote:

Yes. OVER Clause is not working with Query Designer (on Management Studio or Visual Studio).

But it is not a error. You can ignore and continue your rest of work.... On the execution time you will get the proper result

Cheers Manivannan,

Thank you very much for your great help. It worked as magic. Without your help, I would have suffered a lot.

I want also to thank all friends who replied my question. I got benefit from each and all replies.

Thanks to

Arnie Rowland

hunchback

Dalej

and Louis Davidson

Row Total and Column Total in MS Visual Studio report designer 2005

Hi All,

I am using Visual Studio 2005 report desingner and SQL 2005. I have created one matrix report, but i want to insert the column total and row total.

or atleast column total

How do i do that ?

COLUMN1 COLUMN2 COLUMN3

qty amt qty amt qty amt

XXXX 2 11.00 3 22.00 34 23.50

YYYYY 3 22.00 5 27.00 35 33.50

How to do the colunm total.

Regards

Saleem

Assuming you have dynamic column and row groups, you can just right-click on the group and select "Subtotal" from the context menu.

See also: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_7vi0.asp (scroll to the bottom section of "Adding Subtotals")

-- Robert

Row cannot be located for updating

Hi,

I am getting "Row cannot be located for updating. Some values may have
been changed" error when I try to update from visual basic with ado.
This happens only when set as default locale on the pc other language
than English.
Anybody can help on this

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!A M (antmios@.yahoo.com) writes:
> I am getting "Row cannot be located for updating. Some values may have
> been changed" error when I try to update from visual basic with ado.
> This happens only when set as default locale on the pc other language
> than English.
> Anybody can help on this

I can vaguely guess what is going on, but with out a reproducible case
I find it difficult to say anything useful. Can you provide a sample?
That would need to include both the CREATE TABLE statement for the table
and the VB code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Routine crashing of Visual Studio 2005 when running or editing SSIS

We have found that it is common for Visual Studio 2005 to crash when editing or running SSIS packages -- from CTP versions through beta versions and including the release version.

Of course we kept hoping that newer releases would become more stable, or at least more robust -- and now I'm hoping there will be a service pack, which might make it more robust?Can you repro the crashes, have you submitted the bug report?|||Please consider this the bug report then -- this is all I know -- it very often crashes.

We have never observed any correlation to any action in particular, or even to any particular development machine or package.

So there isn't anything obvious for me to even try to reproduce them at a desired time. So I cannot really make any useful bug report -- at least, as a programmer, I think a bug report isn't too useful until it either is reproducible or at the very least is correlated to something.

It just tends to to crash -- well, ok, a common point (but not too helpful I guess) is that all the machines involved use Windows Server 2003, and some version of SQL Server 2005 -- now they all use SQL Server 2005 release or SQL Server 2005 SP1, as all have either been upgraded to release or reinstalled with release -- we no longer use any CTP or beta versions.

Another common point is they mostly use the same set of databases.

Oh, and most are x32 versions, except one is an x64 version of Windows Server 2003 SP1.

On some forum somewhere, someone pointed me to some beta newsgroups and asked me to submit some of my many SSIS bug reports to it, but I spent some time trying to access it, and went through the usual connectivity problems involved with hotmail or passport, and never could get access. I posted my bugs, and my descriptions of how I could not access the site to file them then, and didn't get any followups, so I didn't see anything else to do with them.

I never found any simple working system to submit bugs, except to post them to SSIS forums I could find, so I just did that, in hopes they'd help someone at Microsoft who saw them. But, those bug reports at least had descriptive & often proscriptive info -- here, I don't even have that.

I was kind of hoping that an upgrade to SSIS would come along that would fix a lot of the crashes, and hopefully also fix the parameter parsing problems in SSIS -- apparently the msdn help is still not updated to reflect how OLEDB parameter parsing in the ExecuteSQL control flow task really works?|||bug reports may be submitted at the sql server feedback page: http://connect.microsoft.com/feedback/default.aspx?SiteID=68