We recently setup a new managed instance through the program that allows you to try it for free . When trying to run the following code from Microsoft's documentation : EXECUTE sp_execute_external_script @language =N'Python', @script=N'import sys; print("\n".join(sys.path))' The code fails after 300 seconds with the following error: > Msg 39012, Level 16, State 14, Line 0 > > Unable to communicate with the runtime for 'Python' script for request id: A1D8A9DA-DBB1-4EDE-B589-3AAFD4241D18. Please check the requirements of 'Python' runtime. > > STDERR message(s) from external script: > > SQLSatellite Run() failed. Error code:0x8007271d. > > SqlSatelliteCall error: SQLSatellite Run() failed. Error code:0x8007271d. > > STDOUT message(s) from external script: > > SqlSatelliteCall function failed. Please see the console output for more information. > > Traceback (most recent call last): > > File "D:\WFRoot\Ext\Python.9.4.8.3\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call > > rx_native_call("SqlSatelliteCall", params) > > File "D:\WFRoot\Ext\Python.9.4.8.3\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call > > ret = px_call(functionname, params) > > RuntimeError: revoscalepy function failed. How do I resolve this error?

I am trying to create a script in Microsoft SQL Server Management Studio to fetch the current exchange rate from an API and update a database. I am told it is not posisble and common to do this. -- Change the database context USE lndb; -- Declare variables DECLARE @url NVARCHAR(2000); DECLARE @responseText NVARCHAR(4000); DECLARE @exchangeRate DECIMAL(10, 6); -- Set the API URL SET @url = 'https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF43718/datos/oportuno?token=734b37b3a5099a9d2d39d06478d47e359a9568cd6693116d95b710e6b8be0008 '; -- Make HTTP request and store the response EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @responseText OUTPUT; EXEC sp_OAMethod @responseText, 'open', NULL, 'GET', @url, false; EXEC sp_OAMethod @responseText, 'send'; EXEC sp_OAMethod @responseText, 'responseText', @exchangeRate OUTPUT; EXEC sp_OADestroy @responseText; -- Convert the response to a decimal SET @exchangeRate = CONVERT(DECIMAL(10, 6), @exchangeRate); SELECT @exchangeRate -- Update the teimcs008401 table in the dbo schema INSERT INTO dbo.teimcs008401 (t_bcur, t_ccur, t_rate, t_stdt, t_Refcntd, t_Refcntu) VALUES ('MXP', 'USD', @exchangeRate, GETDATE(), '0', '0'); -- Display the updated exchange rate SELECT * FROM dbo.teimcs008401;

I have a simple SQL file I'm using to perform some test:

`
DECLARE @VAR1 AS VARCHAR(100) = '12345'
DECLARE @VAR2 AS INTEGER      = 54321

WAITFOR DELAY '00:00:02'

INSERT Orders SELECT @VAR1 AS COL1, @VAR2 AS COL2, ...
...

` Before launching all that, I would like to do some cleaning:

`
/* Clean SpecialSubSubEntries */
DELETE SpecialSubSubEntries
  WHERE Id IN (SELECT Id FROM SubSubEntries
                 WHERE SubEntryId IN (SELECT Id FROM SubEntries
                                        WHERE EntryId IN (SELECT Id FROM Entries
                                                            WHERE Name='ENTRY01')
                                      )
               )

DELETE SubSubEntries
  WHERE SubEntryId IN (SELECT Id FROM SubEntries
                         WHERE EntryId IN (SELECT Id FROM Entries
                                             WHERE Name='ENTRY01')
                      )
...

` For readability reasons, I'd like to put all the cleaning commands in a file "cleanup.sql" and launch it at the beginning of my SQL-file. How do I do something like that? I guess it's something like: EXECUTE ".\cleanup.sql" ... but I'm not sure about the syntax.

Python integer type has unlimited precision so it is more than capable to hold a bigint value of MS SQL (64 bit). Still it is implicitly mapped to float64 python type, when passed to an external script. This can cause serious calculation errors for large integers. So why is it mapped to float64? My guess is: R was added before Python via the [Extensibility architecture](https://learn.microsoft.com/en-us/sql/machine-learning/concepts/extensibility-framework?view=sql-server-ver15) and it has fixed precision integers (32 bit). So it can't hold bigints. So perhaps this is a compatibility issue. What is the best practice to ensure precise calculations? Simple but working idea: pass bigints as string then parse them as int. I know it has a slim chance to cause problem in practice, but still good to know. # How can it be a problem: I wrote a simple example to demonstrate how can it be a problem: CREATE TABLE #test ( big_integer BIGINT ); INSERT INTO #test (big_integer) VALUES (36028797018963968), (36028797018963968 + 1); EXECUTE sp_execute_external_script @language = N'Python', @input_data_1 = N'SELECT big_integer FROM #test', @script = N' print(InputDataSet.dtypes) OutputDataSet = InputDataSet ' Executing this code on SQL Server 2019 will give you the result of: | | (No column name) | |---------------------| |1| 36028797018963970 | |2| 36028797018963970 | and because of the print(InputDataSet.dtypes) statement we can see the following message: ... STDOUT message(s) from external script: big_integer float64 dtype: object ... So we got a floating point rounding error. The value of this error for big enough integers is greater than 1, which is the root of this problem. It is out of the scope of this question to teach floating point arithmetics, but I link some good materials if you don't understand what did happen: [Simple example - Stack Overflow](https://stackoverflow.com/questions/249467/what-is-a-simple-example-of-floating-point-rounding-error) . Floating Point Numbers - Computerphile The IEEE 754 Format - Oxford I also share a small ipython sample if you want to experiment with this (which isn't a substitute of learning the theory behind this): In : import numpy as np In : a = 2**55 In : a Out: 36028797018963968 In : float(a) == float(a + 1) Out: True In : float(a) Out: 3.602879701896397e+16 In : float(a + 1) Out: 3.602879701896397e+16 In : np.nextafter(float(a), np.inf) Out: 3.6028797018963976e+16 # Note To run my example T-SQL some conditions must be met: - Machine Learning Services must be installed - external scripts must be enabled - Privilege to execute external scripts must be granted - You must have SQL Server 2017 CTP 2.0 or later

# TLDR for a reader with the same problem If your external script is too slow, you may do something in your scritpt which can be implemented in TSQL, which is a big problem. If this is not the case use CLR or process your data in a external application where the functionality is present. Normally you should not rely on external scripts to achieve high performance. CLR is also more secure . I am not allowed to do the above, because of workspace/interpersonal reasons. # Problem I have to write a query which must rely on regular expressions and I am not allowed to [use CLR](https://stackoverflow.com/questions/61488458/what-is-regexp-replace-equivalent-in-sql-server) or use anything outside the SQL Server environment. So I must use a R or Python external script. I have experience with Python. So I went with that. I wrote the script it works but the input data is extremely large so the external script execution takes a long time. I already heavily optimized the script, but it is still slow. So I plan to parallelize it. Python's [multiprocessing](https://docs.python.org/3/library/multiprocessing.html) module doesn't work inside the external script. So I got a strange idea: What if I horizontally partition the temp table which holds the input data and execute the external script on the partitions using [asynchronous procedure execution](https://stackoverflow.com/questions/4571823/sql-server-tsql-is-it-possible-to-exec-statements-in-parallel), then I just get back their union? Or perhaps there is a better way to parallel execution of an exterenal script? I work on SQL Server 2019 Development version we also have an enterprise version which I guess doesn't matter here. # Details @J.D rightly asked for more details. I am only allowed to share a limited amount: The temp table providing the input has different columns for the actual-, birth- and mother names for different people. I also have a dataframe in the external script containing valid first names and associated genders parsed from a JSON which is passed as a nvarchar(max). This method described here . My script extracts titles, surnames, given names and guess biological sex and martial status. The names are not English names. I apply a function to the first axis of the input Pandas Dataframe which does the extraction. I use only Pandas provided methods. With a standard python environment I would use the [np.array_split](https://stackoverflow.com/questions/17315737/split-a-large-pandas-dataframe) function to split the dataframe and use the multiprocessing module to apply extraction function on the splits in parallel processes, then [concatenate](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html) the dataframes. As you can see this is a text processing problem and cannot be vectorized as mathematical one could be. So tools like Dask [cannot be used here](https://stackoverflow.com/questions/45545110/make-pandas-dataframe-apply-use-all-cores) . # Why not CLR? Using CLR would be a sane choice. So a colleague of mine and I also recommended to use CLR. Our expert said using CLR is very dangerous. Yes I know about the CLR strict security option , but our expert may not. So I am forced to use external script against my better judgement. # Why not use multi-threading Python's Global Interpreter Lock only allows one thread to be executed by the interpreter. So multi-threading with python will not provide parallel computation, at least with the standard implementation. This is not a limitation for Jython or IronPython, but this is a different story.

# Problem The main problem we need to use regular expression on MS SQL Server 2019, with the capability of at least the level on the POSIX Regular expression. # Possible solutions This Q/A from stackoverflow rightly concludes that if you query must rely on regular expressions you shuould use CLR. This Readgate article elaborates this approach more. So one of my colleagues and I proposed this solution, but my other colleague categorically stated that using CLR here would be a huge risk to security and stability, and using external script (Python or R) is more secure. This seems to be dubious claim, since the user code in the [CLR can be managed](https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/assemblies/managing-clr-integration-assemblies?view=sql-server-ver15) , so perhaps the opposite is true, but I was not able to persuade my colleague. In my other [question](https://dba.stackexchange.com/questions/283102/parallel-execution-of-a-sql-server-external-script) which I wrote in my desperation because I was forced to use external script and still produce a blazing fast query. SQLpro user states in his comment that: > Using Python or R can be worst in terms of security rather than using CLR! Which I tend to believe. # Questions So I have two questions: 1. Which Regexp solution is more secure external script or CLR based (as described here )? And why? 2. I also proposed to run the python code on the same Windows Server (must be the same server, because of a policy) but with python intrepeter installed on the OS. Because the results are exported into CSV files either way and stored in the SQL Server. So then I would able to use Python's multiprocessing module to achieve the right performance. The answer was the same that running Python inside SQL Server is more secure than in a outside application. Which is also a questionable claim.