connecting to SQL server using different port

Hi,
I had some code that connected to a SQL server. The server was brought down and our database was moved to a new server. The problem is that the server does not use the default SQL port. I can connect through enterprise manager, but I had to pop a reg key to point to the correct port. How can I specify the port to use in me code, which I've pasted below? Any help is appreciated. My boss is really on me to get this fixed.
Thanks,
CMG

Global SQLCON As New ADODB.Connection

Public Function SQLConnect() As Boolean
SQLCON.ConnectionTimeout = 25
SQLCON.Provider = "sqloledb"
SQLCON.Properties("Network Address").Value = "3.10.202.15"
SQLCON.CommandTimeout = 180 ' set timeout for 3 minutes
SQLCON.Properties("Network Library").Value = "dbmssocn"

SQLCON.Properties("Data Source").Value = "SERVER"
SQLCON.Properties("Initial Catalog").Value = "BW_CONFIG"
SQLCON.CursorLocation = adUseServer ' For ADO cursor location

SQLCON.Properties("User ID").Value = "BW_DBO"
SQLCON.Properties("Password").Value = "w0rkstations"
SQLCON.Open

Make sure you are using the appropriate network library for this:



'win32 winsock tcp/ip
SQLCON.Properties("Network Library").Value = "dbmssocn"
'win32 named pipes
SQLCON.Properties("Network Library").Value = "dbnmpntw"
'win32 spx/ipx
SQLCON.Properties("Network Library").Value = "dbmsspxn"
'win32 multi-protocol
SQLCON.Properties("Network Library").Value = "dbmsrpcn"



And make sure none of the permissions or user groups have changed since the database migration.

Thanks. I believe I am using the correct network library and user. I can connect fine using TCP/IP and the same user, but not from vb. I thought maybe there was a way to specify port in VB?

Make sure you are using the appropriate network library for this:



'win32 winsock tcp/ip
SQLCON.Properties("Network Library").Value = "dbmssocn"
'win32 named pipes
SQLCON.Properties("Network Library").Value = "dbnmpntw"
'win32 spx/ipx
SQLCON.Properties("Network Library").Value = "dbmsspxn"
'win32 multi-protocol
SQLCON.Properties("Network Library").Value = "dbmsrpcn"



And make sure none of the permissions or user groups have changed since the database migration.

Yes trimbandit, you can:

SQLCON.Properties("Data Source").Value = "SERVER,portnumber"

Additionally, I don't know how many instances of SQL Server are running on the server, but I do not hardcode the IP address of the server. Instead I define the host in the Host lookup table/file and use that alias when setting the connection's Data Source property;

in your case you would put an entry into the hosts file like:

3.10.202.15 SERVER

The advantage is that they can move the Database from server to server and you don't need to change your code (unless they change the port ;-), you just edit the Hosts table/file.

HTH

Thanks HTH, it is working fine now. I had tried using:
SQLCON.Properties("Data Source").Value = "SERVER:PORT"
I should have guessed it would be a comma instead of colon.

I am going to go ahead and edit the host file as per your suggestion.

Thanks again!


Yes trimbandit, you can:

SQLCON.Properties("Data Source").Value = "SERVER,portnumber"

Additionally, I don't know how many instances of SQL Server are running on the server, but I do not hardcode the IP address of the server. Instead I define the host in the Host lookup table/file and use that alias when setting the connection's Data Source property;

in your case you would put an entry into the hosts file like:

3.10.202.15 SERVER

The advantage is that they can move the Database from server to server and you don't need to change your code (unless they change the port ;-), you just edit the Hosts table/file.

HTH