Multiple Server Instances: Cannot connect

I have 2 server instances, one called "SQLMACHINE" and the other "SQLMACHINE\MYINSTANCE".  When I attempt to connect with the OLEDB driver (connection string), it WILL connect to SQLMACHINE\MYINSTANCE, but it WILL NOT connect to SQLMACHINE.  It is unable to find the server.

The workaround so far has been to STOP the SQL Service on the SQLMACHINE\MYINSTANCE, which apparently forces OLEDB to the other server by some kind of default.  How can I CHOOSE which server I want to connect to?  Ofcourse, I have tried all the basic stuff, i.e., "local", "SQLMACHINE", "localhost", "127.0.0.1", you name it.  Can't find that server.

Help!
LVL 2
born4codeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AustinSevenConnect With a Mentor Commented:
I think you need to append the allocated port number in the connection string.   If you do server properties in EM for "SQLMACHINE" and "SQLMACHINE\MYINSTANCE", click 'Network Configuration' and then 'TCP/IP in the 'Engabled Protocols' window... Then click 'Properties'.   This will give  you the allocated port number for each.   It is probably 1433 for SQLMACHINE and it should be something else for the instance.  Then, in your connection string, append ',port number' (eg. ',1488').   In such situations, I've always used the IP address, eg. 192.1.1.100,1488 .   Not sure if you can use the SQL Server names or Instance names.

I grabbed this from a similar post...

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21060898.html

 oConn.Open "Provider=sqloledb;" & _
           "Network Library=DBMSSOCN;" & _
           "Data Source=xxx.xxx.xxx.xxx,1433;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User ID=myUsername;" & _
           "Password=myPassword"


AustinSeven
0
 
born4codeAuthor Commented:
Thanks.
I'll give that a try...  great advice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.