Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199sql

duplicates

How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.See if this helps:
http://support.microsoft.com/defaul...kb;en-us;139444
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.|||Please refer to www.aspfaq.com/5006 and provide sufficient information for
others to better understand you problem.
Deletes are applicable to a set of rows, not individual columns. If the
entire row is duplicated, then use the link posted by Vyas & make sure you
explicitly declare a primary key to avoid further duplication. If only the
sytemname is duplicated, they you will have to decide which values of memory
and/or cpu should be preserved and which ones should be deleted. Generally,
one can apply an extrema aggregate like MIN or MAX like:
SELECT *
FROM tbl t1
WHERE t1.Memory = ( SELECT MAX( t2.memory ) FROM tbl t2
WHERE t2.system = t1.system )
You can convert this into a delete like:
DELETE FROM tbl
WHERE Memory = ( SELECT MAX( t1.memory ) FROM tbl t1
WHERE tbl.system = t1.system )
If the duplicated values are determined by memory and/or cpu, you can use
WHERE Memory + '!' + cpu = ( SELECT MAX( t1.Memory + '!' + t1.cpu ) ...
Another way of writing it would be
DELETE FROM tbl
WHERE EXISTS ( SELECT * FROM tbl t1
WHERE t1.Memory > tbl.Memory
OR t1.cpu > tbl.cpu
OR ... )
Anith|||Of course, once you have duplicates purged, you should implement a unique
key constraint to prevent this from occurring again. But to answer your
question, let's say you have a table called Accounts and the column
combination tran_type should be unique. The following query will list all
records that violate the non-unique rule. We are basically joining back to a
group by sub-query having count(*) > 1. You can modify this as needed to fit
your situation. Once done, you can better profile the records to determine
which should be deleted. For example, do you want to keep the first entered
or last entered, or perhaps duplicates entered during a specific time frame
should be deleted.
select
Accounts.*
from
Accounts
join
(
select
account,
tran_type,
count(*) as cnt
from
Accounts
group by
account,
tran_type
having
count(*) > 1
) as x
on x.account= Accounts.account and
x.tran_type= Accounts.tran_type
order by
NSA.account,
NSA.tran_type
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
> How do I delete duplicate entries?
> For example, lets says I have the following table format:
> SystemName, Memory, CPU
> I would like to delete any duplicates that are in SystemName.
>
>

duplicates

hi, here i am giving details about problem,please help me

table a
---
Std_id Std_name Grade Subject Score year
----------------
1 name1 grade3 mathematics 200 200102
2 name2 grade5 science 150 200001
1 name1 grade3 science 300 200102
3 name3 grade4 english 500 200203
4 name4 grade6 social studies 350 200304
5 name5 grade8 history 440 200405

table b
---
subject year
-------
mathematics 200304
science 200304
english 200304
social studies 200304
history 200304
mathematics 200405
science 200405
english 200405
social studies 200405
history 200405

query 1 is
select a.std_id,a.std_name,sum(a.score),b.-- from a,b
where a.subject=b.subject
group by Std_name,....

out put is
std_id std_name sum(score) .....
----------
1 name1 1000
2 name2 300
3 name3 1000
4 name4 700
5 name5 880

but actual output should be like
std_id std_name sum(score)
----------
1 name1 500
2 name2 150
3 name3 500
4 name4 350
5 name5 440

to get this output i have modified the table b and query then i got the right results

table b
---
mathematics 200001
science 200001
english 200001
social studies 200001
history 200001
mathematics 200102
science 200102
english 200102
social studies 200102
history 200102
mathematics 200203
science 200203
english 200203
social studies 200203
history 200203
mathematics 200304
science 200304
english 200304
social studies 200304
history 200304
mathematics 200405
science 200405
english 200405
social studies 200405
history 200405

query is

select a.std_id,a.std_name,sum(a.score),b.-- from a,b
where a.subject=b.subject and
a.year=b.year
group by std_name,....

now actual and expecting results are same
std_id std_name sum(score)
----------
1 name1 500
2 name2 150
3 name3 500
4 name4 350
5 name5 440

so my question comes here
is there any other way which i can get the same out put without adding rows(data) to table b,
please help me out of this problem.

thank you
pracalus27 views no suggestions.please somebody give me suggestion to resolve this problem

thank you
pracalus|||Try this

select a.std_id,a.std_name,sum(a.score),b.-- from a,b
where a.subject=b.subject and a.year=b.year
group by Std_name,....

Otherwise Post your query1 fully|||Hi Madhi,

thank you, you said use year=year then how it can pick the 20001,200102 and 200203 years data.it only fetch 200304 and 200405 years.
table a table b
200001
200102
200203
200304 200304
200405 200405

thank you
pracalus

Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199

Duplicates

Hi all,
This works fine to show duplicates of NumbB Column. I want to show the
duplicates and = different lastnames with those that
are duplicates.
Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
where NumbB in(select NumbB from PC
group by NumbB
having count(NumbB)>1)order by NumbB
thanks
GVOne general approach is:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.col <= t1.col ) >= 2 ;
As a side note, for such questions, always post table DDLs & sample data
along with expected results so that others can understand your requirements
better.
Anith|||> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
Can you rephrase what you want?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"gv" wrote:

> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>
>|||Hi again and thanks
Ok, thanks for your help
I want to pull duplicates of Col2, order them by Col2 and were
Col3(lastname) is the same within the duplicates.
SELECT Col1,Col2,Col3,Col4,Col5
FROM Table1
WHERE Col2 IN(SELECT Col2 FROM Table1
GROUP BY Col2
HAVING COUNT(Col2)>1)
ORDER BY Col2
Sample rows
Col1 Col2 Col3 Col4 Col5
532 000 doe J Intraductal EUS
8675 000 loe S Gastric
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
836 632 QQQ F Gastric
So would like the return to be:
Col1 Col2 Col3 Col4 Col5
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
thanks
GV
"gv" <viatorg@.musc.edu> wrote in message
news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>|||Try (not tested),
SELECT a.Col1,a.Col2,a.Col3,a.Col4,a.Col5
FROM
Table1 as a
inner join
(
SELECT Col2, col5
FROM Table1
GROUP BY Col2, col5
HAVING COUNT(*) > 1
) as b
on a.col2 = b.col2 and a.col5 = b.col5
order by
a.col2, a.col5
AMB
"gv" wrote:

> Hi again and thanks
>
> Ok, thanks for your help
> I want to pull duplicates of Col2, order them by Col2 and were
> Col3(lastname) is the same within the duplicates.
>
> SELECT Col1,Col2,Col3,Col4,Col5
> FROM Table1
> WHERE Col2 IN(SELECT Col2 FROM Table1
> GROUP BY Col2
> HAVING COUNT(Col2)>1)
> ORDER BY Col2
> Sample rows
> Col1 Col2 Col3 Col4 Col5
> 532 000 doe J Intraductal EUS
> 8675 000 loe S Gastric
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
> 836 632 QQQ F Gastric
> So would like the return to be:
> Col1 Col2 Col3 Col4 Col5
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
>
> thanks
> GV
>
>
>
>
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
>
>

Duplicated Subreports

Hi all,

I can't seem to find the solution to this. I have created a report with 5 subreports (which are now linked). The problem is when I run the reports, the 5 reports when run (with a combined page total of 17 pages), now duplicate these records and produce 700 odd pages.

Can anyone help?

Thanks

MarcI would double check your links. Do you have a left outer join going on somewhere ?

duplicated rows

Hello,

I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6….

I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5='minimum' where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?

Hi JIM.H:

FYI, here is a quick sample:

create table tbl_testDup (ID int primary key, F1 varchar(50), F2 varchar(50), F3 varchar(50),
F4 varchar(50), F5 varchar(50), F6 varchar(50))

create procedure sp_MarkMin
as
update tbl_testDup set F5='minimum'
where ID in
(select min(ID) from tbl_testDup group by F1,F2,F3)
go

duplicated rows

Hello,

I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6….

I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=’minimum’ where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?

You can do something like below (assuming that ID column is unique):

update T1

set F5 = 'minimum'

where ID = (

select min(t.ID)

from T1

group by F1, F2, F3

having count(*) > 1

)

Else you can do below:

update t1

set F5 = 'minimum'

from T1 as t1

join (

select F1, F2, F3, min(t.ID) as min_id

from T1

group by F1, F2, F3

having count(*) > 1

) as t2

on t2.F1 = t1.F1 and t2.F2 = t1.F2 and t2.F3 = = t1.F3

where t2.min_id = t1.ID

|||Thanks, this helps a lot. It seems I need to mark all other rows as
NotMin. Is there a way to mark all the other duplicated rows as SET f5 =
'NotMinimum' if they are not Min(ID), there mgth be more than two rows
duplicated.|||

You can change the 2nd UPDATE stmt to:

update t1

set F5 = 'NotMinimum'

from T1 as t1

join (

select F1, F2, F3, min(t.ID) as min_id

from T1

group by F1, F2, F3

having count(*) > 1

) as t2

on t2.F1 = t1.F1 and t2.F2 = t1.F2 and t2.F3 = = t1.F3

where t2.min_id < t1.ID

Duplicated Rows

Hi,

I have just started developing in SQL Express in the last 2 months so still learning. The problem I’m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I’m asking.

A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom.

ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS

SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date],

CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date],

MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan'

WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb'

WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar'

WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr'

WHEN DATEPART(MM,SessionDate)=5 THEN 'May'

WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun'

WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul'

WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug'

WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep'

WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct'

WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov'

WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month'

FROM Sessions

GROUP BY [Job No], Sessions.SessionDate

ORDER BY [Job No]

Results in SQL Server Express

'Job No' 'DaystoDate' 'Start Date' 'End Date' 'End Month'

1113-001 0 08/16/2001 08/16/2001 Aug
1113-002 0.5 07/11/2000 07/11/2000 Jul
1113-002 0.5 02/09/2000 02/09/2000 Feb
1116-001 1 07/07/1999 07/07/1999 Jul
1116-001 1 07/06/1999 07/06/1999 Jul
1118-001 1 01/12/1999 01/12/1999 Jan
1118-001 0.5 03/17/1999 03/17/1999 Mar
1118-001 1 02/23/1999 02/23/1999 Feb
1118-001 1 01/26/1999 01/26/1999 Jan
1118-001 0.5 03/09/1999 03/09/1999 Mar
1118-001 1 12/15/1998 12/15/1998 Dec
1118-001 1 02/09/1999 02/09/1999 Feb

Results in Ms Access

Days Done per Job

JobNo

Days to Date

Start Date

End Date

End Month

1113-001

0.00

16/08/2001

16/08/2001

Aug01

1113-002

1.00

09/02/2000

11/07/2000

Jul00

1116-001

2.00

06/07/1999

07/07/1999

Jul99

1118-001

6.00

15/12/1998

17/03/1999

Mar99

Not being an expert by any means, my hunch is that you should set up the first column as a primary key. It will never allow duplicate values after that. It will take you a second to do it in the SQL database table. Right click on the column name in designer. It may not eliminate the problem completely in a sense that there might be a bug in your code that does it. At least you will get an error message from the server protesting the fact that somebody is sending duplicates to it. It will be a cue for you where to look.|||Cheers Alex will give it a go.

Duplicated Records

invno frecref pono
---- ------ ----
INV0000006 INV000000601 X0000009
INV0000006 INV000000601 X0000010
INV0000006 INV000000601 X0000043
INV0000006 INV000000602 X0000009
INV0000006 INV000000602 X0000010
INV0000006 INV000000602 X0000043
INV0000006 INV000000603 X0000009
INV0000006 INV000000603 X0000010
INV0000006 INV000000603 X0000043
INV0000006 INV000000604 X0000009
INV0000006 INV000000604 X0000010
INV0000006 INV000000604 X0000043

Hi,

how do i elimate duplicated records? i tried suppress if duplicated but cant.I dont see any duplicated records in the data you posted... :confused:|||Hi wizards,

whats i mean were repeated data in column.. how do i suppress it?
just wanted to show only

INV0000006 INV000000601 X0000009
INV000000602 X0000010
INV000000603 X0000043
INV000000604|||Do a group on Invno and place the fields in group footer then it will show only the last record of each groupsql

Duplicated parameter names are not allowed.

I am getting this exception in RC1:

SqlCException: Duplicated parameter names are not allowed. [ Parameter name = @.SOLUTIONID ]

With the following statement:

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID IN (SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID);

Besides the obvious of adding a new parameter with the same value and a different name - are there any plans to fix this?

Another area where SQLCE seems deficient is non-support of SCOPE_IDENTITY(), which although not important for CE itself is very important for TSQL compatibility with SQL Server - where obviously @.@.IDENTITY won't cut it.

A related problem to this is that SQL CE doesn't support batch statements - which sadly destroys the useful pattern of inserting a record with an INSERT and doing an identity SELECT in the same batch.

When programming against a SQL Server there is benefit from doing as much work as possible in one round trip. However with CE all the work is being done on your desktop so there is little to be gained in creating complex, multistepped batches.

Have you considered splitting your query into 2? Secondly have you considered dynamically creating the SQL, or a prepared query?

By splitting it as:

SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID

as a scalar query and then executing the second part as

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID = @.MAXVERSIONID

it allows you to use an index on VERSIONID.

In addition you can check that there is a MAX(VERSIONID) and if there isn't then insert some program logic at this step to avoid the second SELECT.

As for the @.@.IDENTITY problem couldn't you use the Insert method of SQLCEResultSet and then read the identity column?

Just a thought
|||Thanks for the indexing tips Brian, much appreciated - the example TSQL given is unoptimized it's true.

However my question wasn't really about indexing - it was about the subtle differences between SQL Server and SQL CE TSQL (mostly syntactical) which make it really hard to share TSQL between SQL Server and SQL CE.

Is there any chance of SCOPE_IDENTITY() and support for batches sneaking into a version sometime soon? We are using the SQLClient and SQLCE providers via the really useful provider factory in ADO.NET so if the TSQL could be shared that would be ideal.|||I suspect that in the short term SQL CE version of T-SQL will remain very close to that of SQL Mobile from which it has descended.

Perhaps in CE 2.0 some of the features will be added? but wont the mobile people then complain?

Duplicated parameter names are not allowed.

I am getting this exception in RC1:

SqlCException: Duplicated parameter names are not allowed. [ Parameter name = @.SOLUTIONID ]

With the following statement:

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID IN (SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID);

Besides the obvious of adding a new parameter with the same value and a different name - are there any plans to fix this?

Another area where SQLCE seems deficient is non-support of SCOPE_IDENTITY(), which although not important for CE itself is very important for TSQL compatibility with SQL Server - where obviously @.@.IDENTITY won't cut it.

A related problem to this is that SQL CE doesn't support batch statements - which sadly destroys the useful pattern of inserting a record with an INSERT and doing an identity SELECT in the same batch.

When programming against a SQL Server there is benefit from doing as much work as possible in one round trip. However with CE all the work is being done on your desktop so there is little to be gained in creating complex, multistepped batches.

Have you considered splitting your query into 2? Secondly have you considered dynamically creating the SQL, or a prepared query?

By splitting it as:

SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID

as a scalar query and then executing the second part as

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID = @.MAXVERSIONID

it allows you to use an index on VERSIONID.

In addition you can check that there is a MAX(VERSIONID) and if there isn't then insert some program logic at this step to avoid the second SELECT.

As for the @.@.IDENTITY problem couldn't you use the Insert method of SQLCEResultSet and then read the identity column?

Just a thought
|||Thanks for the indexing tips Brian, much appreciated - the example TSQL given is unoptimized it's true.

However my question wasn't really about indexing - it was about the subtle differences between SQL Server and SQL CE TSQL (mostly syntactical) which make it really hard to share TSQL between SQL Server and SQL CE.

Is there any chance of SCOPE_IDENTITY() and support for batches sneaking into a version sometime soon? We are using the SQLClient and SQLCE providers via the really useful provider factory in ADO.NET so if the TSQL could be shared that would be ideal.|||I suspect that in the short term SQL CE version of T-SQL will remain very close to that of SQL Mobile from which it has descended.

Perhaps in CE 2.0 some of the features will be added? but wont the mobile people then complain?

duplicated data display

Hi all. i have the following function below,which use to retrieve the order detail from 2 table which are order detail and product. i have many duplicated order id in order detail, and each order id has a unique product id which link to product to display the product information. however when i run the following function below . its duplicated each product info and dsplay in the combo box. May i know whats wrong with my code?

Public Sub ProductShow()

Dim myReader As SqlCeDataReader
Dim mySqlCommand As SqlCeCommand
Dim myCommandBehavior As New CommandBehavior
Try
connLocal.Open()
mySqlCommand = New SqlCeCommand
mySqlCommand = connLocal.CreateCommand
mySqlCommand.CommandText = "SELECT * FROM Product P,orders O,orderdetail OD WHERE OD.O_Id='" & [Global].O_Id & "' AND P.P_Id=OD.P_Id "
myCommandBehavior = CommandBehavior.CloseConnection
myReader = mySqlCommand.ExecuteReader(myCommandBehavior)
While (myReader.Read())
cboProductPurchased.Items.Add(myReader("P_Name").ToString())
End While
myReader.Close()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
connLocal.Close()
End Try

End Sub

Can you please provide sample data also?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Duplicated Admin Passwords need to be replaced with unique passwor

We need to change passwords for 4 replicating servers. To date, they've all
shared the same Administrator password and connect using NT authentication.
The servers now need to have unique passwords. 2 servers are members of the
same domain and the other two are members of seperate workgroups. What's the
most secure and efficient setup for replicating servers with unique
passwords? Create a unique administrator rights user on each server and set
SQL to start up as this user on each server and duplicate the user on the
other servers so authentication works? ...
Steven,
I assume you are referring to the agent startup logins? Typically you use a
domain login who is a local administrator - same account for the SQL Server
agent and SQL Server Service. If they are different on each server and
replication is using trusted security, have a look in BOL for the rights
required in replication for the SQL Server Agent in each part of your
topology ("replication, security"), and the entry required in the PAL.
HTH,
Paul Ibison

Duplicate xReference Fields in 1 query ?

HI

Its been a while since i have posted here, but this one has stumped me. maybe i am just super hungry right now and missing something obvious... but i can get this one.

i would search the forums for the answer, but not even sure what to search for... not even sure if this (left joins) is the way to accomplish what i want.

Using MS-SQL database as the back end.

I have 3 data tables.
Table 1 is a cross reference table.
Table 2 is the primary data (group data)
Table 3 is the secondary data (users data) (multiple users in a group)

table 1 provides the text that matches the respective status from both the 2nd and 3rd tables.

xRefTable
MemberStatus MemberStatusText
---- ------
0 Non Member
1 Referral
2 Resident
3 Non Resident

PrimaryUserTable
GroupID GroupStatus
-- ----
1 0
2 3
3 1
4 2

SecondaryDataTable
MemberID GroupID MemberStatus
--- --- ----
1 1 0
2 1 0
3 1 0
4 1 0
5 2 3
6 2 3
7 3 1
8 3 1
9 4 2
10 4 1
11 4 1
12 4 3

The ultimate results i am trying to get is something like :

The Results should be able to yield

GroupID GroupStatus GroupStatusText MemberID MemberStatus MemberStatusText
--- ---- ----- --- ---- ------
4 2 Resident 9 2 Resident
4 2 Resident 10 1 Referral
4 2 Resident 11 1 Referral
4 2 Resident 12 3 Non Resident

i am trying to do this with a single SQL query, i am sure it is possible, but cant finger out how to structure it. this is what i was thinking, but i am sure you can see its not going to work.

Lines 4 and 7 are the hangup i believe.
Since both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?

1 SELECT
2 PrimaryUserTable.GroupID,
3 PrimaryUserTable.GroupStatus,
4 xRefTable.MemberStatusText as GroupStatusText

5 SecondaryDataTable.MemberID,
6 SecondaryDataTable.MemberStatus,
7 xRefTable.MemberStatusText as MemberStatusText

8 LEFT JOIN SecondaryDataTable on (PrimaryUserTable.GroupID = SecondaryDataTable.GroupID)
9 LEFT JOIN xRefTable on (PrimaryUserTable.GroupStatus = xRefTable.MemberStatus)
10 LEFT JOIN xRefTable on (SecondaryDataTable.MemberStatus = xRefTable.MemberStatus)

11 WHERE GroupID = 4
12 ORDER BY GroupID, MemberIDSince both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?using table aliasesSELECT PrimaryUserTable.GroupID
, PrimaryUserTable.GroupStatus
, GroupXref.MemberStatusText as GroupStatusText
, SecondaryDataTable.MemberID
, SecondaryDataTable.MemberStatus
, MemberXref.MemberStatusText as MemberStatusText
FROM PrimaryUserTable
INNER
JOIN SecondaryDataTable
on SecondaryDataTable.GroupID = PrimaryUserTable.GroupID
INNER
JOIN xRefTable as GroupXref
on GroupXref.MemberStatus = PrimaryUserTable.GroupStatus
INNER
JOIN xRefTable as MemberXref
on MemberXref.MemberStatus = SecondaryDataTable.MemberStatus
WHERE PrimaryUserTable.GroupID = 4
ORDER
BY PrimaryUserTable.GroupID
, SecondaryDataTable.MemberID:)|||EXCELLENT !

exactly what i needed. the table alisases were just skipping from my mind that night.

THANK YOUsql

Duplicate Values Problem

I trying to insert values from a temporary table into a permanent table. Th
e
problem is the temporary table has duplicate UpdateTime values (issues with
the database used to populate the temporary table) and the UpdateTime is a
primary key in the permanent table.
Is there a way I can remove, or exclude, the duplicate values in the
temporary table before inserting the values into the permanent table?
RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
value with the larger RUN_SER_NO is the one to keep.
UpdateTime GRADE RUN_SER_NO
11-Aug-04 04:00 AA 634
11-Aug-04 05:00 AA 634
11-Aug-04 06:00 AA 634
11-Aug-04 07:00 VVV 636
11-Aug-04 07:00 VVV 635
11-Aug-04 08:00 VVV 635
11-Aug-04 08:00 VVV 636
11-Aug-04 09:00 VVV 636
11-Aug-04 09:00 VVV 635
11-Aug-04 10:00 VVV 635
11-Aug-04 10:00 VVV 636
11-Aug-04 11:00 VVV 636
11-Aug-04 12:00 VVV 636
11-Aug-04 13:00 VVV 636
11-Aug-04 14:00 VVV 636
11-Aug-04 15:00 VVV 636
Thanks in advance,
Raulselect UpdateTime from table_name group by UpdateTime having count(*) > 1
will give you the duplicates.You can remove them by using this query before
inserting into the permanent table.
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>|||How about:
SELECT update_time, grade, run_ser_no
FROM tbl t1
WHERE t1.run_ser_no = ( SELECT MAX( t2.run_ser_no )
FROM tbl t2
WHERE t2.UpdateTime = t1.UpdateTime
AND t1.grade = t2.grade ) ;
Anith|||On Tue, 8 Feb 2005 14:11:04 -0800, Raul wrote:

>I trying to insert values from a temporary table into a permanent table. T
he
>problem is the temporary table has duplicate UpdateTime values (issues with
>the database used to populate the temporary table) and the UpdateTime is a
>primary key in the permanent table.
>Is there a way I can remove, or exclude, the duplicate values in the
>temporary table before inserting the values into the permanent table?
Hi Raul,
SELECT UpdateTime, Grade, Run_ser_no
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.UpdateTime = a.UpdateTime
AND b.Run_ser_no > a.Run_ser_no)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all who replied!
These suggestions are really helpful.
Thanks again,
Raul
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>

Duplicate Values on Top Level of Dimension

Hey there community,

I have a problem, and i am lost as to how i can fix it.. I would be over joyed if someone can give me a hand in identiffy it.. I will try to explain below::

I have a cube for sales, that displays sales by a Market heiracy of Market->Division->Family->Item. When i drill down, sales numbers look fine..

I added in a budget file, which only specifys budgets down the the Division Level.. I have added the Budget Figure as a Measure Group. In the dimension usage tab, i specify the Division as the Granuality for the Budget Measure Group on the Market Dimension..

	Measure Groups
Dimensions	Standard Measure	Budget Measures	Freight Amount
Date	Date	Period	Date
Sales Person	Sales Person		Sales Person
Order Type	Order Type
Customer	Customer	Country	Customer ID
Market	Item	Division

When i build and deploy my cube, and i drag in the Market Heiracy, i add the sales fugures and the budget amounts. The top level, should split down on the Budget Figure, but i get the same value repeated all the way through like below:

Market	Sales Amount	Budget Amount
aaaaa	527434.08	3819283
bbbbb	1605726.50999999	3819283
cccccc	640.06	3819283
ddddd	1488549.15	3819283
eeeee	9107.59	3819283
Grand Total	3631457.39	3819283

when i Drill Down on the Market to the Division Level, the splits are correct, but the totals are all the same all the way down.. The should be the total of that market...

Market	Division	Sales Amount	Budget Amount
aaaaa	GP	24100.27	13244
	PT	503333.81	635552
	Total	527434.08	3819283
bbbbb	GP	333209.5	335234
	Other	167680.46	127426
	PT	1104836.55000001	1122647
	Total	1605726.50999999	3819283
cccccc	PT	640.06	0
	Total	640.06	3819283
ddddd	GP	790620.499999999	794611
	Other	11206.16	14831
	PT	686722.49	769930
	Total	1488549.15	3819283
eeeee	Other	9107.59	5808
	Total	9107.59	3819283
Grand Total		3631457.39	3819283

I would like the totals to be the total budget for each market.. I am stuck, i have tried some different things, but this is as far as can get..

If i choose the Market to be the Granuality for the Budget Measure Group, then i get the reverse of this.. The market splits are correct, but when i drill down to the Division Level, the values repeat..

Please can someone give me some ideas of what to try!

I can provide more data if need be.

Many thanks in advance!

Thanks Scott Lancaster

I reckon you don't have relationship defined between Market and Division. If you will make Market a member property of Division - everything should become fine (and keep Division as granularity for Budget measure group).|||

Thanks Mosha..

Could you please explain how i do this? Im pretty new to SSAS and have taught myself most of the way

Again.. very very much appriciated..

Thanks

|||In the dimension editor, drag Market attribute and drop it into Division attribute. This will create the needed relationship. If still in doubt, read documentation on the subject of "related attributes" or "member properties".|||

Thanks Mosha!!!!

your are a champion.. I have done this and it has all worked perfectly...

Again.. thanks mate..

Scotty

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

duplicate values

hey guys,

ive run into an issue and im not quite sure how to fix it.. let me explain

i have a cube, by market and i wish to show sales and freight costs.

The freight Fact table has no relationship to the market dimension as freigth is charged on customer order level, not item level.

I have data showing like the following:

Year

2006

Market

Sales

Freight Amount

Auto OEM

$7,115,315.22

$56,620.73

Auto Repl

$18,517,004.07

$56,620.73

Intercompany

$4,772,910.37

$56,620.73

Ind OEM

$12,039.74

$56,620.73

Ind Repl

$18,679,313.71

$56,620.73

Other

$84,263.19

$56,620.73

Grand Total

$49,180,846.30

$56,620.73

I would only like the freigth amount to show at the Grand Total Level. Is this possible? or, if not, is there a way you can combine two dimensions into 1? ie, combine a Freight Account dimemsion and the Market Dimension so that i can see Markets, then the different Freight Accounts by rows?

Thanks in advance,

Scotty

If you look in the cube structure tab in BIDS, you will see that each measure group has a property called IgnoreUnrelatedDimensions, this defaults to true which means that when you slice a measure by an unrelated measure you see the total amount (which is the behaviour you are seeing). Changing this property to false will mean that the measures will only display when they are either sliced by a related dimension or at the highest level.

It's hard to be sure, but from what you have said I don't think combining the two dimension would work.

Duplicate Values

I have a table which is a license holder table (i.e., plumbers, electricians etc...) There are some people who appear in the table more than once as they have more than 1 type of license. I am tasked with querying out 200 of these people a week for mailing a recruitment letter which I am doing using the following select statement:

SELECT TOP 200 Technicians.Name, Technicians.Address, Technicians.City, Technicians.State, Technicians.ZipCode, Technicians.LicenseType
FROM Technicians

My problem is that this doesn't deal with the duplicates and distinct won't work because I need to pass the license type and that's the one field that's always distinct while the name and adress fields duplicate.You'll need to determine which LicenseType to return in cases where there is more than one. This example returns the lowest LicenseType alphabetically:

SELECT TOP 200
Technicians.Name,
Technicians.Address,
Technicians.City,
Technicians.State,
Technicians.ZipCode,
min(Technicians.LicenseType) LicenseType
FROM Technicians
group by Technicians.Name,
Technicians.Address,
Technicians.City,
Technicians.State,
Technicians.ZipCode|||Why don't you break the structure into PersonMaster, LicenseMaster, and PersonLicense? Then you can do an insert ... select distinct into those tables accordingly. The rest is common sense.|||Yes, if you can solve your problem by improving your database schema that is always preferable.|||Assume name is enough (You may have to do the whole row).

Notice the duplicity of Data...You really have 2 tables. That would make the SQL even easier...

There's no substitute for a good design..

USE Northwind
GO

CREATE TABLE myTable99(
[Name] varchar(50)
, Address varchar(255)
, City varchar(50)
, State char(2)
, ZipCode varchar(10)
, LicenseType varchar(10)
)
GO

INSERT INTO myTable99(
[Name]
, Address
, City
, State
, ZipCode
, LicenseType)
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC123' UNION ALL
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC456' UNION ALL
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC789' UNION ALL
SELECT 'Blinddude','123 Main St','OhiYo','OH','00000','XXX123' UNION ALL
SELECT 'Blinddude','123 Main St','OhiYo','OH','00000','XXX456' UNION ALL
SELECT 'rdjabarov','123 Main St','San Antonio','TX','00000','EFG123'
GO

SELECT TOP 200
[Name]
, Address
, City
, State
, ZipCode
, LicenseType
FROM myTable99 o
WHERE EXISTS (SELECT
[Name]
, Address
, City
, State
, ZipCode
FROM myTable99 i
WHERE i.[Name] = o.[Name]
GROUP BY
[Name]
, Address
, City
, State
, ZipCode
HAVING COUNT(*) > 1)
GO

DROP TABLE myTable99
GO|||Brett, i am disappointed i'm not in there as a plumber or something

by the way, your query only pulls out people who are multi-licensed

i guess that is one way to interpret "these people" in the original question

i personally would not have interpreted it as 200 of people with more than one license, but rather, 200 people overall, but no individual more than once

once again, good specs are shown to be crucial before we go merrily traipsing down the WHERE EXISTS path...

;) ;) ;)

in any case, would your WHERE clause not work better like this, assuming you were actually interested in pick only multi-license people...
WHERE 1 < ( SELECT count(*)
FROM myTable99 i
WHERE i.[Name] = o.[Name] )it's a correlated subquery after all, so it shouldn't need grouping

p.s. where's that thread where we were talking about the DBA getting the shaft for poor design? i have a link i want to add to it|||Rudy,

There are some people who appear in the table more than once as they have more than 1 type of license.

I just felt that THESE meant THOSE:D

And yes I was debating you're syntax...but I figured dup rows less the license meant the same guy...

Either way...it's a poor design, which I'm sure they're stuck with...

Maybe an updateable view would be a good thing here..|||Originally posted by Brett Kaiser
Maybe an updateable view would be a good thing here.. Nahhh, an updatable view would be a work-around. Fixing the underlying problems in the schema would be the good thing in this case!

-PatP|||Unfortunately, the database is provided directly by the State Board of Licensing and constantly updated so fixing the schema is not really an option. Great suggesions by the way. Because I didn't have a lot of time when I first posted I created a stored procedure that found duplicate license holders and marked all but instance as having already been mailed so therefore my original query which looks for licensees that have not already been mailed works without locating those duplicates. As the State updates the database I'll import only the new rows into the database (with the added [Mailed] bit field) and rerun the stored proc to mark duplicates as having already been mailed leaving me with a distinct record set. Thanks again for all your help!sql

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.
SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
--
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:
> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate UID in PK column

Please excuse my noviceness, but how is it possible that I
can have duplicate records that are exactly the same in
every way when there is a PK field that is meant to be
unique?
Because this has happened, I've had to remove and re-index.
Can anyone please help this novice?That shouldn't happen, and I've never heard that it really happened. But it
is difficult to do anything or troubleshoot if you don't have a repro or
don't have the data anymore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Benjamin" <ben.jones@.trendwest.com.au> wrote in message
news:2c7301c3a8ad$61c25480$3101280a@.phx.gbl...
> Please excuse my noviceness, but how is it possible that I
> can have duplicate records that are exactly the same in
> every way when there is a PK field that is meant to be
> unique?
> Because this has happened, I've had to remove and re-index.
> Can anyone please help this novice?

duplicate the SQL login

I have some sql login but I have forgotten the password. I can't modify the password anyway. Is there anyway to copy the login to a new login so that I can use the new login to access the database which it is granted to?

i am not sure i got the problem correctly ........ but anyhow you can not have duplicate login... but what u can do is you can create script of the existing login and then change only the name and password ... keep all other setting... this is as good as cloning of this login...

BTW ... why you can not change the password

Madhu

Duplicate Text data

Anyone have a method for identifiying dupes in a text field?
coromokes wrote:
> Anyone have a method for identifiying dupes in a text field?
By dupes, you mean the existence of the same text value in more than one
row? If so, you can group on the text column and use a having clause to
test for dupes.
You'll need to convert to a varchar or nvarchar data type first, which
means you'll only get access to teh first 4,000 or 8,0000 characters for
the test. But maybe that's good enough.
Select CAST(TextDataCol as VARCHAR(8000))
From TableName
Group By CAST(TextDataCol as VARCHAR(8000))
Having COUNT(*) > 1
David Gugick
Imceda Software
www.imceda.com
sql

Duplicate tables in subscriber database

we are replicating a table in sql 2000, all seems to be going well, but there
seem to be twice as many tables in the subscriber database than on the
publisher. For Example: there is talble f0092 that has the owner as dbo &
sys7334. This is the case for each single table, thus my database on the
subscriber is almost twice as large. Can anyone tell me why this happening
and what i need to do to correct this isssue
1. What is the sys7334 user? Does it own the replication?
2. Have you tried 1) drop subscription; 2) delete the tables in the
subscription database; 3) re-add subscription ? Or try creating a new
database and subscribe to your publication from it; does the same thing
happen? Those duplicate tables could be left over from a previous attempt
at replication.
Mike
"Mark Alejandro" <MarkAlejandro@.discussions.microsoft.com> wrote in message
news:36FCE4B3-FDB4-4600-885C-8A4D9F66C9B3@.microsoft.com...
> we are replicating a table in sql 2000, all seems to be going well, but
there
> seem to be twice as many tables in the subscriber database than on the
> publisher. For Example: there is talble f0092 that has the owner as dbo &
> sys7334. This is the case for each single table, thus my database on the
> subscriber is almost twice as large. Can anyone tell me why this happening
> and what i need to do to correct this isssue

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my db2 (same structure).
Is there a tool to make this easy?
The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>
|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>
|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
Thanks
GYK

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my DB2 (same structure).
Is there a tool to make this easy?The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my DB2 (same structure).
> Is there a tool to make this easy?
>|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my DB2 (same structure).
> Is there a tool to make this easy?
>|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
Thanks
GYK