Sql Cannot connect: duplicate

Showing posts with label duplicate. Show all posts

Sunday, March 25, 2012

Cannot DROP PROCEDURE 'duplicate incombolists' because it is being referenced by object

How to resolve this problem
Cannot DROP PROCEDURE 'duplicate _in_combolists' because it is being
referenced by object 'combolists_unique'.
Thanks in advance
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Presumably htis is on application of the snapshot? Before being created in
the initialization, objects are dropped. It looks like you have a dependency
which is preventing this being achieved. Please use sp_depends with
'duplicate _in_combolists' to investigate what is preventing the drop.
Removing the dependency should allow the initialization to proceed.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Tuesday, March 20, 2012

cannot delete duplicate rows

Is there any query which will delete the dulpicate rows in all manner of a
table.
But one row of each, duplicate row should remain the table after deletion.
The table does not contain any primary key.
I donot want to use temporary table.
One query only no script or cursor.
Oracle uses rowid in this situation.
Do SQL Server have any trick to do it in one line?
Thanks PaulAnibran
Since you did not post DDL +samole data please look at this example removes
duplications
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>|||Dear Uri Dimant
Please do not insert an primary key. As my table do not have any primary key
nor I am authorised to do that.
Please keep one row of the data which was present as duplicate.
Please do it in one query
<urid@.iscar.co.il> wrote in message
news:uxlNcYA5DHA.2008@.TK2MSFTNGP10.phx.gbl...
> Anibran
> Since you did not post DDL +samole data please look at this example
removes
> duplications
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> > Is there any query which will delete the dulpicate rows in all manner of
a
> > table.
> > But one row of each, duplicate row should remain the table after
deletion.
> > The table does not contain any primary key.
> > I donot want to use temporary table.
> > One query only no script or cursor.
> > Oracle uses rowid in this situation.
> > Do SQL Server have any trick to do it in one line?
> >
> >
> > Thanks Paul
> >
> >
> >
>|||Anirban,
You simply can't do this as a single query. The only way to specify which
rows to delete is to identify those rows based on the values of their
columns. The column values of identical rows are identical, so no
expression can be true for one row and false for an identical row.
You can do this with a temporary table, and you can hide the use of a
temporary table by doing this with a trigger (which creates the temporary
inserted and deleted tables):
create table T (
CustomerID char(5)
)
insert into T
select CustomerID
from Northwind..Orders
go
create trigger T_del on T for delete as
if not exists (
select * from T
)
insert into T
select distinct * from deleted
go
delete from T
go
select * from T
order by CustomerID
go
drop table T
SK
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>|||Thanks steve,
I was not sure some people keep telling me this is possible.
Are you sure this is not possible in sql 2000 also?
Thanks,
Paul
Steve Kass <skass@.drew.edu> wrote in message
news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
> Anirban,
> You simply can't do this as a single query. The only way to specify which
> rows to delete is to identify those rows based on the values of their
> columns. The column values of identical rows are identical, so no
> expression can be true for one row and false for an identical row.
> You can do this with a temporary table, and you can hide the use of a
> temporary table by doing this with a trigger (which creates the temporary
> inserted and deleted tables):
> create table T (
> CustomerID char(5)
> )
> insert into T
> select CustomerID
> from Northwind..Orders
> go
> create trigger T_del on T for delete as
> if not exists (
> select * from T
> )
> insert into T
> select distinct * from deleted
> go
> delete from T
> go
> select * from T
> order by CustomerID
> go
> drop table T
> SK
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> > Is there any query which will delete the dulpicate rows in all manner of
a
> > table.
> > But one row of each, duplicate row should remain the table after
deletion.
> > The table does not contain any primary key.
> > I donot want to use temporary table.
> > One query only no script or cursor.
> > Oracle uses rowid in this situation.
> > Do SQL Server have any trick to do it in one line?
> >
> >
> > Thanks Paul
> >
> >
> >
>|||I don't think it matters whether you use 7.0 or 2000. It might be
possible in other RDBMS products, and it would be possible in SQL Server
if some sort of unique ROWID value were available on every table. Then
you could do
delete from T
where exists (
select *
from T T2
where T.column1 = T2.column1
and T.column2 = T2.column2
and -- same for all columns
and T.ROWID > T2.ROWID
)
But there is no exposed ROWID in any version of SQL Server
SK
Anirban wrote:
>Thanks steve,
>I was not sure some people keep telling me this is possible.
>Are you sure this is not possible in sql 2000 also?
>Thanks,
>Paul
>Steve Kass <skass@.drew.edu> wrote in message
>news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
>
>>Anirban,
>>You simply can't do this as a single query. The only way to specify which
>>rows to delete is to identify those rows based on the values of their
>>columns. The column values of identical rows are identical, so no
>>expression can be true for one row and false for an identical row.
>>You can do this with a temporary table, and you can hide the use of a
>>temporary table by doing this with a trigger (which creates the temporary
>>inserted and deleted tables):
>>create table T (
>> CustomerID char(5)
>>)
>>insert into T
>>select CustomerID
>>from Northwind..Orders
>>go
>>create trigger T_del on T for delete as
>>if not exists (
>> select * from T
>>)
>>insert into T
>>select distinct * from deleted
>>go
>>delete from T
>>go
>>select * from T
>>order by CustomerID
>>go
>>drop table T
>>SK
>>"Anirban" <tulu_paul@.hotmail.com> wrote in message
>>news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
>>
>>Is there any query which will delete the dulpicate rows in all manner of
>>
>a
>
>>table.
>>But one row of each, duplicate row should remain the table after
>>
>deletion.
>
>>The table does not contain any primary key.
>>I donot want to use temporary table.
>>One query only no script or cursor.
>>Oracle uses rowid in this situation.
>>Do SQL Server have any trick to do it in one line?
>>
>>Thanks Paul
>>
>>
>>
>
>

cannot delete duplicate rows

quote:

> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>

|||Dear Uri Dimant
Please do not insert an primary key. As my table do not have any primary key
nor I am authorised to do that.
Please keep one row of the data which was present as duplicate.
Please do it in one query
<urid@.iscar.co.il> wrote in message
news:uxlNcYA5DHA.2008@.TK2MSFTNGP10.phx.gbl...

quote:

> Anibran
> Since you did not post DDL +samole data please look at this example

removes

quote:

> duplications
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
a[QUOTE]
deletion.[QUOTE]
>

|||Anirban,
You simply can't do this as a single query. The only way to specify which
rows to delete is to identify those rows based on the values of their
columns. The column values of identical rows are identical, so no
expression can be true for one row and false for an identical row.
You can do this with a temporary table, and you can hide the use of a
temporary table by doing this with a trigger (which creates the temporary
inserted and deleted tables):
create table T (
CustomerID char(5)
)
insert into T
select CustomerID
from Northwind..Orders
go
create trigger T_del on T for delete as
if not exists (
select * from T
)
insert into T
select distinct * from deleted
go
delete from T
go
select * from T
order by CustomerID
go
drop table T
SK
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...

quote:

> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>

|||Thanks steve,
I was not sure some people keep telling me this is possible.
Are you sure this is not possible in sql 2000 also?
Thanks,
Paul
Steve Kass <skass@.drew.edu> wrote in message
news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...

quote:

> Anirban,
> You simply can't do this as a single query. The only way to specify which
> rows to delete is to identify those rows based on the values of their
> columns. The column values of identical rows are identical, so no
> expression can be true for one row and false for an identical row.
> You can do this with a temporary table, and you can hide the use of a
> temporary table by doing this with a trigger (which creates the temporary
> inserted and deleted tables):
> create table T (
> CustomerID char(5)
> )
> insert into T
> select CustomerID
> from Northwind..Orders
> go
> create trigger T_del on T for delete as
> if not exists (
> select * from T
> )
> insert into T
> select distinct * from deleted
> go
> delete from T
> go
> select * from T
> order by CustomerID
> go
> drop table T
> SK
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
a[QUOTE]
deletion.[QUOTE]
>

|||I don't think it matters whether you use 7.0 or 2000. It might be
possible in other RDBMS products, and it would be possible in SQL Server
if some sort of unique ROWID value were available on every table. Then
you could do
delete from T
where exists (
select *
from T T2
where T.column1 = T2.column1
and T.column2 = T2.column2
and -- same for all columns
and T.ROWID > T2.ROWID
)
But there is no exposed ROWID in any version of SQL Server
SK
Anirban wrote:

quote:

>Thanks steve,
>I was not sure some people keep telling me this is possible.
>Are you sure this is not possible in sql 2000 also?
>Thanks,
>Paul
>Steve Kass <skass@.drew.edu> wrote in message
>news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
>
>a
>
>deletion.
>
>
>

sql

cannot delete duplicate rows

quote:

> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>

|||Sorry Sir this is not the answer I am expecting. Please follow my query.
Do it in one query.
No temp table please.
You can not have a Primary key on the table.
You need to keep one row of data which was duplicate earlier.
i.e. donot delete all the duplicate recordset.
Paul
Carlos Eduardo Rojas <carloser@.mindspring.com> wrote in message
news:uShY#UB5DHA.1632@.TK2MSFTNGP12.phx.gbl...

quote:

> http://support.microsoft.com/defaul...=kb;en-us;70956
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...
a[QUOTE]
deletion.[QUOTE]
>

Sunday, March 11, 2012

Cannot create compound unique index - server reports duplicate rows but there are none

Hi
I am using SQL Server 2000. I have a simple table (only 6 columns)
which currently contains a few hundred rows. There are two fields:
SupplierGroupID and RateAdjustmentDate, the combination of which
should never be repeated.
I am trying to create an index with these two fields specified and the
'Create Unique' box ticked, however, when I try to do this I get an
error saying that duplicate data was found.
There are no duplicates in the table. I have confirmed this by running
the SQL:
Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
from tblInvoiceRateAdjustment
Group By SupplierGroupID, RateAdjustmentID
All records report a count of 1.
I have tried this on two instances of SQL Server on two different
servers and get the same error with both.
Why is SQL Server reporting that this is duplicate data when there is
none?
TIA
PaulHi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<pd_oflaherty@.hotmail.com> wrote in message
news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
> Hi
> I am using SQL Server 2000. I have a simple table (only 6 columns)
> which currently contains a few hundred rows. There are two fields:
> SupplierGroupID and RateAdjustmentDate, the combination of which
> should never be repeated.
> I am trying to create an index with these two fields specified and the
> 'Create Unique' box ticked, however, when I try to do this I get an
> error saying that duplicate data was found.
> There are no duplicates in the table. I have confirmed this by running
> the SQL:
> Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> from tblInvoiceRateAdjustment
> Group By SupplierGroupID, RateAdjustmentID
> All records report a count of 1.
> I have tried this on two instances of SQL Server on two different
> servers and get the same error with both.
> Why is SQL Server reporting that this is duplicate data when there is
> none?
> TIA
> Paul
>|||Thanks for posting but these links don't help me in this situation -
the issue is that there are NO duplicates of the field combination for
which I want to create a compound unique index.
I am certain there are no duplicates but still SQL will not allow the
index to be created.
On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
> <pd_oflahe...@.hotmail.com> wrote in message
> news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
>
> > Hi
> > I am using SQL Server 2000. I have a simple table (only 6 columns)
> > which currently contains a few hundred rows. There are two fields:
> > SupplierGroupID and RateAdjustmentDate, the combination of which
> > should never be repeated.
> > I am trying to create an index with these two fields specified and the
> > 'Create Unique' box ticked, however, when I try to do this I get an
> > error saying that duplicate data was found.
> > There are no duplicates in the table. I have confirmed this by running
> > the SQL:
> > Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> > from tblInvoiceRateAdjustment
> > Group By SupplierGroupID, RateAdjustmentID
> > All records report a count of 1.
> > I have tried this on two instances of SQL Server on two different
> > servers and get the same error with both.
> > Why is SQL Server reporting that this is duplicate data when there is
> > none?
> > TIA
> > Paul- Hide quoted text -
> - Show quoted text -|||pd_oflaherty@.hotmail.com,
You should try:
Select SupplierGroupID, RateAdjustmentDate, Count(*)
from tblInvoiceRateAdjustment
Group By SupplierGroupID, RateAdjustmentDate
having count(*) > 1
go
AMB
"pd_oflaherty@.hotmail.com" wrote:
> Hi
> I am using SQL Server 2000. I have a simple table (only 6 columns)
> which currently contains a few hundred rows. There are two fields:
> SupplierGroupID and RateAdjustmentDate, the combination of which
> should never be repeated.
> I am trying to create an index with these two fields specified and the
> 'Create Unique' box ticked, however, when I try to do this I get an
> error saying that duplicate data was found.
> There are no duplicates in the table. I have confirmed this by running
> the SQL:
> Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> from tblInvoiceRateAdjustment
> Group By SupplierGroupID, RateAdjustmentID
> All records report a count of 1.
> I have tried this on two instances of SQL Server on two different
> servers and get the same error with both.
> Why is SQL Server reporting that this is duplicate data when there is
> none?
> TIA
> Paul
>|||Forget it - I'm being a numpty - there is a duplicate, just didn't see
it despite flagging with a count and repeated scrolling. I either need
new specs or a bigger screen!
On 1 Mar, 14:55, pd_oflahe...@.hotmail.com wrote:
> Thanks for posting but these links don't help me in this situation -
> the issue is that there are NO duplicates of the field combination for
> which I want to create acompounduniqueindex.
> I am certain there are no duplicates but still SQL will not allow theindexto be created.
> On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
> > Hihttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
> > <pd_oflahe...@.hotmail.com> wrote in message
> >news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
> > > Hi
> > > I am using SQL Server 2000. I have a simple table (only 6 columns)
> > > which currently contains a few hundred rows. There are two fields:
> > > SupplierGroupID and RateAdjustmentDate, the combination of which
> > > should never be repeated.
> > > I am trying to create an index with these two fields specified and the
> > > 'Create Unique' box ticked, however, when I try to do this I get an
> > > error saying that duplicate data was found.
> > > There are no duplicates in the table. I have confirmed this by running
> > > the SQL:
> > > Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> > > from tblInvoiceRateAdjustment
> > > Group By SupplierGroupID, RateAdjustmentID
> > > All records report a count of 1.
> > > I have tried this on two instances of SQL Server on two different
> > > servers and get the same error with both.
> > > Why is SQL Server reporting that this is duplicate data when there is
> > > none?
> > > TIA
> > > Paul- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -

Sunday, March 25, 2012

Cannot DROP PROCEDURE 'duplicate incombolists' because it is being referenced by object

Tuesday, March 20, 2012

cannot delete duplicate rows

cannot delete duplicate rows

cannot delete duplicate rows

cannot delete duplicate rows

Sunday, March 11, 2012

Cannot create compound unique index - server reports duplicate rows but there are none

Cannot create compound unique index - server reports duplicate rows but there are none

Cannot create compound unique index - server reports duplicate rows but there are none

Sql Cannot connect

Blog Archive

About Me