I wrote a function in Inno Setup Compiler 5.5.9a to try and change the TCP/IP port that MSSQL Server listens on.
function SetSqlTcpPort(port: Integer): Boolean;
var
WbemLocator: Variant;
WbemServices: Variant;
WQLQuery: string;
WbemObjectSet: Variant;
i: Integer;
tcpProperty: Variant;
tcpNewValue: String;
begin
result := false;
WbemLocator := CreateOleObject('WbemScripting.SWbemLocator');
WbemServices := WbemLocator.ConnectServer('localhost', 'root\CIMV2');
WQLQuery := 'SELECT * FROM ServerNetworkProtocolProperty ' +
'WHERE InstanceName="{#SQL_INSTANCE_NAME}" AND ' +
'ProtocolName="Tcp" AND ' +
'IPAddressName="IPAll"';
WbemObjectSet:= WbemServices.ExecQuery(WQLQuery);
if (not VarIsNull(WbemObjectSet)) then
begin
if (WbemObjectSet.Count > 0) then
begin
for i := 0 to WbemObjectSet.Count - 1 do
begin
tcpProperty := WbemObjectSet.ItemIndex(i);
if (not VarIsNull(tcpProperty)) then
begin
if (tcpProperty.PropertyName = 'TcpPort') then
begin
tcpNewValue := IntToStr(port);
end
else
begin
tcpNewValue := '';
end;
if (tcpProperty.SetStringValue(tcpNewValue) <> 0) then
begin
MsgBox('Error trying to set the new SQL TCP/IP port to ' + IntToStr(port) + '! ' +
'The property "' + tcpProperty.PropertyName + '" could not be set to "' + tcpNewValue + '".', mbCriticalError, MB_OK);
end;
end;
end;
end;
end;
end;
I think I almost have it done but I'm running into an error on the following line...
if (WbemObjectSet.Count > 0) then
It seems like maybe the property .Count does not exist? Also the error says Exception: SWbemObjectSet: Invalid class, seems kinda weird that it would use the text SWbemObjectSet instead of WbemObjectSet.
Any ideas as to what I might be doing wrong?
EDIT #2
OK so I made a few changes to the script that seems to get past the issue I was having and this is what I came up with...
function GetSqlServerVersion: Integer;
var
i: Integer;
WbemLocator: Variant;
WbemServices: Variant;
service: String;
begin
result := -1;
for i := 4 downto 0 do
begin
//Test out the higher versions of SQL Server first
case i of
4: service := 'ComputerManagement13'; //SQL Server 2016
3: service := 'ComputerManagement12'; //SQL Server 2014
2: service := 'ComputerManagement11'; //SQL Server 2012
1: service := 'ComputerManagement10'; //SQL Server 2008
0: service := 'ComputerManagement'; //SQL Server 2005
end;
try
//Try to connect to the wbem locator
WbemLocator := CreateOleObject('WbemScripting.SWbemLocator');
WbemServices := WbemLocator.ConnectServer('localhost', 'root\Microsoft\SqlServer\' + service);
//Got a connection so lets use this one
case i of
0: result := 0; //SQL Server 2005
1: result := 10; //SQL Server 2008
2: result := 11; //SQL Server 2012
3: result := 12; //SQL Server 2014
4: result := 13; //SQL Server 2016
end;
//No need to continue because we found a good connection
Exit;
except
//Got an error so that not a good connection
result := -1;
end;
end;
end;
function SetSqlTcpPort(port: Integer): Boolean;
var
version: Integer;
service: String;
WbemLocator: Variant;
WbemServices: Variant;
WQLQuery: string;
tcpProperties: Variant;
i: Integer;
tcpProperty: Variant;
tcpNewValue: String;
count: Integer;
begin
result := false;
version := GetSqlServerVersion;
if (version > 0) then
begin
service := 'ComputerManagement' + IntToStr(version);
end
else if (version = 0) then
begin
service := 'ComputerManagement';
end
else
begin
MsgBox('There does not seem to be an SQL server installed on this system yet! Tried connecting to SQL Server 2005, 2008, 2012, 2014, and 2016 to no avail. Could not change the SQL TCP/IP Server port!', mbCriticalError, MB_OK);
Exit;
end;
WbemLocator := CreateOleObject('WbemScripting.SWbemLocator');
WbemServices := WbemLocator.ConnectServer('localhost', 'root\Microsoft\SqlServer\' + service);
WQLQuery := 'SELECT * FROM ServerNetworkProtocolProperty ' +
'WHERE InstanceName="{#SQL_INSTANCE_NAME}" AND ' +
'ProtocolName="Tcp" AND ' +
'IPAddressName="IPAll"';
tcpProperties:= WbemServices.ExecQuery(WQLQuery);
if (not VarIsNull(tcpProperties)) then
begin
count:= Length(tcpProperties); //ERROR IS HERE
if (count > 0) then
begin
for i := 0 to count - 1 do
begin
tcpProperty := tcpProperties.ItemIndex(i);
if (not VarIsNull(tcpProperty)) then
begin
if (tcpProperty.PropertyName = 'TcpPort') then
begin
tcpNewValue := IntToStr(port);
end
else
begin
tcpNewValue := '';
end;
if (tcpProperty.SetStringValue(tcpNewValue) <> 0) then
begin
MsgBox('Error trying to set the new SQL TCP/IP port to ' + IntToStr(port) + '! ' +
'The property "' + tcpProperty.PropertyName + '" could not be set to "' + tcpNewValue + '".', mbCriticalError, MB_OK);
end;
end;
end;
end;
end;
end;
Now as you can see I have implemented a new function that tries to determine the version of SQL Server installed by trial and error. This works great but I still seem to be having an issue when it comes to getting the COUNT of objects returned from the ExecQuery method. I know this is returning EXACTLY what I want because I have used wbemtest.exe to run the query with perfect results.
The issue is from the line...
count:= Length(tcpProperties);
So currently the issue is...
How do I get the count of objects/elements that the method ExecQuery() has produced?
I have tried things like Count(tcpProperties), Length(tcpProperties), GetArrayLength(tcpProperties), etc but nothing I do will give me the number of items/rows/elements returned.
EDIT #3
So I upgraded to the Unicode version of Inno Setup and changed some code the Unicode version did not approve of. I thought all was going to be good and now I get this error..
Guess what line that is? Yep you guessed it, it happens on the following line...
count:= tcpProperties.Count;
As you can see from the below image when hovering over tcpProperties, it does not seem to be NULL or nil.
Suggestions?
EDIT #4 (Requested)
wbmetest.exe returns the following. This shows 2 objects just as I would expect the function to return.
