Linked Server Selection Query Fails with "MS DTC has stopped this transaction"
Hi everyone, I’m currently setting up a Linked Server from our head office (server A) SQL Server to a subsidiary unit (Server B) SQL Server. The Linked Server connection tests successfully, and I can log into server A from server B and can using query. However, when I try to execute a query using a 4-part name through Linked server, I encounter the following error: Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction. Environment Details: Head Office (server A) SQL Server: SQL Server 2008 R2 (already upgraded pack SP3) Windows Server 2012 TLS 1.2 enabled MS DTC service is turned on Subsidiary (server B) SQL Server: SQL Server 2016 Windows Server 2016 Standard (64-bit) TLS 1.2 enabled MS DTC service is turned on Networking: The B server connects via VPN to be on the same network as server A Ping and Telnet tests (IP and port) from both sides work fine SQL login from server A to server B(via IP and port) is successful USING PROVIDER: i have try to using SQLNCLI11 and MSOLEDBSQL, but both till error DTC. Linked Server test connection: Success Update, insert query: it's ok, but when i try selection query, i got error DTC has stop this transaction. example: SELECT * FROM [LinkedServerName].[DatabaseName].[dbo].table Has anyone faced a similar issue? Could this be a MS DTC configuration mismatch or network security/firewall/DTC port range issue? Any guidance on how to properly configure MS DTC across different servers/domains/VPNs would be highly appreciated. Thanks in advance!
Bulk Insert causing duplicates in target table
We use ETL tool(C# based) which uses Bulkcopy method to do bulk insert to target table. Recently we are facing 2 Issues in our daily loads 1. Target table with no primary key - the load returns success but it inserts 5x times of the same record. It loads same records 5 times. 2. Target table with Primary Key - The load returns failure with primary key constraint but it inserts the rows into target table. DBA Team is unable to find anything in there logs ,So I am confused why this error occurs. This occurs daily in 10% of the jobs and once you rerun after 2-3 hrs the same job finishes successfully without above mentioned issues. Please suggest how can I debug this issue.Database Restoring Status
Hi All, We are using Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) in our environment DB01 as a Primary and DB02 as a restoring mode Recently I have checked both DB mdf files and these mdf files are different from each other DB01 mdf file size is 121GB and DB02 is 116 GB my query is how to check restoring mode is on working state or not what is the steps to check the status of Database restoring is it restoring successfully or not. Kindly response regarding this subject. Thanks in Advance. Best Regards ZahidSQL Server 2016 crashes
Hey Guys, I am facing really weird issue with MS SQL 2016 crashes. Every so often there is a crash dump generated: SqlDumpExceptionHandler: Process 105 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. This information is very vague so you cannot really determine what is the cause of it. I was playing with the dump file a bit An I got into the module that is causing the crash: ntdll!NtWaitForSingleObject+0x14: 00007ffb`d5255ac4 c3 ret SQL is working the cluster with 4 nodes. Two nodes are in one site and two other nodes are in the second site. Network is working fine. There are no error logs inside the system event viewer or Failover Cluster operational logs. I have noticed that we are running on older version of ODBC drivers which is 17.3.1.1 and the newest one is 17.4.2.1. Do you think this might be related? Regards, Wojciech
Running SQL Agent Jobs with Always Encrypted Column
I am experimenting and exploring always encrypted feature in MSSQL server 2016 in local database. As part of the experiment, I tried to apply the feature in SQL Jobs. I did below steps, but I am getting error. I have written a stored procedure, inside that I am trying to insert an encrypted column of one table to an another encrypted column of another table. Created table(source) with the below script where the FirstName column is encrypted. CREATE TABLE [dbo].[Users]( [FirstName] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL GO Created another table(destination) with the below script where the FirstName column is encrypted. CREATE TABLE [dbo].[AppUsers]( [FirstName] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL ) ON [PRIMARY] GO Then I created a Stored Procedure like below where the first name is passed as a parameter to the SPROC, searched for that record in Users table and inserting the FirstName value into FirstName column of AppUsers table. CREATE PROCEDURE [dbo].[TestSproc] @name NVARCHAR (50) AS BEGIN SET NOCOUNT ON; DECLARE @testValue AS NVARCHAR (50) = (SELECT FirstName FROM Users WHERE FirstName = @name); INSERT INTO AppUsers VALUES (@testValue); SELECT * FROM AppUsers; END Now while executing the SPROC, gives expected result. Declare @firstName nvarchar(50) = 'JohnDoe' EXEC dbo.TestSproc @firstName But when it is scheduled as job as below script, produces below error. USE TestDatabase GO Declare @name nvarchar(50) = 'JohnDoe' EXEC dbo.TestSproc @name Error: Encryption scheme mismatch for columns/variables '@name'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'CPG142_LT1277') (or weaker). (Microsoft SQL Server, Error: 33299) Can someone help to arrive solution for the case?
SQL Server 2016 hangs on install
System Information report written at: 08/28/19 17:56:40 System Name: LLMS-SAL-WKS9 [System Summary] Item Value OS Name Microsoft Windows 10 Pro Version 10.0.18362 Build 18362 Other OS Description Not Available OS Manufacturer Microsoft Corporation System Name LLMS-SAL-WKS9 System Manufacturer LENOVO System Model 10BU0008US System Type x64-based PC System SKU LENOVO_PN_10BU0008US Processor AMD A4-6300B APU with Radeon(tm) HD Graphics, 3700 Mhz, 1 Core(s), 2 Logical Processor(s) BIOS Version/Date LENOVO FNKT21AUS, 8/26/2013 SMBIOS Version 2.7 Embedded Controller Version 255.255 BIOS Mode UEFI BaseBoard Manufacturer LENOVO BaseBoard Product Annapurna CRB BaseBoard Version 0B98401 PRO Platform Role Desktop Secure Boot State Off PCR7 Configuration Elevation Required to View Windows Directory C:\WINDOWS System Directory C:\WINDOWS\system32 Boot Device \Device\HarddiskVolume2 Locale United States Hardware Abstraction Layer Version = "10.0.18362.267" User Name REDACTED Time Zone Central Daylight Time Installed Physical Memory (RAM) 16.0 GB Total Physical Memory 15.2 GB Available Physical Memory 11.8 GB Total Virtual Memory 17.4 GB Available Virtual Memory 13.7 GB Page File Space 2.25 GB Page File C:\pagefile.sys Kernel DMA Protection Off Virtualization-based security Not enabled Device Encryption Support Elevation Required to View Hyper-V - VM Monitor Mode Extensions Yes Hyper-V - Second Level Address Translation Extensions Yes Hyper-V - Virtualization Enabled in Firmware Yes Hyper-V - Data Execution Protection Yes
