Link two tables in UPDATE - ERR:single-row subquery returns more than one row

1 UPDATE PM_ASSIGNMENTS
2 SET PM_ASSIGNMENTS.JOB_TYPE_CODE = (SELECT PM_TASK_DETAILS.JOB_TYPE_CODE
3 FROM PM_ASSIGNMENTS, PM_TASK_DETAILS
4 WHERE ((PM_ASSIGNMENTS.TASK_NUMBER) = (PM_TASK_DETAILS.TASK_NUMBER))
5* AND (PM_ASSIGNMENTS.TASK_NUMBER = '1000'))
SQL> /
SET PM_ASSIGNMENTS.JOB_TYPE_CODE = (SELECT PM_TASK_DETAILS.JOB_TYPE_CODE
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one rowthe sub-query is returning more than one row for the given condition.

either group-by them, or give all user-key conditions.

regards,
Raghu.sql

Line Numbers in SQL

Is there a function I can put into my select clause that tell me what the
line number of the row is?
i.e. in Oracle you can do something like (Been a year since I've used
Oracle)
select rownum, Field1, Field2
from blah
Any help is appreciated. Thanks.No, you could do something with a temp table and IDENTITY. I believe Yukon
has the rownumber function. What do you need it for?
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uTDWhna8DHA.1052@.TK2MSFTNGP12.phx.gbl...
> Is there a function I can put into my select clause that tell me what the
> line number of the row is?
> i.e. in Oracle you can do something like (Been a year since I've used
> Oracle)
> select rownum, Field1, Field2
> from blah
> Any help is appreciated. Thanks.
>|||It's the only way I can think of to alternate row colors in reporting
services...
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:%233NMuic8DHA.1548@.tk2msftngp13.phx.gbl...
> No, you could do something with a temp table and IDENTITY. I believe Yukon
> has the rownumber function. What do you need it for?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uTDWhna8DHA.1052@.TK2MSFTNGP12.phx.gbl...
the
>|||you should have a look at the subject "row style in matrix" in
microsoft.public.sqlserver.reportingsvcs
Jean-Philippe.
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> a crit dans le
message de news:eDztD6e8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> It's the only way I can think of to alternate row colors in reporting
> services...
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:%233NMuic8DHA.1548@.tk2msftngp13.phx.gbl...
Yukon
message
> the
>|||I don't seem to have a message with that title and the newsgroup is not in
Google groups yet...
"Jean Philippe Vankemmel" <vankemjp@.ifrance.com> wrote in message
news:%23zh0zZG9DHA.452@.TK2MSFTNGP11.phx.gbl...
> you should have a look at the subject "row style in matrix" in
> microsoft.public.sqlserver.reportingsvcs
> Jean-Philippe.
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> a crit dans le
> message de news:eDztD6e8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Yukon
> message
what
used
>|||See here - http://www.microsoft.com/sql/commun...ups/default.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:%23k0kO%23K9DHA.4060@.tk2msftngp13.phx.gbl...
> I don't seem to have a message with that title and the newsgroup is not in
> Google groups yet...
> "Jean Philippe Vankemmel" <vankemjp@.ifrance.com> wrote in message
> news:%23zh0zZG9DHA.452@.TK2MSFTNGP11.phx.gbl...
le
> what
> used
>|||Nothing there, either..
Doesn't come up in search.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eKr4rHL9DHA.400@.tk2msftngp13.phx.gbl...
> See here - http://www.microsoft.com/sql/commun...ups/default.asp
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:%23k0kO%23K9DHA.4060@.tk2msftngp13.phx.gbl...
in
> le
reporting
believe
>|||Nothing there? I just went to it, it's toward the bottom of the page, search
for "reporting services"
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uVp1YYL9DHA.2712@.tk2msftngp13.phx.gbl...
> Nothing there, either..
> Doesn't come up in search.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:eKr4rHL9DHA.400@.tk2msftngp13.phx.gbl...
message
not
> in
dans
> reporting
> believe
in
me
I've
>|||you should have a look at the subject "row style in matrix" in
That is what I'm looking up in that newsgroup. I can find the reporting
services newsgroup.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:%23yp%23tcL9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Nothing there? I just went to it, it's toward the bottom of the page,
search
> for "reporting services"
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uVp1YYL9DHA.2712@.tk2msftngp13.phx.gbl...
http://www.microsoft.com/sql/commun...ups/default.asp
> message
> not
> dans
wrote
> in
tell
> me
> I've
>|||Ah, OK.
I use NNTP and searched and found it, this is the excerpt: (See below)
You will need to add an expression the outer most row grouping on the
matrix.
The attached sample report demonstrates how to do this.
Key Areas:
* Matrix Properties Dialog : Groups Tab : Country Group has an expression
on it
that controls the number of rows:
=System.Math.Ceiling(RowNumber(Nothing)/5)
* Place a PageBreakAtEnd on the Matrix Properties Dialog : Groups Tab :
Country Group.
NOTE: There is a bug on this dialog in RTM version. The checkboxes to
set
PageBreakAt[Start | End] are missing. You will need to hand edit the RDL
to get this
to work. Before proceeding backup your RDL. The code snippet shows where
the
PageBreakAtEnd tag was added to the RDL:
<Grouping Name="matrix1_Country">
<GroupExpressions>
<GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd> <<<<<< Tag added
here.
</Grouping>
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Avi" <anonymous@.discussions.microsoft.com> wrote in message
news:B9A6926F-2CBD-4C40-9471-49422C3EA39E@.microsoft.com...
> Hi
> Is there any way by which I can specify the number of rows to be displayed
on one page in matrix?
> Suppose I want only 10 rows to be displyed on one page irrespective of
collapsed or expanded state of the groups.
> How do I achieve this?
> -Avi
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:OeKWKlO9DHA.3704@.tk2msftngp13.phx.gbl...
> you should have a look at the subject "row style in matrix" in
> That is what I'm looking up in that newsgroup. I can find the reporting
> services newsgroup.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:%23yp%23tcL9DHA.548@.TK2MSFTNGP11.phx.gbl...
> search
message
> http://www.microsoft.com/sql/commun...ups/default.asp
is
crit
> wrote
> tell
since
>sql

Line numbers in Report Builder

I have a report in which the user community would like line numbers associated with each row. Has anyone done this or have any ideas how to accomplish this?

Line Item Manufacturer
1 Mazda
2 Ford
3 BMW

ThanksThis seems like something you want to do in the sql query rather than the report. Not sure, exactly how...|||Since you drag elements into the design area and Report Builder is not based on a query, there is no way to create line item numbers within Report Builder.

Is that what you are saying?|||Hi,

You can use RunningValue function as the text value of the line number column.

If you want a numbering for rows within a dataset, you can use

=RunningValue(1, SUM, "Employees")

where Employees is the dataset name.

If you have groups in your reports, say one of the groups has a name "EmployeesByCity", then you can replace the last part of the RunningValue with this group name shown as below

=RunningValue(1, SUM, "EmployeesByCity")

You can check the below link for a sample

http://www.kodyaz.com/article.aspx?ArticleID=40

Eralper|||Thank you for your assistance. "RunningValue" is not available in Report Builder.|||

I've got a similar issue that RunningValue won't work for. Basically, what the business users want is a line number for each line in the report. Independant of the groupings. I believe this has to be done during the rendering of the report as it has to cross groups after all the report processing has been done. Here's a small sample of what they want it to look like.

Column A Column B Column C

1 Group 1 Header

2 Group 2 Header

3 Detail

4 Detail

5 Detail

6 Group 2 Footer

7 Group 1 Footer

The count can reset at the begging of each page, or continue, doesn't really matter. I'm trying everything I can think of.

Any ideas out there?

|||

If you add RowNumber("DSName") in the first column then it should work...

|||

Unfortunately, it's not that easy. Doing that, you get the total #f rows for each group on the group level headers. I need the count to be a straight rendered line number count and not be tied the underlying data set or grouping structure.

Line numbers in Report Builder

I have a report in which the user community would like line numbers associated with each row. Has anyone done this or have any ideas how to accomplish this?

Line Item Manufacturer
1 Mazda
2 Ford
3 BMW

ThanksThis seems like something you want to do in the sql query rather than the report. Not sure, exactly how...|||Since you drag elements into the design area and Report Builder is not based on a query, there is no way to create line item numbers within Report Builder.

Is that what you are saying?|||Hi,

You can use RunningValue function as the text value of the line number column.

If you want a numbering for rows within a dataset, you can use

=RunningValue(1, SUM, "Employees")

where Employees is the dataset name.

If you have groups in your reports, say one of the groups has a name "EmployeesByCity", then you can replace the last part of the RunningValue with this group name shown as below

=RunningValue(1, SUM, "EmployeesByCity")

You can check the below link for a sample

http://www.kodyaz.com/article.aspx?ArticleID=40

Eralper|||Thank you for your assistance. "RunningValue" is not available in Report Builder.|||

I've got a similar issue that RunningValue won't work for. Basically, what the business users want is a line number for each line in the report. Independant of the groupings. I believe this has to be done during the rendering of the report as it has to cross groups after all the report processing has been done. Here's a small sample of what they want it to look like.

Column A Column B Column C

1 Group 1 Header

2 Group 2 Header

3 Detail

4 Detail

5 Detail

6 Group 2 Footer

7 Group 1 Footer

The count can reset at the begging of each page, or continue, doesn't really matter. I'm trying everything I can think of.

Any ideas out there?

|||

If you add RowNumber("DSName") in the first column then it should work...

|||

Unfortunately, it's not that easy. Doing that, you get the total #f rows for each group on the group level headers. I need the count to be a straight rendered line number count and not be tied the underlying data set or grouping structure.

line numbering in result set

Does anybody know how I can get a row count in the result of my query.
So first column should be 1, 2, 3 ...
Thanks,
BoontjeSince you appear to be using Microsoft SQL Server, I'd take the suggestion from Kaiowas in your posting (http://www.dbforums.com/t1095025.html) in that forum. You may get a lot of confusing answers here from people that don't know which database engine you are using.

-PatP|||FYI duplicate posts in multiple forums will be deleted

:)

Line break after group

I need to insert a line break in a detail row after certains groups of data.
example:
Week Day Detail
1 Mon 1
1 Tue 1
1 Wed 2
1 Thu 2
1 Fri 1
2 Mon 1
2 Tue 1
2 Wed 2
2 Thu 2
2 Fri 1
I need it to have a line break after week 1, 2, etc. Also, the detail column
has aggregates, runningvalues etc. There can't be a runningvalue on the line
break. I want the data to look like this:
Week Day Detail
1 Mon 1
1 Tue 1
1 Wed 2
1 Thu 2
1 Fri 1
2 Mon 1
2 Tue 1
2 Wed 2
2 Thu 2
2 Fri 1I would create a group on Week with no group header but a single blank row
group footer. This should be your innermost group. Outer groups will
calculate the aggregates properly. You'll need to scope any running values
to outer groups.
"Dutch" wrote:
> I need to insert a line break in a detail row after certains groups of data.
> example:
> Week Day Detail
> 1 Mon 1
> 1 Tue 1
> 1 Wed 2
> 1 Thu 2
> 1 Fri 1
> 2 Mon 1
> 2 Tue 1
> 2 Wed 2
> 2 Thu 2
> 2 Fri 1
> I need it to have a line break after week 1, 2, etc. Also, the detail column
> has aggregates, runningvalues etc. There can't be a runningvalue on the line
> break. I want the data to look like this:
> Week Day Detail
> 1 Mon 1
> 1 Tue 1
> 1 Wed 2
> 1 Thu 2
> 1 Fri 1
> 2 Mon 1
> 2 Tue 1
> 2 Wed 2
> 2 Thu 2
> 2 Fri 1

Limitations of SQL Server 2000?

Is there a "25 million row" limit to SQL Server? We are running 2000
Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
Server Enterprise edition. Management is saying that we will be
adopting oracle (eventually) as there is a 25 million row limitation to
SQL Server 2000. This is all based on one individual's claims.
I have no idea what type of operation this alleged limitation comes
from.
I have never heard of a thing, nor can I find any supporting evidence
of this online.
Any ideas on what they can be talking about?
I'm not opposed to oracle, just think this we are buying for the sake
of having the biggest and the best.
There is no row limit - period. The individual is just making it up.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1127160347.131024.187710@.g44g2000cwa.googlegr oups.com...
Is there a "25 million row" limit to SQL Server? We are running 2000
Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
Server Enterprise edition. Management is saying that we will be
adopting oracle (eventually) as there is a 25 million row limitation to
SQL Server 2000. This is all based on one individual's claims.
I have no idea what type of operation this alleged limitation comes
from.
I have never heard of a thing, nor can I find any supporting evidence
of this online.
Any ideas on what they can be talking about?
I'm not opposed to oracle, just think this we are buying for the sake
of having the biggest and the best.
|||> Management is saying that we will be
> adopting oracle (eventually) as there is a 25 million row limitation to
> SQL Server 2000. This is all based on one individual's claims.
Total crap. SQL Server is capable of processing billions of rows and
multiple terabytes of data.
For examples, take a look at:
http://www.microsoft.com/sql/techinf...alability.mspx
David Portas
SQL Server MVP
|||You base your business on wild claims such as that? I have used single
tables with over 2 billion rows in them. One database has over 5 billion
total rows in it. This is on a 4 processor SQL 2000 machine.
Andrew J. Kelly SQL MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1127160347.131024.187710@.g44g2000cwa.googlegr oups.com...
> Is there a "25 million row" limit to SQL Server? We are running 2000
> Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
> Server Enterprise edition. Management is saying that we will be
> adopting oracle (eventually) as there is a 25 million row limitation to
> SQL Server 2000. This is all based on one individual's claims.
> I have no idea what type of operation this alleged limitation comes
> from.
> I have never heard of a thing, nor can I find any supporting evidence
> of this online.
> Any ideas on what they can be talking about?
> I'm not opposed to oracle, just think this we are buying for the sake
> of having the biggest and the best.
>
|||My posts are nothing more than seeking knowledge. I'm just a worker bee
asking some questions, because it could have been a combination of
hearsay/telephone game. I felt it would be worthwhile to double check
so I can relay that back up.
Thank you all for your responses...
|||TJI and show my age, it is possible that the individual that gave you the 25M
limit was an oldtimer and not just wrong.
Back in the dark ages of the '80s and early '90s, an object limit was not
entirely unusual. Sybase (SS's predecessaor) and earl SS of those days may
have had a limit. 25M is a bit unusual (8M or 32M or some magic multiple
was more normal), but not impossible.
But it has been gone a long time.
Joseph R.P. Maloney, CSP,CCP,CDP
"Mnemonic" wrote:

> My posts are nothing more than seeking knowledge. I'm just a worker bee
> asking some questions, because it could have been a combination of
> hearsay/telephone game. I felt it would be worthwhile to double check
> so I can relay that back up.
> Thank you all for your responses...
>
|||No, that wouldn't be it. SQL Server has never had such a limit for number of rows in a table (this
going back to MS SQL Server 1.0). At least not from an architectural viewpoint. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:2A05FBDB-F1ED-44B4-AD2D-CA7782810A24@.microsoft.com...[vbcol=seagreen]
> TJI and show my age, it is possible that the individual that gave you the 25M
> limit was an oldtimer and not just wrong.
> Back in the dark ages of the '80s and early '90s, an object limit was not
> entirely unusual. Sybase (SS's predecessaor) and earl SS of those days may
> have had a limit. 25M is a bit unusual (8M or 32M or some magic multiple
> was more normal), but not impossible.
> But it has been gone a long time.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Mnemonic" wrote:

Limitations of SQL Server 2000?

Is there a "25 million row" limit to SQL Server? We are running 2000
Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
Server Enterprise edition. Management is saying that we will be
adopting oracle (eventually) as there is a 25 million row limitation to
SQL Server 2000. This is all based on one individual's claims.
I have no idea what type of operation this alleged limitation comes
from.
I have never heard of a thing, nor can I find any supporting evidence
of this online.
Any ideas on what they can be talking about?
I'm not opposed to oracle, just think this we are buying for the sake
of having the biggest and the best.There is no row limit - period. The individual is just making it up.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1127160347.131024.187710@.g44g2000cwa.googlegroups.com...
Is there a "25 million row" limit to SQL Server? We are running 2000
Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
Server Enterprise edition. Management is saying that we will be
adopting oracle (eventually) as there is a 25 million row limitation to
SQL Server 2000. This is all based on one individual's claims.
I have no idea what type of operation this alleged limitation comes
from.
I have never heard of a thing, nor can I find any supporting evidence
of this online.
Any ideas on what they can be talking about?
I'm not opposed to oracle, just think this we are buying for the sake
of having the biggest and the best.|||> Management is saying that we will be
> adopting oracle (eventually) as there is a 25 million row limitation to
> SQL Server 2000. This is all based on one individual's claims.
Total crap. SQL Server is capable of processing billions of rows and
multiple terabytes of data.
For examples, take a look at:
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.mspx
--
David Portas
SQL Server MVP
--|||You base your business on wild claims such as that? I have used single
tables with over 2 billion rows in them. One database has over 5 billion
total rows in it. This is on a 4 processor SQL 2000 machine.
--
Andrew J. Kelly SQL MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1127160347.131024.187710@.g44g2000cwa.googlegroups.com...
> Is there a "25 million row" limit to SQL Server? We are running 2000
> Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
> Server Enterprise edition. Management is saying that we will be
> adopting oracle (eventually) as there is a 25 million row limitation to
> SQL Server 2000. This is all based on one individual's claims.
> I have no idea what type of operation this alleged limitation comes
> from.
> I have never heard of a thing, nor can I find any supporting evidence
> of this online.
> Any ideas on what they can be talking about?
> I'm not opposed to oracle, just think this we are buying for the sake
> of having the biggest and the best.
>|||My posts are nothing more than seeking knowledge. I'm just a worker bee
asking some questions, because it could have been a combination of
hearsay/telephone game. I felt it would be worthwhile to double check
so I can relay that back up.
Thank you all for your responses...|||TJI and show my age, it is possible that the individual that gave you the 25M
limit was an oldtimer and not just wrong.
Back in the dark ages of the '80s and early '90s, an object limit was not
entirely unusual. Sybase (SS's predecessaor) and earl SS of those days may
have had a limit. 25M is a bit unusual (8M or 32M or some magic multiple
was more normal), but not impossible.
But it has been gone a long time.
Joseph R.P. Maloney, CSP,CCP,CDP
"Mnemonic" wrote:
> My posts are nothing more than seeking knowledge. I'm just a worker bee
> asking some questions, because it could have been a combination of
> hearsay/telephone game. I felt it would be worthwhile to double check
> so I can relay that back up.
> Thank you all for your responses...
>|||No, that wouldn't be it. SQL Server has never had such a limit for number of rows in a table (this
going back to MS SQL Server 1.0). At least not from an architectural viewpoint. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:2A05FBDB-F1ED-44B4-AD2D-CA7782810A24@.microsoft.com...
> TJI and show my age, it is possible that the individual that gave you the 25M
> limit was an oldtimer and not just wrong.
> Back in the dark ages of the '80s and early '90s, an object limit was not
> entirely unusual. Sybase (SS's predecessaor) and earl SS of those days may
> have had a limit. 25M is a bit unusual (8M or 32M or some magic multiple
> was more normal), but not impossible.
> But it has been gone a long time.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Mnemonic" wrote:
>> My posts are nothing more than seeking knowledge. I'm just a worker bee
>> asking some questions, because it could have been a combination of
>> hearsay/telephone game. I felt it would be worthwhile to double check
>> so I can relay that back up.
>> Thank you all for your responses...
>>

Limitations of SQL Server 2000?

Is there a "25 million row" limit to SQL Server? We are running 2000
Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
Server Enterprise edition. Management is saying that we will be
adopting oracle (eventually) as there is a 25 million row limitation to
SQL Server 2000. This is all based on one individual's claims.
I have no idea what type of operation this alleged limitation comes
from.
I have never heard of a thing, nor can I find any supporting evidence
of this online.
Any ideas on what they can be talking about?
I'm not opposed to oracle, just think this we are buying for the sake
of having the biggest and the best.There is no row limit - period. The individual is just making it up.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1127160347.131024.187710@.g44g2000cwa.googlegroups.com...
Is there a "25 million row" limit to SQL Server? We are running 2000
Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
Server Enterprise edition. Management is saying that we will be
adopting oracle (eventually) as there is a 25 million row limitation to
SQL Server 2000. This is all based on one individual's claims.
I have no idea what type of operation this alleged limitation comes
from.
I have never heard of a thing, nor can I find any supporting evidence
of this online.
Any ideas on what they can be talking about?
I'm not opposed to oracle, just think this we are buying for the sake
of having the biggest and the best.|||> Management is saying that we will be
> adopting oracle (eventually) as there is a 25 million row limitation to
> SQL Server 2000. This is all based on one individual's claims.
Total crap. SQL Server is capable of processing billions of rows and
multiple terabytes of data.
For examples, take a look at:
http://www.microsoft.com/sql/techin...calability.mspx
David Portas
SQL Server MVP
--|||You base your business on wild claims such as that? I have used single
tables with over 2 billion rows in them. One database has over 5 billion
total rows in it. This is on a 4 processor SQL 2000 machine.
Andrew J. Kelly SQL MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1127160347.131024.187710@.g44g2000cwa.googlegroups.com...
> Is there a "25 million row" limit to SQL Server? We are running 2000
> Enterprise on a quad processor, 1.5 TB, 4 GB RAM machine under 2003
> Server Enterprise edition. Management is saying that we will be
> adopting oracle (eventually) as there is a 25 million row limitation to
> SQL Server 2000. This is all based on one individual's claims.
> I have no idea what type of operation this alleged limitation comes
> from.
> I have never heard of a thing, nor can I find any supporting evidence
> of this online.
> Any ideas on what they can be talking about?
> I'm not opposed to oracle, just think this we are buying for the sake
> of having the biggest and the best.
>|||My posts are nothing more than seeking knowledge. I'm just a worker bee
asking some questions, because it could have been a combination of
hearsay/telephone game. I felt it would be worthwhile to double check
so I can relay that back up.
Thank you all for your responses...|||TJI and show my age, it is possible that the individual that gave you the 25
M
limit was an oldtimer and not just wrong.
Back in the dark ages of the '80s and early '90s, an object limit was not
entirely unusual. Sybase (SS's predecessaor) and earl SS of those days may
have had a limit. 25M is a bit unusual (8M or 32M or some magic multiple
was more normal), but not impossible.
But it has been gone a long time.
Joseph R.P. Maloney, CSP,CCP,CDP
"Mnemonic" wrote:

> My posts are nothing more than seeking knowledge. I'm just a worker bee
> asking some questions, because it could have been a combination of
> hearsay/telephone game. I felt it would be worthwhile to double check
> so I can relay that back up.
> Thank you all for your responses...
>|||No, that wouldn't be it. SQL Server has never had such a limit for number of
rows in a table (this
going back to MS SQL Server 1.0). At least not from an architectural viewpoi
nt. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:2A05FBDB-F1ED-44B4-AD2D-CA7782810A24@.microsoft.com...[vbcol=seagreen]
> TJI and show my age, it is possible that the individual that gave you the
25M
> limit was an oldtimer and not just wrong.
> Back in the dark ages of the '80s and early '90s, an object limit was not
> entirely unusual. Sybase (SS's predecessaor) and earl SS of those days may
> have had a limit. 25M is a bit unusual (8M or 32M or some magic multiple
> was more normal), but not impossible.
> But it has been gone a long time.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Mnemonic" wrote:
>

limit the select to single row

Hello,
How can I limit the select statement to 1 single row ?
How can I limit the select statement to specific no. of rows ?
Thanks :)SELECT TOP 1
FROM ...
ORDER BY ...
SELECT TOP 10
FROM ...
ORDER BY ...
Note that without ORDER BY, which rows that are returned is undefined.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eitan M" <no_spam_please@.nospam_please.com> wrote in message
news:%23lJnZbwoFHA.860@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I limit the select statement to 1 single row ?
> How can I limit the select statement to specific no. of rows ?
> Thanks :)
>|||The obvious answer to your first question is to use a WHERE clause that
references a unique key.
In the second case you'll probably want to take a look at the SELECT
TOP n feature or use SET ROWCOUNT.
With TOP and ROWCOUNT be aware that unless you specify a unique key in
the ORDER BY clause or use the TOP WITH TIES modifier you may get
undefined results. That ought to be common sense but too many times I
see people omit to specify a well-defined criteria for a top n subset.
David Portas
SQL Server MVP
--

Limit the number of records (with offset)

Hi,

i want to have only a few rows in a table. eg, from row 20 to 50. The problem is, 20 is a variable and 30 is (50 - 20 = 30...) one too
i use this statement:

SELECT * FROM
(SELECT TOP @.MaxRecord * FROM
(SELECT TOP @.MaxRecord * FROM
(SELECT TOP @.Totals * FROM PL4 ORDER BY RailLocation ASC)
AS foo ORDER BY RailLocation DESC )
AS bar )
AS baz ORDER BY RailLocation ASC

Totals is 50 and
MaxRecord is 30 in this example
PL4 was declared before in this Stored Procedure

This aint allowed, only when i hardcoded top 30 and top 50 it works...
And i don't want it to be hardcoded, does someone have a solution for this problem?

ThnxTry this:

EXEC('SELECT * FROM
(SELECT TOP ' + @.MaxRecord + ' * FROM
(SELECT TOP ' + @.Totals + ' * FROM PL4 ORDER BY RailLocation ASC)
AS foo ORDER BY RailLocation DESC )
AS baz ORDER BY RailLocation ASC')|||Nice, it works, thanx

Limit on row size for reading fixed-width .txt?

Hi,
I have a customer who wants to send a fixed width file with 1400 characters per row.
Before I tell the 'OK', I was wondering if there is any limit to the length of rows when using a DTS .txt source.
Anyone know?
Thanks!You have a sql row limit of 8060

Why not bcp the data in?

Does the process have to be repeated?|||My "personal best" was a smidgeon over 20 Kb per row of text. 2000 ten digit integers with a space between them per row.

-PatP|||OK, so 1400 characters is pretty puny.

This will be a weekly feed that begins with an FTP'd file and ends only after updating all of that company's organization structure, employees, employee contacts, etc. and then emailing a summary of the results and any errors to our DBA. Currently we run a bunch of these as DTS as it is easy to have some tasks be custom to the specific customer and others be based on stock scripts.

We're not looking forward to moving to Yukon. I hear that you can't upgrade DTS.

Thanks for the info guys!|||That's not true about upgrading DTS. The problem is HOW you have to upgrade. You basically take the whole VBScript it produces and plop it in there to run. It will run, but you lose all the graphical interface. I'm hoping they've changed that. I haven't really spent time looking at IS since the early notes on it. Too busy looking at all the other kewl features.|||Sounds like this will not go off without some triage and repair, particularly in our more sophisticated, looping DTS where we enable and disable tasks based on the data.|||If you mean data driven tasks, oh boy will you be excited.

There are some features that just plain won't be portable (the VBA/Active X and UDL in particular), others will require significant "triage", but most features ought to be relatively portable. I won't take any bets until I see RC, because there are so many things that aren't "final" yet.

-PatP|||I agree that we won't know until we've started to move our own stuff over.

But I'm betting it will be seriously annoying!