Disconnect server instance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Disconnect server instance

Author

Topic

kapilk1111
Starting Member

3 Posts

Posted - 2010-08-09 : 18:15:20

Hi SQL Server admins,

I'm a developer, not an admin. I want to write a VB script or ASP app that will disconnect the production server instance running on the computer (where the script will run). The script basically will be similar to right-clicking on server instance name and clicking 'Disconnect' in Sql Server Management Studio.
Basically I want to automate this task so that the production server instance is not left open indefinitely by developers in our team.

Is there any way to do this?

Thanks for your suggestions. Kapil

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-09 : 21:51:45

If you're using the connection object


Dim Conn
Set Conn = CreateObject("ADODB.Connection")

Conn.open YOUR_CONNECTION_STRING_HERE

Conn.Close
Set Conn = Nothing

If you're using the Command Object:


Dim cmd
Set cmd = CreateObject("ADODB.Command")

Set cmd = Nothing

kapilk1111
Starting Member

3 Posts

Posted - 2010-08-10 : 00:24:48

Connection is already open through Sql Management studio. I want to close the connection automatically by running a script on all developer machines at night. The script above will not serve the purpose since the connection is being open within the script and not through Sql Management studio.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-10 : 14:43:37

use SQL. Run this as a scheduled job every night


declare @spid int
Declare @kill varchar (12)

create table #t (
	spid int,
	status varchar(64),
	[login] varchar(128),
	hostname varchar(128),
	blkBy varchar(32),
	dbname varchar(128),
	command varchar(128),
	cputime int,
	diskio int,
	lastbatch varchar(24),
	programname varchar(128),
	spid2 int,
	requestId int
)

insert #t
exec sp_who2


Declare c Cursor
Read_Only
For
	select	spid/*, login,
		convert(smalldatetime, convert(char(4), year(getdate())) +
		left(lastbatch, 2) +
		substring(lastbatch, 4, 2) + ' ' + 
		right(lastbatch, 8)
		)
		dt*/
	from	#t
	where	spid > 50
	and	rtrim(status) <> 'BACKGROUND'
	And	datediff(hour, 
			convert(smalldatetime, convert(char(4), year(getdate())) +
			left(lastbatch, 2) +
			substring(lastbatch, 4, 2) + ' ' + 
			right(lastbatch, 8)
			),
			getdate()
		) > 3
open c
fetch next from c into @spid
while @@fetch_status = 0
begin
	set @kill = 'KILL ' + convert(varchar(32), @spid)
	Exec(@kill)
	fetch next from c into @spid
end

close c
deallocate c

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-10 : 14:45:16

might want to change the part in red. it will kill open connections that have been idle for 3 hours or longer the way i posted it

if you run transactional replication, make sure you don't kill those spids

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources