Renaming a database

I've set my database to single user mode,and run:
sp_rename 'oldname' 'newname'
which works just fine.
Now I want to rename the oldname_data.mdf to
newname_data.mdf
To do that, I'm trying to detach the database, then go to
Explorer, rename the file and then reattach the database.
sp_detach_db 'newname'
returns an error:
Server: Msg 3702, Level 16, State 1, Line 1
Cannot drop the database 'newname' because it is currently
in use.
EXEC SQL DISCONNECT newname <or> 'newname'
returns an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'newname'.
How can I determine what has the database "in use" and
break that connection? (I'm on a 7.0 box in this case)
TIA MikeFirst open a query analyzer and connect to the SQL Server
in question. Then run this command:
SP_HOW2
search in the result and find any spid that currently
connected to the database and use this command ti kill
those users:
KILL <spid number>
That sould resolve your problem.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I've set my database to single user mode,and run:
>sp_rename 'oldname' 'newname'
>which works just fine.
>Now I want to rename the oldname_data.mdf to
>newname_data.mdf
>To do that, I'm trying to detach the database, then go to
>Explorer, rename the file and then reattach the database.
>sp_detach_db 'newname'
>returns an error:
>Server: Msg 3702, Level 16, State 1, Line 1
>Cannot drop the database 'newname' because it is
currently
>in use.
>EXEC SQL DISCONNECT newname <or> 'newname'
>returns an error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'newname'.
>How can I determine what has the database "in use" and
>break that connection? (I'm on a 7.0 box in this case)
>TIA Mike
>.
>|||I guess I'm not the only one with typo's today:) I think
that should be SP_WHO2
Vern
>--Original Message--
>First open a query analyzer and connect to the SQL Server
>in question. Then run this command:
>SP_HOW2
>search in the result and find any spid that currently
>connected to the database and use this command ti kill
>those users:
>KILL <spid number>
>That sould resolve your problem.
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
to
>>Explorer, rename the file and then reattach the database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>.
>|||That is correct ;-) it is SP_WHO2.
Thanks for correction.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I guess I'm not the only one with typo's today:) I think
>that should be SP_WHO2
>Vern
>>--Original Message--
>>First open a query analyzer and connect to the SQL
Server
>>in question. Then run this command:
>>SP_HOW2
>>search in the result and find any spid that currently
>>connected to the database and use this command ti kill
>>those users:
>>KILL <spid number>
>>That sould resolve your problem.
>>This posting is provided "AS IS" with no warranties, and
>>confers no rights.
>>http://www.microsoft.com/info/cpyright.htm
>>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
>to
>>Explorer, rename the file and then reattach the
database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>>.
>.
>

Rename machine with SQL server

I'm trying to write a script to run after changing the computer name of a machine running SQL Server 2005. I know about the article on the SQL Developer center that talks about sp_dropserver and sp_addserver. My question is about the Windows groups that are created with the machine name embedded inside (e.g. MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER).

Do I need to worry about these? If the answer is no because the name of the group is irrelevant, how do I go about changing the machine name for the login (i.e. the MACHINENAME in front of the slash)? I don't want to hardcode these group names into my script.

Thanks for any help.

There is no need to do anything with those local groups. They're created when you install/setup sqlserver. When you change the computer name, you would need to update sqlserver so that its servername matches up with the computername. See the following article for more detail.

http://msdn2.microsoft.com/en-us/library/ms143799(SQL.90).aspx

rename machine name

Hi,
After installing SQL 7, I rename the server machine name from A to B. I run
the setup and change server name. The server name shows B when using selec
t @.servername. But, in SQL log file, there is one line still show the old n
ame
Using 'SSMSRP70.DLL' version '7.0.961' to listen on 'A'.
What's the reason for that? Thanks.
Yulingsp_dropserver oldname
sp_addserver newname, LOCAL
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Yuling" <anonymous@.discussions.microsoft.com> wrote in message
news:FA945477-ADF0-4A31-96DA-CAC304CC9547@.microsoft.com...

quote:

---

> Hi,
> After installing SQL 7, I rename the server machine name from A to B. I

---

run the setup and change server name. The server name shows B when using
select @.servername. But, in SQL log file, there is one line still show the
old name

quote:

---

> Using 'SSMSRP70.DLL' version '7.0.961' to listen on 'A'.
> What's the reason for that? Thanks.
> Yuling

---

|||Look at question 5 in the following article:
195759 INF: Frequently Asked Questions - SQL Server 7.0 - SQL Setup
Basically you have to do the following:
Open the SQL Server Network Utility. Select Multiprotocol and
click on Remove. Click Add and select Multiprotocol from the list on the
left
hand window. This should now display the correct servername. Once
Multiprotocol is added, restart the SQL Server service and this should
correct
the problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

rename machine name

Hi
After installing SQL 7, I rename the server machine name from A to B. I run the setup and change server name. The server name shows B when using select @.servername. But, in SQL log file, there is one line still show the old nam
Using 'SSMSRP70.DLL' version '7.0.961' to listen on 'A'
What's the reason for that? Thanks
Yulingsp_dropserver oldname
sp_addserver newname, LOCAL
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Yuling" <anonymous@.discussions.microsoft.com> wrote in message
news:FA945477-ADF0-4A31-96DA-CAC304CC9547@.microsoft.com...
> Hi,
> After installing SQL 7, I rename the server machine name from A to B. I
run the setup and change server name. The server name shows B when using
select @.servername. But, in SQL log file, there is one line still show the
old name
> Using 'SSMSRP70.DLL' version '7.0.961' to listen on 'A'.
> What's the reason for that? Thanks.
> Yuling|||Look at question 5 in the following article:
195759 INF: Frequently Asked Questions - SQL Server 7.0 - SQL Setup
Basically you have to do the following:
Open the SQL Server Network Utility. Select Multiprotocol and
click on Remove. Click Add and select Multiprotocol from the list on the
left
hand window. This should now display the correct servername. Once
Multiprotocol is added, restart the SQL Server service and this should
correct
the problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Removing old .bak & .trn files

I've run into a problem I didn't expect with my backup program. It seems
that Windows admins are very lax about writing scripts to remove old backup
files after copying them to tape, even though they say its easy, they don't
do it for all database backups on all servers in the farm.
So, I'm wondering first, if I should do it from my backup program (based on
RealSQLGuy's) and second, how I should go about it.
My rough thought is to do an xm_cmdshell('dir') in the backup directory into
a table variable, parse the date from the filename into a datetime column,
select the old files and issue an xp_cmdshell('del <file>') command via
dynamic sql.
Thanks,
JayHello Jay!
In SQL Server 2005, you could use Maintenance Plan Wizard to create a
Maintenance Cleanup Task to delete old bak and trn files.
Ekrem Önsoy
"Jay" <spam@.nospam.org> wrote in message
news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
> I've run into a problem I didn't expect with my backup program. It seems
> that Windows admins are very lax about writing scripts to remove old
> backup files after copying them to tape, even though they say its easy,
> they don't do it for all database backups on all servers in the farm.
> So, I'm wondering first, if I should do it from my backup program (based
> on RealSQLGuy's) and second, how I should go about it.
> My rough thought is to do an xm_cmdshell('dir') in the backup directory
> into a table variable, parse the date from the filename into a datetime
> column, select the old files and issue an xp_cmdshell('del <file>')
> command via dynamic sql.
> Thanks,
> Jay
>
>|||Maintenance plan in 2005?
Besides, I do all my maintenance through SQLagent after writing the scripts
myself.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:OOO8bft9HHA.5424@.TK2MSFTNGP02.phx.gbl...
> Hello Jay!
>
> In SQL Server 2005, you could use Maintenance Plan Wizard to create a
> Maintenance Cleanup Task to delete old bak and trn files.
>
> --
> Ekrem Önsoy
>
> "Jay" <spam@.nospam.org> wrote in message
> news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> I've run into a problem I didn't expect with my backup program. It seems
>> that Windows admins are very lax about writing scripts to remove old
>> backup files after copying them to tape, even though they say its easy,
>> they don't do it for all database backups on all servers in the farm.
>> So, I'm wondering first, if I should do it from my backup program (based
>> on RealSQLGuy's) and second, how I should go about it.
>> My rough thought is to do an xm_cmdshell('dir') in the backup directory
>> into a table variable, parse the date from the filename into a datetime
>> column, select the old files and issue an xp_cmdshell('del <file>')
>> command via dynamic sql.
>> Thanks,
>> Jay
>>
>|||This is just me but I would create a batch file to delete the files and give
it to the Windows group to execute it after they do the tape backup. Make
sure that the batch file execution step depends on the tape backup so that
you are not without your backups if the tape backup fails.
"Jay" wrote:
> Maintenance plan in 2005?
> Besides, I do all my maintenance through SQLagent after writing the scripts
> myself.
> "Ekrem Ã?nsoy" <ekrem@.btegitim.com> wrote in message
> news:OOO8bft9HHA.5424@.TK2MSFTNGP02.phx.gbl...
> > Hello Jay!
> >
> >
> > In SQL Server 2005, you could use Maintenance Plan Wizard to create a
> > Maintenance Cleanup Task to delete old bak and trn files.
> >
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> > "Jay" <spam@.nospam.org> wrote in message
> > news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
> >> I've run into a problem I didn't expect with my backup program. It seems
> >> that Windows admins are very lax about writing scripts to remove old
> >> backup files after copying them to tape, even though they say its easy,
> >> they don't do it for all database backups on all servers in the farm.
> >>
> >> So, I'm wondering first, if I should do it from my backup program (based
> >> on RealSQLGuy's) and second, how I should go about it.
> >>
> >> My rough thought is to do an xm_cmdshell('dir') in the backup directory
> >> into a table variable, parse the date from the filename into a datetime
> >> column, select the old files and issue an xp_cmdshell('del <file>')
> >> command via dynamic sql.
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
> >
>
>

Removing Named Instance

We want to rename our instance and I understand there isn't a straightforward way to do so. I'm prepared to run SQLEXPR again to create the new named instance. However, I'm not clear on how to remove the other named instance once the data files have been moved over. I do not want the old service "SQL Server (<old_named_instance>)" to be running. I would also like the files "C:\Program Files\Microsoft SQL Server\MSSQL.1" removed along with the registry entries for this instance.

I tried running SQLEXPR with a /? option but that invoked the installer and did not give me the command line options. Is there a simple way to remove a specific named instance? Thanks.

Go to Add/Remove Programs and look for the entry for Microsoft SQL Server 2005 and click the Remove button. The dialog that opens will show you two lists, the top is list of the installed instances for any instance aware components. (For SQL Express this includes the database engine and reporting services if you installed SSE Advanced) The bottom is any common components that are shared across instances.

You will want to select the named instance for the services you want to remove. Since it sounds like you only have one instance of SQL Express, you'll likely see just the single entrie that reads "SQLEXPRES: Database Engine" in the list. Since you're planning on re-installing another instance, there is no need to remove the shared components unless you really don't want them anymore. Since they are shared, the existing components will continue to work with the new instance you install.

Having selected the items to remove, click Next and walk through the rest of the wizard. Once completed you will have removed the database engine for the named instance SQLEXPRESS. Go ahead and install a fresh copy to your desired instance name and you should be up and running.

For those who care to know, there are some components that are not removed via this uninstall path. They are not removed because they are technically independent programs that we can install automatically, but can't remove automatically without risking breaking things. If you were interested in doing a complete uninstall (which is not the case here) you would need to remove those programs separately from the ARP dialog. These additional components include Management Studio Express and the SQL Server Setup Support Files.

Mike

|||Thanks Mike, this is very helpful. I currently have two named instances and they both appear in the list after clicking Remove, just as you said. Is there any way to peform this function via a command script? We have a Beta version of our product installed in a few remote locations and would prefer having them run a single command file to uninstall the old instance, reinstall the new, etc.|||

The command line interface for the installer (which is also used to remove things) is documented in Books Online. You should check out the REMOVE statement. I also suggest you ask this question in the SQl Setup forum as they may have other/better ideas.

Mike

Removing merge replication

Hello!
I've got quite fu**ed up merge replication still sitting on one DB and I
cannot remove it. When I run sp_removedbreplication db_name I get an error
that it cannot delete conflict table. Now, as I was checking how to correct
this error, I see that sp_MSarticlecleanup is trying to delete those tables.
But the problem is, that when it tries to do this, it uses owner name of the
real table; here's an example:
I have table1 who's owner is joe. Now SQL thinks that the conflict table for
this table1 should also be owned by joe, but it's not. My case is like this:
Original table: joe.table1
Conflict table: dbo.conflict_pub_name_table1
So if you'll check the sp_MSarticlecleanup it tries to find the owner of
original table and then it tries to delete owner.conflict_table table and I
cannot change this anywhere. I also cannot delete those tables manualy as
they are marked as System tables ...
Any hintS?! Can I alter the MSarticlecleanup just for this, so I can
'hardcode' the owner of conflict tables just for this one time run? I tried
to give my self a permission to alter this stored procedure, but no luck; I
can only set EXEC permission.
Any hints greatly appreciated!
Kind regards,
Dejan
--
AKTON Communications d.o.o.
Tbilisijska 81, 1000 Ljubljana, Slovenia
Tel.: +386 1 200 200 1
Fax.: +386 1 200 2011
Dejan,
have a look at Hilary's reply to a post from last week on Removing
Replication. He has a pretty comprehensive script which will as far as I
remember, remove the conflict tables amongst many other things.
Regards,
Paul Ibison
|||Thanks Paul and Hilary, ofcourse!
It did drop everything, including few indexes it shouldn't drop; but I've
recreated them and it works OK now.
Kind regards,
Dejan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLcVMUYFEHA.2768@.tk2msftngp13.phx.gbl...
| Dejan,
| have a look at Hilary's reply to a post from last week on Removing
| Replication. He has a pretty comprehensive script which will as far as I
| remember, remove the conflict tables amongst many other things.
| Regards,
| Paul Ibison
|
|
|||which indexes?
"Dejan Markic" <dejan@.akton.is> wrote in message
news:xMV9c.7161$%x4.956323@.news.siol.net...
> Thanks Paul and Hilary, ofcourse!
> It did drop everything, including few indexes it shouldn't drop; but I've
> recreated them and it works OK now.
> Kind regards,
> Dejan
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eLcVMUYFEHA.2768@.tk2msftngp13.phx.gbl...
> | Dejan,
> | have a look at Hilary's reply to a post from last week on Removing
> | Replication. He has a pretty comprehensive script which will as far as I
> | remember, remove the conflict tables amongst many other things.
> | Regards,
> | Paul Ibison
> |
> |
>
|||The following code line has to be fixed:
select @.qualified_name = QUOTENAME(@.ownername) + '.' +
QUOTENAME(@.conflict_table)
Could be replaced with something like that :
IF EXISTS(
SELECT 1
FROM SYSOBJECTS O
JOIN SYSUSERS U
ON O.UID = U.UID
WHERE O.NAME = @.conflict_table
AND U.NAME = @.ownername )
BEGIN
select @.qualified_name = QUOTENAME(@.ownername) + '.' +
QUOTENAME(@.conflict_table)
END
ELSE
BEGIN
select @.qualified_name = QUOTENAME(@.conflict_table)
END
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Dejan Markic" <dejan@.akton.is> wrote in message
news:L6T9c.7144$%x4.954553@.news.siol.net...
> Hello!
> I've got quite fu**ed up merge replication still sitting on one DB and I
> cannot remove it. When I run sp_removedbreplication db_name I get an error
> that it cannot delete conflict table. Now, as I was checking how to
correct
> this error, I see that sp_MSarticlecleanup is trying to delete those
tables.
> But the problem is, that when it tries to do this, it uses owner name of
the
> real table; here's an example:
> I have table1 who's owner is joe. Now SQL thinks that the conflict table
for
> this table1 should also be owned by joe, but it's not. My case is like
this:
> Original table: joe.table1
> Conflict table: dbo.conflict_pub_name_table1
> So if you'll check the sp_MSarticlecleanup it tries to find the owner of
> original table and then it tries to delete owner.conflict_table table and
I
> cannot change this anywhere. I also cannot delete those tables manualy as
> they are marked as System tables ...
> Any hintS?! Can I alter the MSarticlecleanup just for this, so I can
> 'hardcode' the owner of conflict tables just for this one time run? I
tried
> to give my self a permission to alter this stored procedure, but no luck;
I
> can only set EXEC permission.
> Any hints greatly appreciated!
> Kind regards,
> Dejan
> --
> --
> AKTON Communications d.o.o.
> Tbilisijska 81, 1000 Ljubljana, Slovenia
> Tel.: +386 1 200 200 1
> Fax.: +386 1 200 2011
>

Removing headers from sql output

I'm writing a query which creates a script that I then want to be able to run, but can't get rid of the headers.

eg. my query is similar to this:
USE master
go
set nocount on
go
select 'restore database @.dbname = N' + "'" + db_name() + "'" + ','
go
select '@.filename' +convert(varchar(5),fileid)+ ' = N'+convert(varchar(50),filename)+',' from sysfiles
go

The output is similar this:
------------------------------------------------
restore database @.dbname = N'master',

---------------------
@.filename1 = Nd:\sysdata\SQL2000\MSSQL${instancename}\data\mast er.mdf,
@.filename2 = Nd:\sysdata\SQL2000\MSSQL${instancename}\data\mast log.ld,

I want to get rid of all the '---' and lines between the output.

Can anyone help please?Hello,

which database do you use ? Looks like MSSQL ?

Regards

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||I'm writing the script for SQL2000 -

Removing Duplicate Values in Filter List...

Hello!
I am pulling two values from a field when the report is first run. The
value can either be ACTIVE or DISABLED. This is being accessed in a
parameter and I would like to be able to filter my current report by
selecting either ACTIVE or DISABLED records. The catch is that I need to do
this without taking a trip back to the server.
I could hardcode the two values in there, however that would require the
report to re-render each time the filter is applied. Can I have two distinct
values in my filter and have this applied at the time of the first rendering?
Right now I get all values for my status_cd column which is a bunch of
DISABLED and ACTIVE values. I just need two. Let me know what you think.
Thanks!Just a short clarification. After I wrote all that I realized that I needed
to explain a little more...
We are pulling in the values for the filter from a dataset. The
Active/Disabled example isn't all that great because I can hardcode the
values for that to get it to work. However, the functionality we really need
is as follows...
We have users that are associated with clients. So multiple users have the
same client. In this filter drop-down when it populates from the dataset it
is populating the same client multiple times based upon how many users are
associated with that client. All we need is each client a single time. We
can do this by creating another dataset, but we would like to do it with the
current dataset instead of making a trip back to the server. Your help is
appreciated. Thanks!
"jrennard" wrote:
> Hello!
> I am pulling two values from a field when the report is first run. The
> value can either be ACTIVE or DISABLED. This is being accessed in a
> parameter and I would like to be able to filter my current report by
> selecting either ACTIVE or DISABLED records. The catch is that I need to do
> this without taking a trip back to the server.
> I could hardcode the two values in there, however that would require the
> report to re-render each time the filter is applied. Can I have two distinct
> values in my filter and have this applied at the time of the first rendering?
> Right now I get all values for my status_cd column which is a bunch of
> DISABLED and ACTIVE values. I just need two. Let me know what you think.
> Thanks!

Removing Duplicate Rows Issue

I'm trying to remove duplicate rows from a table. tableA has 2 cols. acctid
and col1 When I run:
select count(distinct accid) from tableA
I get 10 as a result(I guess there are 10 distinct rows in the table,
correct?), but when I run:
select distinct acctid, col2 into #temp from tableA
(those are the only 2 columns in the table) I get 11 as the result, How
come? Shouldn't it say 10 rows affexted and not 11? any ideas?
thanksCan you show us the structure of tableA, and the INSERT statements required
to populate it with these 10 or 11 rows?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> I'm trying to remove duplicate rows from a table. tableA has 2 cols.
acctid
> and col1 When I run:
> select count(distinct accid) from tableA
> I get 10 as a result(I guess there are 10 distinct rows in the table,
> correct?), but when I run:
> select distinct acctid, col2 into #temp from tableA
> (those are the only 2 columns in the table) I get 11 as the result, How
> come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> thanks|||tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
when I do select count(distinct acctid) from tableA I get 10 rows as the
result, then when I select distinct aactid, col1 into #temp from tablea I get
11 rows, Why is this the case? Thank you
"Aaron [SQL Server MVP]" wrote:
> Can you show us the structure of tableA, and the INSERT statements required
> to populate it with these 10 or 11 rows?
> http://www.aspfaq.com/5006
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> acctid
> > and col1 When I run:
> > select count(distinct accid) from tableA
> > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > correct?), but when I run:
> > select distinct acctid, col2 into #temp from tableA
> > (those are the only 2 columns in the table) I get 11 as the result, How
> > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > thanks
>
>|||Because you added a column to your distinct clause in the second query.
select distinct aactid, col1 from tablea
vs
select distinct aactid from tablea
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> when I do select count(distinct acctid) from tableA I get 10 rows as the
> result, then when I select distinct aactid, col1 into #temp from tablea I
get
> 11 rows, Why is this the case? Thank you
> "Aaron [SQL Server MVP]" wrote:
> > Can you show us the structure of tableA, and the INSERT statements
required
> > to populate it with these 10 or 11 rows?
> > http://www.aspfaq.com/5006
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > acctid
> > > and col1 When I run:
> > > select count(distinct accid) from tableA
> > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > correct?), but when I run:
> > > select distinct acctid, col2 into #temp from tableA
> > > (those are the only 2 columns in the table) I get 11 as the result,
How
> > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > thanks
> >
> >
> >|||that was an example, what I'm really trying to do is remove duplicate by
these statements
select distinct acctid, col1 into #temp from tableA
truncate table tableA
insert into tableA select * from #temp
the logic is in the previous posts!
"Jeff Dillon" wrote:
> Because you added a column to your distinct clause in the second query.
> select distinct aactid, col1 from tablea
> vs
> select distinct aactid from tablea
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> > when I do select count(distinct acctid) from tableA I get 10 rows as the
> > result, then when I select distinct aactid, col1 into #temp from tablea I
> get
> > 11 rows, Why is this the case? Thank you
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > Can you show us the structure of tableA, and the INSERT statements
> required
> > > to populate it with these 10 or 11 rows?
> > > http://www.aspfaq.com/5006
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > > acctid
> > > > and col1 When I run:
> > > > select count(distinct accid) from tableA
> > > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > > correct?), but when I run:
> > > > select distinct acctid, col2 into #temp from tableA
> > > > (those are the only 2 columns in the table) I get 11 as the result,
> How
> > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > thanks
> > >
> > >
> > >
>
>|||Yes, your logic is wrong, per my response.
You are aware that distinct acctid, col1 means take (acctid, col1) together,
and find all distinct rows. Distinct doesn't only apply to the first column
before your comma
To find duplicates:
select count(id), id from a
group by id
having count(id) > 1
Jeff
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:2B455174-0A8A-47E3-BB71-AA26CBE37D0E@.microsoft.com...
> that was an example, what I'm really trying to do is remove duplicate by
> these statements
> select distinct acctid, col1 into #temp from tableA
> truncate table tableA
> insert into tableA select * from #temp
> the logic is in the previous posts!
> "Jeff Dillon" wrote:
> > Because you added a column to your distinct clause in the second query.
> >
> > select distinct aactid, col1 from tablea
> >
> > vs
> >
> > select distinct aactid from tablea
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > > tableA has 2 columns acctid and col1. lets say there are 20 rows of
data,
> > > when I do select count(distinct acctid) from tableA I get 10 rows as
the
> > > result, then when I select distinct aactid, col1 into #temp from
tablea I
> > get
> > > 11 rows, Why is this the case? Thank you
> > >
> > > "Aaron [SQL Server MVP]" wrote:
> > >
> > > > Can you show us the structure of tableA, and the INSERT statements
> > required
> > > > to populate it with these 10 or 11 rows?
> > > > http://www.aspfaq.com/5006
> > > >
> > > > --
> > > > http://www.aspfaq.com/
> > > > (Reverse address to reply.)
> > > >
> > > >
> > > >
> > > >
> > > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > > I'm trying to remove duplicate rows from a table. tableA has 2
cols.
> > > > acctid
> > > > > and col1 When I run:
> > > > > select count(distinct accid) from tableA
> > > > > I get 10 as a result(I guess there are 10 distinct rows in the
table,
> > > > > correct?), but when I run:
> > > > > select distinct acctid, col2 into #temp from tableA
> > > > > (those are the only 2 columns in the table) I get 11 as the
result,
> > How
> > > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
> >