Recent Discussions
Truncate and load data
Hi Expert after truncate and load data ... how we can roll back to original data ✅ TEST CASES – PnLID Realignment Fix Objects: CAM.factCancelAndAmends CAM.factCancelAndAmendsEOM CAM.factPnL 🟥 PRE-TEST CASES (Before Fix) Pre-Test Case 1: Identify mismatched PnLID (Daily table) Objective: Verify that Cancel & Amend records reference outdated PnLIDs. Query: SELECT CA.ID AS CancelAmendID, CA.TradeRef, CA.ReportingDateID, CA.PnLID AS Old_PnLID, P.PnLID AS New_PnLID, P.TradeID, P.DealCode FROM CAM.factCancelAndAmends CA JOIN CAM.factPnL P ON CAST(P.TradeID AS VARCHAR(50)) = CA.TradeRef AND P.ReportingDateID = CA.ReportingDateID WHERE CA.PnLID <> P.PnLID; Expected Result (Pre): Records returned Old_PnLID ≠ New_PnLID Confirms PnL reload mismatch Actual Result (Pre): Records returned (e.g. TradeRef 5654234, 5978899) Mismatch confirmed ✅ Pre-Test Case 2: Broken join using PnLID Objective: Confirm that joins on PnLID fail. Query: SELECT CA.TradeRef, CA.ReportingDateID, CA.PnLID, P.PnLID AS FactPnL_PnLID FROM CAM.factCancelAndAmends CA LEFT JOIN CAM.factPnL P ON CA.PnLID = P.PnLID WHERE P.PnLID IS NULL; Expected Result (Pre): Rows returned Indicates broken references Actual Result (Pre): Rows returned ✅ Pre-Test Case 3: EOM table mismatch Objective: Confirm the same issue exists in EOM snapshot. Query: SELECT CA.TradeRef, CA.ReportingDateID, CA.PnLID AS Old_PnLID, P.PnLID AS New_PnLID FROM CAM.factCancelAndAmendsEOM CA JOIN CAM.factPnL P ON CAST(P.TradeID AS VARCHAR(50)) = CA.TradeRef AND P.ReportingDateID = CA.ReportingDateID WHERE CA.PnLID <> P.PnLID; Expected Result (Pre): Rows returned Actual Result (Pre): Rows returned (5 rows) ✅ 🟧 FIX EXECUTED Updated PnLID in: CAM.factCancelAndAmends → 2 rows CAM.factCancelAndAmendsEOM → 5 rows 🟩 POST-TEST CASES (After Fix) Post-Test Case 1: Validate PnLID alignment (Daily table) Objective: Ensure PnLID now matches factPnL. Query: SELECT CA.TradeRef, CA.ReportingDateID, CA.PnLID, P.PnLID AS FactPnL_PnLID FROM CAM.factCancelAndAmends CA JOIN CAM.factPnL P ON CA.PnLID = P.PnLID WHERE CA.TradeRef IN ('5571154', '5769294'); Expected Result (Post): CA.PnLID = FactPnL_PnLID No mismatch Actual Result (Post): IDs match exactly ✅ Post-Test Case 2: No orphan PnL references (Daily) Objective: Ensure all Cancel & Amend records link correctly. Query: SELECT COUNT(*) AS BrokenPnLReferences FROM CAM.factCancelAndAmends CA LEFT JOIN CAM.factPnL P ON CA.PnLID = P.PnLID WHERE P.PnLID IS NULL; Expected Result (Post): 0 Actual Result (Post): 0 ✅ Post-Test Case 3: Validate EOM table alignment Objective: Ensure EOM snapshot is also corrected. Query: SELECT CA.TradeRef, CA.ReportingDateID, CA.PnLID, P.PnLID AS FactPnL_PnLID FROM CAM.factCancelAndAmendsEOM CA JOIN CAM.factPnL P ON CA.PnLID = P.PnLID WHERE CA.TradeRef IN ('5571154', '5769294'); Expected Result (Post): All PnLIDs match Actual Result (Post): All matched ✅ 🟢 FINAL TEST CONCLUSION ✔ Root cause confirmed: PnL reload regenerated PnLIDs ✔ Fix applied successfully ✔ Daily and EOM tables aligned ✔ No orphan PnL references remain
Compat 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. ---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. ---SQL Server 2025 VECTOR functions accepting JSON array strings
Hello! Playing with new VECTOR type and functions, I can read following https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-distance-transact-sql?view=sql-server-ver17 for vector parameters: An expression that evaluates to vector data type. To me this means that any expression (including character strings as JSON-arrays) can be used as parameter. Since INSERT statement accepts to convert a JSON-array string to a VECTOR, I would expect that these function also accept this conversion. However, it appears that we are forced to cast the JSON-array to VECTOR. Any chance to improve this? Here some T-SQL example: declare v1 as VECTOR(3) = '[-1,1,0]' declare S1 as VARCHAR(50) = '[-1,1,0]' drop table tab1; create table tab1 (pkey int not null primary key, emb vector(3)); insert into tab1 values ( 101, v1 ); insert into tab1 values ( 102, S1 ); select * from tab1 order by pkey; select vector_distance('cosine',emb,@v1) from tab1; select vector_distance('cosine',emb,@s1) from tab1; -- fails Seb
Clarification on redistributability of MSSQL JDBC Driver 8.4 components
I am reviewing the licensing terms for Microsoft JDBC Driver 8.4 for SQL Server, specifically for the native authentication library mssql-jdbc_auth-8.4.1.x64.dll and mssql-jdbc-8.4.1.jre8.jar. The license terms state that certain components are redistributable if they are listed in a REDIST.TXT file included with the software. However, in the JDBC Driver 8.4 package (downloaded from the official Microsoft site), we are unable to locate REDIST.TXT file. https://go.microsoft.com/fwlink/?linkid=2137600 Could someone please clarify the following: Are mssql-jdbc_auth-8.4.1.x64.dll and mssql-jdbc-8.4.1.jre8.jar considered redistributable under the Microsoft JDBC Driver 8.4 license terms? If so, is there official documentation or an alternative location where the redistributable components for this driver are listed? If one or both components are not redistributable, what is the recommended and supported approach for applications that require integrated authentication when deployed to customer environments? This clarification is important for us to ensure compliance with Microsoft licensing requirements when distributing our application. Thank you for your assistance.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?
Cannot login to Analyses Services SQL 2025 Standard
Hello, I have installed SQL Server 2025 Standard Developer and no way to login to Analyses Services. I have latest version of SSMS (so drivers/libraries should not be an issue): I keep getting this: I've even tried with connection string with server name and instance - no luck. I know that after installing SQL 2025 Server Enterprise, there is no such problem with Analyses Services. Any help would be very appreciated. Best regards!
Database Level Collation Issues
Some background information. In the past we had a database where we didn't really care about collation at the database, table/column, functions and views. Though now we have a requirement due to a technology acquisition, requires SQL_Latin1_General_CP1_CI_AS. Microsoft seems to not have a simple method that guarantees changing the collation throughout the database on complex DBs with large amounts of tables, columns, functions, etc... specially with high dependencies, FKs and PKs and not affect any of the data (no moving the data is not simple). I have a command, where I stop all of the SQL services and any connections to the DB and use the following command line to change the collation to the one I specify. The issue is this command only works about 65% of the time and no clue to the why it fails the other 35%. The command used is: The parameter "-s" is necessary only if you have a named instance, remove the -s if no instance. sqlservr -m -T4022 -T3659 -s"SQL2019" -q"SQL_Latin1_General_CP1_CI_AS" or sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1_CI_AS" When this does not work, I have a set of 6 SQL scripts to tear the database apart in a complex method to get them operational. Does anybody have any idea why the hit and miss on the command? Or a better method / way to fully change the DB collation?
Major increase in transaction log backup size after upgrading to SQL Server 2022
Has anyone experienced a major increase in transaction log backup size after upgrading to SQL Server 2022? I recently upgraded one of our SQL Server environments from SQL Server 2019 to SQL Server 2022 (migrated databases to new SQL Server 2022 instances). After the upgrade, I noticed that transaction log backup sizes increased by 3× to 5×, even though: No application changes were made Workload and data change volume should be roughly the same Backup schedule remained the same (log backups every 30 minutes) Backup compression is default (same as before) The database is part of an Always On Availability Group running in synchronous commit mode Metrics I obeserved: Transaction log backup file size — increased 3× to 7× Log Write Throughput/sec — more than doubled Write Log Throughput — increased after upgrade Background Write Pages/sec — went up as well Log Bytes Flushed/sec Bytes Sent to Replica/sec — increased post-upgrade Backup/Restore Throughput/sec — increased from ~13 MB/sec → ~38 MB/sec Bytes Sent to Replica/sec (Always On counter) — increased New hardware is used — previous physical disk sector size was 4K, now it's 512 bytes Same Amount of RAM and CPUStruggling with AI/ML and Python installation on MSSQL2025 GA
I swear that i did not have any issues installing AI/ML on CTP2.1, don't believe i tried it on RC0 or RC1, but gosh is installing python, R difficult on GA! wow ! Can Microsoft or some good soul please share exact steps on installing AI/ML on 2025GA w/ Python 3.10 and also please share all of the exact versions needed and the icals (permission setups), Also I'm confused with this virtual account vs domain account setups. Also can i use Python 3.13 or 3.14 ? or am i locked into 3.10 only ? Does any one have the exact steps on Windows 11 Pro for a SQL Server 2025 Enterprise Development environment ? I see this article but its so confusing : https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver17&viewFallbackFrom=sql-server-ver15Determine a Value from a customized field in a select statement.
I am new. I am looking to determine the value of a customized field based on the value of a previous customized field. Here is what I have so far: select c.*, CASE WHEN c.[Case End Date] is null Then DATEDIFF(day,c.[Case Start Date], getdate()-1) ELSE DATEDIFF(day,c.[Case Start Date], c.[Case End Date]) END as [Days Open], CASE WHEN [DAYS OPEN] > 60 THEN 1 ELSE 0 END AS [60DAYSORMORE] From d_case as c The issue I have is that the system will not recognize my [Days Open] as a field. Msg 207, Level 16, State 1, Line 9 Invalid column name 'DAYS OPEN'. How can I make this work in Microsoft SQL Server Management Studio?I need to find a string in a database without knowing table or field
I have a database that contains approximately 130 tables. It's tied to a proprietary application, and I have no way to find its structure except by reverse engineering. But I only need to find the table that contains one particular field in the application's UI, so I thought I'd take a shortcut: use Windows Explorer to search the Microsoft SQL Server directory (and children) for a file that contains a string that I know is in the database. I did this, and got zero matches. I checked how SQL Server stores CHAR and VARCHAR fields. I found that it stores them in UTF-8 encoding. OK, I thought, perhaps Windows Explorer can't find strings encoded in UTF-8. Or perhaps it's programmed to ignore files in the SQL Server folder, like it ignores files in Git repositories. So I tried searching for the string with Notepad++'s Find in Files function. And again, found nothing. What is going on here? Is there any way I can search the raw database files without using SQL Server itself? Alternatively, is there some way I can search an entire database for a string without knowing what column or table it is in?Exporting a database: "Choose a data source." What???
I'm trying to export a SQL Server database, and things aren't going well. I hope someone can help. First, the problem I've solved. I mention it because it may be a clue to what went wrong next. I started SSMS (version 21.5.14+6.36429.23, if that matters), right clicked the database I want to export, and selected Tasks > Export Data. Whoops... Export Data was disabled. I researched this and learned that I must need to install the Import/Export Wizard. Which made no sense, because Import was enabled, and I have imported a database before. (If I hadn't, I'd have none to export!) I tried to (re)install the Wizard, and that fixed the problem, although I still don't understand how it could have. So, I clicked Export Data and got a Welcome screen. I clicked Next and got a screen titled Choose a Data Source. This is incomprehensible to me. I'm running the SQL Server Import/Export Wizard. I got there by right-clicking a particular database in a particular server. The only possible data source is that database. But the Wizard wasn't happy with that, and offered me a dropdown list of 13 options that meant nothing to me. I figured I couldn't go wrong with the default, but I did. The default was "Net Framework Data Provider for Odbc." When I clicked Next, I got an error box that said, "The operation could not be completed. / The ConnectionString property has not been initialized. (System.Data)" I could look up the meaning of the message, but I'm not sure that's what I want to know. Or I could look up the meanings of all 13 options in the dropdown and try to figure out which one to use to export a database, but I've got other things I need to do today. I came here.Failure to run query
Hi, ALL, First I believe that this question is more suited for a C++ devs,, but I can't find it. Sorry. I'm trying to run following query: query1= L"SELECT name FROM sys.key_constraints WHERE type = 'PK' AND parent_object_id = OBJECT_ID(?.?.?);"; with the code below: cbLen[0] = cbLen[1] = cbLen[2] = SQL_NTS; ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt ); ret = SQLPrepare( m_hstmt, qry.get(), SQL_NTS ); ret = SQLBindParameter( m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, m_maxIdLen, 0, catalog.get(), 0, &cbLen[0] ); ret = SQLBindParameter( m_hstmt, 2, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, m_maxIdLen, 0, schema.get(), 0, &cbLen[1] ); ret = SQLBindParameter( m_hstmt, 3, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, m_maxIdLen, 0, table.get(), 0, &cbLen[2] ); ret = SQLExecute( m_hstmt ); that code errors out with the error message "SQL syntax error near p2". Am I not allowed to arametrize this query like that? Thank you.Temporal Table - Change the ValidFrom Time
Sql Server 2022 - We needed a new 'Bonus' value to be active from 11/01/2025 06:00:00. I created a one time SQL job to update the temporal table for the Bonus value (going from 2.175 to 2.00) to run at 11/01/25 06:00:00. In the temporal table, the valid from is (converted to the local time) 2025-11-02 06:00:00.5656752 -06:00. If I pass FOR SYSTEM_TIME FROM '2025-11-02 06:00:00' TO '2999-01-01 23:59:59.999', I will get the previous since the job was just over a 1/2 second late. I've read articles talking about turn off versioning, then updating the History and current tables, then turning versioning back on. Seems like thats not possible with a generate always ValidFrom. I tried to create a temporal table where Validfrom as GENERATED BY DEFAULT but that didn't work. Is there a way I can get my validFrom to exactly 11/01/2025 06:00:00? Is there a better way in the future to get things set to exactly a desired time?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
Recent Blogs
- 1 MIN READVersion 18.6 of the Microsoft ODBC Driver 18 for SQL Server has been released. Version 18.6 brings some minor changes and fixes to the driver. Added Support for the new vector data type (float3...Dec 17, 2025
- This Document helps to troubleshoot or Resolve Replication performance related issues. Get information about “Topology Big Picture”:- Before you dive into solving any issue, you need to fully und...Dec 12, 2025
