How to retrieve data from a SQL Server db

Question

I'm writing a code snippet in VBA to pull data from a SQL Server 2008r2 database. However, I'm getting a error at Conn.Open: [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53].

Using ConnectionString.com and another SO Question, I've written this:

Function GetAddress() As String
    Dim Conn As ADODB.Connection
    Set Conn = New ADODB.Connection
    Dim Rst As ADODB.Recordset
    Set Rst = New ADODB.Recordset

    Conn.ConnectionString = "Provider=SQLNCLI10;Server=12.34.5678;" & _
                            "Database=OurDB;User ID=myuserid;Password=mypw;"
    Conn.Open

    Set Rst.ActiveConnection = Conn
    Rst.Open "Select top 1000 * from MainTable"
End Function

Googling the error suggested that the Named Pipes might be Disabled. Following that lead, I checked the SQL Server Configuration Manager and Named Pipes are quite Enabled (3rd order behind Shared Memory and TCP/IP).

How do I make this error go away? Am I using the right connection string?

Stupid question: Error 53 is `Cannnot find network path`; is your Server path correct? — LittleBobbyTables - Au Revoir, Jul 31 '12 at 16:35
There is a check list for this error here: http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/ — Fionnuala, Jul 31 '12 at 16:40
@JimmyPena, that value is the name of the database inside the SQL Server. — PowerUser, Jul 31 '12 at 18:43
@LittleBobbyTables, Yes, I pulled the IP aka Server path directly from SQL Server. — PowerUser, Jul 31 '12 at 18:44
Found the problem! What I thought was the server "IP" is actually the version of SQL (10.50.2500). Changing it to the name worked. @Hansup, if you post your comment as an answer, green check for you. — PowerUser, Jul 31 '12 at 19:27

score 1 · Accepted Answer · answered Jul 31 '12 at 19:34

1

Double-check this part of your connection string:

Server=12.34.5678

It looks like referring to the server by an IP address, but it's not an IP address.

answered Jul 31 '12 at 19:34

HansUp

95,961
11
77
135

How to retrieve data from a SQL Server db

1 Answers1