"unuse database"?

Hi,
I used database A:
use A
go
(do something)
After I worked with a database A, I want to drop it. I need to change to
another database B by issue the coommands
use B
go
drop databse A
go
I wonder if there is a command to "unuse" A so that I do not need use B to
drop A.
Thanks.You do not have to "unuse" the database; instead switch to master & drop the
desired database.
Anith|||Have to be connected to master to do this, not either of the databases you
are playing with. Then, run:
exec sp_detach_db @.dbname='databaseName'
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"§Chrissi§" wrote:

> Hi,
> I used database A:
> use A
> go
> (do something)
> After I worked with a database A, I want to drop it. I need to change to
> another database B by issue the coommands
> use B
> go
> drop databse A
> go
> I wonder if there is a command to "unuse" A so that I do not need use B to
> drop A.
> Thanks.
>
>|||USE master
but why would you want to do something in a database then drop it?
David Portas
SQL Server MVP
--|||Hi,
You can not a drop a database if it is being used by any session. Only way
is to change the session to point to another database
and drop the database.
Thanks
Hari
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:%23I8VriOUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I used database A:
> use A
> go
> (do something)
> After I worked with a database A, I want to drop it. I need to change to
> another database B by issue the coommands
> use B
> go
> drop databse A
> go
> I wonder if there is a command to "unuse" A so that I do not need use B to
> drop A.
> Thanks.
>|||It is better for you to explicitly choose which database to reset context to
(ex: USE B) than let SQL Server decide by default. Otherwise, any following
T-SQL commands are not deterministic.
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:%23I8VriOUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I used database A:
> use A
> go
> (do something)
> After I worked with a database A, I want to drop it. I need to change to
> another database B by issue the coommands
> use B
> go
> drop databse A
> go
> I wonder if there is a command to "unuse" A so that I do not need use B to
> drop A.
> Thanks.
>

"Go" into comments

Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon, Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
DonaldThe problem is that your GO is *not* inside the comments. The client tool (Query Analyzer, OSQL or
what you are using) will see the GO and separate the batches. So, the first batch doesn't have an
end-comment. And the next batch doesn't have a start-comment.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Correct, the GO is between the comment block...but as you have noticed it is
not treated properly. This works:
alter PROC testdon
AS
-- drop table setup
-- go
SELECT *
FROM MRP_SM_BALPL
It is easy to "multi comment" lines within SQL 2000 Query Analyzer. Simply
highlight the lines and hit Ctrl-Shift-C. Ctrl-Shift-R removes comments.
--
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Because GO is a batch separator and is parsed PRIOR to the comment
delimiters. Try highlighting the code you want to comment and hitting
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Hi Mister Aaron,
Thanks for the explanations,
But : those operation do not work with Query analyser version 7.00.623 :o(
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
Donald
>--Original Message--
>Because GO is a batch separator and is parsed PRIOR to the comment
>delimiters. Try highlighting the code you want to comment and hitting
>CTRL+SHIFT+C -- comment
>CTRL+SHIFT+R -- remove comment
>-- >http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in message
>news:818101c495b5$833dc860$a601280a@.phx.gbl...
>Hi every one,
>Using SQL 7.0,
>Why do i get the following error when i run this :
>
>alter PROC testdon
>AS
>/*
>drop table setup
>go
>*/
>SELECT *
>FROM MRP_SM_BALPL
>error :
>Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon,
>Ligne 6
>Missing end comment mark '*/'.
>Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
>Line 1: Incorrect syntax near '*'.
>If i remove "Go", it works... :o\
>But it's into comments !
>Thanks !
>Donald
>
>.
>|||Also, Go is not a T-SQL keyword. It's just a batch separator that tools
like Query Analyzer understand. ie. you would never use "go" inside a
stored procedure.
--
David G.

"Go" into comments

Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :=20
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :=20
Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon,=20
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald
The problem is that your GO is *not* inside the comments. The client tool (Query Analyzer, OSQL or
what you are using) will see the GO and separate the batches. So, the first batch doesn't have an
end-comment. And the next batch doesn't have a start-comment.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, tat 1, Procdure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, tat 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald
|||Because GO is a batch separator and is parsed PRIOR to the comment
delimiters. Try highlighting the code you want to comment and hitting
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
http://www.aspfaq.com/
(Reverse address to reply.)
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, tat 1, Procdure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, tat 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald
|||Correct, the GO is between the comment block...but as you have noticed it is
not treated properly. This works:
alter PROC testdon
AS
-- drop table setup
-- go
SELECT *
FROM MRP_SM_BALPL
It is easy to "multi comment" lines within SQL 2000 Query Analyzer. Simply
highlight the lines and hit Ctrl-Shift-C. Ctrl-Shift-R removes comments.
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, tat 1, Procdure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, tat 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald
|||Hi Mister Aaron,
Thanks for the explanations,
But : those operation do not work with Query analyser=20
version 7.00.623 :o(
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
Donald=20

>--Original Message--
>Because GO is a batch separator and is parsed PRIOR to=20
the comment
>delimiters. Try highlighting the code you want to=20
comment and hitting
>CTRL+SHIFT+C -- comment
>CTRL+SHIFT+R -- remove comment
>--=20
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:818101c495b5$833dc860$a601280a@.phx.gbl...
>Hi every one,
>Using SQL 7.0,
>Why do i get the following error when i run this :
>
>alter PROC testdon
>AS
>/*
>drop table setup
>go
>*/
>SELECT *
>FROM MRP_SM_BALPL
>error :
>Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon,
>Ligne 6
>Missing end comment mark '*/'.
>Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
>Line 1: Incorrect syntax near '*'.
>If i remove "Go", it works... :o\
>But it's into comments !
>Thanks !
>Donald
>
>.
>
|||Also, Go is not a T-SQL keyword. It's just a batch separator that tools
like Query Analyzer understand. ie. you would never use "go" inside a
stored procedure.
David G.

"Drop User" statement does not seem to work

I restored a copy of the production database onto the staging db server. Som
e
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone encoun
tered
this issue? Thanks.Check out sp_change_users_login in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:FF46C63C-56E8-41F7-B238-2E0DA602E209@.microsoft.com...
I restored a copy of the production database onto the staging db server.
Some
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone
encountered
this issue? Thanks.

"Drop Table" at Stored Procedure

Hello folks,
i like to change a sql-skript to a procedure. The skript includes 20
statements like
select ... into #temp
and
drop table #temp
It runs as a batch if i use
drop table #temp
go
In order to change it to a procedure i change all "drops" to
drop table #temp;
but i can't create the procedure.
The SQL-Server error message was
'Es gibt bereits ein Objekt #Temp'
(translated 'Object #temp still exists')
This Message was shown five times and point to the Select-Staments (not to
all).
To move the drop - Statement direct before the select does'nt help to solve
this behaivor;
I have no idea to solve this problem.
Thanks in advance for any help.
NielsCREATE PROC mysp
AS
IF OBJECT_ID('TempTable') IS NOT NULL
DROP TABLE TempTable
"nieurig" <nieurig@.discussions.microsoft.com> wrote in message
news:045E2CDA-E8BF-4F55-8668-EC1B47139314@.microsoft.com...
> Hello folks,
> i like to change a sql-skript to a procedure. The skript includes 20
> statements like
> select ... into #temp
> and
> drop table #temp
> It runs as a batch if i use
> drop table #temp
> go
> In order to change it to a procedure i change all "drops" to
> drop table #temp;
> but i can't create the procedure.
> The SQL-Server error message was
> 'Es gibt bereits ein Objekt #Temp'
> (translated 'Object #temp still exists')
> This Message was shown five times and point to the Select-Staments (not to
> all).
> To move the drop - Statement direct before the select does'nt help to
solve
> this behaivor;
> I have no idea to solve this problem.
> Thanks in advance for any help.
> Niels
>|||It's a resolution issue. Consider naming the temporary table differently in
each create. The other option is to use dynamic execution, but such approach
involves many issues of its own.
BG, SQL Server MVP
www.SolidQualityLearning.com
"nieurig" <nieurig@.discussions.microsoft.com> wrote in message
news:045E2CDA-E8BF-4F55-8668-EC1B47139314@.microsoft.com...
> Hello folks,
> i like to change a sql-skript to a procedure. The skript includes 20
> statements like
> select ... into #temp
> and
> drop table #temp
> It runs as a batch if i use
> drop table #temp
> go
> In order to change it to a procedure i change all "drops" to
> drop table #temp;
> but i can't create the procedure.
> The SQL-Server error message was
> 'Es gibt bereits ein Objekt #Temp'
> (translated 'Object #temp still exists')
> This Message was shown five times and point to the Select-Staments (not to
> all).
> To move the drop - Statement direct before the select does'nt help to
> solve
> this behaivor;
> I have no idea to solve this problem.
> Thanks in advance for any help.
> Niels
>|||Thanks to Itzik and Uri !!
I will use the workaround with different names of my temp-table.
Have a nice day.
Niels

"DROP INDEX failed because the following SET options have incorrect settings: 'ARITHABORT

Hi,
I've got a big import routine that brings a lot of data in raw tables into
another database - the routine is about 2000 lines long. I have a DTS
package that first brings the data in from CSV files and other sources, then
it runs this sproc to bring the data into the other database, then finally
it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
sproc in the target database which drops all indexes on the target database,
start a transaction, bring all of the data in, then EXEC another sproc on
the target database to recreate the indexes again. Like I said, it's a
monster! :)
This sproc worked flawlessly for about six months, but now when I run the
DTS package it fails on the sproc step with the error mentioned in the
Subject line. The table on which this is failing has a clustered unique
index and 8 other standard indexes, but there are no indexes on calculated
columns or indexed views or anything like that. I am able to run the sproc
on the data from Query Analyzer with no errors, and I have tried DROPping
INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
Can anybody help out with this?
Thanks, JimDo you have any hypothetical indexes created by the ITW ?
select * from sysindexes where name like 'hind_%'
If you have no "real" indexes on computed columns or views its woeth
dropping any hypothetical ones and seeing it that helps
It may be worth explicitly setting the correct set options in your sproc
anyway to be on the safe side
set ANSI_PADDING,ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,ARITHABORT,
QUOTED_IDENTIFIER,ANSI_NULLS on
set NUMERIC_ROUNDABORT off
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim" <jim@.x.com> wrote in message
news:erLkiH%23JEHA.3276@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a big import routine that brings a lot of data in raw tables into
> another database - the routine is about 2000 lines long. I have a DTS
> package that first brings the data in from CSV files and other sources,
then
> it runs this sproc to bring the data into the other database, then finally
> it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
> sproc in the target database which drops all indexes on the target
database,
> start a transaction, bring all of the data in, then EXEC another sproc on
> the target database to recreate the indexes again. Like I said, it's a
> monster! :)
> This sproc worked flawlessly for about six months, but now when I run the
> DTS package it fails on the sproc step with the error mentioned in the
> Subject line. The table on which this is failing has a clustered unique
> index and 8 other standard indexes, but there are no indexes on calculated
> columns or indexed views or anything like that. I am able to run the
sproc
> on the data from Query Analyzer with no errors, and I have tried DROPping
> INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
> Can anybody help out with this?
> Thanks, Jim
>

! PLEASE HELP !! Collation selection on install

Hi, I need to change the collation when I install SQL Express 2005 to SQL_LATIN1_GENERAL_CP1_CI_AS but the drop down on setup only allows

LATIN1_GENERAL.

How do I get the correct collation to appear?

Thanks for any help/tips

hi,

as you can see in http://msdn2.microsoft.com/en-us/library/ms144250.aspx, SQL collations are for compatibility with older settings and are not directly listed (as you want them) in the installation wizard.... you can "only" choose a Windows collation, that lists all the variations of each particular collation, or SQL collation, that you have to "customize" to fit your needs.. so, for SQL_LATIN1_GENERAL_CP1_CI_AS you have to choose Dictionary order, case-insensitive, 1252 character set (or alternate locale to fit your needs)..

regards

|||Kool....thanks a million that did the trick ;-)