Recent Discussions
A bizarre situation - SQL query finds no records when record is present.
I am trying to troubleshoot a bizarre situation, and looking for either an explanation of the cause, or assistance in determining the cause. The situation is esoteric (to say the least), so I'll need to paint a picture. I am using SQL 2019 Enterprise for testing. Our database has Allow Snapshot Isolation set to true, and the Is Read Committed Snapshot On is also true. We have a table called T_App_View, with essentially these important fields (plus some audit fields not shown): CREATE TABLE T_App_View ( C_Id int not null, C_Type varchar( 20 ) not null, C_Entity varchar( 250 ) not null, C_Specialisation varchar( 250 ) null, CONSTRAINT APPVIEW_PKC PRIMARY KEY NONCLUSTERED( C_Id ) ) We have an index on (Type, Entity, Specialisation): create unique index APPVIEW_I01 on T_App_View( C_Type, C_Entity, C_Specialisation ) And from a historical hangover we have a view against the table that is used for queries: CREATE VIEW dbo.VS_App_View AS SELECT * FROM dbo.T_App_View WITH CHECK OPTION The table is used to define "UI views" for our application, and contains about 1000 records. As part of commissioning a new database, we use a tool to load data into this table, and then the data basically sits there unchanged. Each time the tool is run it deletes any old data in the table, before populating the new data. The tool deletes the old data row-by-row rather than using a truncate table statement or similar. When our web tier is asked to provide the "definition" of a view, it performs a query on VS_App_View using the three compound key components of Type, Entity, and Specialisation. A dynamic cursor is used (a historical hangover from many years ago). A typical query would look similar to this: SELECT appview.C_Id, appview.C_Entity_Id, appview.C_Specialisation, appview.C_View_Type FROM dbo.VS_App_View appview WHERE appview.C_View_Type = 'DETAIL' AND appview.C_Entity_Id = 'a' AND appview.C_Specialisation = 'Finance' We are using the standard isolation level of Read Committed (which means Read Committed Snapshot in reality). Notice the select statement is not using an option of READCOMMITTED in the query. The fun part Sometimes when we do a lookup in SQL Server, the above query finds no matching records. This bug is very evasive, we cannot reproduce it, and it appears maybe once or twice a month, and other months not at all. It is not limited to specific key values, it just appears to happen randomly (or the pattern is obscure enough that we cannot yet see it). Our QA team experienced the find failure the other day and one of our developers jumped into SSMS and ran the query manually and sure enough it came back with no records found. And now the bizarre bit: The developer ran the query directly against the T_App_View table, and the record appeared. He then re-ran the query against the View, and the record appeared. We have the Query Store enabled for this database and I can see two plans matching the above query (one for a null specialisation, and one for a non-null specialisation). The query plans look odd (due to the dynamic cursor presumably) because even though the index guarantees a maximum of one matching record, the query plan still shows a temporary table being constructed along with a clustered index for that table. I feel like I am reasonably proficient in SQL Server but this one has me stumped. ChatGpt suggested it is a query plan issue, but I'm skeptical as I've never heard of a query plan not finding a record. I've only experienced query plan issues in terms of performance impacts. Has anyone ever come across such a situation? Is Snapshot Isolation part of the problem here, or database statistics, or the fact we are deleting an entire table of records individually and then repopulating them (resulting in bad statistics)? Should we have used WITH( READCOMMITTEDLOCK ) for this query perhaps? Any suggestions on how to troubleshoot effectively, as in what to look at and for? Thanks in advance for any help.
Documentation contradictory
Hi, ALL, Page https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver17&tabs=sqlpool states [quote] SIZE, MAXSIZE, and FILEGROWTH parameters can be set when a UNC path is specified for the file. [/quote] However later on that same page it states [quote] SIZE can't be specified when the os_file_name is specified as a UNC path. [/quote] I think those 2 sentences contradicts each other.....
Kerberos double hop delegation on SQL Linked Server fails on AG listener after RC4 disablement
Environment 3 Node- SQL Server 2022 with Availability Group Windows Server 2022 - Linked Server configured with Kerberos delegation (double-hop scenario) - RC4 encryption recently disabled via GPO The Problem After disabling RC4 in the domain (not sure of this root cause), Kerberos delegation through a Linked Server stopped working — but only when connecting via the **AG listener name**. Connecting via the **node name** works fine. I try to migrate my service account to gMSA and I've recreate all SPN and all delegation for the new account, but the issue is the same: Authentication works if the linked server us the node name, if it use the Listener AG name the connection fail with ------------------------------ Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456) Connection Id 6d654295-0538-4837-b900-ff65c9e86ee9 at 2026-04-29 11:59:25Z I Confirmed via Kerberos event logging (Event ID 4769 on DC)** On a healthy request (node name), ticket encryption type is `0x12` (AES256). - Confirmed SPN registration with `setspn -L` - Verified Kerberos events on the DC (4768/4769) - Confirmed forwardable flag (`0x40000000`) is present in ticket options — delegation is active - Confirmed pre-auth and session encryption are both `0x12` (AES256) for the working path set `msDS-SupportedEncryptionTypes = AES128+AES256` on the SQL service account and resetting its password rotete kdc key Are there any additional steps needed on the Linked Server or constrained delegation configuration side after the service account change? Any guidance appreciated. Thanks.
MS ODBC and OLE DB failed
Hello, In SQL Server 2022 (16.0.4250.1) showed two fails and can´t continue (see screenshot) On system are installed those versions of ODBC and OLE DB System was previously working (not stopped on this window for fail). We did repair of both installation and restart pc, but not helpful. Whta and how to repair it, please? Thank you.Is there a way to check for ODBC driver UNICODE support
Hi, ALL, I'm writing a C++ code to talk to a different DBMSes and wonder if there is a way to check programmatically for the UNICODE support. I know its a year 2026 and 99.9999% of the programs are using UNICODE, but many drivers are still being shipped as ANSI counterparts as well. Thank you for any pointers.How does GitHub Copilot in SSMS 22 handle database context collection before generating a response?
Hello, I am trying to better understand the internal workflow of GitHub Copilot in SSMS 22, especially for database-specific questions. From the product descriptions, it seems that Copilot can use the context of the currently connected database, such as schema, tables, columns, and possibly other metadata, when answering questions or generating T-SQL. However, I could not find clear official documentation about the actual sequence of operations. My main questions are: Before generating a response, does Copilot first collect database context/metadata from the active connection and then send that context to the LLM as grounding information? Or does it first use the LLM to interpret the user’s request, decide what information is needed, and then retrieve database metadata before generating the final answer? In some explanations, I have seen the phrase "Core SQL Copilot Infrastructure", but I cannot find any official documentation for that term. Is this an official component name? If so, what does it specifically refer to in the SSMS Copilot architecture? When Copilot answers schema-related or data-related questions, what information is retrieved automatically from the connected database, and is any SQL executed as part of that process? Is there any official architectural documentation that explains: context collection, prompt grounding, LLM invocation order, and whether query execution can occur before the final response is generated? I am asking because I want to understand the feature from both an architecture and data governance/security perspective. Any clarification from the product team or documentation links would be greatly appreciated. Thank you.SQL logshipping
Hi Everyone, has any experience an issue whereby you setup log shopping from SQL 2014 to SQL 2022 , when you choose the paths for data and logs to be stored on destination server it defaults to store both files on the Data drive instead of it being seperate drives like you specified? The initial log setup completes with no errors. Would appreciate any help i can get. Thanks RUnable to install SQL Server 2022 Express (installer glitch + SSMS error)
Hi, I recently purchased a new Lenovo laptop, and I am trying to install Microsoft SQL Server 2022 Express along with SSMS. SSMS installed successfully, but SQL Server installation fails, and sometimes the installer UI glitches or does not load properly. Because of this, I am getting connection errors in SSMS like "server not found" and "error 40". I am not very familiar with technical troubleshooting. Can someone guide me step-by-step in a simple way to install SQL Server correctly? Thank you.Migrate SQL 2016 to SQL 2022 - Detail Work Breadown Structre (WBS)
Hi, We’ve started a project to migrate from SQL Server 2016 to SQL Server 2022, and I’m currently preparing a detailed Work Breakdown Structure (WBS). Has anyone in this community gone through a similar migration and been willing to share their project WBS, either in .mpp or Excel format? Regards, Subhasish Roy
SQL Server 2025 Log Shipping Fails with Missing Assembly (sqllogship.exe) on Split-Drive Install
Hello, I am testing SQL Server 2025 in a lab environment and have encountered an issue with log shipping that appears to be related to assembly resolution. Environment: SQL Server 2025 (fresh install, both unattended and manual tested) Windows Server 2022 and Windows Server 2025 (issue occurs on both) SQL binaries installed on E:\ Default system drive is C:\ Issue: When log shipping runs (via SQL Agent job or manually invoking sqllogship.exe), it fails with the following error: Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=17.100.0.0... Observed Behavior: sqllogship.exe is located at:E:\Program Files\Microsoft SQL Server\170\Tools\Binn\ The required assemblies (e.g., Microsoft.SqlServer.ConnectionInfo.dll) are installed at:C:\Program Files\Microsoft SQL Server\170\Shared\MDS5xSMO\ The sqllogship.exe.config file in SQL Server 2025 includes explicit codeBase entries using relative paths:..\..\Shared\MDS5xSMO\Microsoft.SqlServer.ConnectionInfo.dll Because of this, the application attempts to resolve assemblies at:E:\Program Files\Microsoft SQL Server\170\Shared\MDS5xSMO\which does not exist by default. Workaround: Manually copying the shared SMO directory from C: to E: resolves the issue: C:\Program Files\Microsoft SQL Server\170\Shared\MDS5xSMO → E:\Program Files\Microsoft SQL Server\170\Shared\MDS5xSMO After doing this, log shipping works as expected. Comparison with SQL Server 2022: SQL Server 2022 sqllogship.exe.config is empty It does not rely on explicit codeBase paths Log shipping works without requiring any manual file copies Question: Is this expected behavior in SQL Server 2025, or a potential issue with how sqllogship.exe resolves shared assemblies when SQL is installed on a non-system drive? Specifically: Should Shared\MDS5xSMO also be installed on the same drive as the SQL binaries? Or should sqllogship.exe.config be updated to use absolute paths instead of relative ones? Would appreciate any confirmation or guidance from others who may have encountered this. Thanks!SQL Migration from SQL2017 to SQL2022
AG1: Win OS 2016, SQL 2017 AG2: Win OS 2019, SQL 2022 We are trying to migrate database from AG1 to AG2 via distributed AG. As the database is on different version, the status of the db on AG2 will be in Synchronized/In Recovery mode which is not readable. Are there any ways to verify the data integrity of the database as its not readable?
Best Practices for Connecting Internal SQL Server Financial Systems to Online Payment Platforms
I currently have an internal enterprise system used for purchasing, payments, and finance operations. The system runs on an on-premises database using Microsoft SQL Server and stores all financial transactions and internal workflow data. We now have a new requirement to enable online payment services for customers. These services will be exposed externally (likely in the cloud) and must interact with the same financial system so that transactions are reflected in our internal database. My main concerns are related to architecture, security, and data synchronization. Key points about the current setup: The core system and database are hosted internally (on-premises). The database contains sensitive finance and payment data. Internal processes depend on the current database structure and workflows. The new requirements: Develop an online payment service accessible over the internet. Ensure transactions from the online service update the internal system. Maintain data integrity and security. Avoid performance issues for the internal system. I’m evaluating a few possible approaches but I’m unsure which is best practice: Allow the cloud payment service to connect directly to the internal SQL Server database through secure networking. Maintain a replicated or read/write copy of the database in the cloud. Use SQL Server replication (transactional or snapshot) between on-prem and cloud. Introduce an API or middleware layer that handles all transactions and updates the internal database. Maintain separate databases and synchronize transactions asynchronously. My main questions: Is it recommended to expose the internal SQL Server database directly to cloud services? Should I use replication, a secondary database, or a service/API layer? What architecture pattern is commonly used for integrating on-prem financial systems with online payment platforms? How can we ensure consistency between internal transactions and online payments? Are there recommended SQL Server features or patterns for this scenario (replication, service broker, CDC, etc.)? Any advice on best practices, architecture patterns, or real-world implementations would be greatly appreciated.
Best architecture to integrate internal SQL Server system with cloud-based online payment services
I currently have an internal enterprise ERP system also need to be integrated with online payments, and finance operations. The system runs on an on-premises database using Microsoft SQL Server 2022 and stores all financial transactions and internal workflow data. We now have a new requirement to enable online payment services for customers. These services will be exposed externally (likely in the cloud) and must interact with the same financial system so that transactions are reflected in our internal database. My main concerns are related to architecture, security, and data synchronization. Key points about the current setup: The core system and database are hosted internally (on-premises). The database contains sensitive finance and payment data. Internal processes depend on the current database structure and workflows. The new requirements: Develop an online payment service accessible over the internet. Ensure transactions from the online service update the internal system. Maintain data integrity and security. Avoid performance issues for the internal system. I’m evaluating a few possible approaches but I’m unsure which is best practice: Allow the cloud payment service to connect directly to the internal SQL Server database through secure networking. Maintain a replicated or read/write copy of the database in the cloud. Use SQL Server replication (transactional or snapshot) between on-prem and cloud. Introduce an API or middleware layer that handles all transactions and updates the internal database. Maintain separate databases and synchronize transactions asynchronously. My main questions: Is it recommended to expose the internal SQL Server database directly to cloud services? Should I use replication, a secondary database, or a service/API layer? What architecture pattern is commonly used for integrating on-prem financial systems with online payment platforms? How can we ensure consistency between internal transactions and online payments? Are there recommended SQL Server features or patterns for this scenario (replication, service broker, CDC, etc.)? Any advice on best practices, architecture patterns, or real-world implementations would be greatly appreciated.SQL Server issue but don't know what - please help!
Hi, I'm facing a SQL Server focused issue that I don't understand why it's occurring and would like your help to identify and resolve, please. I will provide an in-depth breakdown of the scenario. Two years ago, I created a Azure Data Factory (ADF) Pipeline to take data from Azure Synapse to Azure SQL Server, and two other Pipelines to take data from an On-Premises Sage server to the same Azure SQL Server. These Pipelines were working perfectly up until two days ago (11/03/26) when the Pipelines still always complete successfully but the duration have increased greatly. The below screenshot shows the log of these three Pipeline for the past week. Note how prior to the 12/03/26 the Synapse Pipeline took about 6 minutes to complete and the two Sage Pipelines took around 25 seconds. Also note how on the 12/03/26 the Synapse Pipeline took over 3 hours but the two Sage Pipelines continued with their normal 25 seconds. Notice today (13/03/26) the Synapse Pipeline was still slow but now the two Sage Pipelines are taking over an hour. I'll note here that the Pipelines still complete successfully (so the config must be correct as it has been for two years). Each Pipeline contains a single (for Sage) or several (for Synapse) 'Copy data' objects. The objects have their source configuration to simply extract from the source (so either Synapse or Sage) and then the sink configuration has a 'Pre-Copy script' which simply Truncates the target SQL Server table, before loading to the SQL Server table. The screenshot below is one example - each Pre-Copy script is populated the same but just with the tables being different. When I look at the log for the Pipelines, I see a common theme for each and that is the 'Pre-Copy script' (the Truncate) is consuming 99% of the time. The screenshot below shows this common theme. So at this point, I ask the question why, after two years of all working well and completing very quickly, are the Pipelines taking so long to complete? This also seems to be an intermittent problem as I have performed several manual executions which will take a long while, then revert back to the quick several minutes again (so good), then revert once more to taking a long while again. It is intermittent. See the screenshot below. Notice how the same Pipeline have different durations. It's intermittent. The reason why I think it is a SQL Server focused issue for the following reasons: 1) Synapse - Performing simple commands (Select Top) returns data within seconds. 2) SQL Server - Performing simple commands (Select Top) usually takes seconds but during the past two days is often taking half hour. 3) SQL Server - When amending a SQL View it would take a split second to complete. During the past two days, it goes over ten minutes and still doesn't complete (I was amending the View for testing purposes). 4) SQL Server - When looking at the Views node under a database, during the past two days, it intermittently doesn't show the View. Sometimes, it will work if I log out and then back into SQL Server. 5) Power BI - Refreshing a Power BI report, whether from the Power BI Service or Desktop, call SQL Server Views. These SQL Server Views read from several SQL Server tables. These report refreshes are failing due to an IDbCommand interface error. These Power BI report data refreshes simply read from a SQL Server table via a SQL View. They don't reference or consider at all the any ADF Pipelines. The Pipelines execute between 2am and 3am each morning. The Power BI report refresh their data around 12pm to 5pm. As SQL Server seems to be problematic at the point of the 'Pre-Copy script', which is one of the end to end process, and then problematic at the very other end where Power BI reports consumes SQL Views - leads me to believe the issue is with SQL Server. I am a report developer and not an ADF expert. I've built the ADF process on the side. I haven't changed any development/configuration/etc... between this all working and failing. Our I.T. department have advised they have made no changes to contribute to this issue. Please can someone advise on what's happening here and why this issue has arose when for two years all was fine? Thanks.SSMS 22 - Not seeing the Database name information in the Status Bar and Title bar is gone.
I installed SSMS 22 yesterday and am trying to get used to the features. I am not a fan of the new layout. I might be in the future, but at the moment, not so much. Status Bar settings are all set to [True] But nothing is showing in the Status Bar. There is no Title Bar anymore. WHY Would they take away the Title Bar? That is insane. So, my question to the Forum is this. How do we get the Database Name information in the Status Bar? As stated, everything is set to TRUE in settings. Also, will the Status Bar show the title of the Editor file you are working on? (I miss the old design, to be honest. )Access to SQL Jobs
Hi, I have several jobs in SQL Server Agent. In the department there are 3 people who need administration access to the jobs. What kind of security access must they have? They already have SQL Agent Operator, SQL Agent Reader and SQL Agent User. As far as I know you can only edit jobs where you are the Owner. But, all 3 people must be able to edit all jobs. How to solve that issue?
Cannot connect Azure OpenAI Embeddings model to SQL Server 2025
On SQL Server 2025, I am trying to vectorize a table. To set up the ability for SQL Server 2025 to communicate with Azure OpenAI embeddings model, I first created a master key for encryption. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secret'; GO Then I set up a database scoped credential. CREATE DATABASE SCOPED CREDENTIAL [MyAzureOpenAICredential] WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"api-key":"secret"}'; Then I created an external model. CREATE EXTERNAL MODEL AzureOpenAIEmbeddingsModel WITH ( LOCATION = 'https://{secret}-eastus2.cognitiveservices.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2023-05-15', API_FORMAT = 'Azure OpenAI', MODEL_TYPE = EMBEDDINGS, MODEL = 'text-embedding-3-small', CREDENTIAL = [MyAzureOpenAICredential] ); However, when I run this simple script: DECLARE @text NVARCHAR(MAX) = N'SQL Server 2025 enables AI-powered applications'; DECLARE @embedding VECTOR(1536) = AI_GENERATE_EMBEDDINGS(@text USE MODEL AzureOpenAIEmbeddingsModel); I get this error. The database scoped credential 'MyAzureOpenAICredential' cannot be used to invoke an external rest endpoint. I have read through https://learn.microsoft.com/en-us/training/modules/build-ai-solutions-sql-server/4-integrate-ai-models pertaining to this task. As well as SQL Server 2025 docs for creating a model. I have also read SQL Server 2025 docs for creating https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver17. I have not found any answers.Polybase - Enforce TCP/IP Protocol
Hello, We know Polybase service in SQL server by default makes use of shared memory protocol to connect SQL server (local). I would like to know if there is any way we can change or force the connections to make use of TCP / IP protocol. Any help would be appreciated.
