locked
Connecting to non-local SQL server? RRS feed

  • Question

  • Hi,

    Is it possible to connect my VB Express 2010 to a SQL server running on a server in my network?  When using the Data Source Configuration Wizard I can't seem to find a way to access anything other than a local SQL Express installation.  Is this a limitation of VB Express?

    Thanks in advance,

    Linn

    Thursday, April 5, 2012 8:52 PM

Answers

  • D'oh!

    First of all, using system.data.odbc worked fine right off from a user DSN I created.  Used almost the exact same code as the function listed above.  Mainly just replaced SqlConnection references with ODBCConnection.

    Then I was looking over my SQL code and realized I was using an underscore character in the server name when it should have been a hyphen.  *bangs head on desk*

    Fixed that and it works fine!

    Thanks for the help everyone!

    Linn

    • Marked as answer by lkubler Wednesday, April 11, 2012 7:59 PM
    Wednesday, April 11, 2012 5:57 PM

All replies

  • Yep.

    I found a comprehensive feature comparison only for the 2008 version: 

    http://www.microsoft.com/download/en/details.aspx?id=7940


    Armin

    Thursday, April 5, 2012 9:43 PM
  • Hi Armin,

    Thanks for the response.  I'm not sure I understand.  Looking at the feature comparison you linked to, on page 15 it says the express edition has the database explorer, which "enables you to open data connections and log on to servers and explore their databases."  I can't seem to find that in 2010, wonder if they removed it?

    I also can't find a similar product comparison for 2010.  The only comparison I've found so far doesn't include the Express Edition.

    Thanks,

    Linn

    Friday, April 6, 2012 12:43 PM
  • You'll find Database Explorer in the View menu, after you turn on Expert Settings in Tools->Settings.

    However, when I look in Database Explorer here (in VBX 2010), I see only an option to attach a SQL Server database file to a local SQL Server instance. I do not see an option to connect to a remote SQL Server instance as I do in Visual Studio 2010 Professional/Premium/Ultimate.

    Friday, April 6, 2012 1:27 PM
  • Oh, I hadn't noticed the Expert Settings option before.  But I see what you mean, still can't seem to get to a remote server. 

    Next question is, which version do I need then?  Is the Professional version enough to give me all the functionallity I need or do I need to go to the Premium?

    Thanks,

    Linn

    Friday, April 6, 2012 2:15 PM
  • AFAIK, all "full" versions of Visual Studio 2010 offer the option to connect to a remote SQL Server instance when creating a new data source.

    To be absolutely sure, however, you can download a trial copy of VS 2010 Professional at no cost to be sure it meets all your needs before purchasing a license:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=5414E4C0-C1F8-473E-8E9D-A1A7BE786141&displaylang=zh-t&displaylang=en

    Friday, April 6, 2012 3:43 PM
  • There is nothing stopping a VB application built using the Express IDE from connecting to any other (remote) server.

    However, If you want to connect to a server from within the IDE, and manipulate/create data objects/data sources automatically - as opposed to writing the code yourself - then you'll need VB Pro or later.


    Stephen J Whiteley

    Monday, April 9, 2012 7:52 PM
  • That's good news.  I'll see if I can find a good tutorial on just how to go about creating a connection to SQL server in code then.

    Thanks for the tip,

    Linn

    Tuesday, April 10, 2012 1:58 PM
  • At its simplest (something like):

            Dim myConnectionString As String 'Build the connection string
            Dim mySQLQuery As String 'Build the query to perform
            Dim sql As New SqlClient.SqlConnection(myConnectionString)
            Dim cmd As New SqlClient.SqlCommand
            cmd.CommandText = mySQLQuery
            sql.Open()
            Dim result As Object = cmd.ExecuteScalar
            'Dim result As Integer = cmd.ExecuteNonQuery
            'Dim result As SqlClient.SqlDataReader = cmd.ExecuteReader
            sql.Close()
    


    Stephen J Whiteley

    Tuesday, April 10, 2012 2:58 PM
  • I could find all sorts of discussions and tutorials on this subject, most using something very similar to what you described, Stephen.  However I can't get it to work and I think the issue has to do with the connection string which I didn't find very good help on.

    Here is what I have:

    Imports System.Data.SqlClient

    Module Module1

        Sub Main()

            Dim data As List(Of String)

            data = GetLockedUser()

            For Each user As String In data

                Console.WriteLine(user)

            Next

            Console.WriteLine("Press ENTER to exit")

            Console.ReadLine()

        End Sub

        Public Function GetLockedUser() As List(Of String)

            Dim conn As SqlConnection

            Dim cmd As SqlCommand

            Dim result As New List(Of String)

            Dim cmdString As String = "SELECT name FROM app_user WHERE discontinued = 'F' AND pswd_lockout_ind = 'T'"

            conn = New SqlConnection("Server=sqlserver;uid=username;pwd=password;database=myDB")

            cmd = New SqlCommand(cmdString, conn)

            conn.Open()

            Dim myReader As SqlDataReader

            myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            While myReader.Read()

                result.Add(myReader("name").ToString())

            End While

            conn.Close()

            Return result

        End Function

    End Module

    What I get is a SqlException was unhandled pointing to the conn.open line with a description of:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify that the instance name is correct and that the SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    I have been using ODBC connectivity for years so I'm pretty sure the SQL server is accessible so it must be something in my connection string?

    Any thoughts?

    Thanks,

    Linn

    Tuesday, April 10, 2012 8:43 PM
  • It indicates that the connection wants to use named pipes: your server may not have this enabled (I'm not sure what the default is for SQL Server).

    Make sure you can connect using Microsoft SQL Server Management Studio. Additionally, you can use Sql Server Configuration Manager to determine what protocols are enabled.

    This site seems to have a few options for connection strings: http://www.sqlstrings.com/ There are others, of course. One thing to bear in mind is the server instance name: if you are using SQL Express then you may need to append \sqlexpress to the server name in the connection string.


    Stephen J Whiteley

    Wednesday, April 11, 2012 12:14 PM
  • Hi Stephen,

    I checked and named pipes are enabled on my SQL Server and I can connect from my workstation using the MS SQL Server Management Studio as well as with Excel 2010 and Visual FoxPro using ODBC.

    I looked at the strings recommended by the link you provided and I noticed two differences in their strings versus mine.  They use "data source" over "server" and "initial catalog" versus "database".  I changed my code to match their strings and get the same response.  I even threw "Trusted_connection" = False in for good measure.  Very puzzling.

    I'm trying to attach to a full version of SQL Server 2005 Standard running on a Windows 2003 Standard server.

    I might try and connect using SQL ODBC Connection and see if that works better.

    Thanks,

    Linn

    Wednesday, April 11, 2012 3:33 PM
  • D'oh!

    First of all, using system.data.odbc worked fine right off from a user DSN I created.  Used almost the exact same code as the function listed above.  Mainly just replaced SqlConnection references with ODBCConnection.

    Then I was looking over my SQL code and realized I was using an underscore character in the server name when it should have been a hyphen.  *bangs head on desk*

    Fixed that and it works fine!

    Thanks for the help everyone!

    Linn

    • Marked as answer by lkubler Wednesday, April 11, 2012 7:59 PM
    Wednesday, April 11, 2012 5:57 PM