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 →

How to Calculate the size of sql results in KB?

Question: I’d like to understand if there is a way in SQL Server to estimate the size of a sql result set in kilobytes (KB)? Is there some sort of statistic returned which associates a size statistic to a result set? Answer: The first thing is to consider is that size is not a property of data. This means we need to use various calls to methods which supply the data. SQL Server Client statistics returns data which will supply you with various details. One of the details it supplies is “Bytes received from server”. To access Client Statistics ,... Read more →

SQL Server – SSMS query tuning – simulate the application

I execute queries in SQL Server management studio for query tuning– showing a return time different to other client applications. It’s difficult to know whether it’s the database engine or SSMS which might be causing a problem. Where is the delay , is it the database engine or is t SSMS? As a DBA it is difficult to isolate just the query. A simple technique to use is to return the resultset into local variables. This does a fairly decent job of discounting the SSMS application cost of iterating and displaying the recordset. This way you can focus just on... Read more →