Problem in DTS

Hi ,
I have a transaction table in my ERP Database.
The table size is 3 GB means (50,000,00)*6 records
Now i am transfering to Access database but it give error
exceeds maximium number of rows.
Can any one tell me how can i tarnsfer the data.I dont want to transfer
data to bcp file.
i have sp3 installed on my machine.
from
KillerWhat is creating the error, Access or the tool you are using for the
transfer? DTS should be able to do this transfer for you without any
problems.
--
--Brian
(Please reply to the newsgroups only.)
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>|||This is not a SQL Server issue but an Access one. According to
http://support.microsoft.com/default.aspx?scid=kb;en-us;302524, Access has a
2GB limitation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>

Problem in Cursur

Hello Sir, I have a problem to create a cursur to showing 10 -10 records showing through Advance java Sql Queary. Please tell me to solve this problem.Hi Amandeep singh and welcome to TSDN MSSQL forum,

I have moved your post from the articles to the forum because this is where this type of question should be asked.

Can you confirm which version of MSSQL you are working on ?

Regards

Purple

Moderatorsql

Problem getting rid of nondistinct records

Hello,

I have a problem with which I need some help. I have a table called "user" that contains the userid, name, and address. However for users with multiple listed addresses, there are duplicate records...example:

userid, name, address:

0001, John Smith, 123 main rd.
0001, John Smith, 456 second rd.
0002, Bob Jones, 435 another rd.

I need to combine all the duplicate records into a single entry by concatenating the adresses. (I.E. "0001, John Smith, 123 main rd/456 second rd")

I can select the duplicate rows by doing:

SELECT * FROM user
WHERE userid in (
SELECT userid FROM user
GROUP BY userid
HAVING COUNT(*) > 1 )
ORDER BY userid ASC

Does anyone know how I could concat all the duplicate rows into one and then delete the duplicates? I dont care which order the addresses are concatinated in. Any ideas?

Thanks in advance,
Andrei GirenkovFor normalization concerns you should AVOID to do what you ask. A better solution, but requires you to modify the architecture of your database, is to make an external address table:

User Table:

0001, John Smith
0002, Bob Jones

Address Table:

0001, 123 main rd.
0001, 456 second rd.
0002, 435 another rd.

To solve your problem, anyway, i think you have no other way to use cursors, since the number of equals row you need to concatenate will vary.|||Thanks for the reply, but I already solved the problem. I ended up doing it with for loops. It took a long time to execute but it's a one time operation.

~Andrei|||Originally posted by Andrei_Girenkov
Thanks for the reply, but I already solved the problem. I ended up doing it with for loops. It took a long time to execute but it's a one time operation.

~Andrei

This could be for you or anyother reader.. its an example I put together that would show you efficient ways to point out duplicate records from within a database table. Also applies to most relational database that support the Structured QUery language (SQL)

create table tmp_awahs_dupes_killer
(
record_id varchar(100),
name varchar(100)
)
go
insert into tmp_awahs_dupes_killer values(100, 'In')
insert into tmp_awahs_dupes_killer values(200, 'These')
insert into tmp_awahs_dupes_killer values(300, 'Rows')
insert into tmp_awahs_dupes_killer values(400, ',')
insert into tmp_awahs_dupes_killer values(500, 'These')
insert into tmp_awahs_dupes_killer values(500, 'Are')
insert into tmp_awahs_dupes_killer values(500, 'The')
insert into tmp_awahs_dupes_killer values(500, 'Dupes')
go

select * from tmp_awahs_dupes_killer t1 where (select count(t2.record_id) from tmp_awahs_dupes_killer t2 where t2.record_id= t1.record_id)> 1

drop table tmp_awahs_dupes_killer
go|||Manowar is right. Your database is already denormalized enough. I predict a post six months from now requesting help parsing all those concatenated addresses into separate elements again.

awahteh: I suspect your query would be more efficient using Andrei_Girenkov's linked subquery method, though the optimizer might convert your syntax to this plan prior to execution anyway. If it doesn't, then you will end up executing your subquery once for every record in your main table, while Andrei_Girenkov's method only runs the aggregate once.

blindman

Problem Getting Distinct Records

I have 2 tables that I'm trying to join. The first table is called DeviationMaster and the second is called DeviationDist
I have no problem returning all results for deviationMaster. My problem is, when I try and join DeviationDist to DeviationMaster I get alot of duplicate records. DeviationDist contains a column named DEVDN which is the Deviation Number column. It also contains a column DEVDD which is the Distributor name. I join them by saying where DNumber (which is the Deviation column in DeviationMaster) is equal to DEVDN in DeviationDist. This works fine when I only select the DEVDN column, but I really need the DEVDD column as well. When I grab the DEVDD column, it then shows duplicate records since there can be multiple DEVDD for each DEVDN in DeviationDist.
I guess I need to know how I can only grab 1 occurance for each DEVDN in DeviationDist and relate that to the DeviationMaster table.
This statement works but does not get DEVDD which is the dist number column that I need to get. This of course doesnt duplicate.
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC
This statement gets the DEVDD column but now I may have multiple Deviations present depending on how many times DEVDD repeats for each DEVDN. I need to grab only 1 of each DEVDD and DEVDN.
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDD, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC

I'm stuck, any help is appreciated.

Hello!
Is guess this is a small controversy within SQL - but you should review the DISTINCT versus the GROUP BY - there is a lot of different opinoins there but from my humble position within this coding world
I always use the Group By and I always get what I want....
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
GROUP BY DD.DEVDN
ORDER BY dm.DNumber DESC|||Sorry --
Please remove Select Distinct and Replace with just a Select
Best Regards,
Joe|||

This could be a solution, i think:
SELECT max(dm.DNumber), max(dm.TNumber), max(dm.Status), max(dm.DType), dd.DEVDD, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC
GROUP by dd.DEVDD, dd.DEVDN
but this would make DeviationDist the Mastertable and DeviationMaster the DetailsTable since it would only by used for aggregates.
This way you would get a summary of DEVDD's ans DEVDN's with (in this case) the maxima of DNumber, TNumber, ...
Hope this helps

|||

I tried

SELECT dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
GROUP BY DD.DEVDN
ORDER BY dm.DNumber DESC
I get an error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.DNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.TNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.DType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||This still produced multiple records.|||You could do something like this :
SELECT dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
AND dd.DEVDN IN ( SELECT dd.DEVDN FROM DeviationMaster dm INNER JOIN DeviationDist dd ON dm.DNumber = dd.DEVDN GROUP BY DD.DEVDN)
ORDER BY dm.DNumber DESC
|||

I still need to grab column dd.DEVDD...

Also, these are still returning 35xxxx records and the DeviationMaster only has 8xxx records. I didnt know it wa sso complicated to only grab the first dist is sees for each DEVDN and DNumber...

|||

Lets say for DeviationDist there is DEVDD and DEVDN

If I say select distinct DEVDN, it returns 8xxx records which is good. I need it to do the same when DEVDD is added to the mix but it goes right to 35xxx records if I do distinct devdd, devdn...
Isnt there a way to say for each devdn, get the devdd but ignore the rest?

|||can you provide some sample data and the kind of resultset that you are expecting back. Also is DevDN the primarykey in the DevDD table ?|||

DeviationDist table has a DEVID Column which is just numeric identity, pk... DEVDN which is Deviation Number and DEVDD which is distributor.

I need only one occurance of the DEVDD per each DEVDN and then I need to join that to the DeviationMaster table on column DNumber which is the deviation number.

Lets say this is the Table:

DEVID DEVDN DEVDD
1 100000 0920292
2 100000 2920292
3 100000 2928373
4 100001 9383939
5 100001 4484949
6 100002 5959595
7 100003 3939393

When I run the query, I only want to see records 1, 4, 6, 7. Since 2, 3 are just 2 more distributors for the same deviation (DEVDN) and same as 5. That way, when I link to the DeviationMaster, I will get a one to one result. When I do Distinct like I said above, it still returns all these but I only want 1 record for each DEVDN.

|||

check this :
SELECT
dd.devdn, min(dd.devdd)
,dm.dnumber, dm.tnumber, dm.status, dm.dtype
from
Devdn DD
INNER JOIN DeviationMaster DM ON dm.dnumber = dd.DevId
GROUP BY
dd.devdn,
dm.dnumber, dm.tnumber, dm.status, dm.dtype

|||

That returned no results:

SELECT dd.devdn, min(dd.devdd),dm.dnumber, dm.tnumber, dm.status, dm.dtype
FROM DeviationDist DD
INNER JOIN DeviationMaster DM ON dm.dnumber = dd.DevId
GROUP BY dd.devdn, dm.dnumber, dm.tnumber, dm.status, dm.dtype

|||

here's what I tried :

declare @.dm table ( dnumber int, tnumber int, status varchar(10), dtype varchar(10) )
declare @.dd table (pk int, devdn int, devdd varchar(10) )

insert into @.dm values (1,133,'a', 'a')
insert into @.dm values (2,243,'a', 'a')
insert into @.dm values (3,353,'a', 'a')
insert into @.dm values (4,483,'a', 'a')
insert into @.dm values (5,563,'a', 'a')
insert into @.dm values (6,663,'a', 'a')
insert into @.dm values (7,763,'a', 'a')

insert into @.dd values ( 1 , 100000,1920292)
insert into @.dd values ( 2,100000,2920292)
insert into @.dd values ( 3,100000, 2928373)
insert into @.dd values ( 4,100001, 9383939)
insert into @.dd values ( 5, 100001, 4484949)
insert into @.dd values ( 6,100002, 5959595)
insert into @.dd values ( 7, 100003, 3939393)

Select min(ddd.pk),devdn from @.dd ddd group by ddd.devdn

--the above will give you distinct combination of devdn and devdd
--use the above to join to deviation master
select
min(dd.devdn), min(dd.devdd)
,dm.dnumber, dm.tnumber, dm.status, dm.dtype
from
@.dd dd
inner join @.dm dm on dm.dnumber = dd.pk
and
dd.pk in ( Select min(ddd.pk) from @.dd ddd group by ddd.devdn)
group by dd.devdn,
dm.dnumber, dm.tnumber, dm.status, dm.dtype

Here's the resultset:
100000 1920292 1 133 a a
100001 9383939 4 483 a a
100002 5959595 6 663 a a
100003 3939393 7 763 a a

|||I'm not sure why yours works and mine did nothing. Does it matter that DeviationMaster has no PK and that DEVDN is varchar, not integer?
DNumber should be numeric, TNumber is varchar...
I need exactly the result you got, ahh!

problem deleting large no. of records

Hi all,
I have a table with 6 million rows which takes up about 2GB of memory on
hard disk. So we have decided to clean this table up. We have decided to
delete all records that have syncstamp and logstamp field values less than
the value correspoing '20040131'. This will probably delete 5.5 million rows
out of total 6 million.
When I try to delete records using following script, it is very slow. The
script did not finish executing in three hours. So we had to cancel the
execution of the script. Also the users were not able to use conttlog table
when this query was executing although I am using ROWLOCK table hint.
Is there any other way to fix the speed and concurrency issues with this
script? I know I can't use a loop to delete 5.5 million rows because it will
probably take days to execute it.
Thanks in advance.
-- ****************************************
*******
-- Variable declaration
-- ****************************************
*******
DECLARE @.Date datetime,
@.syncstamp varchar(7)
-- ****************************************
*******
-- Assign variable values
-- ****************************************
*******
SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
-- ****************************************
*******
-- Delete conttlog records
-- ****************************************
*******
SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
DELETE
FROM conttlog with(rowlock)
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstampsql
Do you have Primary key on the table?
I'd try to divide the 'big' transaction/deletion into small ones
See this example
SET ROWCOUNT 1000 --Set the value
WHILE 1 = 1
BEGIN
UPDATE MyTable WHERE col <= datetimecolumn
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"sql" <donotspam@.nospaml.com> wrote in message
news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory
on
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million
rows
> out of total 6 million.
> When I try to delete records using following script, it is very slow.
The
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog
table
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with
this
> script? I know I can't use a loop to delete 5.5 million rows because it
will
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>|||I would recommend doing this in smaller batches -- maybe 10,000 rows at
once:
-- ****************************************
*******
-- Variable declaration
-- ****************************************
*******
DECLARE @.Date datetime,
@.syncstamp varchar(7)
-- ****************************************
*******
-- Assign variable values
-- ****************************************
*******
SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
-- ****************************************
*******
-- Delete conttlog records
-- ****************************************
*******
SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
SET ROWCOUNT 10000
DELETE
FROM conttlog
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstamp
WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE
FROM conttlog
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstamp
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <donotspam@.nospaml.com> wrote in message
news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory
on
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million
rows
> out of total 6 million.
> When I try to delete records using following script, it is very slow.
The
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog
table
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with
this
> script? I know I can't use a loop to delete 5.5 million rows because it
will
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>|||Hi
Do it in smaller batches. This will help with performance.
SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
SET ROWCOUNT 10000
DELETE
FROM conttlog with(rowlock)
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstamp
Regards
Mike
"sql" wrote:

> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory o
n
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million ro
ws
> out of total 6 million.
> When I try to delete records using following script, it is very slow. Th
e
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog tabl
e
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with th
is
> script? I know I can't use a loop to delete 5.5 million rows because it wi
ll
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>
>|||Uri,
One comment on this; I would personally be wary of doing that many
checkpoints during the process, as I believe that it would bring overall
system performance down quite a bit due to the constant disk activity. Is
there a reason you'd recommend doing a checkpoint on every iteration?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OvmficbGFHA.3608@.TK2MSFTNGP14.phx.gbl...
> sql
> Do you have Primary key on the table?
> I'd try to divide the 'big' transaction/deletion into small ones
> See this example
> SET ROWCOUNT 1000 --Set the value
> WHILE 1 = 1
> BEGIN
> UPDATE MyTable WHERE col <= datetimecolumn
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
> END
> SET ROWCOUNT 0
>|||Thank you all for your answers. I will set the ROWCOUNT at the beginning of
the script to either 1000 or 10000. Do you think it is worth creating a
clusterd index on SYNCSTAMP and LOGSTAMP to improve the performance of this
script and then drop it. If so how long do you think it will take to create
such an index. both fields are varchar(7).
Thanks.
"sql" <donotspam@.nospaml.com> wrote in message
news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory on
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million
> rows out of total 6 million.
> When I try to delete records using following script, it is very slow. The
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog
> table when this query was executing although I am using ROWLOCK table
> hint.
> Is there any other way to fix the speed and concurrency issues with
> this script? I know I can't use a loop to delete 5.5 million rows because
> it will probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>|||Hi
Creating a clustetred index will cause the whole table to be re-written.
This will take longer to do than running the delete.
Regards
Mike
"sql" wrote:

> Thank you all for your answers. I will set the ROWCOUNT at the beginning o
f
> the script to either 1000 or 10000. Do you think it is worth creating a
> clusterd index on SYNCSTAMP and LOGSTAMP to improve the performance of thi
s
> script and then drop it. If so how long do you think it will take to creat
e
> such an index. both fields are varchar(7).
> Thanks.
> "sql" <donotspam@.nospaml.com> wrote in message
> news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi
My Sfr 0.02
It is in an implicit transaction, so until the whole batch completes,
nothing is committed.
Regards
Mike
"Adam Machanic" wrote:

> Uri,
> One comment on this; I would personally be wary of doing that many
> checkpoints during the process, as I believe that it would bring overall
> system performance down quite a bit due to the constant disk activity. Is
> there a reason you'd recommend doing a checkpoint on every iteration?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OvmficbGFHA.3608@.TK2MSFTNGP14.phx.gbl...
>
>|||sql wrote:
> Thank you all for your answers. I will set the ROWCOUNT at the
> beginning of the script to either 1000 or 10000. Do you think it is
> worth creating a clusterd index on SYNCSTAMP and LOGSTAMP to improve
> the performance of this script and then drop it. If so how long do
> you think it will take to create such an index. both fields are
> varchar(7).
If you do this, understand that SQL Server will have to rebuild all
non-clustered indexes. So do it off-hours, if at all. I would recommend
you not mess with the indexes since you are working on live data and
use the small batch size.
Is there any way you can do all this testing on a dev/test server?
David Gugick
Imceda Software
www.imceda.com|||What I've done in the past is stuff the records I wish to retain into a new
table and do a rename. The table will need to be offline during this
operation, simply run a test to see how long it takes to populate the new
table with the half million rows. The rename takes less than a second. If yo
u
try this technique I'd recommend doing a create table instead of a select
into so you can double check the referential integrity, default values,
index(s) and all else.
Dan
"sql" wrote:

> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory o
n
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million ro
ws
> out of total 6 million.
> When I try to delete records using following script, it is very slow. Th
e
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog tabl
e
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with th
is
> script? I know I can't use a loop to delete 5.5 million rows because it wi
ll
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>
>

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.
This is similar to the problem in KB Article 816780
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780
|||Ok, then explain why when the index is removed the problem goes away?
|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David
|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas

"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT
'.
This is similar to the problem in KB Article 816780The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780|||Ok, then explain why when the index is removed the problem goes away?|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.
This is similar to the problem in KB Article 816780The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780|||Ok, then explain why when the index is removed the problem goes away?|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem counting records

Hi,

I am struggling with a simple query, but I just don't see it.
I have the following example table.

Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0

The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.

What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.

ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0

Any assistance would be greatly appreciated.Did you think of this:

select t1.ID, t1.Subject, count(1) as Num_replies_to
from tbl t1
left join tbl t2
on t2.Reply_to=t1.ID
where t1.Reply_to=0
group by t1.ID, t1.Subject

Bye, Manfred|||What I would like to have returned is this: a list of messages where

Quote:

Originally Posted by

Reply_to = 0 and the number of replies to this message.

A subquery like the example below is one method.

SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sir Hystrix" <SirHystrix@.netscape.netwrote in message
news:474fe374$0$22307$ba620e4c@.news.skynet.be...

Quote:

Originally Posted by

Hi,
>
I am struggling with a simple query, but I just don't see it.
I have the following example table.
>
Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0
>
The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.
>
What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.
>
ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0
>
Any assistance would be greatly appreciated.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What I would like to have returned is this: a list of messages where
>Reply_to = 0 and the number of replies to this message.

>
A subquery like the example below is one method.
>
SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0
>

I knew it was simple. It had to be simple. I just didn't see it.
Many thanks to both Dan and Manfred.

Cheers.