Failed to connect to server

Question

Using VB.Net 2005, Standard edition and the SQL-Server Express-edition.

 

Question: I'd like to write a backup-system in my small app. Hereby I need to detach the database, before copying the files.

 

References added:

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SmoEnum

Microsoft.SqlServer.SqlEnum

 

Source code:

Dim srv As New Server

srv.DetachDatabase("DBname", True)

 

Problem: Inner exception error: Failed to connect to server.

 

I cannot see why connecting to the server is a problem. Using tables-adapters, etc. are no problem at all. I'm stuck; could anyone point me in the right direction ?

 

Thanks in advance; greetings from Holland,

 

 

Alfred Zwiep

 

 

Answer 1

Hello,

 

Sorry to answer so late but VB is not my prefered language and i had to take my time to produce something

 

You said that SqlConnection is correct ( as you can use tables-adapters

It's possible to use Server with a SqlConnection

i give you the code

Imports Microsoft.SqlServer.Management.Common

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Smo.Wmi

 

Imports System.Data.SqlClient

Imports System.Windows.Forms

Module Module1

Sub Main()

Dim bu As New SqlConnectionStringBuilder()

Dim str As String

Dim basename As String

bu.DataSource = "CHAMBRE\SQLEXPRESS" ' my Sql Server Instance

bu.IntegratedSecurity = True

Dim sqlconn As New SqlConnection(bu.ToString())

sqlconn.Open()

Console.WriteLine("Connection state {0}", sqlconn.State)

Dim srvconn As New ServerConnection(sqlconn)

Dim srv As New Server(srvconn)

Console.WriteLine(srv.ConnectionContext.DatabaseName)

basename = "AAA"

Try

srv.DetachDatabase(basename, True)

Console.WriteLine("{0} DetachDatabase OK", basename)

Catch ex As Exception

str = "Error in DetachDatabase for  " & basename & ex.Message & Environment.NewLine

str = str + ex.InnerException.Message

MessageBox.Show(str)

Console.WriteLine(str)

sqlconn.Close()

Return

End Try

MessageBox.Show("End of program")

sqlconn.Close()

End Sub

End Module

 

I've done console application ( it's the quickest way in VC# )

That is working for me

Maybe you don't know but when in a SqlConnection you don't give the database ( Initial Catalog ) that's the database master which is used

 

It's a little complicated but i've several applications with a mix of SMO and System.Data.SqlClient and i've adopted this way as i'm sure not to have any problem of connection if i'm using a well-tested ConnectionString

 

Don't be surprised by the use of SqlConnectionStringBuilder, it's a longer way than with connectionstring = "..." but it's safer

 

When you use Dim srv As new Server() , you are trying to connect to the master database and you may not have the permission to connect.. with your Windows Authrntification

I think that you need to be sysadmin to detach a database

 

I like the way SqlConnection ==> ServerConnection ==> Server as i separate the connection problems from the DetachDatabase problems

 

I hope that will help you

 

If you need some help , i will come back in 8 hours ( 11:12 PM for me )

 

Have a nice day 

Answer 2

Thank you for the info you gave me. With the code you gave me, you gave me the insight I needed to solve the problem.

 

Greetings,

 

 

Alfred

 

Answer 3

Hello,
I am also facing the same problem. when i try to connect the sql server that are in LAN (VISTA and XP OS) than it throws the same error. mainly this error is occurred in VISTA OS case.
Please suggest how i solve this issue.
Thanks

---

~Khatri Mitesh
[e-mail]
Software Developer
Rajasthan-India
http://www.youtube.com/watch?v=-oJaaPCrk3Q