SQL Timestamp Incremental Load Issue
An incremental load is implemented based on timestamp, using a view to update a fact table. • The fact table contains multiple KPIs coming from different source tables. • Two main KPI tables are included directly in the view and drive the incremental logic (their timestamps change). • Additional KPIs come from other source tables, which are LEFT JOINed to the view. During an incremental run: • Main source tables are updated → timestamp changes • Other source tables are NOT updated → timestamp unchanged • Because of the LEFT JOIN, missing values from these tables are returned as NULL • These NULLs overwrite existing values in the fact table Example Fact table (before load) id app_number score 1 333 5 Source tables • source1, source2 → timestamps updated (drive incremental load) • source3 → timestamp unchanged Stored procedure logic MERGE fact_table tgt USING ( SELECT app_number, ISNULL(score, 0) AS score FROM vw_main_kpis v LEFT JOIN source3 s3 ON v.app_number = s3.app_number ) src ON tgt.app_number = src.app_number WHEN MATCHED THEN UPDATE SET tgt.score = src.score; Result (incorrect) id app_number score 1 333 0 Existing data is lost.
Run a SQL Query with Azure Arc
Hi All, In this article, you can find a way to retrieve database permission from all your onboarded databases through Azure Arc. This idea is born from a customer request around maintaining a standard permission set, in a very wide environment (about 1000 SQL Server). This solution is based on Azure Arc, so first you need to onboard your SQL Server to Azure Arc and enable the SQL Server extension. If you want to test Azure Arc in a test environment, you can use the Azure Jumpstart, in this repo you will find ready-to-deploy arm templates the deploy demos environments. The other solution components are an automation account, log analytics and a Data collection rule \ endpoint. Here you can find a little recap of the purpose of each component: Automation account: with this resource you can run and schedule a PowerShell script, and you can also store the credentials securely Log Analytics workspace: here you will create a custom table and store all the data that comes from the script Data collection Endpoint / Data Collection Rule: enable you to open a public endpoint to allow you to ingest collected data on Log analytics workspace In this section you will discover how I composed the six phases of the script: Obtain the bearer token and authenticate on the portal: First of all you need to authenticate on the azure portal to get all the SQL instance and to have to token to send your assessment data to log analytics $tenantId = "XXXXXXXXXXXXXXXXXXXXXXXXXXX" $cred = Get-AutomationPSCredential -Name 'appreg' Connect-AzAccount -ServicePrincipal -Tenant $tenantId -Credential $cred $appId = $cred.UserName $appSecret = $cred.GetNetworkCredential().Password $endpoint_uri = "https://sampleazuremonitorworkspace-weu-a5x6.westeurope-1.ingest.monitor.azure.com" #Logs ingestion URI for the DCR $dcrImmutableId = "dcr-sample2b9f0b27caf54b73bdbd8fa15908238799" #the immutableId property of the DCR object $streamName = "Custom-MyTable" $scope= [System.Web.HttpUtility]::UrlEncode("https://monitor.azure.com//.default") $body = "client_id=$appId&scope=$scope&client_secret=$appSecret&grant_type=client_credentials"; $headers = @{"Content-Type"="application/x-www-form-urlencoded"}; $uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" $bearerToken = (Invoke-RestMethod -Uri $uri -Method "Post" -Body $body -Headers $headers).access_token Get all the SQL instances: in my example I took all the instances, you can also use a tag to filter some resources, for example if a want to assess only the production environment you can use the tag as a filter $servers = Get-AzResource -ResourceType "Microsoft.AzureArcData/SQLServerInstances" When you have all the SQL instance you can run your t-query to obtain all the permission , remember now we are looking for the permission, but you can use for any query you want or in other situation where you need to run a command on a generic server $SQLCmd = @' Invoke-SQLcmd -ServerInstance . -Query "USE master; BEGIN IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8' begin IF EXISTS (SELECT TOP 1 * FROM tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser end end ELSE begin IF EXISTS (SELECT TOP 1 * FROM tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser end end CREATE TABLE #TUser (DBName SYSNAME,[Name] SYSNAME,GroupName SYSNAME NULL,LoginName SYSNAME NULL,default_database_name SYSNAME NULL,default_schema_name VARCHAR(256) NULL,Principal_id INT); IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid FROM [?].dbo.sysUsers u LEFT JOIN ([?].dbo.sysMembers m JOIN [?].dbo.sysUsers r ON m.groupuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in (''public'',''dbo'',''guest'') ORDER BY u.name ' ELSE INSERT INTO #TUser EXEC sp_MSforeachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id FROM [?].sys.database_principals u LEFT JOIN ([?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' and u.TYPE <> ''S'' and u.name not in (''public'',''dbo'',''guest'') order by u.name '; SELECT DBName, Name, GroupName,LoginName FROM #TUser where Name not in ('information_schema') and GroupName not in ('public') ORDER BY DBName,[Name],GroupName; DROP TABLE #TUser; END" '@ $command = New-AzConnectedMachineRunCommand -ResourceGroupName "test_query" -MachineName $server1 -Location "westeurope" -RunCommandName "RunCommandName" -SourceScript $SQLCmd In a second, you will receive the output of the command, and you must send it to the log analytics workspace (aka LAW). In this phase, you can also review the output before sending it to LAW, for example, removing some text or filtering some results. In my case, I’m adding the information about the server where the script runs to each record. $array = ($command.InstanceViewOutput -split "r?n" | Where-Object { $.Trim() }) | ForEach-Object { $line = $ -replace '\', '\\' ù$array = $array | Where-Object { $_ -notmatch "DBName,Name,GroupName,LoginName" } | Where-Object {$_ -notmatch "------"} The last phase is designed to send the output to the log analytics workspace using the dce \ dcr. $staticData = @" [{ "TimeGenerated": "$currentTime", "RawData": "$raw", }]"@; $body = $staticData; $headers = @{"Authorization"="Bearer $bearerToken";"Content-Type"="application/json"}; $uri = "$endpoint_uri/dataCollectionRules/$dcrImmutableId/streams/$($streamName)?api-version=2023-01-01" $rest = Invoke-RestMethod -Uri $uri -Method "Post" -Body $body -Headers $headers When the data arrives in log analytics workspace, you can query this data, and you can create a dashboard or why not an alert. Now you will see how you can implement this solution. For the log analytics, dce and dcr, you can follow the official docs: Tutorial: Send data to Azure Monitor Logs with Logs ingestion API (Resource Manager templates) - Azure Monitor | Microsoft Learn After you create the dcr and the log analytics workspace with its custom table. You can proceed with the Automation account. Create an automation account using the creating wizard You can proceed with the default parameter. When the Automation Account creation is completed, you can create a credential in the Automation Account. This allows you to avoid the exposition of the credential used to connect to Azure You can insert here the enterprise application and the key. Now you are ready to create the runbook (basically the script that we will schedule) You can give the name you want and click create. Now go in the automation account than Runbooks and Edit in Portal, you can copy your script or the script in this link. Remember to replace your tenant ID, you will find in Entra ID section and the Enterprise application You can test it using the Test Pane function and when you are ready you can Publish and link a schedule, for example daily at 5am. Remember, today we talked about database permissions, but the scenarios are endless: checking a requirement, deploying a small fix, or removing/adding a configuration — at scale. At the end, as you see, Azure Arc is not only another agent, is a chance to empower every environment (and every other cloud provider 😉) with Azure technology. See you in the next techie adventure. **Disclaimer** The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
SQL Server 2005 (compatibility level 90)
Hello, I’m testing the behavior described in the SQL Server documentation for **compatibility level 90** regarding the special attributes `xsi:nil` and `xsi:type`: > “The special attributes `xsi:nil` and `xsi:type` can't be queried or modified by data manipulation language statements. This means that `/e/@xsi:nil` fails while `/e/@*` ignores the `xsi:nil` and `xsi:type` attributes. However, `/e` returns the `xsi:nil` and `xsi:type` attributes for consistency with `SELECT xmlCol`, even if `xsi:nil = "false"`. ” But on **SQL Server 2005**, I can successfully query `@xsi:nil` and it returns the expected value. I’m trying to reproduce the documented “`/e/@xsi:nil` fails” behavior, but I can’t. ### Environment - Product: **Microsoft SQL Server 2005** - Database compatibility level: **90** --- ## ✅ Repro script ```sql IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MyTestSchema') DROP XML SCHEMA COLLECTION MyTestSchema; GO CREATE XML SCHEMA COLLECTION MyTestSchema AS N' <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence> <xsd:element name="element" nillable="true" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'; GO DECLARE @xmlData XML(MyTestSchema) = N' <root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <element xsi:nil="true" /> </root>'; ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as xsi) SELECT @xmlData.query('<result> { /root/element/@xsi:nil } </result>') AS Typed_Result; ``` ### Actual result `Typed_Result` contains `xsi:nil="true"` under `<result>...`. ### Expected result (based on docs) I expected `/root/element/@xsi:nil` to fail, or not return `xsi:nil`. --- ## Questions 1. In the documentation, does “data manipulation language statements” mean only **XML DML** (i.e., `.modify()`), not XQuery used in `SELECT` with `.query()` / `.value()`? 2. Does the “`/e/@xsi:nil` fails” behavior apply only when the XML is stored in a **table column**, not when using an **XML variable**? 3. Is the behavior different between **typed XML** (with an XML schema collection) vs **untyped XML**? 4. Can someone provide a minimal reproduction in SQL Server 2005 where `/e/@xsi:nil` fails as described? Thank you. ---Deep dive into the SSMA Code Conversion Copilot Architecture
The Problem We Set Out to Solve Migrating from Oracle PL/SQL to SQL Server T‑SQL is notoriously complex. While SSMA’s rule engine covers hundreds of conversion rules, edge cases, custom logic, and nuanced syntax but it often slips through. Developers end up spending hours manually fixing scripts, validating correctness, and worrying about regressions. The Copilot was built to tackle this pain point: augment SSMA’s rule engine with large language models (LLMs) that can reason about tricky conversions, explain their logic, and accelerate the migration process. But building trust in AI‑generated code meant we had to design an architecture that was controllable, reliable, and secure. SSMA Code Conversion Copilot was released back in the month of May and some of the use cases are elaborated here. This blog talks about the inner working of Copilot. ⚙️ Semantic Kernel for Skill / Plugin Management At the heart of SSMA Copilot lies Semantic Kernel, Microsoft’s open‑source framework for integrating LLMs. It offers two big capabilities: Prompt management — defining prompts as reusable “skills” with parameters like model, temperature, and token count. Agentic orchestration — automating workflows by chaining tools and prompts together. For Copilot, we deliberately chose only prompt management at this point. We have also added native skills such as checking the correctness of syntax and semantics but have not used agentic orchestration for the current implementation. ❌ Why Not Agentic Features? Agentic orchestration can be powerful, but in practice it wasn’t reliable enough for production migrations. Tool selection logic sometimes failed, leading to incorrect validations or spurious edits. Moreover, we saw an issue with latency. Instead, we implemented a deterministic workflow that gave us full control. ✅ Manual Orchestration Workflow Our workflow looks like this (please refer to the diagram): Partial Migration: SSMA generates a baseline conversion. Copilot Authentication: The Copilot is authenticated using the inputs provided by the user. This is where the model is also decided. Alternately, the user can use the managed endpoint that is controlled by Microsoft. LLM Completion: Copilot fills in gaps. Moreover, it explains the solution, points out the error that it is trying to resolve in simple language. Parsing & Compilation: A target‑dialect parser checks syntax. This catches unsupported constructs or binding issues far more reliably than prompt tuning. Spurious Edit Detection: LLMs are instructed to only enhance flagged portions of code. Any edits to “correct” blocks incur penalties, with a strict threshold of zero spurious edits allowed. Query Execution & Data Generation: Where possible, we generate minimal synthetic data (two rows per table) to validate equivalence between source and target queries. Semantic Equivalence Checks: For cases where execution isn’t feasible, we use LLM‑based scoring to judge logical fidelity. This loop repeats until syntactic and semantic correctness is achieved. By using this workflow, we avoided regression spirals and ensured predictable outcomes across dialects. This workflow was tested using our built-in evaluation framework which has leveraged the rich test cases of SSMA. 🔑 Feature Comparison Managed Endpoint Authentication was released with SSMA 10.4 in November 2025. Managed Endpoint BYOK Provisioning OpenAI Endpoint No Yes LLM Model Selection Automatic Manual Authentication Mandatory Entra ID OpenAI Endpoint and Key Private Endpoint Support No Yes Cross Tenant Dependency* Yes No Pricing Free Consumption in actuals *Cross Tenant Dependency: The endpoint is hosted in Microsoft tenant while the authentication happens in the user tenant. 🔒 Privacy and Data Handling A critical point: we don’t store your data. The scripts you provide are used only for generating the migration output. Once the process completes, the data is flushed. No proprietary code or schema information is retained. This design ensures: Security: We run OpenAI in Microsoft tenant following all security protocols. Trust: Copilot is a tool, not a repository. Compliance: Aligns with enterprise privacy expectations. 🌟 Why This Matters By combining Semantic Kernel framework, SSMA Copilot delivers reliable migrations without sacrificing flexibility. And with the managed endpoint, it’s now easier and safer than ever to adopt — no keys, no storage, no friction. This isn’t just about faster migrations. It’s about building trust in AI‑assisted workflows, ensuring correctness, and giving enterprises confidence that their data is secure. Get started with your Copilot based migration journey using SSMA for OracleCompat level 90: XML string-to-datetime UDF
Hello, I’m testing a behavior described in SQL Server documentation for **database compatibility level 90**. The docs state that a user-defined function that converts an XML constant string value to a SQL Server date/time type is marked as **deterministic**. On **SQL Server 2005**, I’m seeing the opposite: the function is marked as **non-deterministic** (`IsDeterministic = 0`). I’m trying to understand whether I’m missing a requirement/constraint or whether this is a doc mismatch / version-specific behavior. ### Environment - Product: **Microsoft SQL Server 2005** - Database compatibility level: **90** --- ## ✅ Repro script ```sql IF OBJECT_ID('dbo.fn_ParamXmlToDatetime', 'FN') IS NOT NULL DROP FUNCTION dbo.fn_ParamXmlToDatetime; GO CREATE FUNCTION dbo.fn_ParamXmlToDatetime (@xml XML) RETURNS DATETIME WITH SCHEMABINDING AS BEGIN DECLARE @y DATETIME; -- Convert an XML value to DATETIME SET @y = CONVERT(DATETIME, @xml.value('(/r)[1]', 'datetime')); RETURN @y; END GO SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsDeterministic') AS IsDeterministic, OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsPrecise') AS IsPrecise; GO ``` ### Actual result `IsDeterministic = 0` (non-deterministic) ### Expected result (based on docs) `IsDeterministic = 1` (deterministic) for this pattern under compat level 90. --- ## Questions 1. Are there additional conditions required for SQL Server to mark this UDF as deterministic (for example, specific XQuery usage, avoiding `CONVERT`, using `CAST`, using `datetime2` doesn’t exist in 2005, etc.)? 2. Does the determinism rule apply only when converting from an **XML literal constant** inside the function, rather than an XML parameter value? 3. Is this behavior different for **typed XML** (XML schema collections) vs **untyped XML**? 4. Is this a known difference/bug in SQL Server 2005 where the UDF is functionally deterministic but still reported as non-deterministic by `OBJECTPROPERTY`? Thank you for any clarification. ---Query on sys.dm_db_index_usage_stats slow
Hi, we discovered that queries on sys.dm_db_index_usage_stats are getting very slowly when the sql server is running for a longer time without restart. The execution time is up to 30 seconds for the following query: SELECT object_name(object_id) objectName, last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id=db_id() We get the following query plan: The Actual Rows in LOGINDEXSTATS are about 2 million. We found 2 similiar cases by searching the internet: https://stackoverflow.com/questions/52165370/query-against-sys-tables-sys-dm-db-index-usage-stats-slow https://www.linkedin.com/pulse/sql-server-2014-object-dependencies-dmvdmf-slow-andrea-bruschetta We tested the workaround (UPDATE STATISTICS sys.*** WITH FULLSCAN;) without success. How can we increase performance without restarting the database? Regards Dominik
SQL Sever 2025 request with SQL connection
Hello, I installed SQL Server 2025 on a new VM, and I’m using a C# project to initialize my database based on a model (creating tables, foreign keys, default values, etc.). The process is extremely slow on SQL Server 2025: on my SQL Server 2022 environment, it takes about 30 minutes, but my first test on 2025 ran for over 4 hours. I’m connecting to SQL using a SQL authentication login (I tried both SA and a newly created account — same issue). I then tested using a Windows Authentication login, and surprisingly, the performance issue disappeared. Are there any known issues related to SQL authentication in SQL Server 2025?Assess and upgrade to SQL Server 2025 with SSMS Migration Component
Upgrade to SQL Server 2025 with confidence. Starting with SSMS 22, the Hybrid & Migration component now includes upgrade assessment for SQL Server 2025. This feature enables you to quickly evaluate readiness for upgrade. SSMS also provides a streamlined migration path to the instance of higher version if in-place upgrade is not preferred.Modern SQL Server Features That Make Life Better
🚀 Excited to share an upcoming session you won’t want to miss! 📌 Modern SQL Server Features That Make Life Better As data platforms evolve, staying ahead of the curve is essential for every database developer and administrator. This session dives into the latest advancements in SQL Server, including powerful capabilities introduced in SQL Server 2022 that transform the way we manage, optimise, and troubleshoot data workloads. 🔍 What you’ll learn: • How Intelligent Query Processing and Query Store simplify performance tuning and troubleshooting • The impact of Memory Grant Feedback and DOP Feedback on real-world workload performance • New T-SQL enhancements that help developers write cleaner, more efficient code • How temporal tables enable trending over time, point-in-time recovery, and fixing accidental data changes • Key modern features that make database operations more scalable, predictable, and efficient Whether you're a DBA or a developer, this session will equip you with practical insights to make your day-to-day work easier — and your SQL Server environments smarter. 💡Join us and elevate your SQL Server expertise! 🗓️ Date: 22 November 2025 ⏰ Time: 18:00 (CET) 🎙️ Speaker: Lee Markum 📌 Topic: Modern SQL Server Features That Make Life Better
Ollama on HTTPS for SQL Server
Here is a quick procedure to deploy an Ubuntu container with Ollama and expose its API over HTTPS. The goal is to allow a fast deployment, even for those unfamiliar with Docker or Language Models, making it easy to set up an offline platform for generating embeddings and using Small Language Models This is particularly useful when testing SQL Server 2025 for fully on-premises environment use cases, since SQL Server only allows access to HTTPS endpoints. However, HTTP remains open for testing purposes. Please note that this example is CPU-based, as deploying with (integrated) GPU support involves additional, less straightforward steps. This example is provided solely to illustrate the concept, is not intended for production use, and comes without any guarantee of performance or security. Prerequisites To continue, you need to have Docker Desktop, WSL and SQL Server 2025 (currently Release Candidate 1) Docker Desktop Install WSL | Microsoft Learn SQL Server 2025 Preview | Microsoft Evaluation Center Create a Dockerfile First, create a working directory. In this example, C:\Docker\Ollama will be used. Simply create a file named Dockerfile (without an extension) and paste the following content into it. FROM ubuntu:25.10 RUN apt update && apt install -y curl gnupg2 ca-certificates lsb-release apt-transport-https software-properties-common unzip nano openssl net-tools RUN curl -fsSL https://ollama.com/install.sh | bash RUN curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/gpg.key' | gpg --dearmor -o /usr/share/keyrings/caddy-stable-archive-keyring.gpg RUN curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/debian.deb.txt' | tee /etc/apt/sources.list.d/caddy-stable.list RUN apt update && apt install -y caddy RUN mkdir -p /etc/caddy/certs RUN cat > /etc/caddy/certs/san.cnf <<EOF [req] default_bits = 2048 prompt = no default_md = sha256 req_extensions = req_ext distinguished_name = dn [dn] CN = 127.0.0.1 [req_ext] subjectAltName = @alt_names [alt_names] IP.1 = 127.0.0.1 DNS.1 = localhost EOF RUN openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/caddy/certs/localhost.key -out /etc/caddy/certs/localhost.crt -config /etc/caddy/certs/san.cnf -extensions req_ext RUN echo "https://:443 {\n tls /etc/caddy/certs/localhost.crt /etc/caddy/certs/localhost.key\n reverse_proxy localhost:11434\n}" >> /etc/caddy/Caddyfile RUN echo "#!/bin/bash" > /usr/local/bin/entrypoint.sh && \ echo "set -e" >> /usr/local/bin/entrypoint.sh && \ echo "OLLAMA_HOST=0.0.0.0 ollama serve >> /var/log/ollama.log 2>&1 &" >> /usr/local/bin/entrypoint.sh && \ echo "caddy run --config /etc/caddy/Caddyfile --adapter caddyfile >> /var/log/caddy.log 2>&1 &" >> /usr/local/bin/entrypoint.sh && \ echo "tail -f /var/log/ollama.log /var/log/caddy.log" >> /usr/local/bin/entrypoint.sh && \ chmod 755 /usr/local/bin/entrypoint.sh ENTRYPOINT ["/usr/local/bin/entrypoint.sh"] For your information, this file allows the creation of an image based on Ubuntu 25.10 and includes: Ollama, for running the models Caddy, for the reverse proxy Creation of a certificate for the HTTPS endpoint on localhost Create the container After opening a Powershell terminal, execute the following commands: cd C:\Docker\Ollama #Build the image from the Dockerfile. docker build -t ollama-https . #Create a container based on the image ollama-https docker run --name ollama-https -d -it -p 443:443 -p 11434:11434 ollama-https #Copy the certificate created into the current Windows directory docker cp ollama-https:/etc/caddy/certs/localhost.crt . # Install the certificate in Trusted Root Certification Authorities Import-Certificate -FilePath "localhost.crt" -CertStoreLocation "Cert:\LocalMachine\Root" #Check Https (wget https://localhost).Content #Check Http (wget http://localhost:11434).Content Ollama is now running With a browser, connect to https://localhost Retrieve Models No model is retrieved when the image is created, as this depends on each use case, and for some models, the size can be substantial. Here’s a quick example for pulling an embedding model, Nomic, and a small language model, Phi3. Ollama Search docker exec ollama-https ollama pull nomic-embed-text docker exec ollama-https ollama pull phi3:mini A quick example with SQL Server 2025 A quick demonstration using the WideWorldImporters database (Wide World Importers sample database) use [master] GO ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 170 WITH ROLLBACK IMMEDIATE GO DBCC TRACEON(466, 474, 13981, -1) GO Note: With RC1, you can use the PREVIEW_FEATURES database-scoped configuration T-SQL Declare an external model for embeddings. use [WideWorldImporters] GO CREATE EXTERNAL MODEL NomicLocal AUTHORIZATION dbo WITH ( LOCATION = 'https://localhost/api/embed', API_FORMAT = 'ollama', MODEL_TYPE = EMBEDDINGS, MODEL = 'nomic-embed-text' ) to enable semantic search capabilities on StockItems, we will create a dedicated table to store embeddings (no chunking in this example) along with a vector index optimized for cosine similarity use [WideWorldImporters] GO CREATE TABLE [Warehouse].[StockItemsEmbedding](StockItemEmbeddingID int identity (1,1) PRIMARY KEY, StockItemId int, SearchDetails nvarchar(max), Embedding vector(768)) GO INSERT INTO [Warehouse].[StockItemsEmbedding] SELECT si.StockItemID, si.SearchDetails, AI_GENERATE_EMBEDDINGS(si.SearchDetails USE MODEL NomicLocal) /* Generate embeddings from declared external model */ FROM [Warehouse].[StockItems] si GO /* Check */ SELECT * FROM [Warehouse].[StockItemsEmbedding] GO CREATE VECTOR INDEX IXV_1 ON [Warehouse].[StockItemsEmbedding] (Embedding) WITH (METRIC = 'cosine', TYPE = 'DiskANN') GO /* User Input */ DECLARE @UserInput varchar(max) = 'Which product is best suited for shipping small items?' /* and Generate embeddings for user input */ DECLARE @UserInputV vector(768) = AI_GENERATE_EMBEDDINGS(@UserInput USE MODEL NomicLocal) DECLARE @ModelInput nvarchar(max) DECLARE Payload nvarchar(max) DECLARE Response nvarchar(max) /* Similarity Search on StockItems and Model Input creation*/ SELECT @ModelInput = STRING_AGG('ProductDetails: ' + sie.SearchDetails + 'UnitPrice: ' + CAST(si.UnitPrice AS nvarchar(max)), ' \n\n') FROM VECTOR_SEARCH( TABLE = [Warehouse].[StockItemsEmbedding] as sie, COLUMN = Embedding, SIMILAR_TO = @UserInputV, METRIC = 'cosine', TOP_N = 10 ) JOIN [Warehouse].[StockItems] si ON si.StockItemId = sie.StockItemId /* Generate payload for response generation */ SELECT = '{"model": "phi3:mini", "stream": false, "prompt":"You are acting as a customer advisor responsible for recommending the most suitable products based on customer needs, providing clear and personalized suggestions. Question : ' + @UserInput + '\n\nList of Items : ' + @ModelInput + '"}'; EXECUTE sp_invoke_external_rest_endpoint @url = 'https://localhost/api/generate', @method = 'POST', = , @timeout = 230, = OUTPUT; PRINT JSON_VALUE(@response, '$.result.response') LangChain You can also have a try with LangChain. Same demo with a small difference, there is no vector index created on the vector store table. The table has been modified, but only for demonstration purposes. Reference: SQLServer | 🦜️🔗 LangChain # PREREQ #sudo apt-get update && sudo apt-get install -y unixodbc # sudo apt-get update # sudo apt-get install -y curl gnupg2 # curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - # curl https://packages.microsoft.com/config/debian/11/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list # sudo apt-get update # sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 # pip3 install langchain langchain-sqlserver langchain-ollama langchain-community import pyodbc from langchain_sqlserver import SQLServer_VectorStore from langchain_ollama import OllamaEmbeddings from langchain_ollama import ChatOllama from langchain.schema import Document from langchain_community.vectorstores.utils import DistanceStrategy #Prompt for testing _USER_INPUT = 'Which product is best suited for shipping small items?' ############### Params ########################################## print("\033[93mSetting up variables...\033[0m") _SQL_DRIVER = "ODBC Driver 18 for SQL Server" _SQL_SERVER = "localhost\\SQL2K25" _SQL_DATABASE = "WideWorldImporters" _SQL_USERNAME = "lc" _SQL_PASSWORD = "lc" _SQL_TRUST_CERT = "yes" _SQL_VECTOR_STORE_TABLE = "StockItem_VectorStore" # Table name for vector storage _MODIFY_TABLE_TO_USE_SQL_VECTOR_INDEX = True #As vector index not considered currently in langchain and structure does not match vector index requirements _CONNECTION_STRING = f"Driver={{{_SQL_DRIVER}}};Server={_SQL_SERVER};Database={_SQL_DATABASE};UID={_SQL_USERNAME};PWD={_SQL_PASSWORD};TrustServerCertificate={_SQL_TRUST_CERT}" _OLLAMA_API_URL = "https://localhost" _OLLAMA_EMBEDDING_MODEL = "nomic-embed-text:latest" _OLLAMA_EMBEDDING_VECTOR_SIZE = 768 _OLLAMA_SLM_MODEL = "phi3:mini" # Model for SLM queries ################################################################### #Define Ollama embeddings embeddings = OllamaEmbeddings( model=_OLLAMA_EMBEDDING_MODEL, base_url=_OLLAMA_API_URL ) conn = pyodbc.connect(_CONNECTION_STRING) cursor = conn.cursor() #Drop embeddings table if it exists print("\033[93mDropping existing vector store table if it exists...\033[0m") cursor.execute(f"DROP TABLE IF EXISTS Warehouse.{_SQL_VECTOR_STORE_TABLE};") print("\033[93mConnecting to SQL Server and fetching data...\033[0m") cursor.execute("SELECT StockItemId, SearchDetails, UnitPrice FROM Warehouse.StockItems;") rows = cursor.fetchall() print(f"\033[93mFound {len(rows)} records to process\033[0m") # Create documents from the fetched data documents = [ Document( page_content=row.SearchDetails, metadata={ "StockItemId": row.StockItemId, "UnitPrice": float(row.UnitPrice) # Convert Decimal to float } ) for row in rows ] conn.commit() #Creating vector store print("\033[93mCreating vector store...\033[0m") vector_store = SQLServer_VectorStore( connection_string=_CONNECTION_STRING, distance_strategy=DistanceStrategy.COSINE, # If not provided, defaults to COSINE embedding_function=embeddings, embedding_length=_OLLAMA_EMBEDDING_VECTOR_SIZE, db_schema = "Warehouse", table_name=_SQL_VECTOR_STORE_TABLE ) print("\033[93mAdding to vector store...\033[0m") try: vector_store.add_documents(documents) print("\033[93mSuccessfully added to vector store!\033[0m") except Exception as e: print(f"\033[91mError adding documents: {e}\033[0m") #Vector index not yet integrated in SQL Server VectorStore (drop auto-created nonclustered PK and generating int clustered PK if (_MODIFY_TABLE_TO_USE_SQL_VECTOR_INDEX): print("\033[93mModifying structure to create vector index...\033[0m") cursor.execute("DECLARE @AutoCreatedPK sysname, @SQL nvarchar(max);" f"SELECT @AutoCreatedPK = name FROM sys.key_constraints WHERE type = 'PK' AND parent_object_id = object_id('Warehouse.{_SQL_VECTOR_STORE_TABLE}');" f"SELECT @SQL = 'ALTER TABLE Warehouse.{_SQL_VECTOR_STORE_TABLE} DROP CONSTRAINT ' + @AutoCreatedPK + ';'" "EXEC sp_executesql @SQL;" f"ALTER TABLE Warehouse.{_SQL_VECTOR_STORE_TABLE} ADD Alt_Id int identity(1,1);" f"ALTER TABLE Warehouse.{_SQL_VECTOR_STORE_TABLE} ADD CONSTRAINT PK_{_SQL_VECTOR_STORE_TABLE} PRIMARY KEY (Alt_Id);") conn.commit() print("\033[93mCreating vector index...\033[0m") cursor.execute(f"CREATE VECTOR INDEX IV_{_SQL_VECTOR_STORE_TABLE} ON [Warehouse].[{_SQL_VECTOR_STORE_TABLE}] (embeddings) WITH (METRIC = 'cosine', TYPE = 'DiskANN');") conn.commit() #Generate prompt then answer print(f"\033[92mUser Input: {_USER_INPUT}\033[0m") context = [ { "Item": doc.page_content, "UnitPrice": doc.metadata.get("UnitPrice", None) } for doc in vector_store.similarity_search(_USER_INPUT, k=3) ] llm = ChatOllama(model=_OLLAMA_SLM_MODEL,base_url=_OLLAMA_API_URL) prompt = ( f"You are acting as a customer advisor responsible for recommending the most suitable products based on customer needs, providing clear and personalized suggestions" f"Context: {context}\n\nQuestion: {_USER_INPUT}\n\n") response = llm.invoke(prompt) print(f"\033[36m{response.content}\033[0m") Note : If using devcontainer with VSCode add "runArgs": [ "--network=host" ] to devcontainer.json to allow connections to “localhost”. Import and install the previously created certificat docker cp C:\Docker\Ollama\localhost.crt <devcontainer name>:/usr/local/share/ca-certificates/localhost.crt docker exec <devcontainer name> "update-ca-certificates" Disclaimer The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
