Queries returning Multiple instances of the same record

I am running SQL 2005 and have created a simple database for interfacing with Visual Web Dev 2005 and Visual Studio 2005. I noticed that my applications where returning multiple instances of the same records in the queries. I went back to the SQL server and created a query, and sure enough, I'm getting each record returned 3 times. Where do I start to resolve this issue?DId you have the records already duplicated in the database tables ? Or did you just made a mistake in your join, defining the wrong joined keys. THe best thing would be to provide some information like DDL and some sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

My DB and query are much simpler than what you are imagining:

The DB Structure is:

<MemberID, Int,> - Primary Key Autoincrement

<FirstName, nvarchar(30),>

<LastName, nvarchar(30),>

<Salutation, nvarchar(20),>

<MemberType, nvarchar(20),>

<IsNeighbor, tinyint,>

<Title, nvarchar(30),>

<Address, nvarchar(60),>

<Address2, nvarchar(60),>

<City, nvarchar(30),>

<State, nvarchar(2),>

<Zip, nvarchar(9),>

<Phone, nvarchar(10),>

<Email, nvarchar(50),>

<DateJoined, datetime,>

<ExpirationDate, datetime,>

<SubMemberTo, int,>

<Fax, nvarchar(10),>

<Cellphone, nvarchar(10),>

The SELECT query is:

SELECT [MemberID]

,[FirstName]

,[LastName]

,[Salutation]

,[MemberType]

,[IsNeighbor]

,[Title]

,[Address]

,[Address2]

,[City]

,[State]

,[Zip]

,[Phone]

,[Email]

,[DateJoined]

,[ExpirationDate]

,[SubMemberTo]

,[Fax]

,[Cellphone]

FROM [FriendsSQL].[dbo].[Members]

WHERE FirstName = 'ANNE' and LastName = 'REIS'

Without the WHERE clause the query returns the entire DB without duplication, however, when the WHERE clause is included the output is:

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

Notice that the single record is returned 3 times.

|||DOH! You were right. The records were duplicated. Apparently using the SET Insert Unique ON and not having the Primary Key set allowed the duplications. I've cleaned up the mess and I'll try not to shoot off any more toes. Sorry for the bother. I should have caught that one.

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal :) ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> SQL 7.0
>> In QA:
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
>> However, if in QA:
>> "Select * from <table> where name = 'Jones' and ddate
>> = '20041010'
>> The name "Jones" is returned!
>> I can't figure this out for the life of me. It's a
table
>> with 95 million records and a clustered index based on
>> name,date.
>> Any help appreciated.
>> thx,
>> Don
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
> Just to make absolutely sure (nothing personal :) ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
--
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.
|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>
|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table
>
>.
>
|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>
|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith
|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?
|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>

QA returning negative #s as positive

Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Thanks in advance.I haven't had a problem with this. What does your query look like? Are the correct results being retuned to your ASP.NET application?

Terri|||Terri,

The query is a straight forward select, nothing tricky at all. Basically, if I look at the Amount values through the analyzer, it displays them as positive. However, one interesting to note is that it recognizes the number as being negative because if i add another column to the return so that it's "Amount * 1", it will return the correct (negative) value.

It basically seems to be a displaying issue. I google'd this issue, and came across a few posts from other forums where people were having the same issue, but none of the threads had an explanation.

Also, this is a problem only with this table. There are other tables in the warehouse that have decimal(18,2) columns with negative values, and they get displayed correctly. At the same time, though, there's nothing special about the table in question. The amount column is sourced from a different table with a column of number(21,6) type, which is really a glorified decimal, so that doesn't seem to be the problem.

I'm pretty much puzzled as to why this column would be displayed incorrectly like this. I welcome any suggestions/ideas.

Thanks|||Have you been able to resolve this issue? I have been out of town this past week and will jump back into this if you are still stuck.

Terri|||Hey Terri,

Actually, it's been sitting on the back burner, and I've been busy with other things. I would, ultimately, like to figure out what is happening, so if you have any ideas, I am all ears.

I did a search on Google groups, this forum, and a few others, and was unable to find an answer. I did find a few threads on various sources concerning this problem, but there was no resolution from anyone.

Thanks for the help.