Trying to Set a SQL Server Database to Offline through VB.NET

Friends,

I'm having a bit of trouble trying to set a database to offline through VB.net

If I simply type the lines:

 ALTER DATABASE  [IRLRV]
SET OFFLINE

through Query Analyser, it works great.  If however, I write it in my code such as the case in the attahed code snippet, i get a timeout error?  What gives?

It should be noted that if I try to run the same line from Qry Analyser while my program is running, it will sit there and think about it until I have closed my program.  After the program exits, it will then run in qry analyser.

 What syntax change to I need to make?

Thanks in advance!

Regards,
Eric


dsnLocal = "Data Source=""" & LocalWorkstation & """;Initial Catalog=""master"";User Id=""user"";password=""password"""
   
Private Sub SetDatabaseToOffline(ByVal NewDBName As String)
        Dim con As New SqlClient.SqlConnection(dsnLocal)
        Try
            Dim data As New DataSet
            data = New DataSet
            con = New SqlClient.SqlConnection(dsnLocal)
            con.Open()
 
            Dim c As New SqlClient.SqlCommand("ALTER DATABASE [" & NewDBName & "]" & vbCrLf _
            & "SET OFFLINE", con)
            c.ExecuteNonQuery()
 
            con.Close()
            con = Nothing
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
 
        Finally
            If Not con Is Nothing AndAlso con.State <> ConnectionState.Closed Then
                con.Close()
                con = Nothing
            End If
        End Try
    End Sub

Open in new window

indy500fanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
Try to execute it with some additional option:

ALTER DATABASE  [IRLRV]
SET OFFLINE WITH ROLLBACK IMMEDIATE
0
Chris MangusDatabase AdministratorCommented:
You're probably trying to set it offline while your app is connected to it.  Try connecting to another database first in your app, like maybe tempdb, then set IRLRV offline.
0
indy500fanAuthor Commented:
cmangus, If you look at my connection string, I am connected to master.  That is what is so confusing.

brejk, what does ROLLBACK IMMEDIATE do?
0
Chris MangusDatabase AdministratorCommented:
How about users...are you sure all your users are off of the system?

The behavior you're describing sounds like someone is still connected to the database.  You can't take it offline when you have user connections.  You may have to enumerate all the user SPIDs in SQL Server and kill them one by one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
indy500fanAuthor Commented:
cmangus,

Yep, I did infact have one user connected.  I changed all my connection strings in the routine to use the masterdb, and it worked!

Thanks,
Eric
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.