multi rows SELECT

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = TransactionID FROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

Quote:

Originally Posted by Cshrek

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = VolIDFROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

hi
I am not clear with your code. try with this code .this is not exactly suit for ur requirement but this idea will help you

[code]
declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end|||hey, thanks.
wehn i try to run that code:

----------------------------------

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,

@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end

----------------------------------
it all seems to be good, but i get that error:

"Msg 156, Level 15, State 1, Procedure AccountTrakingSELECT, Line 22
Incorrect syntax near the keyword 'for'."

and i defently have no idea what is that mean? or what am i doing worng?

appriciate your help.sql

multi line rows

Using Visual basic 2005

I have a form and set up my datasource, bindings etc.

All works well, adding, updating, and deleting via form to sqlexpress except.....

I have 2 columns set up in sqlserver that get data from muliline textboxes on my form.

Column 1 "Material" is nvarchar(50)

Column 2 "Quantity" is int

The first multine textbox "Material" works fine, eather updating in the textbox or in the Datagridview cell.

the second multiline textbox "Quantity" works when I just put in a number in the first line of the multiline textbox, when I put in the 2nd number on the second line, nothing gets updated.

I I do that in the datagridview cell (Shift-Enter), I get an error.

"System string was not in correct format".

I tried integer.parse, but that dosn't work. I'm assuming it has something to do with the next line ascii code or something like that.

Can anyone out there help with this

It sounds like the problem is that you are trying to treat a multi-line textbox as a collection of values for when updating to the database... instead of the single long string value.

When saving your results back to the database instead of dealing with the textbox’s Text property, try iterating through each of the lines within its Lines property to pull out each individual value, convert it to a number and then deal with it as needed.

|||

Hi nglow,

Did Brendan's answer resolve your issue? I believe he's correct that the integer field is not going to accept all the lines from your multi-line text box as this is returning a string. The integer field can only hold a single integer number and you seem to be suggesting that you want to put more than one number in it. That won't work.

You either need to limit the text box to a single number, or walk each line in the text box and put each number into a separate row in the database.

Regards,

Mike Wachal
SQL Express team

Mark the best post as Answered.

Multi Column full-text search question

I would like to select rows containing some text which is more than one word.
The problem is that the text can appear in different colums, meaning, one
word in this column and another in that. moreove, I need to select only the
rows containing all the words.
The fulltext search is on one table only (multi columns)
The text size in words is variable
Can someone help?
Thanks in advance
This is a difficult question. Basically Contains will only see the words in
your search phrase if they occur in the same column, no matter what type of
search you are doing; single column, multiple column, or all columns.
For FreeText the hit will come if the word or one of the generations of the
word occurs in any of the columns.
create database multicolumn
go
use multicolumn
go
sp_fulltext_database 'enable'
go
create table fulltexttable
(pk int not null identity constraint fulltexttablepk primary key,
charcol1 char(30),
charcol2 char(30),
charcol3 char(30))
GO
insert into fulltexttable(charcol1, charcol2) values('james','bond')
insert into fulltexttable(charcol1, charcol2) values('james bond','mike')
GO
create fulltext catalog test as default
GO
create fulltext index on fulltexttable(charcol1, charcol2)
key index fulltexttablepk
GO
select * from containstable(fulltexttable,*,'"james bond"') -- 1 row, 2nd
one
select * from containstable(fulltexttable,charcol1,'"james bond"') -- 1 row,
2nd one
select * from containstable(fulltexttable,(charcol1,charcol2),'" james
bond"') -- 1 row, 2nd one
select * from freetexttable(fulltexttable,*,'james bond') -- 2 rows, 1 and 2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dedi" <dedi@.discussions.microsoft.com> wrote in message
news:CFE85410-5AFF-4F84-B376-3E864DFDB623@.microsoft.com...
>I would like to select rows containing some text which is more than one
>word.
> The problem is that the text can appear in different colums, meaning, one
> word in this column and another in that. moreove, I need to select only
> the
> rows containing all the words.
> The fulltext search is on one table only (multi columns)
> The text size in words is variable
> Can someone help?
> Thanks in advance
sql

Muliple rows for group side by side with data

I have a report layout that has proved to be a little tricky. I hope
someone on this group can help me out.
I am going to show a simplified version that captures the problem. I
can easily create a report that looks like this:
NAME ADDR1 ADDR2 PHONE ITEM1
ITEM2
ITEM3
ITEM4
ITEM5
...
NAME2 ADDR ADDR PHONE ITEM1
...
where there could be zero or more items for each person. I have been
asked to create a report that looks somthing like thiS:
NAME ITEM1
ADDR1 ITEM2
ADDR2 ITEM3
PHONE ITEM4
ITEM5
...
If there are more than 4 items, then they appear below. If there are
less than 4 items, then the space to the right of the address and phone
is blank.
This is a simplified example. There are actually many more fixed
fields, and they don't fit on 1 line -- it'll take about 7 lines to fit
them. If I were to put the repeating items below the fixed fields,
then 5 items would take a total of 12 lines for this person. By having
the items side by side with the fixed fields, 5 items would only take
the 7 lines and we save a lot of space.
Has anyone done this? Can anyone tell me how to place detail items
side by side with multiple rows of group header data?
Thank you,
TedHi, This is possible with Unions in your dataset.
Select Name From ?
Union
Select ADDR1 From ?
Union
Select ADDR2 From ?
Etc.
gr.
Mike
"Ted K" wrote:
> I have a report layout that has proved to be a little tricky. I hope
> someone on this group can help me out.
> I am going to show a simplified version that captures the problem. I
> can easily create a report that looks like this:
> NAME ADDR1 ADDR2 PHONE ITEM1
> ITEM2
> ITEM3
> ITEM4
> ITEM5
> ...
> NAME2 ADDR ADDR PHONE ITEM1
> ...
> where there could be zero or more items for each person. I have been
> asked to create a report that looks somthing like thiS:
> NAME ITEM1
> ADDR1 ITEM2
> ADDR2 ITEM3
> PHONE ITEM4
> ITEM5
> ...
> If there are more than 4 items, then they appear below. If there are
> less than 4 items, then the space to the right of the address and phone
> is blank.
> This is a simplified example. There are actually many more fixed
> fields, and they don't fit on 1 line -- it'll take about 7 lines to fit
> them. If I were to put the repeating items below the fixed fields,
> then 5 items would take a total of 12 lines for this person. By having
> the items side by side with the fixed fields, 5 items would only take
> the 7 lines and we save a lot of space.
> Has anyone done this? Can anyone tell me how to place detail items
> side by side with multiple rows of group header data?
> Thank you,
> Ted
>|||Thanks Mike. I'm very good with SQL and am confident I can write the query
to get the data back however I want. What I'm looking for is help with the
RDL layout. I have tried nested tables, lists, and other ways, but each has
problems. For example, one layout came up with:
NAME ITEM1
ADDR1
ADDR2
PHONE
ITEM2
ITEM3
ITEM4
ITEM5
...
instead of:
NAME ITEM1
ADDR1 ITEM2
ADDR2 ITEM3
PHONE ITEM4
ITEM5
Any experts out there know how to put the detail rows to the right of the
header fields instead of below?
Thank you very much,
Ted
"Mike Klaarhamer" wrote:
> Hi, This is possible with Unions in your dataset.
> Select Name From ?
> Union
> Select ADDR1 From ?
> Union
> Select ADDR2 From ?
> Etc.
> gr.
> Mike
> "Ted K" wrote:
> > I have a report layout that has proved to be a little tricky. I hope
> > someone on this group can help me out.
> >
> > I am going to show a simplified version that captures the problem. I
> > can easily create a report that looks like this:
> >
> > NAME ADDR1 ADDR2 PHONE ITEM1
> > ITEM2
> > ITEM3
> > ITEM4
> > ITEM5
> > ...
> > NAME2 ADDR ADDR PHONE ITEM1
> > ...
> >
> > where there could be zero or more items for each person. I have been
> > asked to create a report that looks somthing like thiS:
> >
> > NAME ITEM1
> > ADDR1 ITEM2
> > ADDR2 ITEM3
> > PHONE ITEM4
> > ITEM5
> > ...
> >
> > If there are more than 4 items, then they appear below. If there are
> > less than 4 items, then the space to the right of the address and phone
> > is blank.
> >
> > This is a simplified example. There are actually many more fixed
> > fields, and they don't fit on 1 line -- it'll take about 7 lines to fit
> > them. If I were to put the repeating items below the fixed fields,
> > then 5 items would take a total of 12 lines for this person. By having
> > the items side by side with the fixed fields, 5 items would only take
> > the 7 lines and we save a lot of space.
> >
> > Has anyone done this? Can anyone tell me how to place detail items
> > side by side with multiple rows of group header data?
> >
> > Thank you,
> > Ted
> >
> >|||Hi Ted,
I've done a similar thing just via using simple Tables.
I just created 2 tables next to each other, and they should get layed out
correctly.
I'm no expert, but I'm thinking I'd want to create a List with 2 Tables
inside it, like so:
--LIST--
| Table1- Table2- |
|| | | | |
||______| |_____| |
--
And that way you can layout table 1 as you want with the name and address
details, and table 2 with the details of the Items.
Let me know if it works for you, and if u understand my funny explanations.
Andre
"Ted K" wrote:
> Thanks Mike. I'm very good with SQL and am confident I can write the query
> to get the data back however I want. What I'm looking for is help with the
> RDL layout. I have tried nested tables, lists, and other ways, but each has
> problems. For example, one layout came up with:
> NAME ITEM1
> ADDR1
> ADDR2
> PHONE
> ITEM2
> ITEM3
> ITEM4
> ITEM5
> ...
> instead of:
> NAME ITEM1
> ADDR1 ITEM2
> ADDR2 ITEM3
> PHONE ITEM4
> ITEM5
> Any experts out there know how to put the detail rows to the right of the
> header fields instead of below?
> Thank you very much,
> Ted
> "Mike Klaarhamer" wrote:
> > Hi, This is possible with Unions in your dataset.
> > Select Name From ?
> > Union
> > Select ADDR1 From ?
> > Union
> > Select ADDR2 From ?
> > Etc.
> >
> > gr.
> >
> > Mike
> >
> > "Ted K" wrote:
> >
> > > I have a report layout that has proved to be a little tricky. I hope
> > > someone on this group can help me out.
> > >
> > > I am going to show a simplified version that captures the problem. I
> > > can easily create a report that looks like this:
> > >
> > > NAME ADDR1 ADDR2 PHONE ITEM1
> > > ITEM2
> > > ITEM3
> > > ITEM4
> > > ITEM5
> > > ...
> > > NAME2 ADDR ADDR PHONE ITEM1
> > > ...
> > >
> > > where there could be zero or more items for each person. I have been
> > > asked to create a report that looks somthing like thiS:
> > >
> > > NAME ITEM1
> > > ADDR1 ITEM2
> > > ADDR2 ITEM3
> > > PHONE ITEM4
> > > ITEM5
> > > ...
> > >
> > > If there are more than 4 items, then they appear below. If there are
> > > less than 4 items, then the space to the right of the address and phone
> > > is blank.
> > >
> > > This is a simplified example. There are actually many more fixed
> > > fields, and they don't fit on 1 line -- it'll take about 7 lines to fit
> > > them. If I were to put the repeating items below the fixed fields,
> > > then 5 items would take a total of 12 lines for this person. By having
> > > the items side by side with the fixed fields, 5 items would only take
> > > the 7 lines and we save a lot of space.
> > >
> > > Has anyone done this? Can anyone tell me how to place detail items
> > > side by side with multiple rows of group header data?
> > >
> > > Thank you,
> > > Ted
> > >
> > >|||Thanks for replying. Before I tried to work out the 2 tables next to each
other (and how to get the rows to match up right), I got one of my other
layouts working.
In case anyone wants to know, I created a list for the group, put all of the
static fields down, then put a list for the detail items to the right of the
static fields. The important part was to make the detail list as tall as all
of the static fields and also to set CanShrink to true. If there were very
few items, the list would shrink. If there were a lot, the list would grow,
but would not push down any of the static fields because the bottom of the
list was already below the text boxes.
Ted
"Arkiliknam" wrote:
> Hi Ted,
> I've done a similar thing just via using simple Tables.
> I just created 2 tables next to each other, and they should get layed out
> correctly.
> I'm no expert, but I'm thinking I'd want to create a List with 2 Tables
> inside it, like so:
> --LIST--
> | Table1- Table2- |
> || | | | |
> ||______| |_____| |
> --
> And that way you can layout table 1 as you want with the name and address
> details, and table 2 with the details of the Items.
> Let me know if it works for you, and if u understand my funny explanations.
> Andre
> "Ted K" wrote:
> > Thanks Mike. I'm very good with SQL and am confident I can write the query
> > to get the data back however I want. What I'm looking for is help with the
> > RDL layout. I have tried nested tables, lists, and other ways, but each has
> > problems. For example, one layout came up with:
> >
> > NAME ITEM1
> > ADDR1
> > ADDR2
> > PHONE
> > ITEM2
> > ITEM3
> > ITEM4
> > ITEM5
> > ...
> >
> > instead of:
> >
> > NAME ITEM1
> > ADDR1 ITEM2
> > ADDR2 ITEM3
> > PHONE ITEM4
> > ITEM5
> >
> > Any experts out there know how to put the detail rows to the right of the
> > header fields instead of below?
> >
> > Thank you very much,
> > Ted
> >
> > "Mike Klaarhamer" wrote:
> >
> > > Hi, This is possible with Unions in your dataset.
> > > Select Name From ?
> > > Union
> > > Select ADDR1 From ?
> > > Union
> > > Select ADDR2 From ?
> > > Etc.
> > >
> > > gr.
> > >
> > > Mike
> > >
> > > "Ted K" wrote:
> > >
> > > > I have a report layout that has proved to be a little tricky. I hope
> > > > someone on this group can help me out.
> > > >
> > > > I am going to show a simplified version that captures the problem. I
> > > > can easily create a report that looks like this:
> > > >
> > > > NAME ADDR1 ADDR2 PHONE ITEM1
> > > > ITEM2
> > > > ITEM3
> > > > ITEM4
> > > > ITEM5
> > > > ...
> > > > NAME2 ADDR ADDR PHONE ITEM1
> > > > ...
> > > >
> > > > where there could be zero or more items for each person. I have been
> > > > asked to create a report that looks somthing like thiS:
> > > >
> > > > NAME ITEM1
> > > > ADDR1 ITEM2
> > > > ADDR2 ITEM3
> > > > PHONE ITEM4
> > > > ITEM5
> > > > ...
> > > >
> > > > If there are more than 4 items, then they appear below. If there are
> > > > less than 4 items, then the space to the right of the address and phone
> > > > is blank.
> > > >
> > > > This is a simplified example. There are actually many more fixed
> > > > fields, and they don't fit on 1 line -- it'll take about 7 lines to fit
> > > > them. If I were to put the repeating items below the fixed fields,
> > > > then 5 items would take a total of 12 lines for this person. By having
> > > > the items side by side with the fixed fields, 5 items would only take
> > > > the 7 lines and we save a lot of space.
> > > >
> > > > Has anyone done this? Can anyone tell me how to place detail items
> > > > side by side with multiple rows of group header data?
> > > >
> > > > Thank you,
> > > > Ted
> > > >
> > > >sql

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktfWhat do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||select count(IdentityColumn) from tablename
I get ~87,000,000 in return
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||also to just be sure:
Select count(*) IdentityColumn
group by IdentityColumn
having count(*)>1
Returns 0
Thank you
ktf
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||It is:
SQL enterprise 2000 clustered
NT.5.0.(2195)
8.00.760. SP3
7GB memory
4 processor
"Jasper Smith" wrote:
> What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
> and see if that works. It sounds like this bug
> FIX: A parallel query may return unexpected results
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||Sounds like you're running into the bug then. Did you try doing a count with
option(maxdop 1) ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> It is:
> SQL enterprise 2000 clustered
> NT.5.0.(2195)
> 8.00.760. SP3
> 7GB memory
> 4 processor
>
> "Jasper Smith" wrote:
>> What service pack are you on? Also try doing a count with OPTION(MAXDOP
>> 1)
>> and see if that works. It sounds like this bug
>> FIX: A parallel query may return unexpected results
>> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> > The problem I have is the count(*) or count(table identity column) show
>> > a
>> > much bigger number than the actual number of rows.
>> >
>> > When I run
>> > select count(*) from tablename I get ~87,000,000 in return.
>> >
>> > But the MAX number of table identity is in 10 M range also rowcnt from
>> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
>> > not
>> > help me.
>> >
>> > Why I get such different result. What should I do to correct this.
>> >
>> > Thank you,
>> > ktf
>>|||Before I do that:
Is it going to reconfigure and change the server setting or it is only
within the session?
Is it going to put a big impact on the server?
Because I do not want to make that change yet.
Do I have to install SP4? Because we are not on 64-bit server.
Thank you,
"Jasper Smith" wrote:
> Sounds like you're running into the bug then. Did you try doing a count with
> option(maxdop 1) ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> > It is:
> > SQL enterprise 2000 clustered
> > NT.5.0.(2195)
> > 8.00.760. SP3
> > 7GB memory
> > 4 processor
> >
> >
> > "Jasper Smith" wrote:
> >
> >> What service pack are you on? Also try doing a count with OPTION(MAXDOP
> >> 1)
> >> and see if that works. It sounds like this bug
> >>
> >> FIX: A parallel query may return unexpected results
> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> > The problem I have is the count(*) or count(table identity column) show
> >> > a
> >> > much bigger number than the actual number of rows.
> >> >
> >> > When I run
> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >
> >> > But the MAX number of table identity is in 10 M range also rowcnt from
> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> >> > not
> >> > help me.
> >> >
> >> > Why I get such different result. What should I do to correct this.
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
> >>
>
>|||It's a query hint, it only affects the specific query in question. It won't
impact anything else. To avoid any issues just run
select count(*)
from tablename with(nolock)
option(maxdop 1)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> Before I do that:
> Is it going to reconfigure and change the server setting or it is only
> within the session?
> Is it going to put a big impact on the server?
> Because I do not want to make that change yet.
> Do I have to install SP4? Because we are not on 64-bit server.
> Thank you,
>
> "Jasper Smith" wrote:
>> Sounds like you're running into the bug then. Did you try doing a count
>> with
>> option(maxdop 1) ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> > It is:
>> > SQL enterprise 2000 clustered
>> > NT.5.0.(2195)
>> > 8.00.760. SP3
>> > 7GB memory
>> > 4 processor
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> What service pack are you on? Also try doing a count with
>> >> OPTION(MAXDOP
>> >> 1)
>> >> and see if that works. It sounds like this bug
>> >>
>> >> FIX: A parallel query may return unexpected results
>> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> > The problem I have is the count(*) or count(table identity column)
>> >> > show
>> >> > a
>> >> > much bigger number than the actual number of rows.
>> >> >
>> >> > When I run
>> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >
>> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> > from
>> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
>> >> > will
>> >> > not
>> >> > help me.
>> >> >
>> >> > Why I get such different result. What should I do to correct this.
>> >> >
>> >> > Thank you,
>> >> > ktf
>> >>
>> >>
>> >>
>>|||Jasper,
It gave me the correct number.
the config_value and run_value are set to 0 for "max degree of parallelism.
do you think I should turn it on.
Is it necessary to install sql sp4. The site does not say much about sp4.
Thank you,
ktf
"Jasper Smith" wrote:
> It's a query hint, it only affects the specific query in question. It won't
> impact anything else. To avoid any issues just run
> select count(*)
> from tablename with(nolock)
> option(maxdop 1)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> > Before I do that:
> > Is it going to reconfigure and change the server setting or it is only
> > within the session?
> > Is it going to put a big impact on the server?
> > Because I do not want to make that change yet.
> > Do I have to install SP4? Because we are not on 64-bit server.
> >
> > Thank you,
> >
> >
> > "Jasper Smith" wrote:
> >
> >> Sounds like you're running into the bug then. Did you try doing a count
> >> with
> >> option(maxdop 1) ?
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> >> > It is:
> >> > SQL enterprise 2000 clustered
> >> > NT.5.0.(2195)
> >> > 8.00.760. SP3
> >> > 7GB memory
> >> > 4 processor
> >> >
> >> >
> >> > "Jasper Smith" wrote:
> >> >
> >> >> What service pack are you on? Also try doing a count with
> >> >> OPTION(MAXDOP
> >> >> 1)
> >> >> and see if that works. It sounds like this bug
> >> >>
> >> >> FIX: A parallel query may return unexpected results
> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Jasper Smith (SQL Server MVP)
> >> >> http://www.sqldbatips.com
> >> >> I support PASS - the definitive, global
> >> >> community for SQL Server professionals -
> >> >> http://www.sqlpass.org
> >> >>
> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> >> > The problem I have is the count(*) or count(table identity column)
> >> >> > show
> >> >> > a
> >> >> > much bigger number than the actual number of rows.
> >> >> >
> >> >> > When I run
> >> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >> >
> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
> >> >> > from
> >> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
> >> >> > will
> >> >> > not
> >> >> > help me.
> >> >> >
> >> >> > Why I get such different result. What should I do to correct this.
> >> >> >
> >> >> > Thank you,
> >> >> > ktf
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||The fix for this bug is in SP4. Changing the server wide maxdop settings
will obviously affect all other queries so should only be done after
extensive testing. If you are not seeing any application related issues due
to this bug and it is only affecting "DBA" type activities then you don't
necessarily need to get on SP4 however it's probably worth doing anyway to
keep upto to date with the latest bug fixes
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:30CBAF3D-FC19-4F06-89A8-ED074AF96A98@.microsoft.com...
> Jasper,
> It gave me the correct number.
> the config_value and run_value are set to 0 for "max degree of
> parallelism.
> do you think I should turn it on.
> Is it necessary to install sql sp4. The site does not say much about sp4.
> Thank you,
> ktf
> "Jasper Smith" wrote:
>> It's a query hint, it only affects the specific query in question. It
>> won't
>> impact anything else. To avoid any issues just run
>> select count(*)
>> from tablename with(nolock)
>> option(maxdop 1)
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
>> > Before I do that:
>> > Is it going to reconfigure and change the server setting or it is only
>> > within the session?
>> > Is it going to put a big impact on the server?
>> > Because I do not want to make that change yet.
>> > Do I have to install SP4? Because we are not on 64-bit server.
>> >
>> > Thank you,
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> Sounds like you're running into the bug then. Did you try doing a
>> >> count
>> >> with
>> >> option(maxdop 1) ?
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> >> > It is:
>> >> > SQL enterprise 2000 clustered
>> >> > NT.5.0.(2195)
>> >> > 8.00.760. SP3
>> >> > 7GB memory
>> >> > 4 processor
>> >> >
>> >> >
>> >> > "Jasper Smith" wrote:
>> >> >
>> >> >> What service pack are you on? Also try doing a count with
>> >> >> OPTION(MAXDOP
>> >> >> 1)
>> >> >> and see if that works. It sounds like this bug
>> >> >>
>> >> >> FIX: A parallel query may return unexpected results
>> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >>
>> >> >> Jasper Smith (SQL Server MVP)
>> >> >> http://www.sqldbatips.com
>> >> >> I support PASS - the definitive, global
>> >> >> community for SQL Server professionals -
>> >> >> http://www.sqlpass.org
>> >> >>
>> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> >> > The problem I have is the count(*) or count(table identity
>> >> >> > column)
>> >> >> > show
>> >> >> > a
>> >> >> > much bigger number than the actual number of rows.
>> >> >> >
>> >> >> > When I run
>> >> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >> >
>> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> >> > from
>> >> >> > sysindexes shows the correct number of ~10 M. So DBCC
>> >> >> > UPDATEUSAGE
>> >> >> > will
>> >> >> > not
>> >> >> > help me.
>> >> >> >
>> >> >> > Why I get such different result. What should I do to correct
>> >> >> > this.
>> >> >> >
>> >> >> > Thank you,
>> >> >> > ktf
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>sql

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktf
What do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf
|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/default...en-us%3b814509
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktfWhat do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/defaul...ben-us%3b814509
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf

MSSQL2000-Self-Join Query Tuning help

SELECT * FROM TA a WHERE a.rx=264886 and
AN= (select max(AN) FROM TA where rx=a.rx)

I have a table TA with 8+ million rows and there is clustered PK on (rx, AN) columns. The count on rx=264886 is 6000+ rows. This query takes about 1 to 2 minutes to fetch data. Can anyone suggest how to improve performance and fetch data faster?

Thanks, VinnieI'd say:
SELECT TOP 1 * FROM TA WHERE rx=264886
ORDER BY an DESC

<edit>
That only works if there is only one row with an = max(an).|||that statement does not include the max(AN) for rn value. and when i just added "top 1" it did not improve any performance at all with an=(select max(an) ...) clause. Any other ideas?

Thanks, Vinnie