Im working on a project that import data into a gash db. there are about 15
tables in this db and each phase of testing requires the tables to be
cleared. This seems to take ages when i got run a query as follows
delete table1
delete table2
etc
any suggestionon speeding this up ?use
truncate table table1
Use this.. if you don't need the data again. Its faster because its not
logged.
Hope this helps.
--
"Peter Newman" wrote:
> Im working on a project that import data into a gash db. there are about 1
5
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?|||You might consider using TRUNCATE TABLE instead. This statement generally
uses few locks and less log space.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:59687486-40A1-43C0-BBBA-1068519772F2@.microsoft.com...
> Im working on a project that import data into a gash db. there are about
> 15
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?
Showing posts with label import. Show all posts
Showing posts with label import. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Deleting Database users in 2005
We constantly import DB's from our different customers for research
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
Thanks
The best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegrou ps.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>
|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.
sql
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
Thanks
The best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegrou ps.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>
|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.
sql
Deleting Database users in 2005
We constantly import DB's from our different customers for research
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
ThanksThe best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegroups.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
ThanksThe best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegroups.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.
Wednesday, March 21, 2012
Deleting Database users in 2005
We constantly import DB's from our different customers for research
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
ThanksThe best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegroups.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
ThanksThe best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegroups.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.
Friday, February 24, 2012
Delete row when data is numeric?
I'm using a DTS package to import a large CSV file. There is a particular column that contains text or numbers. I want to delete the row if that column has a number, I've used IsNumeric in the selection portion of the statement, but can't figure out how to use it as part of my where clause.Never mind - i got it right after I posted... it has been a long week and I'm not thinking clearly any longer: Where IsNumeric(columnName)=1
Subscribe to:
Posts (Atom)