MS SQL 2016 Management Studio Silent Install

As part of SQL Server 2016 installation SQL Server Management Studio (SSMS) is no longer available by default. It is necessary to manage SSMS as part of a separate installation. This SQL Server 2016 release of SSMS does not require a SQL Server license to install and use. To install a SQL Server 2016 SSMS silent install follow these instructions Step 1- Obtain a copy of the SQL Server 2016 SSMS binaries via Download SQL Server Management Studio (SSMS) and place in a location either on the server or a network path. Step 2 – Ensure the security context of... Read more →

Read SQL Server Error Logs with Powershell and SMO

SQL Server troubleshooting and SQL Server problem analysis usually results in a DBA reading the SQL Server Error Logs and other error logs , such as Windows Events Logs. The extent of analysing error logs depends on the problem. I’ve written in previous posts about the necessity to regularly review the SQL Server Error Logs. There is tons of detail or clues about other potential problems , which may not cause an outage today – but if left untreated could cause an outage tomorrow. How to use xp_readerrorlog parameters to search SQL Error Logs ... In a previous post ,... Read more →

Microsoft ACE OLEDB 12.0 provider is not registered on the local machine

Question : A user contacted me with an error message on a newly installed SQL Server Management Studio. The error message appeared while using the Import Data Wizard on Excel, connecting a SQL Server and extracting the data. Microsoft ACE OLEDB 12.0 provider is not registered on the local machine (System.Data) How can I fix this issue? Answer: This is not strictly a SQL Server Database Engine problem. The error is related to the installation of SQL Server Management Studio. The prerequisite for Microsoft Access Database Engine 2010 is missing. Microsoft Office or the free Microsoft Access Database Engine redistributable... Read more →

SSMS Query Options – Discard results after execution

During SQL Server performance tuning consultancies one of the goals is to recreate the problem in the actual environment. In many cases this is not always possible. You may be sent a query that is experiencing difficulties, but you may not be allowed to use the client application . In these cases I may need to use SQL Server Management Studio (SSMS) One of the benefits in using SSMS is the various Query Options available. An option I use frequently when sql performance tuning is the Discard results after query executes. If the query returns a large recordset resources are... Read more →

Top 4 Best SQL Editors

Based on a recent poll I took the best\most used SQL Editor goes to : SQL Server Management Studio – Query Explorer This is not a surprise as it ships with SQL Server and is the default client . Quite often developers will remain with the tools they first started on. It’s good to maintain an open-mind, there may be more productive methods available. One factor which will influence sql editor tool choice is the range of platforms. For example, if a developer or DBA is writing sql code to support multiplatforms such as SQL Server and DB2 , they... Read more →