Hi there @Joel !
OK, as I wrote above, I did some further investigations - and I made it to work. However to get it to work I used an open source Python version in SQL Server. In SQL Server 2019 CU3+ you have the ability to install your own Python, (and R), runtime, and not use the "standard" Microsoft runtime. For more of that see my post here: https://nielsberglund.com/2020/12/29/updated-bring-your-own-r--python-runtimes-to-sql-server-extensibility-framework/.
Anyway, what you do is you import your .pyd file as you would with any import and the use it. An example below:
EXEC sp_execute_external_script
@language =N'p39',
@script=
N'
import sys
import os
sys.path.append(os.path.abspath("W:/pythontest"))
from hello import hello42
hello42()'
In the snippet above I have:
- created my own Python language, which I call p39 (as per my blog posts)
- I have a .pyd file named hello.pyd, and it is located in w:\pythontest.
- In that file I have a function called hello42.
In my Python script I do some imports followed by from ... and then I just use it. The one thing to think about is that you need to assign the permissions to "ALL APPLICATION PACKAGES" to the directory where your .pyd file is as well as the file itself like so:
icacls w:\pythontest /grant *S-1-15-2-1:(OI)(CI)F /t
Hope this helps!
Niels