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 updating error.

Hello. I'm trying to update some 'text' data directly through SQL Server
Management Studio (2005) on a table and have been encountering the following
error popup:
Microsoft SQL Server Management Studio
No row was updated.
The data in row _ was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
This is strange in that I can update some records in regards to this 'text'
field but then on many other records I get the above error? Would be much
appreciative if anyone would be able to shed some light on this.
Thanks in advance.
J
It just means that the values you're entering are too long. Right-click
and choose "Modify" in Mgmt Studio. There, you'll see a list of the
maximum allowable lengths for each of your columns. You can't type in
anything longer than what's specified there.
-Dave
J wrote:
> Hello. I'm trying to update some 'text' data directly through SQL Server
> Management Studio (2005) on a table and have been encountering the following
> error popup:
>
> Microsoft SQL Server Management Studio
> No row was updated.
> The data in row _ was not committed.
> Error Source: .Net SqlClient Data Provider.
> Error Message: String or binary data would be truncated.
> The statement has been terminated.
> Correct the errors and retry or press ESC to cancel the change(s).
>
> This is strange in that I can update some records in regards to this 'text'
> field but then on many other records I get the above error? Would be much
> appreciative if anyone would be able to shed some light on this.
> Thanks in advance.
> J
>
-Dave Markle
http://www.markleconsulting.com/blog
|||It was strange because there was existing data in these records under the
'text' column already. In trying to update it to a single character I
noticed I received the error messages for records that had lengthy data in
it (like paragraphs long) versus the records that I was able to update that
only had a few characters which I didn't receive the error message and was
able to update it to a single character. Since we're still in the
development stage I ran an UPDDATE statment to set all of this 'text' column
to null and it seems like that this did the trick.
Thanks for your quick reply and info Dave. Much appreciated.
Take care.
J
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:u5rAbRORHHA.3412@.TK2MSFTNGP05.phx.gbl...
> It just means that the values you're entering are too long. Right-click
> and choose "Modify" in Mgmt Studio. There, you'll see a list of the
> maximum allowable lengths for each of your columns. You can't type in
> anything longer than what's specified there.
> -Dave
> J wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

Row updating error.

Hello. I'm trying to update some 'text' data directly through SQL Server
Management Studio (2005) on a table and have been encountering the following
error popup:
Microsoft SQL Server Management Studio
No row was updated.
The data in row _ was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
This is strange in that I can update some records in regards to this 'text'
field but then on many other records I get the above error? Would be much
appreciative if anyone would be able to shed some light on this.
Thanks in advance.
JIt just means that the values you're entering are too long. Right-click
and choose "Modify" in Mgmt Studio. There, you'll see a list of the
maximum allowable lengths for each of your columns. You can't type in
anything longer than what's specified there.
-Dave
J wrote:
> Hello. I'm trying to update some 'text' data directly through SQL Server
> Management Studio (2005) on a table and have been encountering the followi
ng
> error popup:
>
> Microsoft SQL Server Management Studio
> No row was updated.
> The data in row _ was not committed.
> Error Source: .Net SqlClient Data Provider.
> Error Message: String or binary data would be truncated.
> The statement has been terminated.
> Correct the errors and retry or press ESC to cancel the change(s).
>
> This is strange in that I can update some records in regards to this 'text
'
> field but then on many other records I get the above error? Would be much
> appreciative if anyone would be able to shed some light on this.
> Thanks in advance.
> J
>
-Dave Markle
http://www.markleconsulting.com/blog|||It was strange because there was existing data in these records under the
'text' column already. In trying to update it to a single character I
noticed I received the error messages for records that had lengthy data in
it (like paragraphs long) versus the records that I was able to update that
only had a few characters which I didn't receive the error message and was
able to update it to a single character. Since we're still in the
development stage I ran an UPDDATE statment to set all of this 'text' column
to null and it seems like that this did the trick.
Thanks for your quick reply and info Dave. Much appreciated.
Take care.
J
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:u5rAbRORHHA.3412@.TK2MSFTNGP05.phx.gbl...
> It just means that the values you're entering are too long. Right-click
> and choose "Modify" in Mgmt Studio. There, you'll see a list of the
> maximum allowable lengths for each of your columns. You can't type in
> anything longer than what's specified there.
> -Dave
> J wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

Row updating error.

Hello. I'm trying to update some 'text' data directly through SQL Server
Management Studio (2005) on a table and have been encountering the following
error popup:
Microsoft SQL Server Management Studio
No row was updated.
The data in row _ was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
This is strange in that I can update some records in regards to this 'text'
field but then on many other records I get the above error? Would be much
appreciative if anyone would be able to shed some light on this.
Thanks in advance.
JIt just means that the values you're entering are too long. Right-click
and choose "Modify" in Mgmt Studio. There, you'll see a list of the
maximum allowable lengths for each of your columns. You can't type in
anything longer than what's specified there.
-Dave
J wrote:
> Hello. I'm trying to update some 'text' data directly through SQL Server
> Management Studio (2005) on a table and have been encountering the following
> error popup:
>
> Microsoft SQL Server Management Studio
> No row was updated.
> The data in row _ was not committed.
> Error Source: .Net SqlClient Data Provider.
> Error Message: String or binary data would be truncated.
> The statement has been terminated.
> Correct the errors and retry or press ESC to cancel the change(s).
>
> This is strange in that I can update some records in regards to this 'text'
> field but then on many other records I get the above error? Would be much
> appreciative if anyone would be able to shed some light on this.
> Thanks in advance.
> J
>
-Dave Markle
http://www.markleconsulting.com/blog|||It was strange because there was existing data in these records under the
'text' column already. In trying to update it to a single character I
noticed I received the error messages for records that had lengthy data in
it (like paragraphs long) versus the records that I was able to update that
only had a few characters which I didn't receive the error message and was
able to update it to a single character. Since we're still in the
development stage I ran an UPDDATE statment to set all of this 'text' column
to null and it seems like that this did the trick.
Thanks for your quick reply and info Dave. Much appreciated.
Take care.
J
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:u5rAbRORHHA.3412@.TK2MSFTNGP05.phx.gbl...
> It just means that the values you're entering are too long. Right-click
> and choose "Modify" in Mgmt Studio. There, you'll see a list of the
> maximum allowable lengths for each of your columns. You can't type in
> anything longer than what's specified there.
> -Dave
> J wrote:
>> Hello. I'm trying to update some 'text' data directly through SQL Server
>> Management Studio (2005) on a table and have been encountering the
>> following error popup:
>>
>> Microsoft SQL Server Management Studio
>> No row was updated.
>> The data in row _ was not committed.
>> Error Source: .Net SqlClient Data Provider.
>> Error Message: String or binary data would be truncated.
>> The statement has been terminated.
>> Correct the errors and retry or press ESC to cancel the change(s).
>>
>> This is strange in that I can update some records in regards to this
>> 'text' field but then on many other records I get the above error? Would
>> be much appreciative if anyone would be able to shed some light on this.
>> Thanks in advance.
>> J
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

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 failed to retrieve on last operation

Hello,

I am getting the following errors when "inserting" data into my tables using Miscrosoft SQL Server Management Studio with a SQL Server 2005 database...

"This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query."

In the status bar:

"row failed to retrieve on last operation"

I am hoping I just have some database properties set wrong but have not been able to figure it out.

close cursor on commit is set to False, set it to True, retested, no affect on the error.

other info:

The database is used to supply data to a Microsoft Access ADP application

Are you getting any error number with this?

Anything in the SQL logs?

Does this happen on all inserts or just certain ones?

|||

There is no error number that comes with this error.

There is nothing significant in the logs...

This error occurs when inserting a new record to any table in the database. The record does insert but is not visible until I have followed the instructions of the error msg.

Error Log:

2007-07-04 19:00:42.00 Server (c) 2005 Microsoft Corporation.
2007-07-04 19:00:42.00 Server All rights reserved.
2007-07-04 19:00:42.00 Server Server process ID is xxxx.
2007-07-04 19:00:42.00 Server Authentication mode is xxxxxx.
2007-07-04 19:00:42.00 Server Logging SQL Server messages in file xxxxx.
2007-07-04 19:00:42.00 Server This instance of SQL Server last reported using a process ID of xxxx at 7/4/2007 7:00:00 PM (local) 7/5/2007 2:00:00 AM (UTC). This is an informational message only; no user action is required.
2007-07-04 19:00:42.00 Server Registry startup parameters:
2007-07-04 19:00:42.00 Server xxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-07-04 19:00:42.01 Server Detected X CPUs. This is an informational message; no user action is required.
2007-07-04 19:00:42.51 Server Using dynamic lock allocation. Initial allocation of xxxx Lock blocks and xxxxx Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-07-04 19:00:42.57 Server Database mirroring has been enabled on this instance of SQL Server.
2007-07-04 19:00:42.57 spidxs Starting up database 'master'.
2007-07-04 19:00:42.95 spidxs Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-07-04 19:00:43.01 spidxs SQL Trace ID 1 was started by login "xxxxxx".
2007-07-04 19:00:43.04 spidxs Starting up database 'xxxxxxxxxxx'.
2007-07-04 19:00:43.06 spidxs The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-07-04 19:00:43.20 spidxs Starting up database 'model'.
2007-07-04 19:00:43.21 spidxs Server name is 'xxxxx\MYDB'. This is an informational message only. No user action is required.
2007-07-04 19:00:43.43 spidxs Clearing tempdb database.
2007-07-04 19:00:43.71 spidxs Starting up database 'tempdb'.
2007-07-04 19:00:43.75 spidxs The Service Broker protocol transport is disabled or not configured.
2007-07-04 19:00:43.75 spidxs The Database Mirroring protocol transport is disabled or not configured.
2007-07-04 19:00:43.76 spidxs Service Broker manager has started.
2007-07-04 19:00:44.23 Server A self-generated certificate was successfully loaded for encryption.
2007-07-04 19:00:44.25 Server Server is listening on [ xxxxxx].
2007-07-04 19:00:44.25 Server Server local connection provider is ready to accept connection on [ xxx].
2007-07-04 19:00:44.25 Server Server named pipe provider is ready to accept connection on [xxxx].
2007-07-04 19:00:44.25 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2007-07-04 19:00:44.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-07-04 19:00:44.43 spidxs Starting up database 'msdb'.

|||

Can you send the DML of the table? And do you have a trigger on the table? I assume you are doing this in the "Open table" tool.

It seems to me that you possibly are changing the key value in a trigger or something and it cannot find the row with the key you edited. I get that error when I edit the following table:

create table fred

(

fredId int primary key,

value varchar(10)

)

go

create trigger fred$insertTrigger

on fred

after insert

as

update fred

set fredId = fredId * 10000

where fredId in (select fredId from inserted)

go

|||There is a constraint for a default in the table and for some reason, when using the open table tool, the default is not being applied automatically and that is what I believe makes the error msg appear.|||That sounds plausible. Can you send the DML for the table? I will test it too.

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

Rounding issue in calculated field using round (,1) function

I have a couple of calculated fields in a BI Dev Studio Report as follows:

1. round(Fields!CM_Perf_1st_Mth.Value, 1)

2. round(Fields!CM_Perf_1st_Mth.Value, 1) - round(Fields!BM_Perf_1st_Mth.Value, 1)

The first calc field above is returning wrong results i.e. for a value of 2.25, instead of returning 2.3, it is returning 2.2. Similarly for -0.05, it is returning 0.0, instead of -0.1.

Since the results from the first function are wrong, the second function is also returning off values.

Has anyone faced this issue? How does one get around this? I have SQL 2005 Reporting Services with SP1. The result was the same without SP1 also. Seems like a big bug in the round function...

TIA.

Had similiar issues and went with doing rounding functions in a custom code function.

Try using the functions there, you will see a difference.

Daryl

|||The dataset is coming from a SQL Server 2000 database stored proc.|||

doesn't matter where the dataset is coming from. use the =Code.myroundfunction in the field, and pass the field to the code function.

IE:

field1

if you had "=round(field!myfield.value)"

change it to =code.myroundfunction(field!myfield.value)

|||what does the raw sql data look like?|||

Wrote a custom code fx as:

Public Function MyRound(byVal x as decimal, byval y as int16) as decimal
return round(x,y)
end function

Calling this from the calculated field expression does not make a difference at all.

And here's the raw data:

dbo.Composite_Performance

Composite_Code

LU_Weight_Type_Code

Perf_Date

LU_Data_Stage

Perf_Gross

lc1s

ac

9/30/06

Prelim

2.25

dbo.Composite_Performance

Composite_Code

LU_Weight_Type_Code

Perf_Date

LU_Data_Stage

Perf_Gross

ls

ac

9/30/06

Prelim

-0.05

Any other ideas would be greatly appreciated as I am stuck on this and the deliverable is long due and this is the only issue left... TIA.

|||

Here is a link to another forum which should help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=315706&SiteId=1

Daryl

|||

This function works for me.

Public Function MyRound(ByVal x As Decimal, ByVal y As Integer) As Decimal

Return Round(x, y, MidpointRounding.AwayFromZero)

End Function

the definition for round is really confusing.

Daryl