MSSQL connection ramains active!!!

Hi there!

I am having a weird issue with SQL-Server 2000.
I just finishes to write a little administration tool, where one of the
options allow the administrator to create an entire database from a
script. Well, because this database can already exists in MSSQL, I must
drop it before to recreate it.
Take a look what I do:


'Open SQL-Server connection with no database reference
'I must do not pass any database name or else I cannot drop the desired database
'OpenSQLServerLocal is a sub that passes the connection string and where the first parameter is the database name

OpenSQLServerLocal "", "sa", ""

'Open the text file with the script
Open App.Path & "\" & "script.txt" For Input As #1

'Drop database and recreate it
Set rsc = Conn.Execute("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & TheDBName & "')DROP DATABASE [" & TheDBName & "]")
Set rsc = Conn.Execute("CREATE DATABASE [" & TheDBName & "] ON (NAME = N'" & TheDBName & "Data', FILENAME = N'" & gpMSSQLPath & TheDBName & "_Data.MDF' , SIZE = 1, FILEGROWTH = 1) LOG ON (NAME = N'" & TheDBName & "Log', FILENAME = N'" & gpMSSQLPath & TheDBName & "_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)")

'Close SQL-Server connection
Conn.Close
Set Conn = Nothing

'Open SQL-Server connecting to the created database
OpenSQLServerLocal TheDBName, "sa", ""

'Create database tables from the script text file
Do Until EOF(1)
Line Input #1, StrLineIn
Set rsc = Conn.Execute(StrLineIn)
Loop
Close (1)

'Close database
Conn.Close
Set Conn = Nothing


Well, no big deal, uh? The problem is that, when I click the button to create the database, everything runs fine.
But, if I click it again, the system hangs for a while, and then returns with an erros saying that
"cannot drop database because it is in use". I think it's very weird once I am droping the connection after recreate
the database and the tables. I tried to use just Conn.Close, to use Set Conn = Nothing and the both. Nothing seems to work...

Any clues?

Thanks!

Hi,

Not sure though, could you try to add
Set rsc=Nothing

Secondly, could it be possible to have a look at OpenSQLServerLocal's code?


Regards,
Shurik.

Hi Schurik,

The code is:


Public Sub OpenSQLServerLocal(mydatabase As String, myusername As String, mypassword As String)
Dim strSQL As String
Set Conn = New ADODB.Connection
If Trim(myusername) <> "" Then
strSQL = "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=" & mydatabase & ";" & _
"Uid=" & myusername & ";" & _
"Pwd=" & mypassword
Conn.ConnectionString = strSQL
Conn.CursorLocation = adUseServer
Conn.Open
Else
Answer = CoolBox("Invalid null username: cannot login.", vbOKOnly)
End
End If
End Sub

I'm not so sure. :confused: But as I ever drop database / bring database offline using Enterprise manager.
I got dialog ask me about "Database in uses . would you like to terminate all user ?" something like this.
So I think you have to check if there is any active user and kill them
(using sp_who together with KILL.. but I never try :( )

Hi folks,

I found the problem!

When I started the application I created a connection, and did some
queries to recordsets. So, although I closed the connections, I left the
recordsets opened (to use its contents later), and seems that the
connections remained active because that. Closing the recordsets after
close connections, solved the issue.

Thanks anyway to you, guys!

PS: Can you believe that I dreamed with that solution??????