{"id":111077,"date":"2026-06-08T12:00:00","date_gmt":"2026-06-08T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=111077"},"modified":"2026-06-01T11:58:26","modified_gmt":"2026-06-01T11:58:26","slug":"how-to-call-an-ollama-based-ai-text-embeddings-model-from-sql-server-2025","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/how-to-call-an-ollama-based-ai-text-embeddings-model-from-sql-server-2025\/","title":{"rendered":"How to call an Ollama-based AI text embeddings model from SQL Server 2025"},"content":{"rendered":"\n

In the first article<\/a> of this series<\/a>, I explained how to install and configure Ollama to host text embeddings models locally. I also demonstrated how to install Caddy as a proxy to allow SQL Server to use Ollama via https-based calls. In this article, I’ll show you how to make use of this at the SQL Server end.<\/strong><\/p>\n\n\n\n

No time to read? Here’s a quick overview:<\/strong>
To generate text embeddings in SQL Server using a locally hosted Ollama model, enable REST endpoints with sp_configure<\/code>, define the model once using CREATE EXTERNAL MODEL<\/code>, then call AI_GENERATE_EMBEDDINGS<\/code> whenever you need a vector. This avoids messy REST code every time and keeps your embedding logic clean and reusable.<\/p>\n\n\n\n

Which text embeddings models should I use in Ollama?<\/h2>\n\n\n\n

You’ll find that Ollama<\/a> can download and run many text embeddings models. There is a trade-off between the size of the models, the number of vector dimensions they return, and their effectiveness.<\/p>\n\n\n\n

The models I commonly use are:<\/p>\n\n\n\n

Model Name<\/strong><\/td>Vector Dimensions<\/strong><\/td><\/tr>
all-minilm<\/td>384<\/td><\/tr>
nomic-embed-text<\/td>768<\/td><\/tr>
mxbai-embed-large<\/td>1024<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Even the all-minilm<\/strong> model is surprisingly effective with standard text. The number of dimensions is important, as vector data can quickly become some of the largest data in your databases if you aren’t careful.<\/p>\n\n\n\n

How to test the Caddy proxy<\/h2>\n\n\n\n

We need to start by enabling the ability to call REST-based services:<\/p>\n\n\n\n

EXEC sp_configure 'external rest endpoint enabled', 1;\nRECONFIGURE;\nGO<\/pre><\/div>\n\n\n\n
That allows us to use the sp_invoke_external_rest_endpoint<\/code> system stored procedure<\/a> to make REST-based calls.<\/p>\n\n\n\n
Now that we have Ollama and Caddy up and running, we can check that SQL Server can actually call them by executing the following code:<\/p>\n\n\n\n
DECLARE @ReturnCode int;\nDECLARE @Response nvarchar(max);\n\nEXEC @ReturnCode = sys.sp_invoke_external_rest_endpoint\n  @method  = 'GET',\n  @url     = 'https:\/\/localhost:8443\/api\/tags',\n  @headers = N'{\"Accept\":\"application\/json\"}',\n  @response = @Response OUTPUT;\nSELECT @ReturnCode AS ReturnCode,\n        @Response as Response;<\/pre><\/div>\n\n\n\n
You should see a http status code of 200 returned:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
However, if you receive an error that looks like a certificate error, you’ll need to make sure you trust the root certificate for Caddy. Open PowerShell<\/a> and, in the folder where you have downloaded Caddy, execute the following:<\/p>\n\n\n\n
.\\caddy_windows_amd64.exe environ<\/code><\/p>\n\n\n\n
Look for the value of the caddy.AppDataDir<\/code>. On my system, that was C:\\Users\\Greg\\AppData\\Roaming\\Caddy<\/code>. In that folder, navigate to the subfolder pki\\authorities\\local<\/code> and, in here, you’ll find the Caddy root certificate root.crt<\/code>. <\/p>\n\n\n\n
Using Windows certificate manager (as an administrator), import that certificate into the Certificates subfolder under Trusted Root Certification Authorities<\/strong>. If you have old Caddy certificates, you should remove them first. You’ll then need to restart Caddy to complete the process.<\/p>\n\n\n\n
How to retrieve text embeddings via SQL Server REST<\/h2>\n\n\n\n
Given we can make REST-based calls, we could just directly call the Ollama service via the Caddy proxy by doing this:<\/p>\n\n\n\n
DECLARE @Payload nvarchar(max) =\nN'{\"model\":\"nomic-embed-text\",\"prompt\":\"How many products are in stock?\"}';\nEXEC @ReturnCode = sys.sp_invoke_external_rest_endpoint\n  @method  = 'POST',\n  @url     = 'https:\/\/localhost:8443\/api\/embed',\n  @payload = @Payload,\n  @headers = N'{\"Content-Type\":\"application\/json\"}',\n  @response = @Response OUTPUT;\nSELECT @ReturnCode AS ReturnCode,\n       @Response as Response;<\/pre><\/div>\n\n\n\n
If you inspect the returned response, you’ll find the vector for the embeddings.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Using CREATE EXTERNAL MODEL <\/h2>\n\n\n\n
\n    
\n\n
You may also be interested in…
<\/strong>SQL Server 2025 CREATE EXTERNAL MODEL and AI_GENERATE_EMBEDDINGS Commands Explained<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
Now, while we could<\/em> then extract the embeddings from that returned JSON<\/a>, having all this code every time we want to call a model is messy. Instead, SQL Server allows us to define an EXTERNAL MODEL<\/code> that remembers<\/em> how to call the model:<\/p>\n\n\n\n
CREATE EXTERNAL MODEL AllMiniLM\nAUTHORIZATION dbo\nWITH \n(\n      LOCATION = 'https:\/\/localhost:8443\/api\/embed',\n      API_FORMAT = 'Ollama',\n      MODEL_TYPE = EMBEDDINGS,\n      MODEL = 'all-minilm'\n);<\/pre><\/div>\n\n\n\n
The model isn’t a schema-bound object, so we don’t have a schema as part of the name. The LOCATION<\/code> is just the URL we need to call – or the folder if we’re using an ONNX<\/a> (Open Neural Network Exchange) runtime. <\/p>\n\n\n\n
The API_FORMAT<\/code> lets SQL Server know what to expect when calling and receiving a response. Currently, it can be one of either Azure OpenAI<\/a>, OpenAI<\/a>, Ollama, or ONNX Runtime.<\/p>\n\n\n\n
The MODEL_TYPE<\/code> is curious. At present, it can only be EMBEDDINGS<\/code>. However, whenever you see a required parameter and only one permitted value, you just know that the SQL Server team are considering other uses for this.<\/p>\n\n\n\n
Finally, we specify the model to use, and that’s it. It’s now easier for SQL Server to make calls to the model.<\/p>\n\n\n\n
Using AI_GENERATE_EMBEDDINGS<\/h2>\n\n\n\n
Once the external model is created, we can call it by using the AI_GENERATE_EMBEDDINGS<\/code> function. That simplifies the code for working with embeddings.<\/p>\n\n\n\n
Do bear in mind that, before you can use it, that external AI runtimes must be enabled. Depending on the specific version of SQL Server you’re using, you might also need to enable preview features:<\/p>\n\n\n\n
EXECUTE sp_configure 'external AI runtimes enabled', 1;\nRECONFIGURE WITH OVERRIDE;\nGO\nAnd then we can call a new function to easily retrieve embeddings.\nSELECT AI_GENERATE_EMBEDDINGS(N'SQL Server loves AI' \n       USE MODEL AllMiniLM);<\/pre><\/div>\n\n\n\n
Note that the model that I mentioned here is the name we gave to the external model. I often name the external model based on the text model I’m calling.<\/p>\n\n\n\n
Summary and next steps<\/h2>\n\n\n\n
Creating an external model and then calling it via AI_GENERATE_EMBEDDINGS<\/code> makes it easy to work with text embeddings in SQL Server. One further aspect to consider is which<\/em> text to generate the embedding for. SQL Server has provided the AI_GENERATE_CHUNKS<\/code> function to help us with that. We’ll explore that in the next article.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Simple Talk is brought to you by Redgate Software<\/h3>\n                
\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                                            Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: How to call an Ollama-based AI text embeddings model from SQL Server 2025<\/h2>\n\n                        
1. Which Ollama text embedding model should I use with SQL Server?<\/h3>\n            
\n                
Common choices are all-minilm<\/code> (384 dimensions), nomic-embed-text<\/code> (768 dimensions), and mxbai-embed-large<\/code> (1024 dimensions). Even all-minilm<\/code> is surprisingly effective for standard text, and fewer dimensions help keep vector storage manageable.<\/p>\n            <\/div>\n                    
2. How do I enable SQL Server to call Ollama via REST?<\/h3>\n            
\n                
Run sp_configure 'external rest endpoint enabled', 1<\/code> followed by RECONFIGURE<\/code>. This allows you to use sp_invoke_external_rest_endpoint<\/code> to make HTTPS calls to Ollama through your Caddy proxy.<\/p>\n            <\/div>\n                    
3. What is CREATE EXTERNAL MODEL in SQL Server?<\/h3>\n            
\n                
CREATE EXTERNAL MODEL<\/code> defines a reusable reference to an embedding model, storing its URL, API format (Ollama, OpenAI, Azure OpenAI, or ONNX Runtime), and model name, so you don’t have to write REST call code every time.<\/p>\n            <\/div>\n                    
4. How do I generate embeddings in SQL Server with AI_GENERATE_EMBEDDINGS?<\/h3>\n            
\n                
After enabling external AI runtimes and creating an external model, call AI_GENERATE_EMBEDDINGS(N'your text' USE MODEL YourModelName)<\/code>. This returns the vector directly, avoiding the need to parse JSON from raw REST responses.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Learn how to call locally hosted Ollama embedding models from SQL Server using sp_invoke_external_rest_endpoint, CREATE EXTERNAL MODEL, and AI_GENERATE_EMBEDDINGS.…<\/p>\n","protected":false},"author":346483,"featured_media":111067,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[159169,143523,53,143524],"tags":[159075,4168,4170,159401,159400,4150,4151],"coauthors":[159368],"class_list":["post-111077","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ai","category-databases","category-featured","category-sql-server","tag-ai","tag-database","tag-database-administration","tag-greglowollamaseries","tag-ollama","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111077","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/346483"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=111077"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111077\/revisions"}],"predecessor-version":[{"id":111120,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111077\/revisions\/111120"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/111067"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=111077"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=111077"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=111077"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=111077"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}