Sql Cannot connect: rows

Showing posts with label rows. Show all posts

Thursday, March 22, 2012

Cannot delete rows that contain same data

Using SQL Sever 2005 Management Studio I am trying to delete rows that are
duplicated in a table. There is no Primary Key. I get an error message to
the effect that the rows cannot be deleted because it would effect other
rows. If I attempt to change a field in the row I get the same error. In
this application there may be many duplicate rows that will need to deleted
at various times.
Thanks,
Bob HillerNo primary key is often an indication of a data model problem. Even if this
is simply a staging table used as part of an ELT process, you can add a
surrogate key to facilitate set-based processing and use GUI tools.
Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
you can specify a TOP clause on a delete statement to delete only a
specified number of rows like the example below. Similarly, you can use SET
ROWCOUNT in earlier version but need to be careful to execute SET ROWCOUNT 0
afterward.
CREATE TABLE Table1 (Col1 int)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
SELECT * FROM Table1
DELETE TOP (2) FROM Table1 WHERE Col1 = 1
SELECT * FROM Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
> Using SQL Sever 2005 Management Studio I am trying to delete rows that are
> duplicated in a table. There is no Primary Key. I get an error message to
> the effect that the rows cannot be deleted because it would effect other
> rows. If I attempt to change a field in the row I get the same error. In
> this application there may be many duplicate rows that will need to
> deleted at various times.
> Thanks,
> Bob Hiller
>|||Dan,
Yes, All of the columns contain the same values. Is there any way to delete
these rows by hitting the delete key or right click/delete?
Thanks,
Bob Hiller
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
> No primary key is often an indication of a data model problem. Even if
> this is simply a staging table used as part of an ELT process, you can add
> a surrogate key to facilitate set-based processing and use GUI tools.
> Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
> you can specify a TOP clause on a delete statement to delete only a
> specified number of rows like the example below. Similarly, you can use
> SET ROWCOUNT in earlier version but need to be careful to execute SET
> ROWCOUNT 0 afterward.
> CREATE TABLE Table1 (Col1 int)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> SELECT * FROM Table1
> DELETE TOP (2) FROM Table1 WHERE Col1 = 1
> SELECT * FROM Table1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
>|||AFAIK, you can't delete rows using the GUI when all columns have the same
value. This is because the tools generate a DELETE statement behind the
scenes and the row(s) you want to delete is ambiguous when all columns have
the same value. If you must use a GUI, you'll need to add a column to
uniquely identify a row:
ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
>|||Bob,
As Dan has already said, you will probably struggle to delete these rows
using a GUI tool (e.g. Enterprise Manager) because there is no key defined
for the table. You should probably first add a key to the table, then do
your DELETE operations, and then, if you really don't want a key any more
you could remove the key definition (and in doing so, re-introduce the
design flaw).
There are probably ways around this, if you're prepared to write your
own SQL DELETEs rather than use a GUI to perform the delete ops. But that
doesn't change the fact that there is a design problem which needs to be
fixed. A correct RDBMS (database) design will always see a field (or set of
fields) defined as a unique key, even on temporary tables. Perhaps the only
exception would be staging tables used to clean data, but those will,
nevertheless, need a key defined at some point during the migration process.
HTH
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
>|||The Table I am referring to is just being used for testing. I was using a
random number generator in my program that I developed to send the data to
the table. The random number generator at some point started duplicating
numbers. I am now going to implement the table in production mode and there
will of course be a key. It is very important because I am recording
automotive serial numbers which must be unique.
Thanks,
Bob Hiller
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
> Bob,
> As Dan has already said, you will probably struggle to delete these
> rows using a GUI tool (e.g. Enterprise Manager) because there is no key
> defined for the table. You should probably first add a key to the table,
> then do your DELETE operations, and then, if you really don't want a key
> any more you could remove the key definition (and in doing so,
> re-introduce the design flaw).
> There are probably ways around this, if you're prepared to write your
> own SQL DELETEs rather than use a GUI to perform the delete ops. But that
> doesn't change the fact that there is a design problem which needs to be
> fixed. A correct RDBMS (database) design will always see a field (or set
> of fields) defined as a unique key, even on temporary tables. Perhaps the
> only exception would be staging tables used to clean data, but those will,
> nevertheless, need a key defined at some point during the migration
> process.
> HTH
> Robert
>
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
>|||Hey Bob,
Since this sounds like it's an issue of data cleansing for development
work, try something like the following:
SELECT DISTINCT *
INTO newTable
FROM oldTable
TRUNCATE TABLE oldTable
INSERT INTO oldTable
SELECT *
FROM newTable
DROP TABLE newTable
HTH,
Stu|||Bob,
Fair enough mate!
Just a small observation, for what it may be worth, (and yes, I'm being
somewhat pedantic here):
If you've got a scenario where you're first building a test system, and
then shifting to production (which is practically always the case with any
form of software development) then you might as well spend the extra time
developing your test DDL as fully as possible, because doing so will aid you
when it comes to evaluating the behaviour and performance of your client
software. The point here is, if a unqiue constraint had been enforced on
your test table, you're client program would never have been able to insert
those "duplicate" rows -- and so, you'd have been a step ahead...
Wishing you well with your project,
Regards,
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uWN7VS5SGHA.736@.TK2MSFTNGP12.phx.gbl...
> The Table I am referring to is just being used for testing. I was using a
> random number generator in my program that I developed to send the data to
> the table. The random number generator at some point started duplicating
> numbers. I am now going to implement the table in production mode and
> there will of course be a key. It is very important because I am recording
> automotive serial numbers which must be unique.
> Thanks,
> Bob Hiller
>
> "Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
> news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
>

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]
>

Cannot Create private Time Dimension

I have a fact table with 32.000.000 rows. In cube editor , I want to create
a
private time dimension based on a datetime type column of my fact table.
after I press next button on "select advanced options" window, several
minutes passes and then a "finish" button appears in the same window
instead of next button. when I pressed finish, I got pop up message "the nam
e
cannot be an empty string" but I have no place to write the name of the
dimension.I typically discourage creating a time dimension like this. I strongly
recommend that you create an independent dimension table which incorporates
"Time". Then use the timestamps from your fact table as FKs to this
dimension. This is normally the best approach because it:
1) allows you to model data which does not exist, e.g. holidays, weekends,
etc, which are not represented in your fact table.
2) dimension processing goes much faster (i.e. you don't have to scan
multiple rows); this is particularly true here because the system will issue
a SELECT DISTINCT to process the private time dimension.
3) private dimensions must always be reprocessed when you process the fact
table -- which can greatly increase your overhead (see #2 above)
4) you will always be limited to a single partition because you can't build
across multiple fact tables (which can limit your scalability, query
response time, rolling "n" months -- all of which use multiple partitions).
5) if you create an independent time dimension table, you can have
interesting extensions to just a timestamp, e.g. model holidays, seasons,
work-days compared to weekends, etc.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_Tr" <Banu_Tr@.discussions.microsoft.com> wrote in message
news:ABBE65C4-C6C8-4DF5-AEB8-2DFBE6489608@.microsoft.com...
> I have a fact table with 32.000.000 rows. In cube editor , I want to
create a
> private time dimension based on a datetime type column of my fact table.
> after I press next button on "select advanced options" window, several
> minutes passes and then a "finish" button appears in the same window
> instead of next button. when I pressed finish, I got pop up message "the
name
> cannot be an empty string" but I have no place to write the name of the
> dimension.

Cannot Create private Time Dimension

I have a fact table with 32.000.000 rows. In cube editor , I want to create a
private time dimension based on a datetime type column of my fact table.
after I press next button on "select advanced options" window, several
minutes passes and then a "finish" button appears in the same window
instead of next button. when I pressed finish, I got pop up message "the name
cannot be an empty string" but I have no place to write the name of the
dimension.
I typically discourage creating a time dimension like this. I strongly
recommend that you create an independent dimension table which incorporates
"Time". Then use the timestamps from your fact table as FKs to this
dimension. This is normally the best approach because it:
1) allows you to model data which does not exist, e.g. holidays, weekends,
etc, which are not represented in your fact table.
2) dimension processing goes much faster (i.e. you don't have to scan
multiple rows); this is particularly true here because the system will issue
a SELECT DISTINCT to process the private time dimension.
3) private dimensions must always be reprocessed when you process the fact
table -- which can greatly increase your overhead (see #2 above)
4) you will always be limited to a single partition because you can't build
across multiple fact tables (which can limit your scalability, query
response time, rolling "n" months -- all of which use multiple partitions).
5) if you create an independent time dimension table, you can have
interesting extensions to just a timestamp, e.g. model holidays, seasons,
work-days compared to weekends, etc.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_Tr" <Banu_Tr@.discussions.microsoft.com> wrote in message
news:ABBE65C4-C6C8-4DF5-AEB8-2DFBE6489608@.microsoft.com...
> I have a fact table with 32.000.000 rows. In cube editor , I want to
create a
> private time dimension based on a datetime type column of my fact table.
> after I press next button on "select advanced options" window, several
> minutes passes and then a "finish" button appears in the same window
> instead of next button. when I pressed finish, I got pop up message "the
name
> cannot be an empty string" but I have no place to write the name of the
> dimension.
sql

Monday, March 19, 2012

Cannot create index, timeout expires (SQL Server Express 2005)

I am having trouble creating a primary key on a large table (6 mil
rows)... on a SQL Server Express 2005 database...
After I make the changes to the table and Save, the operation starts
but times out after about 30 seconds, saying that the timeout has
expired.
I tried looking for a timeout that would govern this but I have not
been able to find one...
My query execution timeout is set to 0 (which is unlimited), though it
should be irrelevant because this is not a query.... (in fact, I have
no problem running quries that take a few mins)...
Any idea where/what I need to change to resolve this'
Thanks in advance.<sdragolov@.gmail.com> wrote in message
news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
>I am having trouble creating a primary key on a large table (6 mil
> rows)... on a SQL Server Express 2005 database...
> After I make the changes to the table and Save, the operation starts
> but times out after about 30 seconds, saying that the timeout has
> expired.
> I tried looking for a timeout that would govern this but I have not
> been able to find one...
> My query execution timeout is set to 0 (which is unlimited), though it
> should be irrelevant because this is not a query.... (in fact, I have
> no problem running quries that take a few mins)...
> Any idea where/what I need to change to resolve this'
>
What tool are you using to run the query? Can you get a script of the
change and run it in a query window in SQL Server Management Studio?
David|||Well, in theory it's not a "query", right' It's a DDL...
I am using Management Studio - I right click on the table,and do
Modify, then I select the two fields and right click and do "Primary
Key"... Then I finally do Save and that's where the error comes after
about 30 seconds...
I guess I could get the DDL text of adding these PKs and then try to
run that in a query window... though why would that be any
different...'
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
> >I am having trouble creating a primary key on a large table (6 mil
> > rows)... on a SQL Server Express 2005 database...
> >
> > After I make the changes to the table and Save, the operation starts
> > but times out after about 30 seconds, saying that the timeout has
> > expired.
> >
> > I tried looking for a timeout that would govern this but I have not
> > been able to find one...
> >
> > My query execution timeout is set to 0 (which is unlimited), though it
> > should be irrelevant because this is not a query.... (in fact, I have
> > no problem running quries that take a few mins)...
> >
> > Any idea where/what I need to change to resolve this'
> >
>
> What tool are you using to run the query? Can you get a script of the
> change and run it in a query window in SQL Server Management Studio?
> David|||<sdragolov@.gmail.com> wrote in message
news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> Well, in theory it's not a "query", right' It's a DDL...
> I am using Management Studio - I right click on the table,and do
> Modify, then I select the two fields and right click and do "Primary
> Key"... Then I finally do Save and that's where the error comes after
> about 30 seconds...
> I guess I could get the DDL text of adding these PKs and then try to
> run that in a query window... though why would that be any
> different...'
Because it's the client tool that generates the timeout, not the server. If
Management Studio will give you the DDL, just run it in a new query window.
David|||David - your suggestion did work... Thanks!
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> > Well, in theory it's not a "query", right' It's a DDL...
> >
> > I am using Management Studio - I right click on the table,and do
> > Modify, then I select the two fields and right click and do "Primary
> > Key"... Then I finally do Save and that's where the error comes after
> > about 30 seconds...
> >
> > I guess I could get the DDL text of adding these PKs and then try to
> > run that in a query window... though why would that be any
> > different...'
> Because it's the client tool that generates the timeout, not the server. If
> Management Studio will give you the DDL, just run it in a new query window.
> David

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 -

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

Wednesday, March 7, 2012

Cannot copy column names view result set

In SQL 2005 - when I display the results of a View and Copy all rows and columns, the resulting Paste in Excel does not include the column names.

How can I set SQL 2005 so the names of the columns will come along with the content of the copy function?

Background:

When I am using a SQL Query (instead of a view) I have the ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc.

My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.

Thank you,

Poppa Mike

The same way. Instead of opening the view into that grid thingy, open a query window and do a select from the view.|||

Yes Michael - I've thought of that but it causes me to take two steps. One to create the view and a second to get the results of the view by running a query against the view. I'd like to avoid the SQL Two-Step and just have the ability to copy the results from the view like I used to be able to do in SQL 2000. I appreciate your thoughts.

Regards, Poppa Mike

|||I was searching for the the same thing ..what i did was instead of selecting results to grid..choose the result as result to text and U can open the result file in excel and sort it out.

Thursday, March 22, 2012

Cannot delete rows that contain same data

Tuesday, March 20, 2012

cannot delete duplicate rows

cannot delete duplicate rows

cannot delete duplicate rows

cannot delete duplicate rows

Cannot Create private Time Dimension

Cannot Create private Time Dimension

Monday, March 19, 2012

Cannot create index, timeout expires (SQL Server Express 2005)

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

Wednesday, March 7, 2012

Cannot copy column names view result set

Sql Cannot connect

Blog Archive

About Me