Using Trigger to Log Row Insert and Delete
Log inserts and deletes of a table to a log table.
SQL Server Email Alert with Table
Note: Need to enable email and setup mail profile first. Example 1 Example 2 uspConvertTableToHtml Stored Procedure Code that calls uspConvertTableToHtml Stored Procedure
SSIS Permissions
SQL Server Integration Services provides certain fixed database-level roles to help secure access to packages that are stored in SQL Server. The available roles are different depending on whether you’re saving packages in the SSIS Catalog database (SSISDB) or in the msdb database. Roles in the SSIS Catalog database (SSISDB) The SSIS Catalog database (SSISDB)... » read more
Display Line Number in SQL Server Management Studio (SSMS)
SQL Server – Displaying line numbers in Query Editor – SSMS Step1: Go to Tools > Options. Step2: In the Options dialog box navigate to Text Editor > Transact-SQL > General. Step 3: Check “Line Numbers” and click on “OK” Now, when a query window is opened Line Numbers will be displayed.
The requested OLE DB provider SQLNCLI10.1 is not registered.
Error: Getting the following error when trying to create linked servers (from an old script) on SQL Server 2019. The requested OLE DB provider SQLNCLI10.1 is not registered. Fix: In the linked server script, replace @provider=N’SQLNCLI10‘ with @provider=N’SQLNCLI‘
THE LOCAL NODE IS NOT PART OF QUORUM
Error: While trying to setup a couple of SQL Enterprise servers in an Availability Group I kept getting the message “The local node is not part of quorum and is therefore unable to process this operation” Fix: I had done things in the wrong order. I had ticked “Enable AlwaysoOn Availability Groups” in SQL Server Configuration Manager... » read more
Failed to obtain cluster information when trying to add Availability Replicas.
Error: When trying to add server to Availability Replicas, you get the following error… Failed to obtain cluster information. Either the specified instance of SQL Server is not running on a Windows Server Failover Cluster (WSFC)node, or the user lacks sysadmin permissions on the SQL Server instance to obtain the cluster information. Fix: 1) check... » read more
Cannot Open Backup Device Operating System Error 5 (Access is Denied)
Error: Error when trying to create a database backup. Fix: Look for the service SQL Server (MSSQLServer) and see the username under ‘Log On As’. Add this user to have full control of permissions on the directory of back up by navigating to the folder / properties/ under secirity tab / Edit and give the... » read more
Setup New Windows Clustering on AlwaysOn Database Servers
Make sure to install “Windows Failover Cluster” feature on all nodes in the cluster. Open up “Failover Cluster Manager”. Right click on Failover Cluster Manager and select “Create Cluster”. Select list of servers to be in the cluster. Run Cluster Validation. Provide a “Cluster Name”. Provide a “Networks/IP Address”. Setup a listener is optional but... » read more
Install the Failover Clustering feature
Install the Failover Clustering feature Start Server Manager. On the Manage menu, select Add Roles and Features. On the Before you begin page, select Next. On the Select installation type page, select Role-based or feature-based installation, and then select Next. On the Select destination server page, select the server where you want to install the feature, and then select Next. On the Select server roles page, select Next. On the Select features page,... » read more