Problem with SQL transacts

I have an issue with duplicates in a table but the whole rows are not
duplicated just certain values. Could anyone point me in the right
direction for creating a script to fix the problem as described
below. I have a basic understanding of tsql and have used the count
function but this is a bit beyond me at the moment:
Table1
col1 col2 col3 col4 col5
101 abc xyz A other
101 abc uvw A other
101 def xyz A other
102 def uvw A other
103 abc xyz A other
104 def uvw A other
col 2 and col 3 should only ever have the same values in a single row
but as you can see rows 5 and 6 contain duplicates I'd like to be able
to runa script that would find these duplicates and then check col1
and update col 4 from A to D thats should rectify my issues.
Any help much appreciated
RobFirst, once you have corrected the current data problem add a UNIQUE
constraint . The basis of database integrity is to prevent problems
from happening in the first place.
The basic query to find duplicates:
SELECT col2, col3, count(*) as DupCount
FROM Sometable
GROUP BY col2, col3
HAVING count(*) > 1
I think this does what you want:
UPDATE Whatever
SET col4 = 'D'
WHERE EXISTS
(SELECT * FROM Whatever as B
WHERE Whatever.col2 = D.col2
AND Whatever.col3 = D.col3
AND Whatever.col1 < D.col1)
AND col4 <> 'D'
Roy Harvey
Beacon Falls, CT
On Sat, 12 Jan 2008 11:00:34 -0800 (PST), rob.stevenson@.isoftplc.com
wrote:
>I have an issue with duplicates in a table but the whole rows are not
>duplicated just certain values. Could anyone point me in the right
>direction for creating a script to fix the problem as described
>below. I have a basic understanding of tsql and have used the count
>function but this is a bit beyond me at the moment:
>Table1
>col1 col2 col3 col4 col5
>101 abc xyz A other
>101 abc uvw A other
>101 def xyz A other
>102 def uvw A other
>103 abc xyz A other
>104 def uvw A other
>col 2 and col 3 should only ever have the same values in a single row
>but as you can see rows 5 and 6 contain duplicates I'd like to be able
>to runa script that would find these duplicates and then check col1
>and update col 4 from A to D thats should rectify my issues.
>Any help much appreciated
>Rob|||Hi Rob
I don't understand your description of how you want to 'fix' the problem.
Can you give more details, including what this data should like after your
fix?
This query will find your duplicates:
SELECT col2, col3
FROM Table1
GROUP BY col2, col3
HAVING count(*) > 1
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
<rob.stevenson@.isoftplc.com> wrote in message
news:364750da-441c-41f0-82f9-a7285fc23ba3@.l32g2000hse.googlegroups.com...
>I have an issue with duplicates in a table but the whole rows are not
> duplicated just certain values. Could anyone point me in the right
> direction for creating a script to fix the problem as described
> below. I have a basic understanding of tsql and have used the count
> function but this is a bit beyond me at the moment:
> Table1
> col1 col2 col3 col4 col5
> 101 abc xyz A other
> 101 abc uvw A other
> 101 def xyz A other
> 102 def uvw A other
> 103 abc xyz A other
> 104 def uvw A other
> col 2 and col 3 should only ever have the same values in a single row
> but as you can see rows 5 and 6 contain duplicates I'd like to be able
> to runa script that would find these duplicates and then check col1
> and update col 4 from A to D thats should rectify my issues.
> Any help much appreciated
> Rob|||On 12 Jan, 19:18, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> First, once you have corrected the current data problem add a UNIQUE
> constraint . =A0The basis of database integrity is to prevent problems
> from happening in the first place.
> The basic query to find duplicates:
> SELECT col2, col3, count(*) as DupCount
> =A0 FROM Sometable
> =A0GROUP BY col2, col3
> HAVING count(*) > 1
> I think this does what you want:
> UPDATE Whatever
> =A0 =A0SET col4 =3D 'D'
> =A0WHERE EXISTS
> =A0 =A0 =A0 =A0(SELECT * FROM Whatever as B
> =A0 =A0 =A0 =A0 =A0WHERE Whatever.col2 =3D D.col2
> =A0 =A0 =A0 =A0 =A0 =A0AND Whatever.col3 =3D D.col3
> =A0 =A0 =A0 =A0 =A0 =A0AND Whatever.col1 < D.col1)
> =A0 =A0AND col4 <> 'D'
> Roy Harvey
> Beacon Falls, CT
> On Sat, 12 Jan 2008 11:00:34 -0800 (PST), rob.steven...@.isoftplc.com
> wrote:
>
> >I have an issue with duplicates in a table but the whole rows are not
> >duplicated just certain values. =A0Could anyone point me in the right
> >direction for creating a script to fix the problem as described
> >below. =A0I have a basic understanding of tsql and have used the count
> >function but this is a bit beyond me at the moment:
> >Table1
> >col1 col2 col3 col4 col5
> >101 =A0abc =A0xyz =A0A =A0other
> >101 =A0abc =A0uvw =A0A =A0other
> >101 =A0def =A0 xyz =A0A =A0other
> >102 =A0def =A0 uvw =A0A =A0other
> >103 =A0abc =A0xyz =A0A =A0other
> >104 =A0def =A0 uvw =A0A =A0other
> >col 2 and col 3 should only ever have the same values in a single row
> >but as you can see rows 5 and 6 contain duplicates I'd like to be able
> >to runa script that would find these duplicates and then check col1
> >and update col 4 from A to D thats should rectify my issues.
> >Any help much appreciated
> >Rob- Hide quoted text -
> - Show quoted text -
Hi Roy,
Thanks for that the second script was spot on with some minor tweaking
very much appreciated.
Thanks, Robsql

Problem with slowly delete many filtered rows in table.

I use cursor for delete 20 milions rows from one table, but it's very slowly. I want use delete rows in a non-resource consuming manner. I don't need from transaction log, but don't know how turn off it.

Please help me!

10x

Why are you using a cursor for this? Can't you form the DELETE statement into a SET-oriented operation? Cursors are going to be much less efficient than a straight-DELETE DML operation. (FYI, TRUNCATE is the only logging-optimized DELETE operation available in the server.)

Thanks,
--R

|||

The basic process that most people do is something like the following:

declare @.row int

set @.row = 1

while @.row < somevalue

begin

delete from table where primary key between @.row and @.row + 1000

set @.row = @.row + 1000

end

This basically walks the table and allows you to delete rows in chunks which minimizes the impact of the delete operation. If this is SQL Server 2005, I would use partitioning to switch the rows you want to delete out of the table and then truncate the staging table you moved the data to.

|||

I use the cursore because i want remove filtered data from this table. I have relation with another table and result to deleted rows is round 21 milions records from 41 milions. The big table have 9 indexes. Is it a problem to slowly execute? I probe to not use the cursor. Write where clause with operator IN, but this not solve the problem. Now delete indexes, but problem stay. For cursor i use option FORWARD_ONLY, and delete records with

DECLARE abc CURSOR FORWARD_ONLY FOR
SELECT * FROM MyTable INNER JOIN

OtherTable ON(OtherTable .MyTable_ID=MyTable .ID)

OPEN abc

FETCH NEXT FROM abc

DELETE MyTable WHERE CURRENT OF abc

CLOSE abc

DEALLOCATE abc

Can you offer some idea?

|||

Hi, maybe I am wrong but do you this this could work ?

Delete MyTable where exists (select * from OtherTable where OtherTable.MyTable_ID = MyTable.ID)

|||

Thanks knuti,

I solve this problem with part delete of N records with ROWCOUNT and WAITFOR. This is slowly but tables stay unlocked.

Problem with slowly delete many filtered rows in table.

I use cursor for delete 20 milions rows from one table, but it's very slowly. I want use delete rows in a non-resource consuming manner. I don't need from transaction log, but don't know how turn off it.

Please help me!

10x

Why are you using a cursor for this? Can't you form the DELETE statement into a SET-oriented operation? Cursors are going to be much less efficient than a straight-DELETE DML operation. (FYI, TRUNCATE is the only logging-optimized DELETE operation available in the server.)

Thanks,
--R

|||

The basic process that most people do is something like the following:

declare @.row int

set @.row = 1

while @.row < somevalue

begin

delete from table where primary key between @.row and @.row + 1000

set @.row = @.row + 1000

end

This basically walks the table and allows you to delete rows in chunks which minimizes the impact of the delete operation. If this is SQL Server 2005, I would use partitioning to switch the rows you want to delete out of the table and then truncate the staging table you moved the data to.

|||

I use the cursore because i want remove filtered data from this table. I have relation with another table and result to deleted rows is round 21 milions records from 41 milions. The big table have 9 indexes. Is it a problem to slowly execute? I probe to not use the cursor. Write where clause with operator IN, but this not solve the problem. Now delete indexes, but problem stay. For cursor i use option FORWARD_ONLY, and delete records with

DECLARE abc CURSOR FORWARD_ONLY FOR
SELECT * FROM MyTable INNER JOIN

OtherTable ON(OtherTable .MyTable_ID=MyTable .ID)

OPEN abc

FETCH NEXT FROM abc

DELETE MyTable WHERE CURRENT OF abc

CLOSE abc

DEALLOCATE abc

Can you offer some idea?

|||

Hi, maybe I am wrong but do you this this could work ?

Delete MyTable where exists (select * from OtherTable where OtherTable.MyTable_ID = MyTable.ID)

|||

Thanks knuti,

I solve this problem with part delete of N records with ROWCOUNT and WAITFOR. This is slowly but tables stay unlocked.

Problem with single quotes

Hi,
I've a column (type: varchar) in a table. I need to add single quotes in the
value while inserting the rows. But I get an error. Please see below:
create table #t1 (address varchar(20))
go
insert into #t1 values ('St John's street')
Error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ')
Since the database is on hosting providers server, I cannot change the
settings of the server. Please help me by providing any suitable solution.
Also, note that I've an ASP script which insert rows into the table.
Thanks in advance.
-VenkatHi Venkat,
To solve the problem you need to generate insert statement like following in
your ASP script:
insert into #t1 values ('St John''s street')
i.e. replace single quote ( ' ) by two single quotes ( '' )
Krish
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq#aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
the
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||Hi
A single quote within a string can be escapped with a second quote.
insert into #t1 (address) values ('St John''s street')
John
"G.V.Reddy" wrote:

> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in t
he
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>
>|||Thank you very much Krish and John.
Since the address is entered by the visitors on the web site, do we need to
check each and every value entered/inserted into the varchar field for the
single quotes? In case the answer is Yes, I think we can do it by writing a
function which replaces a single quote with adding another quote. Is there
any other simple method to integrate this functionality (escaping with
another single quote) into the ASP code?
Thanks in advance.
-Venkat
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
> the value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||G.V.Reddy wrote:
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we
> need to check each and every value entered/inserted into the varchar
> field for the single quotes? In case the answer is Yes, I think we
> can do it by writing a function which replaces a single quote with
> adding another quote. Is there any other simple method to integrate
> this functionality (escaping with another single quote) into the ASP
> code?
> Thanks in advance.
> -Venkat
>
Look into the use of "parameters". I don't know the exact details for *asp*,
but they are something like:
* provide placeholders in you sql string :
insert into #t1 values (?)
* create a parameter, fill it with the "plain" value ("St John's street"), n
o need
to escape quotes, then add that parameter to the command object
* execute the query
Hans Kesting|||Hi
For ASP/ADO check out the SQL Server samples
http://msdn.microsoft.com/library/d...
5ym.asp
John|||replace(strParam, "'","''")|||Yes, a function to "double up" the single quote items will help. It would be
even better to use parameterized command objects instead of concatenated SQL
strings for communicating with the database (I am guessing you are using ADO
in your ASP application). Building concatenated SQL strings leaves you
application open to SQL Injection attacks, which is a severe security issue.
For information on ADO Command objects and parameters, see:
http://msdn.microsoft.com/library/d...rsreference.asp
For information about SQL injection attacks, see:
http://search.microsoft.com/search/.../>
0&s=1&swc=0
http://www.google.com/search?hl=en&q=sql+injection
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:OxZF4q$aFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we need
to
> check each and every value entered/inserted into the varchar field for the
> single quotes? In case the answer is Yes, I think we can do it by writing
a
> function which replaces a single quote with adding another quote. Is there
> any other simple method to integrate this functionality (escaping with
> another single quote) into the ASP code?
> Thanks in advance.
> -Venkat
>
> "G.V.Reddy" <vreddyg@.go.com> wrote in message
> news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
below:
solution.
>
>sql

Problem with ROLAP dimension updates

Simple scenario:
Cube based on a single table. (FactContacts)
One HOLAP count of rows measure.
One ROLAP fact dimension with an attribute based on a nullable string column. (Status)

Everything works fine as long as I process the cube manually through VS. However, I often get the following error if I rely on the cube to update automatically as my source table changes. (Interestingly the error doesn't always occur.)

The query could not be processed:
o Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_FactContacts, Column: Status, Value:aaaa.
o Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

My first guess was that things are getting updated in the wrong sequence (measure before dimension), so I've tried changing the ProcessingPriority properties on the dimension and on the measure group but that didn't have any effect.

Any ideas?
I should probably also clarify I have proactive caching enabled, both on the dimension and on the measure group. My notification method is SQL Server.

I'd really like to know if this could possibly be a bug of SSAS, or if I'm just missing something. This problem is pretty much a showstopper for my project, as real-time data is a requirement.

problem with reordering of rows

I am using MSSQL 2000, I need to display a cv page in which a person projects are displayed the way he enters them into the database and he should be able to reorder the projects according to the proirity. I have no clue how to do this ? I am a newbie in asp.net,C# field.

I searched info on google and forums but didn't find anything related to my understanding. I'am displaying all his projects using a Projects table in which i have four columns ProjectID, ProjTitle, ProjDirector, ProjDescription. i want the user to be able to reorder the
projects row by moving a task up or down based on priority.Any help will be greatly appreciated .. I am really stuck and frustated with this task ..please help me friends..

Thank you very much

can you please help me friends in the above problem...

Thank you

|||

Can two projects have the same Title? I would immagine that would be confusing -- if Title is supposed to be unique, then make it the PK. Also, your column names contain the prefix "Proj" -- unecessary.

To answer your question, though, simply add a column called Display_Sequence. Then your insert statement would be

INSERT INTO Projects
(Title, Director, Description, Display_Seq)
SELECT 'My Project', 'Jim Jones', 'This is my project', MAX(Display_Seq)
FROM Projects