When using [SERVER_PROP],[PORT_PROP] in Database server it does not work

[jstuardo]

Hello,

I need to allow user to specify a different database port at installation time.

Please see this image:

SQLServerProp.png (20.1 KiB) Viewed 5631 times

As you see, I use [SERVER_PROP],[PORT_PROP] in the Server specification, as documentation says. However, it does not work. When installing and trying to create the scripts, a timeout occur. This does not happen if I use [SERVER_PROP] as the server.
I am attaching the AIP file also.

Regards
Jaime

[Catalin]

Hello Jaime,

I can not say for sure why this happens.

First of all, are you sure your SQL Server Instance runs on a port number different than the default one (1433)?

In order to check this, please open a run command (WIN+R buttons combination) and type the following:

Code: Select all

%windir%\System32\cliconfg.exe

In the "SQL Server Client Network Utility" window, please select "TCP/IP" from under the "Disabled protocols" and click on the "Enable" button.

After doing so, please click on the "TCP/IP" from under the "Enabled protocols by order" and click on the "Properties" button.

A new Window should be spawned, showing the default port of your SQL Server Instance.

If the port is set to the default one (1433), then the fact that this is not working is normal.

If the port is different than the default one, then could you please let me know what version of SQL Server you are using so I can further test this on my end?

Looking forward to hearing from you.

Best regards,
Catalin

[jstuardo]

Sorry, it is working now. I did the test again, and this worked.

Regards
Jaime

[jstuardo]

Sorry again,

I have found where the problem is.

The problem is when "(local)" is specified in the server field and the default port 1433. When I use "localhost" instead of "(local)", it works.

The fact is that the name "(local)" is added automatically by the SQL Server browse.

It works even when using the IP address and the instance name and any SQL Server port (of course, if SQL Server is listening on that port).

I think that when using "(local)" as the server, connection is made using shared memory. That is why it worked before, when no port is specified in the Server field of the DB configuration in AI. If the port is specified, system immediately tries to use TCP/IP connection and since TCP/IP does not know what "(local)" is, the time out occurs.

Can I use a workaround on this? Maybe, if the selection is "(local)", the port field not to be taken into account.

SQL Setting.png (76.79 KiB) Viewed 5601 times

[Catalin]

Hello Jaime,

I am glad you got this working.

However, I did some tests on my machine and I was still not able to reproduce this behavior.

Could you please give me some more details, such as:

- what version of SQL Server are you using?

- from what I can see, you are using a default instance (please correct me if I'm wrong)

- on what OS are you testing this behavior?

I'm mostly asking for this because I want to reproduce this behavior on our end as well, as I find your information regarding localhost and (local) really useful and interesting and I would like to further investigate this.

Looking forward to hearing from you.

Best regards,
Catalin

[jstuardo]

Dear Catalin,

My SQL Server version is 2014, but I think this will happen in all versions. I am using the default instance but it also happens if it is not the default.

My OS is Windows 10 Home.

In summary, the issue is as following: when setting [SERVER_PROP],[PORT_PROP] in the database configuration page, system assumes I want to connect to SQL Server using TCP/IP protocol.

There are some points to note here:

• For local SQL Server instance, by default TCP/IP protocol is disabled. That is the importance of (local) name. When connection string uses it in the data source parameter, connection is made to the default instance using shared memory

• For remote SQL Server instances, TCP/IP connection is fine, so only in that case, the [SERVER_PROP],[PORT_PROP] setting works. That is important if the port is not the default.

• If setting is [SERVER_PROP], as I had before, it works for all cases, local and remote, only if the default port. If that is the case, when using (local) as the datasource, connection is made using shared memory. If the datasource is an IP, localhost or a machine name, connection is made using TCP/IP. Of course, TCP/IP should be enabled in the target machine.

• If the SQL Server Dialog uses Server Browse to get the list of servers discovered in the network, it returns (local) as the name of the local machine, instead of localhost or the machine name.

I think one solution could be to set the property dynamically. Is that possible?

As you see in my SQL Server dialog, I have 2 fields, one for the db instance and one for the port.

What if I define the Server property in the SQL Databases page as just [SERVER_PROP] and in my dialog change that value to be [SERVER_PROP],[PORT_PROP] only when the server is different from (local)? is that possible?

Other question. Is it possible to do some action when the combobox change its value? for example, if the combobox value is (local), the port field should be disabled since when (local), port number is useless.

Kind regards
Jaime

[jstuardo]

Hello Catalin,

What do you think about this approach? I haven't try it yet:

Copy the whole "Base de Datos" node.

One of them, with this condition:

Code: Select all

SERVER_PROP = "(local)"

. In this case, Server field equal to

Code: Select all

[SERVER_PROP]

.

The other node, with this condition:

Code: Select all

SERVER_PROP <> "(local)"

. In this case, Server field equal to

Code: Select all

[SERVER_PROP],[PORT_PROP]

That way. When wizard asks for the server and port, if server in the combobox is (local), the database node with

Code: Select all

SERVER_PROP = "(local)"

is executed. On the other hand, if the server is anything else, the database node with

Code: Select all

SERVER_PROP <> "(local)"

is executed.

Regards
Jaime

[Catalin]

Hello Jaime,

Please accept my apologies for the delayed reply.

At a first glance, this approach looks good. However, I would suggest testing this and seeing if it is indeed working.

Another approach I was thinking about (I did not test this, though) was maybe having one "Set installer property value" events, which set the "SERVER_PROP" property based on the value it is initialized to. E.g.:

Code: Select all

if SERVER_PROP <> "(local)" then SERVER_PROP = [SERVER_PROP],[PORT_PROP]

However, the [PORT_PROP] should be initialized before the dialog is spawned.

Hope this helps somhow.

Best regards,
Catalin