How to define custom port for MSSQL?

questions
,
1

I have a code that looks something like this:

VersionDB            FILE,DRIVER('MSSQL','/BUSYHANDLING=2 /VERIFYVIASELECT=TRUE'),OWNER(glo:Connect),NAME('dbo.VersionDB'),PRE(VDB),BINDABLE,THREAD !                     
VDB                      KEY(VDB:VDBnro),NAME('VDB_VDB'),PRIMARY !                     
Ver                      KEY(VDB:Version),NAME('VDB_Ver')  !                     
Record                   RECORD,PRE()
VDBnro                      LONG,NAME('"VDB_VDBnro"')      !                     
Version                     CSTRING(11),NAME('"VDB_Version"') !                     
                         END
                     END

and then glo:Connect = '127.0.0.1,test,sa,sa'

Now I don’t seem to find a way to connect to db which has non default port like 14330 instead of 1433.

2

I don’t know if this helps, but in my connect string for Oracle I have something like 127.0.0.1:1521/ so maybe adding :14330 to your IP would do it.

3

I have tried that and it doesn’t work. I just get:

ConnectSQL,Connection to SQL server failed! Error:||(08001) SQL Server does not exist or access denied.

Most of the AI’s suggest that I should use glo:Connect = '127.0.0.1,14330,test,sa,sa' but doesn’t work either.

The documentation doesn’t mention anything about the port just the server:

The OWNER attribute for MSSQL takes the format:

server,<database>,<uid>,<pwd><;LANGUAGE=language><;APP=name><;WSID=name>

LANGUAGE
 The language used by MSSQL Server.
 
APP
 The name of the application.
 
WSID
 The workstation ID. Typically, this is the network name of the computer on which the application resides.
 
See your MSSQL Server documentation for information on these settings.
4

Maybe you need that Data Source part. Connect via an IP address - Microsoft.Data.SqlClient - ConnectionStrings.com

5

But where/how would I define that since the format based on MSSQL docs is:

server,<database>,<uid>,<pwd><;LANGUAGE=language><;APP=name><;WSID=name>

And isn’t that link for .NET client or is that what C10 MSSQL driver is using underneath?

6

My expertise in that area comes from looking at this page and sharing the link that looked like it might be useful. SQL Server connection strings - ConnectionStrings.com

Other than that, no idea. Sorry.

7

I can’t test it at the moment, maybe this will work:
<server>,<database>,<uid>,<pwd>;Network=dbmssoc;Server=tcp:<servertcpnameoraddress>,<port>

8

I tried with 127.0.0.1,test,sa,sa;Network=dbmssoc;Server=tcp:127.0.0.1,14330 and got the same error as before:

Connection to SQL server failed! Error:||(08001) SQL Server does not exist or access denied.
9

Maybe it could be a firewall issue, it’s the sql on the same machine?

1 Like
10

Just a WAG from the help topic “Using SQL Tables in your Clarion Application”

The SQL drivers now parse out double quotes within the owner attribute to allow commas to be passed in a section. For example, you can now do

“server,port”,database,uid,pwd
to connect to an MS-SQL database running on a non-standard port.

4 Likes
11

That was it, huge thanks!

Using "127.0.0.1,14330",test,sa,sa worked.