Clear Connections command

Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.
Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
sqlsql

Clear Connections command

Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.

Checking existance of record in Stored Procedure

Hi,

I am new to Stored Procedures. There is a simple procedure I want to create.

The scenario is this :

There is a Table named Test. In that table is a Column named 'TestID'. Now I want to create a Stored Procedure which inserts a record in Test, but if 'TestID' passed to it already exists, it should throw an error.

I am using C# and SQL Server 2005 Express.
Some code in C# also would be helpful.

You would need to create EITHER a unique index on the column, or a TRIGGER.

With the information at hand, I would vote for a UNIQUE index, and set the column to NOT NULL (so that a value MUST be supplied).

|||Thanks for the reply.

But I want to manage it programaticaly. I know about the constraints and SQL. But I am learning T-SQL and have created a scenario by myself to work on.

Basically I want to know how to throw error from stored proc? Or any way to let the app. know of some exceptional situations, like in .Net?|||

Inside your sp put the following code..

Code Snippet

Create Procedure YouSpName(@.TestId int, other params....)

as

Begin

If Exists(Select 1 From Test Where TestId=@.TestId)

Begin

Raiserror ('Recorde Alreay Found', 16, 1)

Return

End

--Your actual code will be appended here

..

..

..

End

|||Thanks, It worked fine.|||

One of the 'hallmarks' of a competent and experienced SQL Server developer/dba, is to understand the impact of various options.

A TRIGGER executing consumes more server resources than a CONSTRAINT. A TRIGGER will, most likely, issue locks on data that could, under high usage scenarios, cause blocking situations for other users.

A CONSTRAINT has NONE of the negative impact of a TRIGGER, in fact, it is the least impactful way to force data conformance. When a CONSTRAINT is indicated, it is the 'best' option to engage. And a CONSTRAINT failure will definitely 'throw' an error that the application can catch and handle.

|||Thanks Arnie,

I can see what you are saying. I should have used the Unique Constraint. But as I said, this not the real thing. It's not part of a project or even a program. I wanted to know how to tell the application that an unexpected situation occurred in the stored procedure. Same as throwing exceptions in C#, VB or any .Net language. So, I created this hopeless scenario.

But I appreciate your concern and this information you typed might be useful to a lot of people who doesn't know this.

Thanks.

check values record by record

Consider this scenario.

I have two database in the sql server and consider that i have a query which has 4 tables inner joined.

When i execute the query in the database1 , the query is returning rows, But when i execute the same query in the database2, the query is not retuning rows . I know that the

no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge

list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be

appericated.

I have used the third party tool called sql server comparison tool which gives me the desired result.

http://www.sql-server-tool.com/?src=dtc#nd0

|||

SELECT *

FROM database1.dbo.MyTable

EXCEPT

SELECT *

FROM database2.dbo.MyTable

|||

This EXCEPT command is giving error in sql 2000 . will it work in sql 2005

Check the Permission of the table

Hi all
Is there a way to check the permissions given to all tables
in the database.
My Scenario is
There are 100+ tables in the database and less than 10
tables have been given Select permission access. I want to
find out which 10 tables have been given the SELECT
permission.
Any system stored procedure or script to find this.
Thanks in advance.
Anand.See
INFORMATION_SCHEMA.TABLE_PRIVILEGES
>--Original Message--
>Hi all
>Is there a way to check the permissions given to all
tables
>in the database.
>My Scenario is
>There are 100+ tables in the database and less than 10
>tables have been given Select permission access. I want
to
>find out which 10 tables have been given the SELECT
>permission.
>Any system stored procedure or script to find this.
>Thanks in advance.
>Anand.
>.
>