Unanswered: Can't connect SQL2K named instance using OLE DB
I am having a hard time trying to connect to a remote SQL Server 2000 named
instance using an OLE DB connection, the computer I am trying to connect
from has SQL Server 7 installed, but MDAC 2.6 SP1 (which should be
sufficient?). Errormsg below:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
/asp/test.asp, line 5
The remote server is listening on TCP/IP, port 1434
I am using a sql-login. The remote server is in mixed mode.
I've tried connect using Query Analyzer in order to troubleshoot the
problem, and came to the following conclusion:
- If i add an alias for the server in Client Network Configuration,
and configure the connection to use TCP/IP, 1434, then I'm able to
connect to the remote server using Query Analyzer. However,
my asp-page still won't work.
- If i don't add an alias, but run Client Network Utility and add
TCP/IP (port 1434) as first protocol, and Named Pipes second
protocol... then I am not able to connect using Query Analyzer or
the asp-page. How come?
The code in test.asp looks like this:
Set objC = Server.CreateObject("ADODB.Connection")
objC.Open "Provider='SQLOLEDB.1';" & _
"Persist Security Info=True;" & _
"User ID='myusername';" & _
"Password='mypassword';" & _
"Initial Catalog=mydatabase;" & _
"Server=REMOTESERVER\INSTANCE;" & _
"Data source=REMOTE SERVER\INSTANCE"
objC.Execute "SELECT * FROM [dbo].[EverybodyListens_Employee] WHERE CGid =
Some rows of the code above are wrapped.
REMOTESERVER is the remote server,
and INSTANCE is the instance name.