deleting duplicate record

in my table, I have several rows that are complete duplicates. Needless to
say, I can't open the table and just delete 1 row.
How do I delete the copies and not all?
Take a look at this link.
I've used it in the past to remove thousands of duplicates (dont ask how
they got there )
Once you've gotten rid of the dups, whack some contraints on where
necessary.
http://support.microsoft.com/default...b;en-us;139444
Immy
"Johnfli" <john@.ivhs.us> wrote in message
news:eXaiytCLGHA.1272@.TK2MSFTNGP10.phx.gbl...
> in my table, I have several rows that are complete duplicates. Needless
> to say, I can't open the table and just delete 1 row.
> How do I delete the copies and not all?
>
>

deleted my database log file, cant reattach

Hello,

A utility I was running caused our database's log file to run out of
hard drive.

So, I detached the database, deleted the log file, and tried to
reattach.

SQL server fails to reattach because it cant find the log file. (Why
it cant just create a new default one is beyond me...but...)

EXEC sp_attach_single_file_db @.dbname = 'sgvault',
@.physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\sgvault.mdf''

Could not open new database 'sgvault'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL\data\sgvault_log.LDF' may be
incorrect.

Do you know how I can fix this dilemma? Thanks!josepinchero@.yahoo.com (Jose) wrote in message news:<b9dff992.0408251727.2fe3e679@.posting.google.com>...
> Hello,
> A utility I was running caused our database's log file to run out of
> hard drive.
> So, I detached the database, deleted the log file, and tried to
> reattach.
> SQL server fails to reattach because it cant find the log file. (Why
> it cant just create a new default one is beyond me...but...)
> EXEC sp_attach_single_file_db @.dbname = 'sgvault',
> @.physname = 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\sgvault.mdf''
> Could not open new database 'sgvault'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft SQL Server\MSSQL\data\sgvault_log.LDF' may be
> incorrect.
> Do you know how I can fix this dilemma? Thanks!

Assuming that you detached the database with sp_detach_db, then this
should work. If not, and assuming that you have the correct path to
the .mdf in your command, as well as full permissions in that folder,
then you will probably have to restore from backup. You can then
shrink the log file as described here:

http://support.microsoft.com/defaul...8&Product=sql2k

Simon|||[posted and mailed]

Jose (josepinchero@.yahoo.com) writes:
> A utility I was running caused our database's log file to run out of
> hard drive.
> So, I detached the database, deleted the log file, and tried to
> reattach.

That was a very, very bad thing to do. Never, never delete log fils
(unless you really want to get rid of that database). And if you really
must do, first make sure that copied it somewhere else first.

I leave this is as a memento not only for you, but for anyone who might
see these thread on Google in the future.

> SQL server fails to reattach because it cant find the log file. (Why
> it cant just create a new default one is beyond me...but...)
> EXEC sp_attach_single_file_db @.dbname = 'sgvault',
> @.physname = 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\sgvault.mdf''
> Could not open new database 'sgvault'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft SQL Server\MSSQL\data\sgvault_log.LDF' may be
> incorrect.

I tried this exercise (with a database I could afford to waste), and I
got this message too, but the next message said that it was creating
an LDF for me.

It may be that your database was not shut down cleanly, for instance
because you panicked and killed SQL Server before you detached.

> Do you know how I can fix this dilemma? Thanks!

If you have a good backup, restore it. If not, well, you may be able
to get to that data - or you may not.

I know of a way to make the database accessible from SQL Server, in so
far that you can say "use db". But depending on what that application
was doing, your database may be more or less corrupt. This is because
you may get the database in the middle of a transaction, so that page
linkings may be bad. And if even DBCC comes out clean, your own data
may be inconsistent.

I am not going to post the steps bring the database back online, because
they are far too dangerous. I would strongly recommend you to open a
case with Microsoft Support. No that is not going to be cheap, but you
need to weigh that against of losing the data altogether.

If you absolutely want to fight this alone, I can send the instructions
by mail.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Deleted default database, now can't get in

Excuse my cross post but I wasn't sure which group was best.
I was cleaning up my SQL 2005 instance and deleted my default database
(obviously I forgot that i changed it to my default database), now I cannot
use the Studio Manager (it uses Windows Authentication), and I tried using
sqlcmd, but it says it won't accept remote connections, even though I set it
to accept them using the configuration manager.
Any one have any ideas of how I can rectify this situation? The only thing i
don't want to do, is lose the work i have already done on this, set up
replication and jobs, but if I have to I guess I'll have to.
Thanks,
NancyI'm not sure if the SQLCMD error about enabling remote connections is
related to the default database problem. Have you tried connecting with an
explicit database specification? For example:
sqlcmd -d master -E
Hope this helps.
Dan Guzman
SQL Server MVP
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
>
Can you get in as a different login (say as an adminstrator)?
Problem is, with a default database, the login has no place to map to, so
for security purposes can't let you in.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
Well if you dropped the default database, about the only thing you can do is
restore from your backup.
Dropping the database deletes it and all the objects.
> Thanks,
> Nancy
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks, but I tried that it doesn't work either, I should have been more
specific in my information, but thanks for the quick reply.
Nancy
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
> I'm not sure if the SQLCMD error about enabling remote connections is
> related to the default database problem. Have you tried connecting with
> an explicit database specification? For example:
> sqlcmd -d master -E
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>|||How to I restore if I can't get in, even using the Administrative connection
gets a remote connection not allowed error using sqlcmd?
Thanks, though,
Nancy
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23I5M%23hHfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
> Can you get in as a different login (say as an adminstrator)?
> Problem is, with a default database, the login has no place to map to, so
> for security purposes can't let you in.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
> Well if you dropped the default database, about the only thing you can do
> is restore from your backup.
> Dropping the database deletes it and all the objects.
>
>> Thanks,
>> Nancy
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||You seem to be working from your own workstation. Can you log directly onto
the SQL Server machine and run SQLCMD from there?
RLF
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
> Nancy
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
What does "does not work" mean? Do you get an error message? In such case
what?
I just tried setting the default database for a login, and then I dropped
that database. When I logged in with
sqlcmd -U frits -P xxxxx -S .\NELJÄ
this failed with "Cannot open user default database." as expected.
However, when I tried:
sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
I was able to get in.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||This instance is on my workstation.
Thanks,
Nancy
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OBvqxnHfHHA.3648@.TK2MSFTNGP05.phx.gbl...
> You seem to be working from your own workstation. Can you log directly
> onto the SQL Server machine and run SQLCMD from there?
> RLF
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
>> Nancy
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections,
>> even though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>>
>|||The server (my workstation is actively refusing the connection...may be
caused by the fact that by default SQL Server refuses remote connections - I
have set it up to accept remote connections.
This is the error I get when I tried your sqlcmd as well as the ones I tried
originally to get to the master db.
Nancy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99108DEBF1DDYazorman@.127.0.0.1...
> Nancy Lytle (nancy_lytle@.payformance.com) writes:
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
> What does "does not work" mean? Do you get an error message? In such case
> what?
> I just tried setting the default database for a login, and then I dropped
> that database. When I logged in with
> sqlcmd -U frits -P xxxxx -S .\NELJÄ
> this failed with "Cannot open user default database." as expected.
> However, when I tried:
> sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
> I was able to get in.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Just to let everyone know the outcome, after trying everything i could think
of , and that you all could suggest I almost gave up.
But first thing this morning I downloaded a trial of EMS SQL Manager for
SQL Server and installed it, and with it I could get into the instance and
change my default database back to master.
Thank goodness, that saved me a reinstall and hours of work,
Again, thank you all for your suggestions,
Nancy
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> The server (my workstation is actively refusing the connection...may be
> caused by the fact that by default SQL Server refuses remote connections
> - I have set it up to accept remote connections. This is the error I get
> when I tried your sqlcmd as well as the ones I tried originally to get
> to the master db.
Obviously the problem is something else than the missing default database.
If I understand your post correctly, the SQL Server is on your local
machine. If you then get the error "actively refused the connection",
I would recommend that you restart the SQL Server service, to see if
this helps.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Deleted default database, now can't get in

Excuse my cross post but I wasn't sure which group was best.
I was cleaning up my SQL 2005 instance and deleted my default database
(obviously I forgot that i changed it to my default database), now I cannot
use the Studio Manager (it uses Windows Authentication), and I tried using
sqlcmd, but it says it won't accept remote connections, even though I set it
to accept them using the configuration manager.
Any one have any ideas of how I can rectify this situation? The only thing i
don't want to do, is lose the work i have already done on this, set up
replication and jobs, but if I have to I guess I'll have to.
Thanks,
Nancy
I'm not sure if the SQLCMD error about enabling remote connections is
related to the default database problem. Have you tried connecting with an
explicit database specification? For example:
sqlcmd -d master -E
Hope this helps.
Dan Guzman
SQL Server MVP
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>
|||"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
>
Can you get in as a different login (say as an adminstrator)?
Problem is, with a default database, the login has no place to map to, so
for security purposes can't let you in.

> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
Well if you dropped the default database, about the only thing you can do is
restore from your backup.
Dropping the database deletes it and all the objects.

> Thanks,
> Nancy
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks, but I tried that it doesn't work either, I should have been more
specific in my information, but thanks for the quick reply.
Nancy
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
> I'm not sure if the SQLCMD error about enabling remote connections is
> related to the default database problem. Have you tried connecting with
> an explicit database specification? For example:
> sqlcmd -d master -E
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>
|||How to I restore if I can't get in, even using the Administrative connection
gets a remote connection not allowed error using sqlcmd?
Thanks, though,
Nancy
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23I5M%23hHfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Can you get in as a different login (say as an adminstrator)?
> Problem is, with a default database, the login has no place to map to, so
> for security purposes can't let you in.
>
> Well if you dropped the default database, about the only thing you can do
> is restore from your backup.
> Dropping the database deletes it and all the objects.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||You seem to be working from your own workstation. Can you log directly onto
the SQL Server machine and run SQLCMD from there?
RLF
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
> Nancy
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>
|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
What does "does not work" mean? Do you get an error message? In such case
what?
I just tried setting the default database for a login, and then I dropped
that database. When I logged in with
sqlcmd -U frits -P xxxxx -S .\NELJ
this failed with "Cannot open user default database." as expected.
However, when I tried:
sqlcmd -U frits -P xxxxx -S .\NELJ -d tempdb
I was able to get in.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||This instance is on my workstation.
Thanks,
Nancy
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OBvqxnHfHHA.3648@.TK2MSFTNGP05.phx.gbl...
> You seem to be working from your own workstation. Can you log directly
> onto the SQL Server machine and run SQLCMD from there?
> RLF
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
>
|||The server (my workstation is actively refusing the connection...may be
caused by the fact that by default SQL Server refuses remote connections - I
have set it up to accept remote connections.
This is the error I get when I tried your sqlcmd as well as the ones I tried
originally to get to the master db.
Nancy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99108DEBF1DDYazorman@.127.0.0.1...
> Nancy Lytle (nancy_lytle@.payformance.com) writes:
> What does "does not work" mean? Do you get an error message? In such case
> what?
> I just tried setting the default database for a login, and then I dropped
> that database. When I logged in with
> sqlcmd -U frits -P xxxxx -S .\NELJ
> this failed with "Cannot open user default database." as expected.
> However, when I tried:
> sqlcmd -U frits -P xxxxx -S .\NELJ -d tempdb
> I was able to get in.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Just to let everyone know the outcome, after trying everything i could think
of , and that you all could suggest I almost gave up.
But first thing this morning I downloaded a trial of EMS SQL Manager for
SQL Server and installed it, and with it I could get into the instance and
change my default database back to master.
Thank goodness, that saved me a reinstall and hours of work,
Again, thank you all for your suggestions,
Nancy
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>

Deleted default database, now can't get in

Excuse my cross post but I wasn't sure which group was best.
I was cleaning up my SQL 2005 instance and deleted my default database
(obviously I forgot that i changed it to my default database), now I cannot
use the Studio Manager (it uses Windows Authentication), and I tried using
sqlcmd, but it says it won't accept remote connections, even though I set it
to accept them using the configuration manager.
Any one have any ideas of how I can rectify this situation? The only thing i
don't want to do, is lose the work i have already done on this, set up
replication and jobs, but if I have to I guess I'll have to.
Thanks,
NancyI'm not sure if the SQLCMD error about enabling remote connections is
related to the default database problem. Have you tried connecting with an
explicit database specification? For example:
sqlcmd -d master -E
Hope this helps.
Dan Guzman
SQL Server MVP
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
>
Can you get in as a different login (say as an adminstrator)?
Problem is, with a default database, the login has no place to map to, so
for security purposes can't let you in.

> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
Well if you dropped the default database, about the only thing you can do is
restore from your backup.
Dropping the database deletes it and all the objects.

> Thanks,
> Nancy
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks, but I tried that it doesn't work either, I should have been more
specific in my information, but thanks for the quick reply.
Nancy
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
> I'm not sure if the SQLCMD error about enabling remote connections is
> related to the default database problem. Have you tried connecting with
> an explicit database specification? For example:
> sqlcmd -d master -E
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>|||How to I restore if I can't get in, even using the Administrative connection
gets a remote connection not allowed error using sqlcmd?
Thanks, though,
Nancy
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23I5M%23hHfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Can you get in as a different login (say as an adminstrator)?
> Problem is, with a default database, the login has no place to map to, so
> for security purposes can't let you in.
>
> Well if you dropped the default database, about the only thing you can do
> is restore from your backup.
> Dropping the database deletes it and all the objects.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||You seem to be working from your own workstation. Can you log directly onto
the SQL Server machine and run SQLCMD from there?
RLF
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
> Nancy
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
What does "does not work" mean? Do you get an error message? In such case
what?
I just tried setting the default database for a login, and then I dropped
that database. When I logged in with
sqlcmd -U frits -P xxxxx -S .\NELJ
this failed with "Cannot open user default database." as expected.
However, when I tried:
sqlcmd -U frits -P xxxxx -S .\NELJ -d tempdb
I was able to get in.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This instance is on my workstation.
Thanks,
Nancy
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OBvqxnHfHHA.3648@.TK2MSFTNGP05.phx.gbl...
> You seem to be working from your own workstation. Can you log directly
> onto the SQL Server machine and run SQLCMD from there?
> RLF
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
>|||The server (my workstation is actively refusing the connection...may be
caused by the fact that by default SQL Server refuses remote connections - I
have set it up to accept remote connections.
This is the error I get when I tried your sqlcmd as well as the ones I tried
originally to get to the master db.
Nancy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99108DEBF1DDYazorman@.127.0.0.1...
> Nancy Lytle (nancy_lytle@.payformance.com) writes:
> What does "does not work" mean? Do you get an error message? In such case
> what?
> I just tried setting the default database for a login, and then I dropped
> that database. When I logged in with
> sqlcmd -U frits -P xxxxx -S .\NELJ
> this failed with "Cannot open user default database." as expected.
> However, when I tried:
> sqlcmd -U frits -P xxxxx -S .\NELJ -d tempdb
> I was able to get in.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> The server (my workstation is actively refusing the connection...may be
> caused by the fact that by default SQL Server refuses remote connections
> - I have set it up to accept remote connections. This is the error I get
> when I tried your sqlcmd as well as the ones I tried originally to get
> to the master db.
Obviously the problem is something else than the missing default database.
If I understand your post correctly, the SQL Server is on your local
machine. If you then get the error "actively refused the connection",
I would recommend that you restart the SQL Server service, to see if
this helps.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Deleted Default Database

I had two databases on my server, and I just deleted the one which SQL Server
has identified as the default database. Now I can't connect to the server
via SQL Enterprise Manager. How can I rectify this situation? The server
itself is not having a problem, I'm just not able to connect remotely.
Actually, EM is the source of the problem. Login via another program (e.g.,
query analyzer) and reset the default db to one that exists (using
sp_defaultdb).
"KitCaz" <KitCaz@.discussions.microsoft.com> wrote in message
news:8F218C95-680F-4656-8AF9-354EA23E0694@.microsoft.com...
> I had two databases on my server, and I just deleted the one which SQL
Server
> has identified as the default database. Now I can't connect to the server
> via SQL Enterprise Manager. How can I rectify this situation? The server
> itself is not having a problem, I'm just not able to connect remotely.
|||Great, thanks! I see now it was a user ID thing.
"Scott Morris" wrote:

> Actually, EM is the source of the problem. Login via another program (e.g.,
> query analyzer) and reset the default db to one that exists (using
> sp_defaultdb).
> "KitCaz" <KitCaz@.discussions.microsoft.com> wrote in message
> news:8F218C95-680F-4656-8AF9-354EA23E0694@.microsoft.com...
> Server
>
>

Deleted Builtin\Administrator group now can’t login

As the title says I stupidly deleted the Builtin\Administrator group now can’t login. Is there anyway get back in?

I did not setup the server up so I’m unsure what the SA password is. As a last resort could I rebuild the Master database and then over write if backup?

As yourself say i think it is the solution (use same install disk).

I didn't try a way :

-restoring master backup of your situation to other server

-stop de service, copy mdf and ldf of master of that server,

-stop de service of problem server

-replace files of master and startup the service

|||

Would this work?

Well I won't make this mistake again

Thanks,

Alan

|||

What version of SQL Server are you using? For SQL Server 2005, a machine admin can connect by starting the server in single-user mode.

For SQL Server 2000, you could also enable mixed-mode authentication and connect as sa, if you know its password.

Thanks

Laurentiu

|||

I am adding a link to the blog article describing how to recover from this situation on SQL Server 2005 (it describes the mechanism that Laurentiu mentioned):

http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul. This worked great

DeleteCommand, Stored Procedures, and ReturnValue Parameters = cant be done?

I've a SqlDataSource control that has stored procedures specified for each of its commands: SelectCommand, InsertCommand, UpdateCommand, DeleteCommand . And for Insert, Update and Delete, I've specified asp:parameters for each stored procedure's parameters. Now, my stored procedures all have return values, and I've successfully accessed the return values for Insert and Update, but for some reason, I'm getting very wrong results for Delete.

<DeleteParameters>
<asp:Parameter Name="result" Type="Int32" Direction="ReturnValue" />
<asp:Parameter Name="myID" Type="Int32" />
</DeleteParameters>

The moment I add my "result" with the direction ReturnValue, I instantly get a"Procedure or function <storedprocedurename> has too many arguments specified." error. I checked my SQL Profiler, and it seems that the page is passing result as an Input parameter, instead of keeping it as a ReturnValue! e.g.

exec spName @.myID=1,@.result=NULL

when it should be

exec spName @.myID=1

I get the correct behavior with Update and Insert, so I'm wondering whether if this is a bug or by-design behavior or something very screwy with my computer?

Help? Thoughts?

Hi there,

I hope you are using the "out" keyword for the parameter that needs to be passed out.

Why dont you specify the default value for the out parameter in the asp:Parameter list?

thanks,

Murthy here

|||

Do you mean the OUTPUT keyword for the stored procedures? I'm not using OUTPUT parameters, I'm looking for the ReturnValue. What I'm actually doing is trying to access the ReturnValue of the stored procedure, like below

create procedure spTest
@.myID int = 0As
'some sql statements herereturn 1GO

The value I am interested in is1.The problem, however, is that my parameter, even though it's specified as a ReturnValue, it seems to act as an Input value regardless. And it end up passing the parameter into the stored procedure when it's not supposed to at all.

However, if I'm misunderstanding your solution: where exactly should I be using this keyword?

|||

Ok are you running the stored procedure manually?

If yes, you can check the number of rows affected as:

int status=command1.ExecuteNonQuery().

and then if the status is -1 then no rows have been affected else count is greater than 0.

You do not have to explicitly add a return value to check the status of the stored procedure. Hope I am clear.\

thanks,

Murthy here

|||

No, I'm not running the stored procedure manually. Yes, I know I am able to get the status (although your example returns rows affected, right?) if I were. [The reason why I'm checking the returnvalue is because there are various reasons why the process may fail, and in each instance, I return a different value to depict it.]

My question/problem is that setting theReturnValue parameters for theSQLDataSource control'sDELETE command does NOT seem to work (although I feel it should); instead it seems to ignore the direction and sets it to Input. I'm half-convinced this is a bug-- so I've moved on to using ObjectDataSource instead.

|||

Whatever works for you,

Murthy here

|||

Hi Jnghh,

You may visit the link http://forums.asp.net/thread/1670367.aspx which has the solution to these similar problems.

Hope it helps.

Thanks.

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?
Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>
|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundb?e" wrote:

> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the servers
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>
|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>
|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundb?e" wrote:

> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundb?e" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundb?e" wrote:

> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the serve
rs
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundb?e" wrote:

> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundb?e" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbøe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundbøe" wrote:
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the servers
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbøe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundbøe" wrote:
> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundbøe" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> > Hello!
> >
> > I would like to set up replication (cluster) on my SQL server 2000.
> > Unfortunately the server name is local so I cant create the replication. I
> > have to delete the server registration and create a new one with the
> > servers actual name. This is a production server. If I delete the
> > registration server and create a new one, will all my databases bee gone?
> > Do I have to create them from scratch or restore them from backup?
> >
>
>

Delete records in a table with 15 dependencies

Hi, I need to delete records in a table with 15 dependencies.
I can't drop the 15 dependencies before delete records.
Any ideas to delete records without dropping dependencies
Thanks,
Do you mean you want to delete a PK record which is used as a FK in 15
tables?
You could change the cascade option on the FKs to be a setting other than
the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL | SET
DEFAULT }
Alternatively you could temporarily disable the FK constraint (ALTER TABLE
cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of this
approach.
Thinking a little wider, I'd like to know more about the design you have
which will need to allow for orphaned records, if I have understood this
correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks a lot.
Your concern is correct 100%.
We are doing the test only for the same concern
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23eBTMeZCHHA.4224@.TK2MSFTNGP06.phx.gbl...
> Do you mean you want to delete a PK record which is used as a FK in 15
> tables?
> You could change the cascade option on the FKs to be a setting other than
> the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL | SET
> DEFAULT }
> Alternatively you could temporarily disable the FK constraint (ALTER TABLE
> cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of
> this approach.
> Thinking a little wider, I'd like to know more about the design you have
> which will need to allow for orphaned records, if I have understood this
> correctly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

Delete Records From a Table Using Records in Another Table.

I have been searching many postings and I cant seem to find anyone
that has this answer so I decided to post. I am using SQL
(Transact-SQL).If I have 2 tables with columns
acct_num,activity_date,and pay_amt and I want to delete one instance
of a record in table 1 for every instance of that record in table 2
how could I do that. For example.

Table 1
----
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00

Table 2
----
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00

I need a delete statement that will find 2 of the 5 records(It doesn't
matter which 2) and delete them.
Leaving table one looking like this.
Table 1
----
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00

How can I do this??On 20 Aug 2004 11:28:35 -0700, Philip Mette wrote:

> I have been searching many postings and I cant seem to find anyone
> that has this answer so I decided to post. I am using SQL
> (Transact-SQL).If I have 2 tables with columns
> acct_num,activity_date,and pay_amt and I want to delete one instance
> of a record in table 1 for every instance of that record in table 2
> how could I do that. For example.
> Table 1
> ----
> acct activity_date pay_amt
> 123 5/1/2004 50.00
> 123 5/1/2004 50.00
> 123 5/1/2004 50.00
> 123 5/1/2004 50.00
> 123 5/1/2004 50.00
> Table 2
> ----
> acct activity_date pay_amt
> 123 5/1/2004 50.00
> 123 5/1/2004 50.00
> I need a delete statement that will find 2 of the 5 records(It doesn't
> matter which 2) and delete them.
> Leaving table one looking like this.
> Table 1
> ----
> acct activity_date pay_amt
> 123 5/1/2004 50.00
> 123 5/1/2004 50.00
> 123 5/1/2004 50.00
> How can I do this??

Oy vey. There's no other fields in your table? No primary key? If you had a
primary key it wouldn't be so bad. But without a primary key, there's no
way to delete one row and leave another alone.

The nostandard MS SQL Server feature SET ROWCOUNT could get you out of it
if you are deleting from only one group at a time (say, iterating through
the groups in table2 on the client side or with a cursor). But that's very
ugly.

Tables NEED a primary key. Mr. Celko constantly makes this point, and he's
right.

With a primary key, if the tables were defined as

CREATE TABLE Table1 (
TransID int identity(4309,1) primary key,
acct char(3),
activity_date datetime,
pay_amt money
);

CREATE TABLE Table2 (
TransID int identity(2389,1) primary key ,
acct char(3),
activity_date datetime,
pay_amt money
);

INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)

INSERT INTO Table2 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table2 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)

then you can use this monstrosity to delete as many rows from table1 as
there are matching rows in table2:

DELETE FROM Table1
WHERE Table1.TransID IN
(
SELECT TransID FROM
(
SELECT X.TransID,X.acct,X.activity_date,X.pay_amt,COUNT(Y .TransID) num
FROM Table1 X
INNER JOIN Table1 Y
ON X.acct=Y.acct
AND X.activity_date=Y.activity_date
AND X.pay_amt=Y.PAY_AMT
WHERE X.TransID >= Y.TransID
GROUP BY X.TransID,X.acct,X.activity_date,X.pay_amt
) T1
WHERE T1.num <= (
SELECT COUNT(*) FROM Table2 T2
WHERE T1.acct=T2.acct
AND T1.activity_date=T2.activity_date
AND T1.pay_amt=T2.PAY_AMT
)
)

I'm pretty sure this can be simplified into at least one fewer derived
table, but my head hurts from doing this much.|||[posted and mailed, please reply in news]

Philip Mette (philipdm@.msn.com) writes:
> I have been searching many postings and I cant seem to find anyone
> that has this answer so I decided to post. I am using SQL
> (Transact-SQL).If I have 2 tables with columns
> acct_num,activity_date,and pay_amt and I want to delete one instance
> of a record in table 1 for every instance of that record in table 2
> how could I do that. For example.
>...
> I need a delete statement that will find 2 of the 5 records(It doesn't
> matter which 2) and delete them.
> Leaving table one looking like this.

As Ross Presser noted there is no primary key in your table, and tables
are supposed to have primary keys to uniquely identify each row. When you
have not, and this causes problems you are out in the wilderness and have
to apply funny tricks to get back home.

Here is a solution that does not create extra table. It is non-tested,
since you did not include CREATE TABLE statements and INSERT statements
for your sample data.

DECLARE @.accno int, @.actdate datetime, @.payamt money, @.cnt int

DECLARE cur INSENSITIVE CURSOR FOR
SELECT DISTINCT accno, actdate, payamt
FROM tbl1

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @.accno, @.actdate, @.payamt
IF @.@.fetch_status <> 0
BREAK

SELECT @.cnt = (SELECT COUNT(*) FROM tbl1
WHERE accno = @.accno AND @.actdate = actdate
AND payamt = @.payamt) -
(SELECT COUNT(*) FROM tbl2
WHERE accno = @.accno AND @.actdate = actdate
AND payamt = @.payamt)
IF @.cnt <= 0
CONTINUE

SET ROWCOUNT @.cnt

DELETE tbl1
WHERE accno = @.accno AND @.actdate = actdate AND payamt = @.payamt

SET ROWCOUNT 0
END

DEALLOCATE cur

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 20 Aug 2004 21:33:53 +0000 (UTC), Erland Sommarskog wrote:

> Philip Mette (philipdm@.msn.com) writes:
>> I have been searching many postings and I cant seem to find anyone
>> that has this answer so I decided to post. I am using SQL
>> (Transact-SQL).If I have 2 tables with columns
>> acct_num,activity_date,and pay_amt and I want to delete one instance
>> of a record in table 1 for every instance of that record in table 2
>> how could I do that. For example.
>>...
>> I need a delete statement that will find 2 of the 5 records(It doesn't
>> matter which 2) and delete them.
>> Leaving table one looking like this.
> As Ross Presser noted there is no primary key in your table, and tables
> are supposed to have primary keys to uniquely identify each row. When you
> have not, and this causes problems you are out in the wilderness and have
> to apply funny tricks to get back home.
> Here is a solution that does not create extra table. It is non-tested,
> since you did not include CREATE TABLE statements and INSERT statements
> for your sample data.

[snip]

Very clever. I still don't feel comfortable enough with cursors to write
such code off the cuff. :)

One minor point: you have it computing the difference between count(tbl1.*)
and count(tbl2.*), and deleting that number -- so if tbl1 starts with five
and tbl2 with two, it deletes three. But rereading his original request, I
think he wanted two deleted -- and that's how I wrote my monstrosity.|||Ross Presser (rpresser@.imtek.com) writes:
> Very clever. I still don't feel comfortable enough with cursors to write
> such code off the cuff. :)

Clever? Quite ugly, but it was an ugly problem.

> One minor point: you have it computing the difference between
> count(tbl1.*) and count(tbl2.*), and deleting that number -- so if tbl1
> starts with five and tbl2 with two, it deletes three. But rereading his
> original request, I think he wanted two deleted -- and that's how I
> wrote my monstrosity.

Do I have to say it again? When you have a problem like this, please
include:
o CREATE TABLE statements for your tables.
o INSERT statements with the sample data.
o The expected result.

This makes it very easy to cut and paste and from that produce a tested
solution. If you don't provide that, you are likely to get an untested
solution, because people who answer questions in these newsgroups do this
for fun, and not because they like typing. So you who have the problem
do the boring mechanical work, and we take care of the fun part: the
intellectual challenge.

So, there! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns954C6F3E593CDYazorman@.127.0.0.1>...
> Ross Presser (rpresser@.imtek.com) writes:
> > Very clever. I still don't feel comfortable enough with cursors to write
> > such code off the cuff. :)
> Clever? Quite ugly, but it was an ugly problem.
> > One minor point: you have it computing the difference between
> > count(tbl1.*) and count(tbl2.*), and deleting that number -- so if tbl1
> > starts with five and tbl2 with two, it deletes three. But rereading his
> > original request, I think he wanted two deleted -- and that's how I
> > wrote my monstrosity.
> Do I have to say it again? When you have a problem like this, please
> include:
> o CREATE TABLE statements for your tables.
> o INSERT statements with the sample data.
> o The expected result.
> This makes it very easy to cut and paste and from that produce a tested
> solution. If you don't provide that, you are likely to get an untested
> solution, because people who answer questions in these newsgroups do this
> for fun, and not because they like typing. So you who have the problem
> do the boring mechanical work, and we take care of the fun part: the
> intellectual challenge.
> So, there! :-)

Thanks Ross
This code does work except for the fact it deletes two many records.
Any ideas how to adjust this to make it delete the records I need
deleted? I am not familiar with cursor sql.|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns954C6F3E593CDYazorman@.127.0.0.1>...
> Ross Presser (rpresser@.imtek.com) writes:
> > Very clever. I still don't feel comfortable enough with cursors to write
> > such code off the cuff. :)
> Clever? Quite ugly, but it was an ugly problem.
> > One minor point: you have it computing the difference between
> > count(tbl1.*) and count(tbl2.*), and deleting that number -- so if tbl1
> > starts with five and tbl2 with two, it deletes three. But rereading his
> > original request, I think he wanted two deleted -- and that's how I
> > wrote my monstrosity.
> Do I have to say it again? When you have a problem like this, please
> include:
> o CREATE TABLE statements for your tables.
> o INSERT statements with the sample data.
> o The expected result.
> This makes it very easy to cut and paste and from that produce a tested
> solution. If you don't provide that, you are likely to get an untested
> solution, because people who answer questions in these newsgroups do this
> for fun, and not because they like typing. So you who have the problem
> do the boring mechanical work, and we take care of the fun part: the
> intellectual challenge.
> So, there! :-)

Thank you Ross and Erland for all your help.
Ross yours worked perfect! I will make sure in the future that I post
my table creation so that it makes more sense.|||Philip Mette (philipdm@.msn.com) writes:
> This code does work except for the fact it deletes two many records.
> Any ideas how to adjust this to make it delete the records I need
> deleted? I am not familiar with cursor sql.

If I have any ideas? Yes, I have ideas. They were in fact in the post
that you quoted, but I repeat it again:

>> Do I have to say it again? When you have a problem like this, please
>> include:
>> o CREATE TABLE statements for your tables.
>> o INSERT statements with the sample data.
>> o The expected result.
>>
>> This makes it very easy to cut and paste and from that produce a tested
>> solution. If you don't provide that, you are likely to get an untested
>> solution, because people who answer questions in these newsgroups do this
>> for fun, and not because they like typing. So you who have the problem
>> do the boring mechanical work, and we take care of the fun part: the
>> intellectual challenge.

I am sorry, but if you want correct and tested solution, you need to
put in some effort yourself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Delete record in sysdatabases

Hi,
in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
the DB compare the write: "(6.5 compatible)".
I don't know what this DB is. I want to remove it but by the right button of
the mouse I can't do anything.
I deleted the .mdf and .ldf files but the DB is not removed from the DB
list. It is present in the "sysdatabases" table of "master" database but I
can't remove the record.
It is possibible to delete the record from the table "sysdatabases"?
Thank you very much.
Davide Franzoni wrote:
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
Have you tried dropping it using the DROP DATABASE command?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||sysdatabases is one of the critical table that sql server uses. it is not a
recommended to issue any DML command directly on it. So my answer is NOT TO
delete
vt
"Davide Franzoni" <DavideFranzoni@.discussions.microsoft.com> wrote in
message news:E4864CFC-59FB-4F52-AFD3-3E4FB46ED5FF@.microsoft.com...
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
> of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
|||or try
sp_detach_db
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455491C7.8090402@.realsqlguy.com...
> Davide Franzoni wrote:
> Have you tried dropping it using the DROP DATABASE command?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com