How to consolidate duplicate records

Hello,
I'm assigned a task to clean up a table which has the data like below:
col1--col2--col3--col4
103 20 606 $50
103 20 606 $60
I was told to consolidate the sales$ and only keep one record, the primary
key is on col1,col2 and col3.
How can I do this?
Thanks,
SarahWhat about
SELECT col1,col2,col3,sum(col4) as sales
From YourTable
Group by col1,col2,col3
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"SG" <sguo@.coopervision.ca> schrieb im Newsbeitrag
news:%23DMa7GoRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm assigned a task to clean up a table which has the data like below:
> col1--col2--col3--col4
> 103 20 606 $50
> 103 20 606 $60
> I was told to consolidate the sales$ and only keep one record, the primary
> key is on col1,col2 and col3.
> How can I do this?
> Thanks,
> Sarah
>|||You can use GROUP BY like:
SELECT col1, col2, col3, SUM( col4 ) AS "col4"
FROM tbl
GROUP BY col1, col2, col3 ;
Anith|||Thanks Anith and Jens for your quick response. How could I forget this? I
made a mistake to group by sales column which I should use sum()so it gave
me the wrong result.
Thanks again,
Sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eChkdLoRFHA.2252@.TK2MSFTNGP15.phx.gbl...
> You can use GROUP BY like:
> SELECT col1, col2, col3, SUM( col4 ) AS "col4"
> FROM tbl
> GROUP BY col1, col2, col3 ;
> --
> Anith
>

How to clean up transaction logs

Hello,
Now, I use SQL Server 2000 and Analysis Manager. I have
some problem about transaction logs that have been
expanded day by day for the database of SQL and Ananlysis
Manager. Currently, size of transaction log files are
bigger than data files.
Please advise me how to clear or clean up transaction
logs.
Thank you for your help in advance.
KitRefer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||In my opinion:
BACKUP LOG database_name [WITH TRUNCATE_ONLY]
DBCC SHRINKDATABASE database_name
Hope it helps
Zbig
Uzytkownik "Kit" <anonymous@.discussions.microsoft.com> napisal w wiadomosci
news:2ff501c4bfee$5f5cdea0$a301280a@.phx.gbl...
> Hello,
> Now, I use SQL Server 2000 and Analysis Manager. I have
> some problem about transaction logs that have been
> expanded day by day for the database of SQL and Ananlysis
> Manager. Currently, size of transaction log files are
> bigger than data files.
> Please advise me how to clear or clean up transaction
> logs.
> Thank you for your help in advance.
> Kit
>

How to clean up transaction logs

Hello,
Now, I use SQL Server 2000 and Analysis Manager. I have
some problem about transaction logs that have been
expanded day by day for the database of SQL and Ananlysis
Manager. Currently, size of transaction log files are
bigger than data files.
Please advise me how to clear or clean up transaction
logs.
Thank you for your help in advance.
Kit
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||In my opinion:
BACKUP LOG database_name [WITH TRUNCATE_ONLY]
DBCC SHRINKDATABASE database_name
Hope it helps
Zbig
Uzytkownik "Kit" <anonymous@.discussions.microsoft.com> napisal w wiadomosci
news:2ff501c4bfee$5f5cdea0$a301280a@.phx.gbl...
> Hello,
> Now, I use SQL Server 2000 and Analysis Manager. I have
> some problem about transaction logs that have been
> expanded day by day for the database of SQL and Ananlysis
> Manager. Currently, size of transaction log files are
> bigger than data files.
> Please advise me how to clear or clean up transaction
> logs.
> Thank you for your help in advance.
> Kit
>

How to clean out Replication on DBRepaired DB

Hi all,

I had a suspect database that I had to kill.. it was non-recoverable.
The DB was a publisher and distributor for replication.
The Publications are still listed Enterprise Manager, the distribution database is still poopulated with publication info for this database.

Anyone know an easy way to clean out the replication info for the Dropped DB?

Thanks,
RyanHave you tried setting status with SP_RESETSTATUS and may use sp_helpreplicationdboption to get info. on replication.|||Yes, Thank you for replying.

I used SP_RESETSTATUS to set the status on the DB so it is not suspect.
I tried to kill replication at this point but it could not access the DB becasue the MDF file was corrupted. Nothing I could do would get the database recognizable...the recover function on start-up would consistantly fail because of a bad FCB on the file.

So I used the DBCC DBREPAIR command to kill the database...but in the replication monitor in Enterprise manager, the publications still exist for the non-existant DB. The Distribution database still contains info about those publications. Is there a Stored Procedure that anyone knows of to cleanup the distribution database?

I am also planning on applying SP4 to this SQL7 server..but have been waiting to clean up the databases first. Any thoughts?

Thank you so much to the DBFORUM community!

Ryan Jones

Originally posted by Satya
Have you tried setting status with SP_RESETSTATUS and may use sp_helpreplicationdboption to get info. on replication.|||Use SP_REMOVEDBREPLICATION and then drop the database using DROP DATABASE statement. Then go for SP4.

How to clean machine for SQL2005 Reporting Services RTM

hi all,

I am trying to get Reporting Services to install on a machine that previously had the September CTP install on it.

The SQL Server part of the install has been fine and all seems to be operational as expected but the Reporting Services fails to install with the following errors:

"An instance of the same name is already installed on this computer..."

This is thrown out of the ValidateInstanceName func in the MSI. I am telling it to use the Default Instance as there is only one SQL instance running (to my knowledge).

How do I clean up the machine so that it does not think another instance is present? I have tried MSIZAP and that has not helped. I have removed references from IIS, COM+, file system and registry.. but there must be some I am missing. Does anyone know what ValidateInstanceName is actually looking at/for?

Thanks
PaulIf in doubt..

Uninstall SQL Server 2005 and remove every reference to SQL (as far as possible) from the registry..

Fixed the issue.

how to clean all messages of a queue ?

How to clean all messages of a queue ?

Online books says it can be done as below:
RECEIVE * FROM ExpenseQueue

But it only delete a row every time,and I found all the three statement only delete a row:

RECEIVE * FROM ExpenseQueue
RECEIVE TOP(1) * FROM ExpenseQueue
RECEIVE TOP(n) * FROM ExpenseQueue

The RECEIVE statement (as it exists today) only fetches messages belonging to a single conversation group at a time. So the only way to get rid of all messages is to call RECEIVE in a loop:

WHILE (1)
BEGIN
RECEIVE * FROM ExpenseQueue
IF (@.@.ROWCOUNT = 0)
BREAK
END

Are you just looking at means to clean up the queue, or do you really want to fetch multiple conversation groups in a single batch. If it is the latter, could you explain your requirements further to help us improve the product in future versions?

Thanks,
Rushi
--
Developer, Service Broker Team, SQL Server Engine
Microsoft Corporation

|||Rushi's answer assumes that you indeed just want to get rid of the messages and leave the conversations active. If you also want to clean up the conversations - say you have run a bunch of tests and found your application doesn't work correctly - you might want to try one of these scripts which work in ascending dgrees of severity. If you truely don't care at all about the messages or conversations, try the last one:

-- End conversations that are in an error state

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'ER'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End conversations that are half closed

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'DI'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End all conversations

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

|||scripts of Roger_MS can solve my problem, but seems it clean all messages of all queues. If I want to clean a queue only, should I use my Queue_Name to replace sys.conversation_endpoints ? Just as

declare conv cursor for select conversation_handle from Queue_Name

And I think it is convenient to use a statement like "alter queue XXX with cleanup" .

Thank you.|||That would clean up one end of the conversations in the queue. Keep in mind that conversations always involve 2 queues so if you want to clean up the conversations you will have to clean up both queues. This is definitely not something you want to do regularly. Clearing out a queue is equivalent to truncating a table - there will often be valuable data destroyed if you do this so this would generally be something you do only during development.

How to clean (empty) a table?

Hi:
I have a table in a database, how to clean this table (remove all data
rows)? I guess there should be some SQL command to do that? There are
millions of rows, kind of hard to empty the table by hand.
Thanks for your helpDELETE tablename
or
TRUNCATE TABLE tablename
The latter is handy if there are no PK/FK relationships that it would break,
because (a) it is lightly logged, and (b) it also resets the seed for an
IDENTITY column, if one exists.
"Polaris" <etpolaris@.hotmail.com> wrote in message
news:#2EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I have a table in a database, how to clean this table (remove all data
> rows)? I guess there should be some SQL command to do that? There are
> millions of rows, kind of hard to empty the table by hand.
> Thanks for your help
>|||Thanks guys for your quick help!
Polaris <etpolaris@.hotmail.com> wrote in message
news:#2EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I have a table in a database, how to clean this table (remove all data
> rows)? I guess there should be some SQL command to do that? There are
> millions of rows, kind of hard to empty the table by hand.
> Thanks for your help
>|||Slight correction:
The table you try to TRUNCATE can't be referenced by any Foreign Keys, but
it can reference other tables and it can have a primary key.
Also, permissions on TRUNCATE TABLE are limited to the sysadmin, db_owner
and ddl_admin roles, which probably makes it a poor solution in production
systems.
Jacco
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eApNZzwRDHA.2252@.TK2MSFTNGP12.phx.gbl...
> Polaris,
> TRUNCATE TABLE <tablename> is a good option if the tables doesnt have any
> PK/FK's.This is minimally logged and since you are dealing with millions
of
> rows, this means a lot.If you cant use TRUNCATE TABLE, then the only
option
> left would be to do DELETE <tablename>.But here, do the DELETE in small
> batches and then truncate the transaction log in between so that you dont
> end up with a big log .
>
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Polaris" <etpolaris@.hotmail.com> wrote in message
> news:%232EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> > Hi:
> >
> > I have a table in a database, how to clean this table (remove all data
> > rows)? I guess there should be some SQL command to do that? There are
> > millions of rows, kind of hard to empty the table by hand.
> >
> > Thanks for your help
> >
> >
>|||Hi all!
I am in need of writing a few stored procedures.
The first one is to create a stored procedure to recover a database from
backup and the second one is to create a stored procedure to execute a
transaction log backup (even though I know this can be done through a
maintainence plan). Any help would be greatly appreciated.
Thanks in advance!|||Jacco,
Thanks for the correction! My intention was same as you mentioned but my
wording was wrong.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23hraMMxRDHA.2196@.TK2MSFTNGP11.phx.gbl...
> Slight correction:
> The table you try to TRUNCATE can't be referenced by any Foreign Keys, but
> it can reference other tables and it can have a primary key.
> Also, permissions on TRUNCATE TABLE are limited to the sysadmin, db_owner
> and ddl_admin roles, which probably makes it a poor solution in production
> systems.
> Jacco
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:eApNZzwRDHA.2252@.TK2MSFTNGP12.phx.gbl...
> > Polaris,
> >
> > TRUNCATE TABLE <tablename> is a good option if the tables doesnt have
any
> > PK/FK's.This is minimally logged and since you are dealing with millions
> of
> > rows, this means a lot.If you cant use TRUNCATE TABLE, then the only
> option
> > left would be to do DELETE <tablename>.But here, do the DELETE in small
> > batches and then truncate the transaction log in between so that you
dont
> > end up with a big log .
> >
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Polaris" <etpolaris@.hotmail.com> wrote in message
> > news:%232EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> > > Hi:
> > >
> > > I have a table in a database, how to clean this table (remove all data
> > > rows)? I guess there should be some SQL command to do that? There are
> > > millions of rows, kind of hard to empty the table by hand.
> > >
> > > Thanks for your help
> > >
> > >
> >
> >
>