topic Re: Execute SAS Program via VBA: specifying port number > max int in SAS Programming

Execute SAS Program via VBA: specifying port number > max int

Andy_D — Thu, 19 Jul 2018 12:18:16 GMT

Hi,

I've just stumbled across this thread and would appreciate any help with the below problem:

I'm trying to connect to a remote SAS server using the below code in VBA

Public obSAS As sas.Workspace
Public obWorkspaceManager As New SASWorkspaceManager.WorkspaceManager

Sub sas_connect()

    Dim obConnection As New ADODB.Connection
    Dim obRecordSet As New ADODB.Recordset
    Dim obServerDef As New SASWorkspaceManager.ServerDef
    Dim xmlString As String
    Dim sourcebuffer As String

    obServerDef.Port = Port Number
    obServerDef.Protocol = ProtocolBridge
    obServerDef.MachineDNSName = Company Name

    Set obSAS = obWorkspaceManager.Workspaces.CreateWorkspaceByServer(Name, VisibilityProcess, obServerDef, Username, Password, xmlString)

End Sub

When assigning the port number in VBA the maximum integer is 32767 (32-bit integer), but the port number I use to connect my profile to the remote SAS server is higher than this.

Thanks in advance,

Andy

Re: Execute SAS Program via VBA: specifying port number > max int

ballardw — Thu, 19 Jul 2018 23:22:17 GMT

32767 looks more like a 16 bit integer (with one bit reserved for sign).

Long time since I did anything with VBA and never did anything with ports but I would try making the variable holding the port either a double or possibly string and see if that passes.

Re: Execute SAS Program via VBA: specifying port number > max int

ChrisBrooks — Thu, 19 Jul 2018 23:37:44 GMT

I don't have access to a machine with SAS IOM installed at the moment so I can't check any of this but I think, with a little educated guesswork, I know what is happening here. It may be a little bit esoteric but bear with me......

Firstly some definitions:

A 16 bit unsigned integer can contain values 0 through 65,535;
A 16 bit signed integer can contain values -32,768 through 32,767;
A 32 bit unsigned integer can contain values 0 through 4,294,967,295; and
A 32 bit signed integer can contain values -2,147,483,648 through 2,147,483,647

Port numbers range from 0 to 65535 with numbers 1 to 1024 reserved.

I suspect that the port attribute of the serverdef object supplied by SAS is actually a 16 bit signed integer which is why you cannot use port numbers greater than 32767 (in my experience there are usually plenty of ports available within this limit).

There are two ways to overcome this:

Persuade SAS to change the port attribute to an unsigned 16 bit integer (you can't have negative port numbers) - however I suspect you will have little luck with that; or
Change the port you are using so that it is within the 32767 limit for a signed 16 bit integer

Is there any reason you can't use a port within that limit as I would be amazed to find there were none free?

Re: Execute SAS Program via VBA: specifying port number > max int

ChrisBrooks — Thu, 19 Jul 2018 23:39:47 GMT

@ballardw I suspect the problem lies with the port attribute of the serverdef object which is supplied by SAS and not the variable @Andy_D is using to hold the port number.

Re: Execute SAS Program via VBA: specifying port number > max int

ballardw — Thu, 19 Jul 2018 23:52:28 GMT

@ChrisBrooks wrote:

@ballardw I suspect the problem lies with the port attribute of the serverdef object which is supplied by SAS and not the variable @Andy_D is using to hold the port number.

Like I implied, that was a guess. But I follow your idea and it makes a lot of sense.

Re: Execute SAS Program via VBA: specifying port number > max int

Andy_D — Fri, 20 Jul 2018 09:00:53 GMT

Thanks Chris - Think I'll have to contact the admin in order to find out if any other ports within the 16-bit integer range have been assigned to the host server which we use.