MS SQL

TSQL BEGIN DISTRIBUTED TRANSACTION

Specifies the start of a Transact-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC). Unlike a transaction on a local database, a distributed transaction involves altering data on multiple databases. Consequently, distributed transaction processing is more complicated, because the database must coordinate the committing or rolling back of the changes in a transaction as a self-contained unit. Sources: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-distributed-transaction-transact-sql?view=sql-server-ver15

Linked Server unable to begin a distributed transaction

Error: Fix: Make sure the “Enable Promotion of Distributed Transaction” is set to “False” on the linked server property. ‘remote proc transaction promotion‘ is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off... » read more

The partner transaction manager has disabled its support for remote/network transactions.

Error: OLE DB provider “SQLNCLI11” for linked server “xxxxxx” returned message “The partner transaction manager has disabled its support for remote/network transactions.”. Msg 7391, Level 16, State 2, Procedure dbo.xxxxxx, Line 1 [Batch Start Line 14] The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “xxxxxx” was unable to begin... » read more

Always On and Database Backup

Database backup will not happen on the primary server unless you specific in the “Backup Preferences” of Availability Group Properties. Note: Default is set to “Prefer Secondary”. When setting up database backup via maintenance plan, the job will still run and report success, however the backup files will not be created because the fn_hadr_backup_is_preferred_replica(‘MyDB’) flag... » read more

Maintenance Plans for SQL Server

SSMS -> Management -> Maintenance Plans Maintenance Plan Tasks Back Up Database Check Database Integrity Execute SQL Server Agent Job Execute T-SQL Statement History Cleanup Maintenance Cleanup Notify Operator Rebuild Index Reorganize Index Shrink Database Update Statistics Sources: https://docs.mendix.com/developerportal/deploy/mendix-sql-maintenance-plans

Update Time Zone DateTime Value

Change SQL Server Authentication Mode

Change authentication mode with SSMS In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties. On the Security page, under Server authentication, select the new server authentication mode, and then click OK. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server. In Object Explorer, right-click your server, and then... » read more

SSRS: Enable Remote Errors

Enable remote errors through SQL Server Management Studio (Native Mode) Start Management Studio and connect to a report server instance. Right-click the report server node, and select Properties. Click Advanced to open the properties page. In the Security section, in EnableRemoteErrors, select True. Click OK. Sources: https://docs.microsoft.com/en-us/sql/reporting-services/report-server/enable-remote-errors-reporting-services?view=sql-server-ver15

Reapply User Login After Database Restore

Run the following on the source server and run the output on the destination server. Note: Run after database has been restored.

Create Linked Server Error, Cannot generate SSPI context

Error: Possible Fixes: In the Security section of the linked server, switch to “Be made using this security context” and provide a login SQL account.