or-tools called from SQL Server 2022 not working

[dawiepoolman]

What version of OR-Tools and what language are you using?
Version: main
Language: Python 3.10.0rc2 within SQL Server 2022
Which solver are you using (e.g. CP-SAT, Routing Solver, GLOP, BOP, Gurobi)
SCIP
What operating system (Linux, Windows, ...) and version?
Windows 10 Pro
What did you do?
Steps to reproduce the behavior:
I am trying to solve a sample MIP problem as defined here but using the standard 'sp_execute_external_script' store procedure from SQL Server 2022

I have configured the Python runtime environment in SQL as defined in Microsofts documentation here

Other custom Python libraries works correctly in SQL Server e.g. "text-tools" (see install instructions here)

However, I don't get any results back from Python when solving the sample MIP problem using or-tools. The problem solves perfectly fine when I run it in Visual Code.

What did you expect to see
I expect the same result in SQL as in the Visual Code IDE i.e. the names and values of the solve returned in a pandas dataframe

What did you see instead?
null i.e. the solve does not return data.
I also tried solving a small standard problem formulated as an .mps file using the model_builder library as we concluded in the closed issue here. To solve a .mps file in Python from within SQL is ultimately my objective.
When I do this:

from ortools.linear_solver.python import model_builder
model = model_builder.ModelBuilder()
print(model) 

I do see a memory address which I assume means the library might have imported correctly i.e.:?
<ortools.linear_solver.python.model_builder.ModelBuilder object at 0x0000024EABBAF700>

Anything else we should know about your project / environment
Herewith my full T-SQL script that generated the Python code and then calls the 'sp_execute_external_script' with the python code and in input via variable @pythonscript:

When I print the content of variable @pythonscript and run it in python manually it works perfectly fine:

/*  ----    Test OR-Tools Solve     */
use MLDemo
--in case the python runtime environment restricts custom libraries like ortools to only work within the database for which it was installed
go

--Example from https://github.com/google/or-tools/blob/stable/ortools/linear_solver/samples/simple_mip_program.py

declare @pythonscript nvarchar(max)
set @pythonscript =N'
#!/usr/bin/env python3
# Copyright 2010-2022 Google LLC
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
"""Integer programming examples that show how to use the APIs."""
# [START program]
# [START import]
import numpy as np
import pandas as pd
from ortools.linear_solver import pywraplp
# [END import]


def main():
    # [START solver]
    # Create the mip solver with the SCIP backend.
    solver = pywraplp.Solver.CreateSolver('+'''SCIP'''+')
    if not solver:
        return
    # [END solver]

    # [START variables]
    infinity = solver.infinity()
    # x and y are integer non-negative variables.
    x = solver.IntVar(0.0, infinity, '+'''x'''+')
    y = solver.IntVar(0.0, infinity, '+'''y'''+')

    print('+'''Number of variables ='''+', solver.NumVariables())
    # [END variables]

    # [START constraints]
    # x + 7 * y <= 17.5.
    solver.Add(x + 7 * y <= 17.5)

    # x <= 3.5.
    solver.Add(x <= 3.5)

    print('+'''Number of constraints ='''+', solver.NumConstraints())
    # [END constraints]

    # [START objective]
    # Maximize x + 10 * y.
    solver.Maximize(x + 10 * y)
    # [END objective]

    # [START solve]
    status = solver.Solve()
    # [END solve]

    # [START print_solution]
    if status == pywraplp.Solver.OPTIMAL:
        print('+'''Solution:'''+')
        print('+'''Objective value ='''+', solver.Objective().Value())
        print('+'''x ='''+', x.solution_value())
        print('+'''y ='''+', y.solution_value())
    else:
        print('+'''The problem does not have an optimal solution.'''+')
    # [END print_solution]

    # [START advanced]
    print('+'''\nAdvanced usage:'''+')
    print('+'''Problem solved in %f milliseconds'''+' % solver.wall_time())
    print('+'''Problem solved in %d iterations'''+' % solver.iterations())
    print('+'''Problem solved in %d branch-and-bound nodes'''+' % solver.nodes())
    # [END advanced]

	# [START SQL Dataframe OutputDataSet]
    np_var_names = np.array([])
    np_var_values = np.array([])
    np_var_names = np.append('+'''x'''+', '+'''y'''+')
    np_var_values = np.append(x.solution_value(), y.solution_value())   
     
    df = pd.DataFrame(np_var_names, columns =['+'''VariableName'''+'])
    df['+'''VariableValue'''+'] = np_var_values.tolist()
    OutputDataSet = df
    print(OutputDataSet)
	# [END SQL Dataframe OutputDataSet]

if __name__ == '+'''__main__'''+':
    main()
# [END program]
'

--print @pythonscript

EXEC sp_execute_external_script @language = N'Python'
	,@script= @pythonscript
	,@input_data_1 = N''
    ,@output_data_1_name = N'OutputDataSet'
WITH RESULT SETS
( ( [VariableName] VARCHAR(50) NOT NULL,
	[VariableValue] float NULL)
)

edit:
In order to check again that dependencies are installed I re-ran the sqlmlutils in an elevated cmd prompt:

sqlmlutils.SQLPackageManager(connection).install("ortools")
The version of ortools you are trying to install is 9.5.2237.
The version installed on the server is 9.5.2237

sqlmlutils.SQLPackageManager(connection).install("pandas")
The version of pandas you are trying to install is 1.5.3.
The version installed on the server is 1.5.3

sqlmlutils.SQLPackageManager(connection).install("numpy")
The version of numpy you are trying to install is 1.24.2.
The version installed on the server is 1.24.2

[lperron]

Moving to discussions.

[lperron]

Actually, i do not how what the '+'''Scip'''+' is interpreted by python. Can you check with just 'scip' ?

[dawiepoolman]

The '+'''SCIP'''+' in T-SQL resolves to the following being passed into Python i.e. when you run: print @pythonscript in SQL:
solver = pywraplp.Solver.CreateSolver('SCIP')
The same goes for variables e.g. '+'''x'''+' produces 'x'

I have tested with
solver = pywraplp.Solver.CreateSolver("SCIP")

Both versions solves in Visual Code but neither solve in SQL Server

[dawiepoolman]

This small example proves that the pandas library works fine:

declare @pythonscript nvarchar(max)
set @pythonscript =N'
import numpy as np
import pandas as pd
#test df
data = {"VariableName": ["Alice", "Bob", "Charlie", "Dave"],
        "VariableValue": [25, 30, 35, 40]}
df = pd.DataFrame(data)
OutputDataSet = df
print(OutputDataSet)
'
--print @pythonscript
EXEC sp_execute_external_script @language = N'Python'
	,@script= @pythonscript
	,@input_data_1 = N''
    ,@output_data_1_name = N'OutputDataSet'
WITH RESULT SETS
( ( [VariableName] VARCHAR(50) NOT NULL,
	[VariableValue] float NULL)
)

output:

and the Messages tab:

[dawiepoolman]

This T-SQL with double quotes instead of single quotes also solves fine in Python natively i.e. in Visual Code but produces no results when called within SQL Server:

/*  ----    Test OR-Tools Solve     */
use MLDemo
--in case the python runtime environment restricts custom libraries like ortools to only work within the database for which it was installed
go

--Example from https://github.com/google/or-tools/blob/stable/ortools/linear_solver/samples/simple_mip_program.py

declare @pythonscript nvarchar(max)
set @pythonscript =N'
#!/usr/bin/env python3
# Copyright 2010-2022 Google LLC
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
"""Integer programming examples that show how to use the APIs."""
# [START program]
# [START import]
import numpy as np
import pandas as pd
from ortools.linear_solver import pywraplp
# [END import]


def main():
    # [START solver]
    # Create the mip solver with the SCIP backend.
    solver = pywraplp.Solver.CreateSolver("SCIP")
    if not solver:
        return
    # [END solver]

    # [START variables]
    infinity = solver.infinity()
    # x and y are integer non-negative variables.
    x = solver.IntVar(0.0, infinity, "x")
    y = solver.IntVar(0.0, infinity, "y")

    print("Number of variables =", solver.NumVariables())
    # [END variables]

    # [START constraints]
    # x + 7 * y <= 17.5.
    solver.Add(x + 7 * y <= 17.5)

    # x <= 3.5.
    solver.Add(x <= 3.5)

    print("Number of constraints =", solver.NumConstraints())
    # [END constraints]

    # [START objective]
    # Maximize x + 10 * y.
    solver.Maximize(x + 10 * y)
    # [END objective]

    # [START solve]
    status = solver.Solve()
    # [END solve]

    # [START print_solution]
    if status == pywraplp.Solver.OPTIMAL:
        print("Solution:")
        print("Objective value =", solver.Objective().Value())
        print("x =", x.solution_value())
        print("y =", y.solution_value())
    else:
        print("The problem does not have an optimal solution.")
    # [END print_solution]

    # [START advanced]
    print("\nAdvanced usage:")
    print("Problem solved in %f milliseconds" % solver.wall_time())
    print("Problem solved in %d iterations" % solver.iterations())
    print("Problem solved in %d branch-and-bound nodes" % solver.nodes())
    # [END advanced]

	# [START SQL Dataframe OutputDataSet]
    np_var_names = np.array([])
    np_var_values = np.array([])
    np_var_names = np.append("x", "y")
    np_var_values = np.append(x.solution_value(), y.solution_value())   
     
    df = pd.DataFrame(np_var_names, columns =["VariableName"])
    df["VariableValue"] = np_var_values.tolist()
    OutputDataSet = df
    print(OutputDataSet)
	# [END SQL Dataframe OutputDataSet]

if __name__ == "__main__":
    main()
# [END program]
'

--print @pythonscript

EXEC sp_execute_external_script @language = N'Python'
	,@script= @pythonscript
	,@input_data_1 = N''
    ,@output_data_1_name = N'OutputDataSet'
WITH RESULT SETS
( ( [VariableName] VARCHAR(50) NOT NULL,
	[VariableValue] float NULL)
)

error message:

[dawiepoolman]

Ok, I managed to get the SQL Server 2022 solve working using a .mps file in the following way:

1. Put a custom .py file that does the SCIP solve in in the library of the SQL Python runtime environment.
2. Export an .mps file to the same directory
3. Call the custom .py from SQL using the sp_execute_external_script procedure to populate a pandas df

Herewith the T-SQL:

------------Solve MPS via wrapper py file---

-- To allow advanced options to be changed.  
EXECUTE sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

use MLDemo
GO

--Note, the mps file has to be written to a table line-by line in order for the bcp command to not give errors on exporting the file as one @mps variable

drop table if exists dbo.MPSFile
create table dbo.MPSFile(RowID integer identity (1,1) 
				        ,mps_text nvarchar(max))

truncate table dbo.MPSFile
declare @mps nvarchar(max)

set @mps = 'NAME          TESTPROB'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = 'ROWS'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = ' N  COST'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = ' L  LIM1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = ' G  LIM2'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = ' E  MYEQN'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = 'COLUMNS'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    XONE      COST                 1   LIM1                 1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    XONE      LIM2                 1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    YTWO      COST                 4   LIM1                 1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    YTWO      MYEQN               -1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    ZTHREE    COST                 9   LIM2                 1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    ZTHREE    MYEQN                1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = 'RHS'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    RHS1      LIM1                 5   LIM2                10'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = '    RHS1      MYEQN                7'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = 'BOUNDS'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = ' UP BND1      XONE                 4'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = ' LO BND1      YTWO                -1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = ' UP BND1      YTWO                 1'
insert into dbo.MPSFile(mps_text) values(@mps)
set @mps = 'ENDATA'
insert into dbo.MPSFile(mps_text) values(@mps)


--Export the mps file to the current Win dir where the solver can read it in the same Python runtime as SQL server
--First generate a blank file manually and then give right click and edit the security of the file to give Everyone full control to read and write to the file:
--C:\Program Files\Python310\Lib\site-packages\ortools\linear_solver\python\SQLgeneratedMPS.mps

DECLARE @sql VARCHAR(8000);
SELECT @sql = 'bcp "SELECT [mps_text] from [MLDemo].[dbo].[MPSFile] order by [RowID] asc" queryout "C:\Program Files\Python310\Lib\site-packages\ortools\linear_solver\python\SQLgeneratedMPS.mps" -c -t, -T -S' + @@SERVERNAME;
exec master..xp_cmdshell @sql;

--import our custom library and call its df
declare @pythonscript nvarchar(max)
set @pythonscript =N'
import pandas as pd
from ortools.linear_solver.python import SolveSQLgeneratedMPSusingSCIP as MySCIPSolve
OutputDataSet = pd.DataFrame(MySCIPSolve.OutputDataSet)
'

--Save the solve results in a table
drop table if exists #OutputDataSet
CREATE TABLE #OutputDataSet (
  VariableName varchar(50)
  ,VariableValue float
);

--Call the solve
INSERT INTO #OutputDataSet (VariableName
						   ,VariableValue)
EXEC sp_execute_external_script @language = N'Python'
	,@script = @pythonscript
	,@input_data_1 = N''
    ,@output_data_1_name = N'OutputDataSet'

--Retrieve the results to be used in SQL
SELECT VariableName
	  ,VariableValue
FROM #OutputDataSet

GO

Herewith the SCIP solver in Python.
Save the contents to a file in SQL Server 2022 runtime environment e.g.:
"C:\Program Files\Python310\Lib\site-packages\ortools\linear_solver\python\SolveSQLgeneratedMPSusingSCIP.py"

import os
import numpy as np
import pandas as pd
from ortools.linear_solver.python import model_builder
path = os.path.dirname(__file__)
mps_path = f'{path}/SQLgeneratedMPS.mps'
model = model_builder.ModelBuilder()
model.import_from_mps_file(mps_path)
solver = model_builder.ModelSolver('SCIP')
solver.solve(model)
variables = [model.var_from_index(i) for i in range(model.num_variables)]
np_var_names = np.array([])
np_var_values = np.array([])
for variable in variables:
    np_var_names = np.append(np_var_names, variable.name)
    np_var_values = np.append(np_var_values, solver.value(variable))    
df = pd.DataFrame(np_var_names, columns =['VariableName'])
df['VariableValue'] = np_var_values.tolist()
OutputDataSet = df

Results will be retrieved via a pandas dataframe and be put into a hash table:

Let's hope this helps other people solving problems using SQL Server as well because a lot of problem datasets might come from SQL databases.

[dawiepoolman]

added note for clarity:
The path for your custom .py script might need to be updated to the runtime environment generated by SQL Server's sqlmlutils e.g.:
"C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER2022\MSSQL\ExternalLibraries\5\2\1\ortools\linear_solver\python\SolveSQLgeneratedMPSusingSCIP.py"

for earlier versions of SQL e.g. SQL Server 2019:
"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER2019\PYTHON_SERVICES\Lib\site-packages\ortools\linear_solver\python\SolveSQLgeneratedMPSusingSCIP.py"

edit: for Python installation instructions in SQL Server 2019 see here

for example, run this in elevated cmd prompt to install or-tools using pip in SQL runtime environment:
"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER2019\PYTHON_SERVICES\Scripts\pip.exe" install --upgrade ortools