none
Trying to discover correct syntax for Connection String RRS feed

  • Question

  • There's a 10 year old VB6 app that I'm trying to get working on my local network.  The problem is that it won't connect to my local copy of SQL Server even though it has no problem doing so at a client's site and on the home network of a colleague.  To see if there was some sort of a strange problem connecting from my computer, I installed a copy of SQL Server Management Studio Express.  Into this utility app I entered the following data:

     Server name:    Server\SQLExpress
     Authentication:   SQL Server Authentication
     Login:               Abc_Admin
     Password:         abc123

    Please note that I've altered the Login & Password in this posting for security reasons but I assure they're correct in practice.

    Then when I press Connect ... it connects properly to SQL Server as it should!  So this told me that there was no problem, in principle, with connecting to SQL Server from my test computer.

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    So then took the key VB6 code in question and created a little procedure to try it out.  Here's the code:

    Private Sub TestConnection()

      Dim conTest As New ADODB.Connection
      Dim msg As String

      On Error GoTo ConnectError

     ' --------------- Connection using SQLOLEDB ----------------
      With conTest
        .Provider = "SQLOLEDB"
        .ConnectionString = "User ID=" & "Abc_Admin" & _
          ";Password=" & "abc123" & _
          ";Data Source=" & "Server\SQLExpress" & _
          ";Initial Catalog=" & "AbcAppDatabase"
        .Open
      End With

      MsgBox "Connection Succeeded!", vbOKOnly + vbInformation, "Connection Tester"
      conTest.Close

      Exit Sub

    ConnectError:

      msg = "Status: ERROR" & vbCrLf & vbCrLf
      msg = msg & "Error code: " & Err.Number & vbCrLf
      msg = msg & "Error description: " & Err.Description

      MsgBox msg, vbOKOnly + vbCritical, "Connection Tester"

    End Sub


    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    The code always failed to connect, offering an obscure error code: -2147467259   with this description: [DBMSSOCN] General network error

    I have no idea what this means nor how it helps resolve the problem.

    Any ideas?

    Robert W.
    Sunday, November 15, 2009 11:30 PM

All replies

  • hi,
    I would suggest you to refer the site below for trouble shooting the connection string issue:
    http://www.connectionstrings.com/
    I'm quite confident that you will get the break through for your problem
    Manish Patil http://patilmanishrao.wordpress.com Posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, November 16, 2009 5:56 AM
  • Hi Robert,

    Do you mean you are working with VB6?
    If it is that case, you can get help from this sticky,
    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/6a0719fe-14af-47f7-9f51-a8ea2b9c8d6b

    Because microsoft has already ended free support for Visual Basic 6.
    http://news.cnet.com/Microsoft-walks-VB-tight-rope/2100-1007_3-5620821.html


    Best Regards
    Yichun Feng
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Edited by Yichun_Feng Thursday, November 19, 2009 9:23 AM
    Wednesday, November 18, 2009 6:13 AM
  • Hi Yichun,

    Let me explain the full story . . .

    I'm in the process of building an ASP.Net 3.5 / C# intranet-hosted web application.  It is the replacement & successor for a VB6 app that was written between 1998-2002.  So for all intents & purposes the legacy app (the VB6 one) serves as the "blueprint" for the new one.

    With the legacy app I have both the compiled version, which installs via a Setup file and all the source code.

    I am working with a colleague of mine.  He has successfully installed the legacy app on his home network and got it to connect to SQL Server Express 2005 (aka Version 9.00).  In doing so he discovered that things are not so straightforward when connecting VB 6 (with ADO 2.7) to SQL Server Express.  In his case he had to define SQL Server Express via an IP address and a port number.

    I posted my inquiry in this forum because I'm convinced that my problem is an ADO connection issue.  I was hoping to find someone who had faced a similar challenge.

    Robert
    Wednesday, November 18, 2009 6:17 PM
  • You may want to check the below link. The questions here are primarily related to ADO.NET and not Classic ADO.

    http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, November 18, 2009 8:34 PM
  • Thanks, Paul.  That URL was one of about a dozen that I carefully read through.  My SQL Server Express is correctly configured but I still cannot connect with VB6.
    Wednesday, November 18, 2009 11:16 PM
  • Looking at your connection string parameters again, I don't think you're using the correct OLEDB provider. Below is a SQL Server 2008 example (not sure which version of Express you are using):

    Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, November 19, 2009 1:19 PM
  • Hi Paul,

    I tried both "SQLNCLI10" and "SQLNCLI10.1".  I could not get either to work with VB6.  Perhaps I've not configured things correctly.  Could you share with me a VB6 project that you know works properly to connect to SQL Server 2008 or 2005 Express?

    Robert

    Thursday, November 19, 2009 6:58 PM
  • I've spoken with my colleague again and he's convinced that if I specify the IP address of the server that SQL Server is on, along with the SQL Server Port Number then I can get this to work.

    So I went looking for that port number.  I found this: http://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-instance

    Following the instructions, here's my TCP/IP settings: http://pelalusa.com/Downloads/Public/TCP-IP_Properties.jpg

    Which one is the port number?

    Note that when I tried step #4, the T-SQL code returned 'null'.

    Might it be that my SQL Server currently does not have a port number?

    Robert
    Friday, November 20, 2009 4:50 AM
  • What version of Windows (w/SP) are you running? I'm beginning to think that there may be a problem with the client driver installation.

    The following might help with respect to port configuration. Since you're connecting using a named instance the port would be dynamic.


    You may also want to try the following forums:

    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/threads
    http://www.sqlteam.com/forums/forum.asp?FORUM_ID=20



    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, November 20, 2009 1:45 PM
  • Windows XP / SP3

    Thanks for the links, Paul, I will check them out.

    Robert
    Friday, November 20, 2009 11:26 PM
  • One other thought, if possible try connecting from the app on another machine. If it works then it's likely an issue with the client configuration on your machine. I was going to suggest installing the latest version of MDAC but it may already be installed under SP3.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Saturday, November 21, 2009 4:07 AM
  • Since our last correspondence I have tried:
    • Connecting from a laptop on my network
    • Connecting from the app installed directly on the SQL Server box
    Both are running WinXP/SP3.  Both fail.

    I'm at wits end and feel I have no choice but to hire a SQL Server / ADO guru here in Vancouver where I live and get him to resolve this.  Ughhh.

    Thank you for your continued support,

    Robert
    Saturday, November 21, 2009 4:38 AM
  • Just to make sure, you did configure SQL Server Express security for SQL Server and Windows Authentication mode correct? You can verify this setting in SQL Server Management Studio (Right click on the server, Properties, Security).
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, November 23, 2009 3:44 PM
  • No, it has been set to SQL Server Authentication and has been that way for 10 years.  We can't now switch to Windows Authentication.
    Monday, November 23, 2009 6:36 PM
  • OK, now I'm confused. SQL Server Express hasn't been around for that long. Are you actually using SQL Server Express or SQL Server?

    In any event, the options for Express are Windows Authentication, or Windows and SQL Server Authentication (mixed mode).
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, November 23, 2009 8:36 PM
  • The legacy app was built with VB6 and originally used SQL Server 2000.   My client has since upgraded their SQL Server all the way up to 2008.  THEY use SQL Server, not SQL Express.

    But my colleague & I only have SQL Server Express installed on our home office networks.  On his system he has both the legacy app (VB6) and the new app (ASP.Net 3.5) working fine with SQL Server Express.

    On my system I have the new app working fine but cannot get the legacy app to connect to SQL Server Express.  This is what my posting has been about all along.
    Monday, November 23, 2009 10:59 PM